I am trying to read a CSV file into a datagrid (the csv file has 4 columns and up to 3000 records) and i found the following piece of code by JOE_MS which does exactly what i want:
Using parser As New Microsoft.VisualBasic.FileIO.TextFieldParser("c:\temp\myFile.csv")
parser.SetDelimiters(",")
While Not parser.EndOfData
Me.DataGridView1.Rows.Add(parser.ReadFields())
End While
End Using
However i cannot find the textfieldparser in my system. Do i need to add a reference or does this only work in VB.NET 2005 as i am using VB.NET 2003 If i can't get it in VB.NET 2003 how do i do it
Help please!!
CSV file records
Smacker
-EDIT NOTE-
For some reason the forum won't let me edit the post, but I want to point out that a minor error was found in the following code of the LoadDelimitedData routine:
For Each flds As List(Of String) In ar
If flds.Count > 0 Then
If flds.Count > dt.Columns.Count Then
For i As Integer = 0 To flds.Count - dt.Columns.Count
dt.Columns.Add()
Next
End If
Dim c As Integer = 0
Dim dr As DataRow = dt.NewRow
For Each s As String In flds
dr.Item(c) = s
c += 1
Next
dt.Rows.Add(dr)
End If
Next
This code should actually read:
For Each flds As List(Of String) In ar
If flds.Count > 0 Then
If flds.Count > dt.Columns.Count Then
For i As Integer = 0 To flds.Count - dt.Columns.Count - 1
dt.Columns.Add()
Next
End If
Dim c As Integer = 0
Dim dr As DataRow = dt.NewRow
For Each s As String In flds
dr.Item(c) = s
c += 1
Next
dt.Rows.Add(dr)
End If
Next
Thanks to Keith Blackwell for pointing this out!!
Tryin2Bgood
Yes Microsoft.VisualBasic.FileIO.TextFieldParser is not available in .NET Framework 1.1.
I guess you will have to use an System.IO.StreamReader, read each line as a single string and split it using the String.Split function.
You have to be carefull with columns having a comma inside them! In this case columns are enclosed in double quotes.
eg.
value 1,value 2,"this is, value 3",value 4
spinal
I read through the article but still uncertain which one should I use. I could have drop your txtConnection and txtDataAdapter or use your long version code.
Please let me know.
Niro
Sandro Haupt
Heres a function I wrote quite some time ago (took a while to find it!) that builds a DataTable on the fly based on a delimited text file. It handles the delimiting character exisiting inside of a quoted value and handles records with varied field counts:
Public Function DelimitedTextToTable(ByVal FileName As String, ByVal Delimiter As Char, Optional ByVal FirstRowColumnNames As Boolean = False, Optional ByVal ColumnNames() As String = Nothing) As DataTable Dim dt As New DataTable(System.IO.Path.GetFileNameWithoutExtension(FileName)) Dim SafeChar As Char = Chr(31) Dim fs As System.IO.StreamReader Tryfs = System.IO.File.OpenText(FileName)
Catch ex As Exception Throw New Exception("File not found, or error opening file. (" & FileName & ")", ex) End Try Dim str As String = fs.ReadToEndfs.Close()
Dim Recs() As String = str.Split(ControlChars.CrLf) If Recs.Length < 1 Then Throw New Exception("File opened successfuly, but no records were found.") End If Dim enm As IEnumerator = Recs.GetEnumerator If FirstRowColumnNames Thenenm.MoveNext()
Dim s As String = CType(enm.Current, String).Trim Dim fix() As Char = s.ToCharArray Dim InQuotes As Boolean = False For i As Integer = 0 To fix.Length - 1 Select Case fix(i) Case Delimiter If Not InQuotes Thenfix(i) = SafeChar
End If Case ControlChars.QuoteInQuotes =
Not InQuotes End Select Next Dim sb As New System.Text.StringBuildersb.Append(fix)
Dim c() As String = sb.ToString.Split(SafeChar) Dim Cols(c.Length) As Data.DataColumn For i As Integer = 0 To c.Length - 1cols(i) =
New DataColumnCols(i).ColumnName = c(i)
Cols(i).Caption = c(i)
Nextdt.Columns.AddRange(Cols)
ElseIf Not IsNothing(ColumnNames) Then Dim cols(ColumnNames.Length) As Data.DataColumn For i As Integer = 0 To ColumnNames.Length - 1cols(i) =
New DataColumnCols(i).ColumnName = ColumnNames(i)
Cols(i).Caption = ColumnNames(i)
Nextdt.Columns.AddRange(Cols)
End If While enm.MoveNext Dim s As String = CType(enm.Current, String).Trim Dim fix() As Char = s.ToCharArray Dim InQuotes As Boolean = False For i As Integer = 0 To fix.Length - 1 Select Case fix(i) Case Delimiter If Not InQuotes Thenfix(i) = SafeChar
End If Case ControlChars.QuoteInQuotes =
Not InQuotes End Select Next Dim sb As New System.Text.StringBuildersb.Append(fix)
Dim r() As String = sb.ToString.Split(SafeChar) If r.Length > dt.Columns.Count Then For i As Integer = dt.Columns.Count To r.Length - 1 Dim col As New DataColumncol.ColumnName = "Auto_Column_" & i.ToString
col.Caption = "Auto_Column_" & i.ToString
dt.Columns.Add(col)
Next End If For i As Integer = 0 To r.Length - 1 Dim trims(0) As Charr(i) = r(i).Trim(
New Char() {ControlChars.Quote}) Nextdt.Rows.Add(r)
End While Return dt End FunctionExample usage might look like:
Dim dt As New DataTabledt =
Me.DelimitedTextToTable("c:\test2.csv", ",", True)DataGrid1.DataSource = dt
HTH!
Tom2U
*EDIT NOTE*
This post was modified on 9/6/06 to correct a typo in the explanation and add two missing lines of code.
There's still more to add, but here's a VS05 version of the TextDataProvider. I didn't bother with the actual data provider implementations but this class can load a delimited text file into a DataSet or List(Of List(Of String)) and save changes back to a text file.
This code can be dropped into a component file; just add a new component to the project and then paste this code over the pregenerated class code. I'll post a link to the finished project and assembly when its complete (still want to add support for fixed width files, collection other than List(of List(of)), and DataColumn.DataType parsing).
Public Class TextDataProvider
Private myPath As String = String.Empty
Private myDel As String = ","
Private myFirstHeader As Boolean = False
Private myLastLoad As New TimeSpan(0)
Private myLastSave As New TimeSpan(0)
Private myDataSet As New DataSet
Public ReadOnly Property DataSet() As DataSet
Get
If Me.DesignMode Then
Return Nothing
Else
Return Me.myDataSet
End If
End Get
End Property
Public Property Delimiter() As String
Get
Return Me.myDel
End Get
Set(ByVal value As String)
If value = String.Empty Then
Me.myDel = ","
Else
Me.myDel = value
End If
End Set
End Property
Public ReadOnly Property LastLoadTime() As TimeSpan
Get
Return Me.myLastLoad
End Get
End Property
Public ReadOnly Property LastSaveTime() As TimeSpan
Get
Return Me.myLastSave
End Get
End Property
Public Property FirstRowColumnHeaders() As Boolean
Get
Return Me.myFirstHeader
End Get
Set(ByVal value As Boolean)
Me.myFirstHeader = value
End Set
End Property
Public Property Path() As String
Get
Return Me.myPath
End Get
Set(ByVal value As String)
Me.myPath = value
End Set
End Property
Public Function GetData() As List(Of List(Of String))
If myPath = String.Empty Then
Throw New Exception("Path not set")
Exit Function
End If
If Not System.IO.File.Exists(Me.myPath) Then
Throw New System.IO.FileNotFoundException(myPath)
Exit Function
End If
Dim fp As Microsoft.VisualBasic.FileIO.TextFieldParser = My.Computer.FileSystem.OpenTextFieldParser(Me.myPath)
fp.TextFieldType = FileIO.FieldType.Delimited
fp.TrimWhiteSpace = True
fp.Delimiters = New String() {Me.myDel}
Dim ar As New List(Of List(Of String))
While Not fp.EndOfData
ar.Add(New List(Of String)(fp.ReadFields))
End While
fp.Close()
Return ar
End Function
Public Sub WriteData(ByVal data As List(Of List(Of String)))
If myPath = String.Empty Then
Throw New Exception("Path not set")
Exit Sub
End If
Dim sb As New System.Text.StringBuilder
For Each l As List(Of String) In data
Dim isnew As Boolean = True
For Each s As String In l
If Not isnew Then
sb.Append(Me.myDel)
Else
isnew = False
End If
sb.Append(s)
Next
sb.Append(ControlChars.NewLine)
Next
System.IO.File.WriteAllText(Me.myPath, sb.ToString)
End Sub
Public Function LoadDelimitedData() As DataSet
If myPath = String.Empty Then
Throw New Exception("Path not set")
Exit Function
End If
Dim start As Date = Now
If Not System.IO.File.Exists(Me.myPath) Then
Throw New System.IO.FileNotFoundException(myPath)
Exit Function
End If
Dim TableName As String = System.IO.Path.GetFileNameWithoutExtension(Me.myPath)
Dim fp As Microsoft.VisualBasic.FileIO.TextFieldParser = My.Computer.FileSystem.OpenTextFieldParser(Me.myPath)
fp.TextFieldType = FileIO.FieldType.Delimited
fp.TrimWhiteSpace = True
fp.Delimiters = New String() {Me.myDel}
Dim ar As New List(Of List(Of String))
While Not fp.EndOfData
ar.Add(New List(Of String)(fp.ReadFields))
End While
fp.Close()
If ar.Count > 0 Then
Dim dt As DataTable
If Not Me.myDataSet.Tables.Contains(TableName) Then
dt = New DataTable
dt.TableName = TableName
Me.myDataSet.Tables.Add(dt)
Else
dt = Me.myDataSet.Tables(TableName)
End If
If myFirstHeader Then
For i As Integer = 0 To ar(0).Count - 1
If Not dt.Columns.Contains(ar(0)(i)) Then
dt.Columns.Add(ar(0)(i))
End If
Next
ar.RemoveAt(0)
End If
For Each flds As List(Of String) In ar
If flds.Count > 0 Then
If flds.Count > dt.Columns.Count Then
For i As Integer = 0 To flds.Count - dt.Columns.Count
dt.Columns.Add()
Next
End If
Dim c As Integer = 0
Dim dr As DataRow = dt.NewRow
For Each s As String In flds
dr.Item(c) = s
c += 1
Next
dt.Rows.Add(dr)
End If
Next
End If
Me.myLastLoad = Now.Subtract(start)
Return Me.myDataSet
End Function
Public Function LoadDelimitedData(ByVal ds As DataSet) As DataSet
Me.myDataSet = ds
Return Me.LoadDelimitedData
End Function
Public Function LoadDelimitedData(ByVal dt As DataTable) As DataSet
Dim strsave As String = Me.myPath
Me.myPath = System.IO.Path.GetDirectoryName(Me.myPath) & System.IO.Path.DirectorySeparatorChar & dt.TableName & System.IO.Path.GetExtension(Me.myPath)
If Me.myDataSet.Tables.Contains(dt.TableName) Then
dt = Me.DataSet.Tables(dt.TableName)
Else
Me.DataSet.Tables.Add(dt)
End If
Me.myPath = strsave
Return Me.LoadDelimitedData
End Function
Public Sub SaveDelimitedData()
If myPath = String.Empty Then
Throw New Exception("Path not set")
Exit Sub
End If
Dim strsav As String = Me.myPath
For Each dt As DataTable In Me.myDataSet.Tables
Me.myPath = System.IO.Path.GetDirectoryName(Me.myPath) & System.IO.Path.DirectorySeparatorChar & dt.TableName & System.IO.Path.GetExtension(Me.myPath)
Me.SaveDelimitedData(dt)
Next
Me.myPath = strsav
End Sub
Public Sub SaveDelimitedData(ByVal dt As DataTable)
If myPath = String.Empty Then
Throw New Exception("Path not set")
Exit Sub
End If
Dim start As Date = Now
Dim sb As New System.Text.StringBuilder
If dt.Rows.Count > 0 Then
If Me.myFirstHeader Then
Dim isnew As Boolean = True
For Each col As DataColumn In dt.Columns
If Not isnew Then
sb.Append(Me.myDel)
Else
isnew = False
End If
sb.Append(col.ColumnName)
Next
sb.Append(ControlChars.NewLine)
End If
For Each dr As DataRow In dt.Rows
Dim isnew As Boolean = True
For Each col As DataColumn In dt.Columns
If Not isnew Then
sb.Append(Me.myDel)
Else
isnew = False
End If
sb.Append(dr.Item(col))
Next
sb.Append(ControlChars.NewLine)
Next
System.IO.File.WriteAllText(Me.myPath, sb.ToString)
End If
Me.myLastSave = Now.Subtract(start)
End Sub
End Class
Tinu
It depends on your version of Visual Studio.
If you are running Visual Studio 2003, use the txtConnection and txtDataAdapter from the download. If you are running Visual Studio 2005 copy and paste the TextDataProvider class. You can use a plain class file instead of a component if you want - just modify the DataSet property by removing the If statement that checks for DesignMode.
Senthil A
Chris Baldwin - MSFT
Here's a link to a TextDataProvider control. This dll contains a DataConnection and DataAdapter for delimited text files (using the routine above). You can use Add Items on the Toolbox to add these two components to VS. Drop a txtConnection and a txtDataAdapter onto your form. Set the connection string on the txtConnection to: Provider=delimited;data source=c:\test2.csv;access mode=readwrite - where "source" is the path to your file. Now you can use the Fill() method on the txtDataAdapter to fill a DataSet and the Update() method to write any changes to the DataTable back to a delimited text file.
Here's the link to the compiled assembly.
Here's a link to the project.
I wrote these a while ago... They work but are not completely error trapped. You'll have to ensure that the connection string is formatted properly; provider should always be "delimited" and access mode can be "read", "write", or "readwrite".