JMO9966
asked on
Reading a .CSV file with data rows having varying column counts
I've been using this Class module for reading .csv files into my VB.Net DataTables, but I just found it will not work when the data files has rows with a varying number of columns.
The .csv file's first row has 4 columns, the second row has 5 columns, the third fow has 6 columns.
I get an error when the Sub AddRow runs since it attempts to add 5 values and first row only had 4 ??
"Cannot find column 4."
I'd like to hear your thoughts on how you would read this type of data file into a DataTable.
Thanks,
JMO9966
tblQuote_Data = fileReader.ReadFile(pathFi le)
Public Function ReadFile(ByVal fileName As String) As DataTable
' Initialize the return values
Dim list As New List(Of String())
Dim table As DataTable = Nothing
Using parser As New TextFieldParser(fileName)
' Setup the comma-delimited file parser.
parser.TextFieldType = FieldType.Delimited
parser.Delimiters = New String() {","}
parser.HasFieldsEnclosedIn Quotes = True
While Not parser.EndOfData
Try
' Read the comma-delimited text as fields into a string array.
Dim input As String() = parser.ReadFields()
If table Is Nothing Then
table = Me.CreateTable(Path.GetFil eName(file Name), input)
End If
If input(0).Trim <> "" Then
Me.AddRow(table, input)
Else
'Else send to Error table
End If
Catch ex As MalformedLineException
' Ignore invalid lines.
End Try
End While
End Using
Return table
End Function
Private Function CreateTable(ByVal name As String, ByVal input As String()) As DataTable
Dim table As New DataTable(name)
For index As Integer = 1 To input.Length
table.Columns.Add("F" & index)
Next index
Return table
End Function
Private Sub AddRow(ByVal table As DataTable, ByVal input As String())
Dim row As DataRow = table.NewRow()
For index As Integer = 0 To table.Columns.Count
If index < input.Length Then
row(index) = input(index)
End If
Next index
table.Rows.Add(row)
End Sub
The .csv file's first row has 4 columns, the second row has 5 columns, the third fow has 6 columns.
I get an error when the Sub AddRow runs since it attempts to add 5 values and first row only had 4 ??
"Cannot find column 4."
I'd like to hear your thoughts on how you would read this type of data file into a DataTable.
Thanks,
JMO9966
tblQuote_Data = fileReader.ReadFile(pathFi
Public Function ReadFile(ByVal fileName As String) As DataTable
' Initialize the return values
Dim list As New List(Of String())
Dim table As DataTable = Nothing
Using parser As New TextFieldParser(fileName)
' Setup the comma-delimited file parser.
parser.TextFieldType = FieldType.Delimited
parser.Delimiters = New String() {","}
parser.HasFieldsEnclosedIn
While Not parser.EndOfData
Try
' Read the comma-delimited text as fields into a string array.
Dim input As String() = parser.ReadFields()
If table Is Nothing Then
table = Me.CreateTable(Path.GetFil
End If
If input(0).Trim <> "" Then
Me.AddRow(table, input)
Else
'Else send to Error table
End If
Catch ex As MalformedLineException
' Ignore invalid lines.
End Try
End While
End Using
Return table
End Function
Private Function CreateTable(ByVal name As String, ByVal input As String()) As DataTable
Dim table As New DataTable(name)
For index As Integer = 1 To input.Length
table.Columns.Add("F" & index)
Next index
Return table
End Function
Private Sub AddRow(ByVal table As DataTable, ByVal input As String())
Dim row As DataRow = table.NewRow()
For index As Integer = 0 To table.Columns.Count
If index < input.Length Then
row(index) = input(index)
End If
Next index
table.Rows.Add(row)
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ended up setting my For Next loop to a hard-coded number of columns since this is a static value.
The csv file does not contain a column header row.