Export DataGrid to .csv (vb.net)

[Homerboy]

I have the following code which will export my datagrid to .csv. The problem that I have though is that I need the values to be wrapped in double quotes (") so that any values with commas won't be thrown off when opening in Excel etc.

Can somebody help me with this? I feel as if the solution is easy, but I just can't see it this morning...

Thanks in advance.

 Try
            If Not Directory.Exists("C:\My Files\" & Format(Now(), "yyyy") & "\") Then
                Directory.CreateDirectory("C:\My Files\" & Format(Now(), "yyyy") & "\")
            End If
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

        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("C:\My Files\" & Format(Now(), "yyyy") & "\" & Format(Now(), "yyyy-MM-dd") & " file.csv")
            sw.WriteLine(String.Join(",", headers))
            For Each r In rows
                sw.WriteLine(String.Join(",", r))
            Next
        End Using

 

[LevelSea]

You could use a different delimiter, such as tab. Excel should handle that fine.

 

[Homerboy]

You could use a different delimiter, such as tab. Excel should handle that fine.

I guess I misspoke. It has to be a comma delimited file in the end. (eventually processed by 3rd party)

 

[Homerboy]

I think I've got it...

 Select Array.ConvertAll(row.Cells.Cast(Of DataGridViewCell).ToArray, Function(c) If(c.Value IsNot Nothing, """" + c.Value.ToString + """", ""))

 

[Jaydip]

Try this

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    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("Jay.txt")
        sw.WriteLine(String.Join(",", headers))
        For Each r In rows
            sw.WriteLine(String.Join(",", r))
        Next
    End Using
    Process.Start("Jay.txt")

 

[KB]

I would think it would be here:

If(c.Value IsNot Nothing, """" & c.Value.ToString & """", ""))

Edit:
Looks like I was too slow.

 

[Homerboy]

I would think it would be here:

If(c.Value IsNot Nothing, """" & c.Value.ToString & """", ""))

Edit:
Looks like I was too slow.

🙂

I just needed some more coffee before my eyes saw it.

 

[Homerboy]

Oops! Almost forgot the headers too!

Select """" + header.HeaderText + """").ToArray