I'm fetching data from database as a DataTable and need to convert into CSV string in VB.NET.
Add a comment
|
2 Answers
Create a generic method with DataTable, CSV Headers, DataTable Columns parameters:
Private Function CSVBuilder(dt As DataTable, headers As List(Of String), columns As List(Of String)) As String
Dim sCSV = New StringBuilder(String.Join(",", headers))
sCSV.Append(Environment.NewLine)
Dim view As New DataView(dt)
Dim tDt As DataTable = view.ToTable(True, columns.ToArray)
For Each row As DataRow In tDt.Rows
'-- Handle comma
sCSV.Append(String.Join(",", (From rw In row.ItemArray Select If(rw.ToString.Trim.Contains(","), String.Format("""{0}""", rw.ToString.Trim), rw.ToString.Trim))))
sCSV.Append(Environment.NewLine)
Next
Return sCSV.ToString
End Function
And then call in your code to get CSV string:
CSVBuilder(dataTable,
New List(Of String) From {"Header Column 1", "Header Column 2", ...},
New List(Of String) From {"DataTableColumn1", "DataTableColumn2", ...})
2 Comments
Joel Coehoorn
Seems like an odd API surface. I'd think you would write the method to either assume the datatable already has the correct columns, or ask for the DataView directly instead. Additionally, I'd infer the headers from the
.Columns collection in the datatable. Then you only need one parameter to the method: CSVBuilder(dt). Finally, I might also prefer to return a stream, or ask for a stream to write into.
Sajan Chhipa
@JoelCoehoorn, thank you for the response. Yes, I can pass datatable columns in the
.Columns collection but for CSV file headers I think, still need to pass headers list to CSVBuilder method. Do you have any better idea to manage headers as well in the datatable and pass that datatable only to the CSVBuilder method?In response to the comment, since this wouldn't fit in that space:
Private Function CSVBuilder(dt As DataTable) As String
Dim sCSV As New StringBuilder()
'Headers
Dim delimeter As String = ""
For Each col As String In dt.Columns.Select(Func(col) col.ColumnName)
If col.Contains(",") Then col = """" & col & """"
sCSV.Append(delimeter).Append(col)
delimeter = ","
Next
sCSV.AppendLine()
For Each row As DataRow In tDt.Rows
sCSV.AppendLine(String.Join(",", (From rw In row.ItemArray Select If(rw.ToString.Trim.Contains(","), String.Format("""{0}""", rw.ToString.Trim), rw.ToString.Trim))))
Next
Return sCSV.ToString
End Function
Now, I did remove this code:
Dim view As New DataView(dt)
Dim tDt As DataTable = view.ToTable(True, columns.ToArray)
But I wouldn't do this as part of the CSVBuilder() method. If you want to project a specific view of a table, I would do that separately from creating the CSV data. You could make a separate method for it:
Public Function GetProjection(dt As DataTable, columns As IEnumerable(Of String)) As DataTable
Dim view As New DataView(dt)
Return view.ToTable(True, columns.ToArray())
End Function
And then you call them together like this:
Dim dt As DataTable = '.... original table here
Dim columns() As String = '... the columns you want
Dim csv As String = CSVBuilder(GetProjection(dt, columns))
or like this:
Dim dt As DataTable = '.... original table here
Dim columns() As String = '... the columns you want
Dim dt1 = GetProjection(dt, columns)
Dim csv As String = CSVBuilder(dt1)
This is called Currying, and it's a good thing to do.
Finally, I'll repeat my suggestion to think in terms of writing to a stream. Long strings with repeated append operations can cause real problems for the .Net garbage collector. Using StringBuilder can help, but won't fully eliminate these problems. Writing to a Stream, which is often connected to a file on disk, gives you the opportunity to completely eliminate this issue. Plus, it will likely save you work later on.
1 Comment
Sajan Chhipa
I will do more experiment on this. So I'm working on CSV sending in the email attachment. If you have any good example of code that I can refer and send CSV file in the email using Stream that would help a lot.