Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Export DatagridView or DataSet as CSV File

Posted on 2009-03-30
22
Medium Priority
?
1,450 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 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
 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

721 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