Solved

Export DatagridView or DataSet as CSV File

Posted on 2009-03-30
22
1,441 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

623 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