Thursday, April 05, 2007

CSV2Table Function

This is a simple code which used to read from a CSV file and import to become DataTable.


Public Shared Function CSV2DataTable(ByVal fname As String) As Data.DataTable
Dim f As New System.IO.StreamReader(fname)
Dim strLabel As String = f.ReadLine

Dim dt As New DataTable("CSVTable")
Dim reSplit As New System.Text.RegularExpressions.Regex("\s*[,]?\s*(?(""[^\""]*""|[^,\""]*))", System.Text.RegularExpressions.RegexOptions.IgnoreCase Or System.Text.RegularExpressions.RegexOptions.Singleline)
Dim mLabel As System.Text.RegularExpressions.Match = reSplit.Match(strLabel)
While mLabel.Success
dt.Columns.Add(mLabel.Result("${value}").Trim(""""c), GetType(String))
mLabel = mLabel.NextMatch
End While

Dim strDataLine As String = Nothing
While Not f.EndOfStream
strDataLine = f.ReadLine
Dim nrow As Data.DataRow = dt.NewRow
Dim intCnt As Integer = 0
Dim mValue As System.Text.RegularExpressions.Match = reSplit.Match(strDataLine)
While mValue.Success
nrow.Item(intCnt) = mValue.Result("${value}").Trim(""""c)
intCnt += 1
mValue = mValue.NextMatch
End While
dt.Rows.Add(nrow)
End While

Return dt

End Function

Just a basic one hope it helps, if you got suggestion for improvement, just drop me an email.

regards,
choongseng

No comments: