Export DatagridView or DataSet as CSV File

In this case, the title really does say it all.

The easier of the two(DatagridView or DataSet) suggestions will work.
I have a form that has a DataGridView on it that gets populated via a DataSet.
On a button click, I would like to take that data and using the SaveFileDialog element and save the data in .csv format.

Thanks In Advance..
LVL 1
H-SCAsked:
Who is Participating?
 
rockiroadsCommented:
Maybe something like this
Iterate thru rows and cols of datagrid and write it to a file

        Dim oFile As System.IO.File
        Dim oRite As System.IO.StreamWriter
        Dim iRow As Integer
        Dim iCol As Integer

        oRite = oFile.CreateText("C:\myexport.csv")
        For iRow = 0 To DataGrid1.VisibleRowCount - 1
            For iCol = 0 To DataGrid1.VisibleColumnCount - 1
                'MsgBox(DataGrid1.Item(iRow, iCol))
                If iCol > 0 Then oRite.Write(",")
                oRite.Write(DataGrid1.Item(iRow, iCol))
            Next
            oRite.WriteLine()
        Next
        oRite.Close()

0
 
Bob LearnedCommented:
I would think that selecting all the cells, copying the text to the clipboard with comma delimiters, and then writing that text to a file would be easier than working with a DataSet.
0
 
H-SCAuthor Commented:
TheLearnedOne,

How would that look, code wise?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
VBRocksCommented:
Here's an example of how you can do this:

        'Create demo DataSet, add a Table and 3 columns
        Dim ds As New DataSet()
        With ds.Tables.Add("Table1")
            .Columns.Add("Col1")
            .Columns.Add("Col2")
            .Columns.Add("Col3")

            'Add demo data
            For i As Integer = 1 To 33 Step 3
                .Rows.Add("Item " & i.ToString(), _
                        "Item " & (i + 1).ToString(), _
                        "Item " & (i + 2).ToString())
            Next
        End With


        Dim path As String = "C:\temp\myCsvOutputFile.csv"
        Dim writer As New IO.StreamWriter(path, False, System.Text.Encoding.Default)

        'Loop through each row and write it out to a file
        For Each row As DataRow In ds.Tables("Table1").Rows
            writer.WriteLine(Join(row.ItemArray, ",")) : Next


        'Close file
        writer.Close()
        writer.Dispose()
        writer = Nothing

        MsgBox("done")

0
 
rockiroadsCommented:
actually thinking about it, I think the .VisisbleRowCount/ColumnCount is the wrong property to use
0
 
rockiroadsCommented:
ok, back from school run so can continue with this. Need to get the number of rows and columns from the datasource. Example I used to verify was a datatable that was the datasource of the datagrid.

        Dim dt As DataTable

        dt = DataGrid1.DataSource
        For iRow = 0 To dt.Rows.Count - 1
            For iCol = 0 To dt.Columns.Count - 1

But since you are after a dataset, this could be adapted to use that instead.
0
 
H-SCAuthor Commented:
VBRocks,

When I try that it seems to output a csv file but.. when I open the excel the data is listed as

item1      item2     item3
item4      item5     item6

and so on ....
0
 
H-SCAuthor Commented:
rockiroads,

Looking at your suggestion now..
0
 
VBRocksCommented:
That's exactly how it's supposed to work.  Are you expecting something else?

0
 
H-SCAuthor Commented:
VBRocks,

That is the actual data that it puts into the spreadsheet.
It reads

item1      item2     item3
item4      item5     item6

I like the format, and what it does but it doesn't show my actual data
0
 
H-SCAuthor Commented:
rockiroads,

For some reason, I keep getting an error of:

Unable to cast object of type 'System.Windows.Forms.BindingSource' to type 'System.Data.DataTable'.

any suggestions?
0
 
rockiroadsCommented:
Its what I said, I used DataTable as an example. Whats the datasource for your datagrid?
0
 
VBRocksCommented:
Well, that's just an example.  That data is the demo data.

Just get a reference to the table that is the DataSource of your grid:

        'you can use this if the DataGridView's DataSource is the DataTable
        'Dim table As DataTable = Me.DataGridView1.DataSource

        'If the DataGridView's DataSource is a BindingSource, then cast it, then get it's datasource
        Dim table As DataTable = DirectCast(Me.DataGridView1.DataSource, BindingSource).DataSource

        Dim path As String = "C:\temp\myCsvOutputFile.csv"
        Dim writer As New IO.StreamWriter(path, False, System.Text.Encoding.Default)

        'Loop through each row and write it out to a file
        For Each row As DataRow In table .Rows
            writer.WriteLine(Join(row.ItemArray, ",")) : Next


        'Close file
        writer.Close()
        writer.Dispose()
        writer = Nothing

        MsgBox("done")

0
 
VBRocksCommented:
You can also just iterate through the BindingSource itself:

        Dim bs As New BindingSource
        bs = DirectCast(Me.DataGridView1.DataSource, BindingSource)

        Dim path As String = "C:\temp\myCsvOutputFile.csv"
        Dim writer As New IO.StreamWriter(path, False, System.Text.Encoding.Default)

        'Loop through each row and write it out to a file
        For Each rv As DataRowView In bs
            writer.WriteLine(Join(rv.Row.ItemArray, ",")) : Next


        'Close file
        writer.Close()
        writer.Dispose()
        writer = Nothing

        MsgBox("done")
0
 
H-SCAuthor Commented:
rockiroads,
it is
DataTable1BindingSource, that has DataMember value of DataTable1 and a DataSource of DataSet1
0
 
rockiroadsCommented:
If its dataset then I guess it would be something like this. I think we can do .tables(0) to get to the table


Dim ds As DataSet

        ds = DataGrid1.DataSource
        For iRow = 0 To ds.tables(0).rows.count-1
            For iCol = 0 To ds.tables(0).Columns.Count - 1

0
 
H-SCAuthor Commented:
rockiroads,

here is what I have.  I do not get any errors, but do not get any data either....any ideas
thanks for working with me on this

Dim oRite As System.IO.StreamWriter
        Dim iRow As Integer
        Dim iCol As Integer
        Dim ds As DataSet
        ds = DataSet1
        oRite = IO.File.CreateText("C:\myexport.csv")
        For iRow = 0 To ds.Tables("datetable1").Rows.Count - 1
            For iCol = 0 To ds.Tables(0).Columns.Count - 1
                If iCol > 0 Then oRite.Write(",")
                oRite.Write(ds.Tables(iRow, iCol))
            Next
            oRite.WriteLine()
        Next
        oRite.Close()
0
 
rockiroadsCommented:
did u step thru it to see if it is going into the loop? and also to confirm the number of rows and cols it finds

some inconsistency as well, u have specified tablename in row but not in column
0
 
rockiroadsCommented:
Also with a dataset, dont u get to the value like this? Im on a laptop without access to using datasets so cant verify dataset code

ds.Tables(0).Rows(iRow).Item(iCol)


i.e.

For iRow = 0 To ds.Tables("datetable1").Rows.Count - 1
            For iCol = 0 To ds.Tables("datetable1").Columns.Count - 1
                If iCol > 0 Then oRite.Write(",")
                oRite.Write(ds.Tables("datetable1").row(irow).item(icol)
            Next
            oRite.WriteLine()
        Next
0
 
H-SCAuthor Commented:
rockiroads,

This looks perfect.
I had to make a change to the source sql to enclose some field data that has comma's within it in quotes, to allow for comma seperation to be accurate in the end file.
I guess my next question is, instead of the static file location...how can I incorporate that SaveFileDialog element into this?
0
 
rockiroadsCommented:
How about this

        Dim saveFileDialog1 As New System.Windows.Forms.SaveFileDialog()
        Dim sFile As String

        sFile = ""
        saveFileDialog1.Filter = "CSV (*.csv)|*.csv"
        If saveFileDialog1.ShowDialog() = DialogResult.OK Then
            sFile = saveFileDialog1.FileName
        End If


Use sFile as your filename
0
 
H-SCAuthor Commented:
rockiroads,
that worked perfect!

Many thanks for your patience
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.