trying to write to a csvfile from a data set

itms

Active member
Joined
Feb 21, 2018
Messages
31
Programming Experience
10+
Hi,
I am trying to do something that I thought would be really simple, but I spent a day trying to find this and this is the best I could find and it does not work.

I have retived data from SQL server and populated a data grid, not all I want to do is send it out to a CSV file. I found this code below on the net, but when I run it it comes back with an error:
"System.NullReferenceException: 'Object reference not set to an instance of an object.'" at this line:
VB.NET:
csv += cell.Value.ToString().Replace(",", ";") & ","c
I am not sure what it means, except for at the end there are no Nulls in the data.
Can someone please tell me how to get this to work, or if you have a better solution please le me know.

Thank you

VB.NET:
 'Build the CSV file data as a Comma separated string.
        Dim csv As String = String.Empty
        'Add the Header row for CSV file.
        For Each column As DataGridViewColumn In Me.dtgDataGridViewer.Columns
            csv += column.HeaderText & ","c
        Next
        'Add new line.
        csv += vbCr & vbLf
        'Adding the Rows
        For Each row As DataGridViewRow In dtgDataGridViewer.Rows
            For Each cell As DataGridViewCell In row.Cells
                'Add the Data rows.
                csv += cell.Value.ToString().Replace(",", ";") & ","c
            Next
            'Add new line.
            csv += vbCr & vbLf
        Next
        'Exporting to Excel
        Dim folderPath As String = "C:\Temp\"
        File.WriteAllText(folderPath & "DataGridViewExport.csv", csv)
    End Sub
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
1. Export from the data source, not the grid. If you have bound a DataTable to that grid, export from that DataTable.
2. Don't replace commas with semicolons. If the data might contain a field delimiter (in this case, a comma), a row delimiter (in this case, a line break) or a double-quote then the proper course of action is to wrap the values in double-quotes and escape any double-quotes in the data with another double-quote. You can either do that for all values, all string values or just those values that need it. The easiest option is to just do it for all values. Excel will parse that data automatically, as will a TextFieldParser if you need to read the data in code.

If you do that and you still end up with a NullRfeerenceException then it's because you're doing something wrong, because a DataTable should never contain Nothing. I suspect that what is happening is that the code you're using is processing the blank data entry row at the bottom of the grid as well. The Value of each cell in that row is Nothing and you can't call ToString on no object. If you exclude that row then every cell should have something in it, so there can be no null reference. Even "blank" cells should contain DBNull.Value, which is how ADO.NET represents a database NULL.

Here's the code that I would recommend:
Private Function DataTableToCsv(table As DataTable, Optional includeColumnHeaders As Boolean = True) As String
    'Use a StringBuilder rather than a String for efficiency.
    Dim outputBuilder As New StringBuilder

    Dim columns = table.Columns
    Dim columnCount = columns.Count

    If includeColumnHeaders Then

        For i = 0 To columnCount - 1
            'Add a field delimiter before all but the first column header.
            If i > 1 Then
                outputBuilder.Append(",")
            End If

            outputBuilder.Append(columns(i).ColumnName)
        Next
    End If

    Dim rows = table.Rows

    For i = 0 To rows.Count - 1
        'Add a row delimiter before all but the first row.
        If i > 0 OrElse includeColumnHeaders Then
            outputBuilder.AppendLine()
        End If

        Dim row = rows(i)

        For j = 0 To columnCount - 1
            'Add a field delimiter before all but the first field value.
            If i > 1 Then
                outputBuilder.Append(",")
            End If

            'Add leading and trailing double-quotes and escape each double-quote in the data with another double-quote.
            outputBuilder.Append("""" & row(j).ToString().Replace("""", """""") & """")
        Next
    Next

    Return outputBuilder.ToString()
End Function

In your case, you can call that method something like this:
File.WriteAllText(DataTableToCsv(DirectCast(dtgDataGridViewer.DataSource, DataTable)), "C:\Temp\DataGridViewExport.csv")
 

itms

Active member
Joined
Feb 21, 2018
Messages
31
Programming Experience
10+
1. Export from the data source, not the grid. If you have bound a DataTable to that grid, export from that DataTable.
2. Don't replace commas with semicolons. If the data might contain a field delimiter (in this case, a comma), a row delimiter (in this case, a line break) or a double-quote then the proper course of action is to wrap the values in double-quotes and escape any double-quotes in the data with another double-quote. You can either do that for all values, all string values or just those values that need it. The easiest option is to just do it for all values. Excel will parse that data automatically, as will a TextFieldParser if you need to read the data in code.

If you do that and you still end up with a NullRfeerenceException then it's because you're doing something wrong, because a DataTable should never contain Nothing. I suspect that what is happening is that the code you're using is processing the blank data entry row at the bottom of the grid as well. The Value of each cell in that row is Nothing and you can't call ToString on no object. If you exclude that row then every cell should have something in it, so there can be no null reference. Even "blank" cells should contain DBNull.Value, which is how ADO.NET represents a database NULL.

Here's the code that I would recommend:
Private Function DataTableToCsv(table As DataTable, Optional includeColumnHeaders As Boolean = True) As String
    'Use a StringBuilder rather than a String for efficiency.
    Dim outputBuilder As New StringBuilder

    Dim columns = table.Columns
    Dim columnCount = columns.Count

    If includeColumnHeaders Then

        For i = 0 To columnCount - 1
            'Add a field delimiter before all but the first column header.
            If i > 1 Then
                outputBuilder.Append(",")
            End If

            outputBuilder.Append(columns(i).ColumnName)
        Next
    End If

    Dim rows = table.Rows

    For i = 0 To rows.Count - 1
        'Add a row delimiter before all but the first row.
        If i > 0 OrElse includeColumnHeaders Then
            outputBuilder.AppendLine()
        End If

        Dim row = rows(i)

        For j = 0 To columnCount - 1
            'Add a field delimiter before all but the first field value.
            If i > 1 Then
                outputBuilder.Append(",")
            End If

            'Add leading and trailing double-quotes and escape each double-quote in the data with another double-quote.
            outputBuilder.Append("""" & row(j).ToString().Replace("""", """""") & """")
        Next
    Next

    Return outputBuilder.ToString()
End Function

In your case, you can call that method something like this:
File.WriteAllText(DataTableToCsv(DirectCast(dtgDataGridViewer.DataSource, DataTable)), "C:\Temp\DataGridViewExport.csv")


When I tried your code I get the following error

Error: Unable to cast object of type 'System.Data.DataView' to type 'System.Data.DataTable'.

Any ideas of what I need to do here?

This is my current code that calls your function:


VB.NET:
 Dim dt As New DataUtils
        Dim cn2 As String = dt.SQLbConnect
        Dim commandString As String = "SELECT * FROM NewStar"
        Try
            Dim myDataAdapter As _
            New SqlDataAdapter(commandString, cn2)
            Dim myDataSet As New DataSet()
            myDataAdapter.Fill(myDataSet, "dtgDataGridViewer")
            File.WriteAllText(DataTableToCsv(DirectCast(dtgDataGridViewer.DataSource, DataTable)), "C:\Temp\DataGridViewExport.csv")

        Catch ex As Exception
            'If an error occured alert the user
            lblErrorMsgs.Visible = True
            txtErrorMessgs.Visible = True
            txtErrorMessgs.Text = "Error: " + ex.Message
        End Try
    End Sub

Thank you
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
If the DataSource of the grid is a DataView rather than a DataTable then obviously you can't cast it as type DataTable. Cast it as type DataView and get the DataTable from that to pass into that method.

That said, there's almost certainly no point binding a DataView in the first place. The only reason to do so is if you want to bind the same data to multiple controls but you want to be able to sort and filter then independently. If you're only binding once then just bind the DataTable. When you do so, the data actually comes from the table's DefaultView property anyway, which is a DataView.
 
Top Bottom