Exporting a datagridview to a csv file

  • (2 Pages)
  • +
  • 1
  • 2

25 Replies - 8997 Views - Last Post: 25 January 2011 - 12:39 PM Rate Topic: -----

#1 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Exporting a datagridview to a csv file

Posted 24 January 2011 - 08:25 AM

I have the following code:
Public Class Payrollfinal
    Private Sub Payrollfinal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub
    Private Sub payrollsubmitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles payrollsubmitButton.Click
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand
        Dim _CMD As SqlCommand = New SqlCommand
        Dim adapter As System.Data.SqlClient.SqlDataAdapter
        Dim ds As New DataSet
        Try
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
                .Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
                .CommandText = "sp_allsum"
                oDr = .ExecuteReader()
                oCmd.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try

        Try
            adapter.Fill(ds)

            If (ds Is Nothing) Then
                'it's empty
                MsgBox("There was no data for this time period, press Ok to continue", "No Data")
                oCmd.Connection.Close()
                Exceptions.Hide()
            Else
                'it exists and there are rows 
                adapter.Fill(ds)
                oCmd.Connection.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
    End Sub
    Private Sub exportfileButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exportfileButton.Click
        'Declaration of Variables
        Dim dt As DataTable
        Dim dr As DataRow
        Dim myString As String
        Dim bFirstRecord As Boolean = True
        Dim myWriter As New System.IO.StreamWriter("C:\payroll.csv")
        myString = ""
        Try
            For Each dt In MyDataSet.Tables
                For Each dr As DataRow In dt.Rows
                    bFirstRecord = True
                    For Each field As Object In dr.ItemArray
                        If Not bFirstRecord Then
                            myString.AppendText(",")
                        End If
                        myString.AppendText(field.ToString)
                        bFirstRecord = False
                    Next
                    'New Line to differentiate next row
                    myString.AppendText(Environment.NewLine)
                Next
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Write the String to the Csv File
        myWriter.WriteLine(myString)
        'Clean up
        myWriter.Close()
    End Sub
End Class



and when two things happen when I try to use this code (which I modified after finding it on another site)
1. Intellisense tells me that MyDataSet is not defined.
2. When I define MyDataSet as
Dim MyDataSet as New Dataset
Intellisense tells me that I'm told that AppendText is not part of String. Can anyone assist?

Thank you,

Doug

This post has been edited by dougancil2010: 24 January 2011 - 08:26 AM


Is This A Good Question/Topic? 1
  • +

Replies To: Exporting a datagridview to a csv file

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8957
  • View blog
  • Posts: 33,584
  • Joined: 12-June 08

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 09:17 AM

That is correct - append text tends to be for textbox controls. Use this command:

<string object> += "<new text>"


Quote

2. When I define MyDataSet as
Dim MyDataSet as New Dataset
Intellisense tells me that I'm told that AppendText is not part of String. Can anyone assist?

Where are you trying to use "appendtext" with "mydataset"?
Was This Post Helpful? 1
  • +
  • -

#3 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 09:35 AM

modi,

If you look here

 If Not bFirstRecord Then
                            myString.AppendText(",")
                        End If
                        myString.AppendText(field.ToString)
                        bFirstRecord = False
                    Next
                    'New Line to differentiate next row
                    myString.AppendText(Environment.NewLine)




thats where intellisense is telling me that .appendtext is not a member of String.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8957
  • View blog
  • Posts: 33,584
  • Joined: 12-June 08

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 09:42 AM

Okay so you were not using 'append text' with the dataset? I thought that's what you meant in that quote in my post above; hence the confusion.

... but yes, as my post above states intellisense is right; append text is not part of the string functionality. Use what I suggested above. My guess is the code you copied was referencing a textbox or something of that nature.
Was This Post Helpful? 2
  • +
  • -

#5 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1043
  • View blog
  • Posts: 4,057
  • Joined: 02-July 08

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 09:58 AM

I would also recommend a StringBuilder here as it will speed this up quite a bit. Imports System.Text to access it.
Was This Post Helpful? 2
  • +
  • -

#6 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 10:18 AM

Modi,

So where would what you suggested go?

Hawk,

What will it speed up, the reading of the data?
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8957
  • View blog
  • Posts: 33,584
  • Joined: 12-June 08

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 10:23 AM

Where you have the .appendtext use the "+=". Pretty simple.

Or use something like the string concat method.
http://msdn.microsof...=VS.100%29.aspx

... or as Hawk suggested - string builder. You have plenty of options.
Was This Post Helpful? 1
  • +
  • -

#8 Shadar  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 46
  • View blog
  • Posts: 157
  • Joined: 26-May 09

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 11:23 AM

I don't know if this will help or not but I wrote a class a while back to dump everything in a datagrid to an ASCII file. (See code below) It is pretty self explanatory and lets you choose what character to use as a separator as well as enclosing each field in quotation marks.


Imports System.IO
Public Class clsDGVToASCII
    Public Function DGVTOASCII(ByVal inDGV As DataGridView, ByVal inPath As String, ByVal inFilename As String, ByVal chSep As Char) As Boolean
        Dim strASCIIToWrite As String = ""
        Dim FailedToWrite As Boolean = False
        For Each dgvr As DataGridViewRow In inDGV.Rows
            For Each dgvc As DataGridViewCell In dgvr.Cells
                If Not dgvc.Value Is Nothing Then
                    strASCIIToWrite &= Chr(34) & dgvc.Value.ToString & Chr(34) & chSep
                End If
            Next
            strASCIIToWrite = Strings.Left(strASCIIToWrite, strASCIIToWrite.Length - 1)
            strASCIIToWrite &= vbCrLf
        Next
        If FailedToWrite = WriteToFile(strASCIIToWrite, inPath, inFilename) = False Then
            Return False
        End If
        Return True
    End Function

    Public Function WriteToFile(ByVal inTextToWrite As String, ByVal inpath As String, ByVal inFilename As String) As Boolean
        Dim FileToWrite As String
        If Strings.Right(inpath.Trim, 1) <> "\" Then
            FileToWrite = inpath & "\" & inFilename
        Else
            FileToWrite = inpath & inFilename
        End If

        If File.Exists(FileToWrite) Then
            Dim retval As DialogResult
            retval = MsgBox(FileToWrite & " already exists. Do you want to overwrite?", MsgBoxStyle.YesNoCancel, "File Already Exists")
            If retval = DialogResult.No or retval = DialogResult.Cancel Then
                Return False
            ElseIf retval = DialogResult.Yes Then
                Using sw As StreamWriter = File.CreateText(FileToWrite)
                    sw.Write(inTextToWrite)
                    sw.Close()
                End Using
                Return True
            End If
        Else
            Using sw As StreamWriter = File.CreateText(FileToWrite)
                sw.Write(inTextToWrite)
                sw.Close()
            End Using
            Return True
        End If
        Return False
    End Function
End Class


This post has been edited by Shadar: 24 January 2011 - 01:24 PM

Was This Post Helpful? 3
  • +
  • -

#9 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 12:09 PM

Shadar,

How can I get a buttonclickevent to fire off your function?
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8957
  • View blog
  • Posts: 33,584
  • Joined: 12-June 08

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 12:18 PM

Copy is class to a new, empty, file in your project.
Compile it.

In your button click event create a variable that is the instance of that class.
Instantiate it.
Use the methods that came from it!

That or gut the methods out of the class and plunk them down in your code. Call as per usual.

Edit: Stray thought - I hope this app is not "mission critical" at any level there...

This post has been edited by modi123_1: 24 January 2011 - 12:20 PM

Was This Post Helpful? 1
  • +
  • -

#11 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1043
  • View blog
  • Posts: 4,057
  • Joined: 02-July 08

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 12:29 PM

Yes faster reading of data. Here's another option Dataset.WriteXML and Dataset.ReadXML! MSDN info.
Was This Post Helpful? 1
  • +
  • -

#12 Shadar  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 46
  • View blog
  • Posts: 157
  • Joined: 26-May 09

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 12:30 PM

Dim DGVToWrite As New clsDGVToASCII

    Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button.Click
        Dim blnWritten As Boolean
        blnWritten = DGVToWrite.DGVTOASCII(dgv1, "E:\Temp\", "Test.txt", ",")
    End Sub



Also of note, this was an early version (but it works). I have looked for the final version that had file reading and several other functions but haven't been able to find it. If you are going to use this for anything other than a class project, you will probably want to add errorhandling to the class.

This post has been edited by Shadar: 24 January 2011 - 12:31 PM

Was This Post Helpful? 1
  • +
  • -

#13 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 12:55 PM

Shadar,

What values are
clsDGVToASCII
and
dgv1
in your code?

Those aren't otherwise declared.
Was This Post Helpful? 0
  • +
  • -

#14 Shadar  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 46
  • View blog
  • Posts: 157
  • Joined: 26-May 09

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 01:11 PM

View Postdougancil2010, on 24 January 2011 - 01:55 PM, said:

Shadar,

What values are
clsDGVToASCII
and
dgv1
in your code?

Those aren't otherwise declared.

clsDGVToASCII = name of the class. (See second line of class above)
dgv1 = datagridview control name.

This post has been edited by Shadar: 24 January 2011 - 01:13 PM

Was This Post Helpful? 1
  • +
  • -

#15 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Exporting a datagridview to a csv file

Posted 24 January 2011 - 02:17 PM

Shadar,

Here's my code:

 Imports System.Data.SqlClient
Imports System.IO

Public Class Payrollfinal
    Private Sub Payrollfinal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub
    Private Sub payrollsubmitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles payrollsubmitButton.Click
        exportfileButton.Enabled = False
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand
        Dim _CMD As SqlCommand = New SqlCommand
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim ds As New DataSet
        Try
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
                .Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
                .CommandText = "sp_allsum, "
                oDr = .ExecuteReader()
                oCmd.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        Try
            adapter.Fill(ds)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
        exportfileButton.Enabled = True
    End Sub
    Public Function DGVTOASCII(ByVal inDGV As DataGridView, ByVal inPath As String, ByVal inFilename As String, ByVal chSep As Char) As Boolean
        Dim strASCIIToWrite As String = ""
        Dim FailedToWrite As Boolean = False
        For Each dgvr As DataGridViewRow In inDGV.Rows
            For Each dgvc As DataGridViewCell In dgvr.Cells
                If Not dgvc.Value Is Nothing Then
                    strASCIIToWrite &= Chr(34) & dgvc.Value.ToString & Chr(34) & chSep
                End If
            Next
            strASCIIToWrite = Strings.Left(strASCIIToWrite, strASCIIToWrite.Length - 1)
            strASCIIToWrite &= vbCrLf
        Next
        If FailedToWrite = WriteToFile(strASCIIToWrite, inPath, inFilename) = False Then
            Return False
        End If
        Return True
    End Function

    Public Function WriteToFile(ByVal inTextToWrite As String, ByVal inpath As String, ByVal inFilename As String) As Boolean
        Dim FileToWrite As String
        If Strings.Right(inpath.Trim, 1) <> "," Then
            FileToWrite = inpath & "," & inFilename
        Else
            FileToWrite = inpath & inFilename
        End If

        If File.Exists(FileToWrite) Then
            Dim retval As DialogResult
            retval = MsgBox(FileToWrite & " already exists. Do you want to overwrite?", MsgBoxStyle.YesNoCancel, "File Already Exists")
            If retval = DialogResult.No Then
                Return False
            ElseIf retval = DialogResult.Cancel Then
                Return False
            ElseIf retval = DialogResult.Yes Then
                Using sw As StreamWriter = File.CreateText(FileToWrite)
                    sw.Write(inTextToWrite)
                    sw.Close()
                End Using
                Return True
            End If
        Else
            Using sw As StreamWriter = File.CreateText(FileToWrite)
                sw.Write(inTextToWrite)
                sw.Close()
            End Using
            Return True
        End If
        Return False
    End Function

    Private Sub exportfileButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exportfileButton.Click
        Dim DGVToWrite As New clsDGVToASCII
        Dim blnWritten As Boolean
        blnWritten = DGVToWrite.DGVTOASCII(dgv1, "E:\Temp\", "Test.txt", ",")

    End Sub
End Class



and I'm not sure what to replace your clsDVCToASCII and dgv1 with.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2