Convert CSV String to DataTable
Parses a CSV-formatted string and converts it into a DataTable.
This function uses TextFieldParser for robust parsing, which handles quoted fields and delimiters properly.
VB
Public Function ConvertCsvString2DataTable(CsvString As String,
Optional RecordSeparator As String = vbCrLf,
Optional FieldSeparator As String = ";") As Data.DataTable
Dim dtTable As New Data.DataTable
Dim bFirstRecord As Boolean = True
Try
Using strReader As New IO.StringReader(CsvString)
Using textparser As New TextFieldParser(strReader)
textparser.Delimiters = {FieldSeparator}
While Not textparser.EndOfData
Dim curRow = textparser.ReadFields()
' Set column headers from the first record
If bFirstRecord Then
For i As Integer = LBound(curRow) To UBound(curRow)
dtTable.Columns.Add(curRow(i))
Next
bFirstRecord = False
Else
' Add each subsequent row
Dim drRow As Data.DataRow = dtTable.NewRow()
For j = LBound(curRow) To UBound(curRow)
drRow(j) = curRow(j)
Next
dtTable.Rows.Add(drRow)
End If
End While
End Using
End Using
Catch ex As Exception
Dim szFunctionName As String = New Diagnostics.StackTrace().GetFrame(0).GetMethod().Name
Throw New ArgumentException(szModuleName + "." + szFunctionName + vbTab + ex.Message)
End Try
'Try to convert Boolean and date
ConvertBooleanColumns(dtTable) '"True"/"False" or "0"/"1"
ConvertDateColumns(dtTable) '"yyyy-MM-dd" or "yyyyMMdd"
Return dtTable
End Function