Reading .CSV File with StreamReader

[billboy]

I am using the following code to read a .txt file into a dataset sucessfully
I am trying to alter the code to read a .csv file into a but get an error on the

oRows(intCounter) = strFields "Cannot find column 30 "

Dim strDelimiter As String = (",")
        Dim strFilePath As String = (path & "test.csv")
        Dim oDS As New DataSet()
        Dim strFields As String
        Dim oTable As New DataTable()
        Dim oRows As DataRow
        Dim intCounter As Int32 = 0
       
        oDS.Tables.Add("Property")

        Dim oSR As New StreamReader(strFilePath)
        'Go to the top of the file
        oSR.BaseStream.Seek(0, SeekOrigin.Begin)
        'Add in the Header Columns
        For Each strFields In oSR.ReadLine().Split(strDelimiter)
            oDS.Tables(0).Columns.Add(strFields)
        Next
        'Now add in the Rows
        oTable = oDS.Tables(0)
        While (oSR.Peek() > -1)
            oRows = oTable.NewRow()
            For Each strFields In oSR.ReadLine().Split(strDelimiter)
                oRows(intCounter) = strFields
                intCounter = intCounter + 1
            Next
            intCounter = 0
            oTable.Rows.Add(oRows)
        End While

I read the .txt file by changing the strDelimiter to (vbTab)

[amrita]

Try this code.

 Dim strConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";Extended Properties=Text;"
        Dim conn As New OleDbConnection(strConnString)

        Try
            conn.Open()

            Dim cmd As New OleDbCommand("SELECT * FROM [" & strFileName & "]", conn)
            Dim da As New OleDbDataAdapter()
            da.SelectCommand = cmd

            Dim ds As New DataSet()
            da.Fill(ds)
            da.Dispose()
            Return ds.Tables(0)
        Catch
            Return Nothing
        Finally
            conn.Close()
        End Try

[billboy]

Try this code.

 Dim strConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";Extended Properties=Text;"
        Dim conn As New OleDbConnection(strConnString)

        Try
            conn.Open()

            Dim cmd As New OleDbCommand("SELECT * FROM [" & strFileName & "]", conn)
            Dim da As New OleDbDataAdapter()
            da.SelectCommand = cmd

            Dim ds As New DataSet()
            da.Fill(ds)
            da.Dispose()
            Return ds.Tables(0)
        Catch
            Return Nothing
        Finally
            conn.Close()
        End Try

Thanks i know how to do it that way, i am trying to learn how to do it using a StreamReader

[jmcilhinney]

1. Open the file by creating a StreamReader.
2. Loop until there's no data left.
3. In the loop, read a line of text.
4. Split the line on the commas to produce an array.
5. Create a new DataRow.
6. Assign the array to the row's ItemArray property.

[billboy]

1. Open the file by creating a StreamReader.
2. Loop until there's no data left.
3. In the loop, read a line of text.
4. Split the line on the commas to produce an array.
5. Create a new DataRow.
6. Assign the array to the row's ItemArray property.

Thanks I am going to try and start from scratch, but can you see why the exisitng code doesnt work with .csv but does work with .txt?

If i understand correctly and perhaps i dont, the code should do the same thing excpet one splits on vbTab and the other on ","

The error is saying it cannot find column 30 well column 30 is the last column

again it works fine on a txt file, not understanding the difference at this point

[jmcilhinney]

When reporting an error, please report the ACTUAL error message, not just an approximation, and the line it occurs on.

I would suggest looking at the array returned by Split. The IDE will show you exactly how many elements it contains and what their values are. Presumably you are assuming that there's more elements than there are at some point.

[billboy]

The error is: IndexOutOfRangeException was unhandled

cannot find column 30

it occurs at the line

oRows(intCounter) = strFields

there are 30 columns in the csv file , if i delete one column then the message says cannot find column 29, so the problem is with the last column

[jmcilhinney]

Obviously there are more elements in the array than there are columns in the DataTable. You need to debug and examine your data as you go. As you read each line, actually have a look at the data you're reading. Have a look at the array returned by Split to see how many elements it has.

[billboy]

Ok found the problem is because one of the fields contains an adress that has a comma in it
1313 Mockingbird lane, Transylvania Ca 913333