CSV -> DataGridView

[Ryan1]

I am currently trying to load a CSV file into a data table. Here is the code I am using to do so:

Imports System.IO
 
Public Class Form1
 
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 
    End Sub
 
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim SR As StreamReader = New StreamReader("C:\Tester.csv")
        Dim i As Long = 0
        Dim line As String = SR.ReadLine()
        Dim strArray As String() = line.Split(",")
        Dim dt As DataTable = New DataTable()
        Dim row As DataRow
 
        For Each s As String In strArray
            dt.Columns.Add(New DataColumn())
        Next
        Do
            line = SR.ReadLine
            row = dt.NewRow()
            row.ItemArray = line.Split(",")
            dt.Rows.Add(row)
 
        Loop While Not line = String.Empty
 
        DataGridView1.DataSource = dt
 
 
    End Sub
End Class

I am getting a:

Object reference not set to an instance of an object

Regarding the:

row.ItemArray = line.Split(",")


I am new to VB. Trying to build a program that can go through and error check the values of massive CSV files. I will eventually be throwing in IF statements. Any chance I can get this running? Thanks

 

[JohnH]

It is the 'line' variable that is Nothing on that code line. ReadLine returns Nothing when the StreamReader reaches end of stream.

Please read the forum FAQ for how you can post code. I fixed your post this time.

 

[Ryan1]

Sorry about that, thank you very much.

 

[Ryan1]

For anyone working with a similar problem the answer is to limit how many rows you read. This can be helpful by using this code:

[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] lineCount = [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]File[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].ReadAllLines([/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]"C:\Users\Ryan\Documents\Work\NewWiasrd\newVb\MyProject\Tester.csv"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]).Length[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][/SIZE][/FONT][/SIZE][/FONT]

Than you can setup the loop that populates your datatable to stop once it reaches that number. Note: If you go over that number by even one line than you will receive an error.

 

[jmcilhinney]

For anyone working with a similar problem the answer is to limit how many rows you read. This can be helpful by using this code:

[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] lineCount = [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]File[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].ReadAllLines([/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]"C:\Users\Ryan\Documents\Work\NewWiasrd\newVb\MyProject\Tester.csv"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]).Length[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][/SIZE][/FONT][/SIZE][/FONT]

Than you can setup the loop that populates your datatable to stop once it reaches that number. Note: If you go over that number by even one line than you will receive an error.

No, that is a very bad solution. If you do that then you will read the entire file twice. The proper way is like this:

Using reader As New StreamReader(filePath)
    Do Until reader.EndOfStream
        Dim line = reader.ReadLine()

        'Use line here.
    Loop
End Using

If you're reading a CSV file then it would be better to use a TextFieldParser, which has an EndOfData property that works similarly.

 

[Ryan1]

Awesome, thank you.

 

[Ryan1]

I currently have the program running great, your advice was very helpful.
I am having a problem with reading the first row of the csv file into the datatable, it seems to completely skip it.

[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff] Dim SR As StreamReader = New StreamReader)(file)
        Dim line As String = SR.ReadLine() 
        Dim strArray As String() = line.Split(",")
        Dim dt As DataTable = New DataTable()
        Dim row As DataRow       
 
dt.Columns.Add(New DataColumn("Age", GetType(Long)))
'...
‘this goes on for new 250 data columns
'... 

Do Until SR.EndOfStream 'this loop populates the data grid
 
            line = SR.ReadLine
 
            row = dt.NewRow()
 
            row.ItemArray = line.Split(",")
 
            dt.Rows.Add(row)
 
 Loop

[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

I have a feeling it has something to do with trying to write into the fields I have added as data columns, however I receive no errors. Unsure of how to solve this. Thanks for the help.

 

[Herman]

I posted this before, but there is a simpler way to read a CSV in a datatable... This also has the benefit of letting you handle file format errors, like missing delimiters and such. Just handle the OleDbExceptions that pop out.

Function ReadCSV(ByVal CSVFilename As String) As DataTable
    ReadCSV = New DataTable("Results")
 
    Dim CSVFileInfo As New System.IO.FileInfo(CSVFilename)
 
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""" & CSVFileInfo.DirectoryName & """;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"
    Dim CommandString As String = "SELECT * FROM [" & CSVFileInfo.Name & "];"
 
    Using Conn As New System.Data.OleDb.OleDbConnection(ConnectionString)
        Using Cmd As New System.Data.OleDb.OleDbCommand(CommandString, Conn)
            Using Adapter As New System.Data.OleDb.OleDbDataAdapter(Cmd)
                Adapter.Fill(ReadCSV)
            End Using
        End Using
    End Using
End Function

 

[Ryan1]

Although your method may be more effective, I have already fully designed the rest of the program and am unfamiliar with that code block.
Do you know why this isn't reading the first data row? Thanks

 

[JohnH]

Although your method may be more effective, I have already fully designed the rest of the program and am unfamiliar with that code block.
Do you know why this isn't reading the first data row? Thanks

Probably HDR=Yes

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

 

[Ryan1]

Probably HDR=Yes

I am using streamreader to read the csv file

 

[Herman]

The reason why you should try to understand and apply that code block is that this is the way you will do pretty much ANY data access with ADO.Net. Access database, Excel workbook, SQL database? just change the connection string. Sure you can read it line by line, split, replace, format, etc... But that is like making your own bricks to build a house. Just because you have already written some code, you shouldn't dismiss alternatives, especially when the alternative solves your problem in much less code and shows you how to properly access data.

 

[JohnH]

I am using streamreader to read the csv file

Is it possible you call ReadLine, and discard that line, before looping through the rest of the file?

 

[IanRyder]

Hi,

I currently have the program running great, your advice was very helpful.
I am having a problem with reading the first row of the csv file into the datatable, it seems to completely skip it.

Is it possible you call ReadLine, and discard that line, before looping through the rest of the file?

JohnH had the right idea but I think missed your error. It is here, on the second line:-

Dim SR As StreamReader = New StreamReader)(file)
Dim line As String = SR.ReadLine()

To fix, get rid of the read statement when declaring your line variable since you then overwrite the first line of the file with the first iteration of your loop.

Hope that helps.

Cheers,

Ian

 

[JohnH]

Dim SR As StreamReader = New StreamReader)(file)
Dim line As String = SR.ReadLine()

Yes, that is the one I was referring to. Beginners need to learn to re-evaluate their own code.