Export CSV From a DataTable

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.

Delimiter

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.

Text Qualifiers

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.

Configurable Column Headers

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

Generate the CSV string

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

Source Code

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