Solved

Export DatagridView or DataSet as CSV File

Posted on 2009-03-30
22
1,413 Views
Last Modified: 2012-06-27
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..
0
Comment
Question by:H-SC
  • 9
  • 8
  • 4
  • +1
22 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24018759
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
 
LVL 1

Author Comment

by:H-SC
ID: 24018793
TheLearnedOne,

How would that look, code wise?
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 24018891
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24018894
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24018924
actually thinking about it, I think the .VisisbleRowCount/ColumnCount is the wrong property to use
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24019283
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
 
LVL 1

Author Comment

by:H-SC
ID: 24019568
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
 
LVL 1

Author Comment

by:H-SC
ID: 24019577
rockiroads,

Looking at your suggestion now..
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 24019595
That's exactly how it's supposed to work.  Are you expecting something else?

0
 
LVL 1

Author Comment

by:H-SC
ID: 24019668
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
 
LVL 1

Author Comment

by:H-SC
ID: 24019736
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24019787
Its what I said, I used DataTable as an example. Whats the datasource for your datagrid?
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 24019791
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
 
LVL 27

Expert Comment

by:VBRocks
ID: 24019822
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
 
LVL 1

Author Comment

by:H-SC
ID: 24019837
rockiroads,
it is
DataTable1BindingSource, that has DataMember value of DataTable1 and a DataSource of DataSet1
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24019849
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
 
LVL 1

Author Comment

by:H-SC
ID: 24020119
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24020235
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24020270
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
 
LVL 1

Author Comment

by:H-SC
ID: 24021648
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24022795
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
 
LVL 1

Author Comment

by:H-SC
ID: 24022965
rockiroads,
that worked perfect!

Many thanks for your patience
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now