0

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).

Community
  • 1
  • 1
benj
  • 113
  • 3
  • 11

2 Answers2

2

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.

jmcilhinney
  • 50,448
  • 5
  • 26
  • 46
  • This has done just what i was after, thank you. The quotation solution however is producing an error "Replace is not a member of system.array". My data should not contain quotations but it may be of use to other people reading this solution. – benj Oct 27 '14 at 08:06
  • I've updated the last code snippet to take into account that `r` is an array of `String` rather than just a `String`. That said, you might want to include the `Replace` call in the expression that populates `rows` to avoid having to do it later at all. – jmcilhinney Oct 27 '14 at 08:42
0

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!

SSS
  • 4,807
  • 1
  • 23
  • 44
  • Thank you for your reply. I could certainly learn more about CSV in general so this will be of use. – benj Oct 27 '14 at 08:09