Simple function for converting a .NET DataTable to CSV format.

Need to convert a datatable directly to CSV string, write the datatable in a CSV file, or export it in a Excel compatible format?

This method can be used for creating a .csv string or file that can be opened with Excel.
Excel can import CSV, Excel parses automatically CSV data when the CSV file have a ".xls" extension.


''' Exporting a datatable to an Excel file
Public Sub WriteDataTableToCsvFile(ByVal dtTowrite As DataTable, ByVal strFileName As String)
Dim sw As New System.IO.StreamWriter(strFileName, False, System.Text.Encoding.Unicode)
' see function below
dim strContent as string = ConvertDataTableToCSV(dtTowrite, ControlChars.Tab)
sw.Write(strContent)
sw.Close()
End Sub

'''  Converting a datatable to as comma separated value string format, compatible for Excel.
''' This method dynamically create columns headers.
''' The method ensures thant the separator value will be removed from
''' the datatable fields if it is already there, for consistency purpose.
Public Function ConvertDataTableToCSV(ByVal dtTowrite As DataTable, _
ByVal strColSeparator As String) As String
' Line breaks are reserved for separating rows
If (strColSeparator = Environment.NewLine) Then
Throw New Exception("A new line cannot be used for a csv columns separator.")

Dim sb As New System.Text.StringBuilder
Dim intNbCols As Integer = dtTowrite.Columns.Count

'Write headers
For i As Integer = 0 To intNbCols - 1
sb.Append(dtTowrite.Columns(i).ColumnName.Replace(strColSeparator, " ") _
+ strColSeparator)
Next
sb.Append(Environment.NewLine)

'write data
For Each line As DataRow In dtTowrite.Rows
For i As Integer = 0 To intNbCols - 1
sb.Append(line(i).ToString().Replace(strColSeparator, " ") + _
strColSeparator)
Next
sb.Append(Environment.NewLine)
Next

Return sb.ToString()
End Function

That's all. It can be tested with the following code:



Dim dt As New DataTable()
dt.Columns.Add(New DataColumn("date", GetType(System.DateTime)))
GetType(System.DateTime)))
dt.Columns.Add(New DataColumn("name", GetType(System.String)))
dt.Columns.Add(New DataColumn("id", GetType(System.Int32)))


Dim line As DataRow = dt.NewRow()
line("date") = Now()
line("name") = "Albert Einstein"
line("id") = 28847
line("test") = "sdfiom " + ControlChars.Tab + " pok po"
dt.Rows.Add(line)

line = dt.NewRow()
line("date") = Now().AddYears(99)
line("name") = "Charles Darwin"
line("id") = 28847
line("test") = "sdfsdfsdfsdfsdf"
dt.Rows.Add(line)

line = dt.NewRow()
line("date") = Now().AddDays(2)
line("name") = "Sigmund Freud"
line("id") = 23423423
' ligne("test") = EMPTY ROW
dt.Rows.Add(line)

Dim strTest As String = ConvertDataTableToCSV(dt, ControlChars.Tab)
Dim sw As New System.IO.StreamWriter("C:\Test.xls")
sw.Write(strTest)
sw.Close()

No comments: