Export CSV from Dataset

Before XML is widely used for data communications between the systems/applications, we have used CSV. Even now for when the new systems are being developed which needs to be communicated with these legacy applications, we have no choice if we cant choose anything other than those systems are using. I am planning to develop a class which can be useful for exporting CSV.

CSV is a character separated values format so we have to choose a character as a delimiter. As delimiters are chosen based on various factors we need our class to be delimiters configurable.

As there will be a risk if the delimiter is already present in the data, the text qualifiers are used to identify the text element’s boundary. Within this boundary if a delimiter character is present then the class will not consider it as a delimiter.

Some of the interfaces will not like headers, so make a switch to turn off the column header generations.

For generating the file with text quantifiers, surround the item with quantifier character both the side. Once done add a delimiter character next to that. If this is done for all rows then the string is ready to be written into the file system.

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports System.IO
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Csv As New Exporter()
Using CsvWriter As New StreamWriter("C:\TestFile.csv")
CsvWriter.Write(Csv.CsvFromDatatable(GetSampleData()))
End Using
System.Diagnostics.Process.Start("C:\TestFile.csv")
MessageBox.Show("done")
End Sub
Private Function GetSampleData() As DataTable
Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Persist Security Info=True;Integrated Security = True")
Dim CategoryAdapter As New SqlDataAdapter("SELECT CategoryID,CategoryName,Description FROM Categories", con)
Dim ProductData As New DataSet()
CategoryAdapter.Fill(ProductData, "Categories")

        Return ProductData.Tables(0)
End Function

End Class

Public Class Exporter
Public Sub New()
TextDelimiter = ","c
TextQualifiers = """"c
HasColumnHeaders = True
End Sub
Private _TextDelimiter As Char
Public Property TextDelimiter() As Char
Get
Return _TextDelimiter
End Get
Set(ByVal value As Char)
_TextDelimiter = value
End Set
End Property
Private _TextQualifiers As Char
Public Property TextQualifiers() As Char
Get
Return _TextQualifiers
End Get
Set(ByVal value As Char)
_TextQualifiers = value
End Set
End Property
Private _HasColumnHeaders As Boolean
Public Property HasColumnHeaders() As Boolean
Get
Return _HasColumnHeaders
End Get
Set(ByVal value As Boolean)
_HasColumnHeaders = value
End Set
End Property
Public Function CsvFromDatatable(ByVal InputTable As DataTable) As String
Dim CsvBuilder As New StringBuilder()
If HasColumnHeaders Then
CreateHeader(InputTable, CsvBuilder)
End If
CreateRows(InputTable, CsvBuilder)
Return CsvBuilder.ToString()
End Function
Private Sub CreateRows(ByVal InputTable As DataTable, ByVal CsvBuilder As StringBuilder)
For Each ExportRow As DataRow In InputTable.Rows
For Each ExportColumn As DataColumn In InputTable.Columns
Dim ColumnText As String = ExportRow(ExportColumn.ColumnName).ToString()
ColumnText = ColumnText.Replace(TextQualifiers.ToString(), TextQualifiers.ToString() + TextQualifiers.ToString())
CsvBuilder.Append(TextQualifiers + ColumnText + TextQualifiers)
CsvBuilder.Append(TextDelimiter)
Next
CsvBuilder.AppendLine()
Next
End Sub
Private Sub CreateHeader(ByVal InputTable As DataTable, ByVal CsvBuilder As StringBuilder)
For Each ExportColumn As DataColumn In InputTable.Columns
Dim ColumnText As String = ExportColumn.ColumnName.ToString()
ColumnText = ColumnText.Replace(TextQualifiers.ToString(), TextQualifiers.ToString() + TextQualifiers.ToString())
CsvBuilder.Append(TextQualifiers + ExportColumn.ColumnName + TextQualifiers)
CsvBuilder.Append(TextDelimiter)
Next
CsvBuilder.AppendLine()
End Sub
End Class

View the original article here