Solved

Export DatagridView or DataSet as CSV File

Posted on 2009-03-30
22
1,403 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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