CODEWITHSUNDEEP
vb.netdatagridviewexport-to-csv
benj
benj

Reputation: 133

Write DataGridView to csv handling commas in values

I have some code from here: DataGridView to CSV File Which is looping DataGridView data (source from a web service) to write to a csv. However, i haven't been able to figure out how to handle variables that contain commas. I know they should be commented but am not sure how to work this into the existing loop.

The write must also be able to handle null and boolean fields. Which is why i have struggled to adapt code from elsewhere. Assistance appreciated as always. Existing code as follows.

    Dim headers = (From header As DataGridViewColumn In DataGridView1.Columns.Cast(Of DataGridViewColumn)() _
          Select header.HeaderText).ToArray
    Dim rows = From row As DataGridViewRow In DataGridView1.Rows.Cast(Of DataGridViewRow)() _
               Where Not row.IsNewRow _
               Select Array.ConvertAll(row.Cells.Cast(Of DataGridViewCell).ToArray, Function(c) If(c.Value IsNot Nothing, c.Value.ToString, """"))
    Using sw As New IO.StreamWriter("csv.txt")
        sw.WriteLine(String.Join(",", headers))
        For Each r In rows
            sw.WriteLine(String.Join(",", r))
        Next
    End Using
    Process.Start("csv.txt")

I can't change the delimiter as this will affect subsequent automated use of the file. (output must be csv).

Upvotes: 0

Views: 4228

Answers (2)

jmcilhinney
jmcilhinney

Reputation: 54477

You simply wrap at least those values that contain commas in double quotes. Any proper routine for reading CSV files should read any value wrapped in double quotes as a single value, whether it contains commas or not. An example of correct reading of a CSV file that way is the VB TextFieldParser class. With this in mind, change this:

sw.WriteLine(String.Join(",", r))

to this:

sw.WriteLine("""" & String.Join(""",""", r) & """")

or, if the escaped quotes seem confusing, this:

sw.WriteLine(ControlChars.Quote & String.Join(ControlChars.Quote & "," & ControlChars.Quote, r) & ControlChars.Quote)

I should also mention that, just like in VB literals, double quotes need to be escaped within quoted strings in a CSV file. With this in mind, if there's any chance that a value of yours might contain a double quote then you should do this:

sw.WriteLine("""" & String.Join(""",""", r.Select(Function(s) s.Replace("""", """"""))) & """")

By the way, to see all this in action, all you have to do is create a blank CSV file in Windows Explorer, open it in Excel, enter some data that contains commas and/or double-quotes, save it and then open it in Notepad. You'll see that any value that contains a comma or double-quote is wrapped in double quotes and any double-quotes in values are escaped with another double-quote. In your case, you can be selective like that but it's easier just to wrap everything in double quotes. Doing so will have no ill-effect other than to increase the size of the file.

Upvotes: 2

SSS
SSS

Reputation: 5413

This is not a simple problem. A good start is this link, which is about as close to a specification for CSV as you will find: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm

You'll also be better off using a DataTable to set up your data, then binding with DataGridView.DataSource = MyDataTable.

There IS built-in support for exporting a DataTable to XML - DataTable.WriteXml which might do what you need.

Good luck!

Upvotes: 0

Related Questions