Results 1 to 18 of 18

Thread: Export CSV Data From Datagridview?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    395

    Export CSV Data From Datagridview?

    I have a datagridview that is loading the first line of csv data from the file and putting it into column 1.. for each csv field a new row in the column.
    Next to that is column 2 that has a datagridview combobox for each entry with default values that the user can choose to "Map" both of these fields together.
    So I need now to have an export function, it needs to do a few things:

    1: Check that the user has mapped all combobox choices and that it is not a null selection if so show msgbox "Please Select Mapping.."
    2: Save the file with only the mapped choices for example if a field in the combobox was set to "Ignore Field" it will need to edit this field out of the file, for all lines..
    3: It will rename the previously named fields what the combobox defaults are..
    4: If the file did not have a csv header, it will now include it and if it did, it will do above and just rename and/or edit out the fields as necessary..

    normally I could check if a combobox is empty but I am not sure how in this example since it is in a datagridview and a bunch of them are loaded depending on how many csv fields there are..
    how do I check to see if all the datagridview comboboxes have been selected and not empty?
    Last edited by DreamWarrior77; Nov 11th, 2021 at 11:19 AM.

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Export CSV Data From Datagridview?

    normally I could check if a combobox is empty but I am not sure how in this example since it is in a datagridview and a bunch of them are loaded depending on how many csv fields there are..
    how do I check to see if all the datagridview comboboxes have been selected and not empty?
    Have "Ignore Field" as one of the combobox list items. When you setup the mapping dgv default all rows to "Ignore Field"

    Code:
    MapDataGridView.Rows.Add(currentField, "Ignore Field")
    If you set the dgv combobox Display Style property to DropDownButton then it can't be blank. Since you don't know what fields will be present, I don't anyway to verify they have mapped the fields correctly.


    You could set the Display Style property to Combobox and leave it empty when you setup the mapping dgv. Then,

    Code:
            Dim allMapped As Boolean = True
            For Each row As DataGridViewRow In MapDataGridView.Rows
                If row.Cells("Defaults").Value Is Nothing Then
                    MessageBox.Show("Invalid Data")
                    allMapped = False
                End If
            Next
    But this still doesn't verify that all the fields have been mapped, it just verifies they were not left empty.
    Last edited by wes4dbt; Nov 12th, 2021 at 02:29 PM.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    395

    Re: Export CSV Data From Datagridview?

    ok Thanks where should I place this?

    Code:
    DataGridView1.Rows.Add(currentField, "Ignore Field")
    in my import csv code.. it says it requires something unless I am not realizing where to place it..

    Code:
    Private Sub LoadMapCSVFileToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles LoadMapCSVFileToolStripMenuItem.Click
            OpenFileDialog1.Filter = "CSV Files (*.csv*)|*.csv"
            OpenFileDialog1.FileName = ""
            If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
    
                DataGridView1.Rows.Clear()
    
                Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(OpenFileDialog1.FileName)
    
                    MyReader.TextFieldType = FileIO.FieldType.Delimited
                    MyReader.SetDelimiters(",")
                    Dim currentRow As String()
                    Try
                        currentRow = MyReader.ReadFields()
                        Dim currentField As String
                        For Each currentField In currentRow
                            'MsgBox(currentField)
                            'add a row to the dgv
                            DataGridView1.Rows.Add(currentField)
                        Next
                    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                        MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
                    End Try
                End Using
            End If
        End Sub

  4. #4
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Export CSV Data From Datagridview?

    You don't have to place it anywhere. You just modify the line that add rows to DataGridView1.

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Export CSV Data From Datagridview?

    Got bored and started playing around with this problem. I decided it would be less complicated to just use Oledb to import the csv file and then use a DataAdapter to insert the new records to the database import table.

    Code:
    Imports System.Data.SqlClient
    
    Imports System.Data.OleDb
    
    Public Class Form3
    
        Private dt As New DataTable
    
        Private Sub MapButton_Click(sender As Object, e As EventArgs) Handles MapButton.Click
    
            Try
                Using csvCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\AJunk2019;Extended Properties='text;HDR=No;FMT=Delimited'"),
                da As New OleDbDataAdapter("Select * from [test.csv]", csvCon)
                    csvCon.Open()
                    da.Fill(dt)
    
                    For i As Integer = 0 To dt.Columns.Count - 1
                        MapDataGridView.Rows.Add(dt.Rows(0).Item(i).ToString, "Ignore Field")
                    Next
    
                    MapDataGridView.AllowUserToAddRows = False
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    
        Private Sub ImportButton_Click(sender As Object, e As EventArgs) Handles ImportButton.Click
            'Remove unwanted columns and display mapped data
            For i As Integer = MapDataGridView.Rows.Count - 1 To 0 Step -1
    
                If MapDataGridView.Rows(i).Cells("Defaults").Value IsNot Nothing Then
                    If MapDataGridView.Rows(i).Cells("Defaults").Value.ToString = "Ignore Field" Then
                        dt.Columns.RemoveAt(i)
                    Else
                        dt.Columns(i).ColumnName = MapDataGridView.Rows(i).Cells("Defaults").Value.ToString
                    End If
                End If
            Next
    
            If Me.HasHeadersCheckBox.Checked Then
                dt.Rows.RemoveAt(0)
            End If
            ImportDataGridView.DataSource = dt
    
        End Sub
    
        Private Sub ExportToDbButton_Click(sender As Object, e As EventArgs) Handles ExportToDbButton.Click
            'Create a DataAdapter with the same fields as in the import DataTable (dt)
            'Use a CommandBuilder to create the InsertCommand for the DataAdapter, this means your database import table must have a PrimaryKey.
            'I added a field to the import database table with an Identity datatype because of the auto increment.
    
            'Build SQL string
            Dim sqlStr As String, ifFirst As Boolean
            ifFirst = True
            sqlStr = "Select "
            For Each col As DataColumn In dt.Columns
                If Not ifFirst Then
                    sqlStr &= ", "
                Else
                    ifFirst = False
                End If
                sqlStr &= col.ColumnName
            Next
    
            sqlStr &= " From contacts"
    
            Using con As New SqlConnection(My.Settings.BooksDBConnectionString),
                    da As New SqlDataAdapter(sqlStr, con),
                    cmdBldr As New SqlCommandBuilder(da)
    
                'Mark all rows in the import datatable as Added
                For Each row As DataRow In dt.Rows
                    If Not row.RowState = DataRowState.Added Then row.SetAdded()
                Next
                da.Update(dt)
    
                MessageBox.Show("Done")
            End Using
    
        End Sub
    End Class
    This is just the bare bones, no data verification.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    395

    Re: Export CSV Data From Datagridview?

    I really appreciate it Wes, I just don't know if I want to use a database yet, if I do it would be a MySQL database since that is what I am using for my web hosting..
    Even still though, the project just breaks down differently that what you have.. maybe if I describe it more..

    Basically I have 1 Form that is for Importing/Loading CSV files that I will then "map" the first column data from the original csv file data to the dgv combobox choices, including an "Ignore Field" for all other fields that the program does not support..

    It will need to do a couple things though, first check that all combobox choices have been made, that the user does not have a blank one, but like you said I can just add it so that it defaults to the "Ignore Field" but I was worried that a user could screw up by just accidentally bypassing a choice and not realizing it was supposed to be mapped and NOT set to Ignore.. so I wanted to stay with the blank default for this because of that.. so it will check that all comboboxes have been made and if not then a messagebox "Please Map All Selections First.."

    Then when all have been made/checked then it will Export The file to a .csv file with openfiledialog.

    When user maps all relevant choices and Ignores the rest of them that we don't support then user will Export by clicking the Export Button and the program will map the new combobox choices to the old choices, overwriting them in some cases (example: old csv data was "Fname" for First Name and is now mapped to "First Name") and deleting the others in the case of the "Ignore Field" has been chosen.

    One issue also is that some of the csv files for import/loading either have or don't have a header info for them so when exporting them I somehow need to deal with this..
    I guess I could make a popup selection form that will show the original data so user can see if it has the csv header or not and select if it has it or does not then it will proceed with the export either Editing the old header or just adding it the the newly mapped/edited data/csv file..

    For this part I don't require a database since I only would like to deal with the raw csv files. I appreciate your time though my friend you rock.

    I would ideally like to have it so that I can sue a module as I really loved using .bas files back in vb6 it just makes everything so much easier and less confusing at times.

    So I need to think about this for a minute..

    - Public Subs
    Open_CSV_FileDLG - this is just to open a csv via openfiledialog
    Save_CSV_FileDLG - this is just to save a csv via savefiledialog
    Load_CSV_To_DGV - can be used with or without OpenCSVFileDLG
    Save_CSV_To_File - can be used with or without SaveCSVFileDLG
    Export_Mapped_CSV_To_File
    Export_Mapped_CSV_To_File_With_ProgressBar

    I would like to for the Export Mapped CSV to have 2 versions of this sub, one that includes use of a progress bar and one that does not..

    This is for the exporting but for the other part of the project I will be loading these mapped csv files into another dgv and then "Scanning" them for various things..
    Syntax being one of them. I hope to just get this first Export part done but it should not be too difficult but I will require help since I am learning still.
    Thank you for your time and effort, it is very much appreciated
    Last edited by DreamWarrior77; Nov 13th, 2021 at 07:35 PM.

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Export CSV Data From Datagridview?

    Then when all have been made/checked then it will Export The file to a .csv file with openfiledialog.
    If you want to save it as csv then use the SaveFileDialog.

    Code:
        Private Sub CSVExportButton_Click(sender As Object, e As EventArgs) Handles CSVExportButton.Click
    
            Dim csvStr As New StringBuilder()
    
            'Headers
            Dim delimeter As String = ""
            If Me.HasHeadersCheckBox.Checked Then
                For Each col As DataColumn In dt.Columns
                    csvStr.Append(delimeter).Append(col.ColumnName)
                    delimeter = ","
                Next
                csvStr.AppendLine()
            End If
    
            For Each row As DataRow In dt.Rows
                csvStr.AppendLine(String.Join(",", row.ItemArray))
            Next
    
            Dim saveLocation As New SaveFileDialog
            saveLocation.InitialDirectory = "C:\AJunk2019"
            If saveLocation.ShowDialog() = DialogResult.OK Then
                File.WriteAllText(saveLocation.FileName, csvStr.ToString)
            End If
    
    
        End Sub
    I don't know the format your import file, some wrap the fields/headers in double quotes. So you may have to format the string differently but the logic is the same.

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    395

    Re: Export CSV Data From Datagridview?

    I meant to say SaveFileDialog, I must be tired sorry..

    Nice, ok testing it out now but getting an error on this line:

    Code:
    For Each row As DataRow In DataGridView1.Rows
    System.InvalidCastException: 'Unable to cast object of type 'System.Windows.Forms.DataGridViewRow' to type 'System.Data.DataRow'.'


    Using this:

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim csvStr As New StringBuilder()
    
            'Headers
            Dim delimeter As String = ""
            If Me.HasHeadersCheckbox.Checked Then
                For Each col As DataColumn In DataGridView1.Columns
                    csvStr.Append(delimeter).Append(col.ColumnName)
                    delimeter = ","
                Next
                csvStr.AppendLine()
            End If
    
            For Each row As DataRow In DataGridView1.Rows
                csvStr.AppendLine(String.Join(",", row.ItemArray))
            Next
    
            Dim saveLocation As New SaveFileDialog
            saveLocation.InitialDirectory = "C:\"
            If saveLocation.ShowDialog() = DialogResult.OK Then
                File.WriteAllText(saveLocation.FileName, csvStr.ToString)
            End If
        End Sub
    Last edited by DreamWarrior77; Nov 14th, 2021 at 12:36 AM.

  9. #9
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Export CSV Data From Datagridview?

    Well that's because that's not what my example does. lol

    A DataRow and a DataGridRow are two different types. If you want to use a DataGridViewRows then you will have to construct the output string differently.

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    395

    Re: Export CSV Data From Datagridview?

    Wait you lost me.. ok I was supposed to leave it as dt and not change it to datagridview but I think I am missing something because how is it taking the data from the datagridview columns otherwise if it is not seeing it?
    Last edited by DreamWarrior77; Nov 14th, 2021 at 12:38 PM.

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Export CSV Data From Datagridview?

    Look at my example. It uses a DataTable as the dgv datasource. But you can do it any way you want. If you want to use an unbound dgv that will work.

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    395

    Re: Export CSV Data From Datagridview?

    I am still not understanding the example, as you know I am using a datagridview but in your example there is no reference to a datagridview control..
    How can it map the data from dgv column 1 to dgv column 2 if there is no reference to the dgv in the code?

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Export CSV Data From Datagridview?

    Look at post #5, the WHOLE example.

    I'm using two dgv's. The last example was just a modified export function, since the example in #5 uses a database and you don't want to use a database. READ the Event name "CSVExportButton_Click". The original data has already been imported, mapped and loaded into the second dgv.

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    395

    Re: Export CSV Data From Datagridview?

    ok Thanks but I will not be using a database for this, I appreciate you taking the time to create that example though, it's just not how I need to do it.
    I am needing to just simplify what you have done to be honest because I have no need to map it, then import it to a database and then export it to a database..

    I only need to at this point, use a command button to map and export to a csv file.. which should be easier.

    No second dgv needed either.. unless of course I want to preview it but I can already see what I have mapped in the original dgv control so it is not required.

    I am still a noob with vb, so I need help figuring these things out.
    Last edited by DreamWarrior77; Nov 14th, 2021 at 02:29 PM.

  15. #15
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Export CSV Data From Datagridview?

    ok Thanks but I will not be using a database for this, I appreciate you taking the time to create that example though, it's just not how I need to do it.
    Fine but my example doesn't use a database. lol


    No second dvg needed either.. unless of course I want to preview it but I can already see what I have mapped in the original control so it is not required.
    Then don't use the second dgv, after the datatable has been modified then do the export.

  16. #16
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Export CSV Data From Datagridview?

    Code:
    Imports System.Data.SqlClient
    
    Imports System.Data.OleDb
    
    Public Class Form3
    
        Private dt As New DataTable
    
        Private Sub MapButton_Click(sender As Object, e As EventArgs) Handles MapButton.Click
    
            Try
                Using csvCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\AJunk2019;Extended Properties='text;HDR=No;FMT=Delimited'"),
                da As New OleDbDataAdapter("Select * from [test.csv]", csvCon)
                    csvCon.Open()
                    da.Fill(dt)
    
                    For i As Integer = 0 To dt.Columns.Count - 1
                        MapDataGridView.Rows.Add(dt.Rows(0).Item(i).ToString, "Ignore Field")
                    Next
    
                    MapDataGridView.AllowUserToAddRows = False
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    
        Private Sub ImportButton_Click(sender As Object, e As EventArgs) Handles ImportButton.Click
            'Remove unwanted columns and display mapped data
            For i As Integer = MapDataGridView.Rows.Count - 1 To 0 Step -1
    
                If MapDataGridView.Rows(i).Cells("Defaults").Value IsNot Nothing Then
                    If MapDataGridView.Rows(i).Cells("Defaults").Value.ToString = "Ignore Field" Then
                        dt.Columns.RemoveAt(i)
                    Else
                        dt.Columns(i).ColumnName = MapDataGridView.Rows(i).Cells("Defaults").Value.ToString
                    End If
                End If
            Next
    
            If Me.HasHeadersCheckBox.Checked Then
                dt.Rows.RemoveAt(0)
            End If
            ImportDataGridView.DataSource = dt
    
        End Sub
    
        Private Sub CSVExportButton_Click(sender As Object, e As EventArgs) Handles CSVExportButton.Click
    
            Dim csvStr As New StringBuilder()
    
            'Headers
            Dim delimeter As String = ""
            If Me.HasHeadersCheckBox.Checked Then
                For Each col As DataColumn In dt.Columns
                    csvStr.Append(delimeter).Append(col.ColumnName)
                    delimeter = ","
                Next
                csvStr.AppendLine()
            End If
    
            For Each row As DataRow In dt.Rows
                csvStr.AppendLine(String.Join(",", row.ItemArray))
            Next
    
            Dim saveLocation As New SaveFileDialog
            saveLocation.InitialDirectory = "C:\AJunk2019"
            If saveLocation.ShowDialog() = DialogResult.OK Then
                File.WriteAllText(saveLocation.FileName, csvStr.ToString)
            End If
    
    
        End Sub
    
    End Class
    See no database, now it exports to csv file.

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    395

    Re: Export CSV Data From Datagridview?

    ok I can try it and get back to you:

    what is this:

    Code:
    Source= C:\AJunk2019;Extended Properties='text;HDR=No;FMT=Delimited'"),
    what would I put for my source?

    and this

    Code:
    [test.csv]

  18. #18
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Export CSV Data From Datagridview?

    Think about it, what does it look like.

    Path
    file name

    Give it a try if you want. If you want to use a different method , no problem.

    If you want to learn about the method I used then Google "Visual Basic using Oledb import csv"

    https://social.msdn.microsoft.com/Fo...orum=vbgeneral

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width