CODEWITHSUNDEEP
sqlvb.netexcelcsvgridview
daniel aagentah
daniel aagentah

Reputation: 1702

SQL Table in CSV file shows NULL Rows as " " in Excel

Basically, I am displaying an SQL table in my VB.NET gridview, once its in my gridview, I then have a button that exports the results from visual studio into a CSV file, Everything in the CSV file looks fine, except any cell that is NULL in the SQL table now displays as: 

 

I tried to fix this in a stored procedure using the code bellow:

UPDATE EmailManagementTable
SET Excluded = ''
WHERE Excluded IS NULL

But even with this code I still get the same problem.

This is my Code for the Button that exports the gridview results to CSV:

    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment;filename=gvtocsv.csv")
    Response.Charset = ""
    Response.ContentType = "application/text"
    Dim sBuilder As StringBuilder = New System.Text.StringBuilder()
    For index As Integer = 0 To GridView1.Columns.Count - 1
        sBuilder.Append(GridView1.Columns(index).HeaderText + ","c)
    Next
    sBuilder.Append(vbCr & vbLf)
    For i As Integer = 0 To GridView1.Rows.Count - 1
        For k As Integer = 0 To GridView1.HeaderRow.Cells.Count - 1
            sBuilder.Append(GridView1.Rows(i).Cells(k).Text.Replace(",", "") + ",")
        Next
        sBuilder.Append(vbCr & vbLf)
    Next
    Response.Output.Write(sBuilder.ToString())
    Response.Flush()
    Response.[End]()

I was wondering if I could get some assistance with this?

Thankyou in advance

Upvotes: 0

Views: 2050

Answers (1)

daniel aagentah
daniel aagentah

Reputation: 1702

I solved my problem!

I edited some code inside my Export Button, Here's the code:

    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment;filename=gvtocsv.csv")
    Response.Charset = ""
    Response.ContentType = "application/text"
    Dim sBuilder As StringBuilder = New System.Text.StringBuilder()
    For index As Integer = 0 To GridView1.Columns.Count - 1
        sBuilder.Append(GridView1.Columns(index).HeaderText + ","c)
    Next
    sBuilder.Append(vbCr & vbLf)
    For i As Integer = 0 To GridView1.Rows.Count - 1
        For k As Integer = 0 To GridView1.HeaderRow.Cells.Count - 1
            sBuilder.Append(GridView1.Rows(i).Cells(k).Text.Replace(",", "").Replace(" ", "") + ",")
        Next
        sBuilder.Append(vbCr & vbLf)
    Next
    Response.Output.Write(sBuilder.ToString())
    Response.Flush()
    Response.[End]()

Upvotes: 1

Related Questions