none
how import csv file to datagridview RRS feed

  • Question

  • Hi guys, I need help to import my csv file to Datagridview columns.

    My csv Text:

    Data as of : 10/10/2015,,,,,,,,
    Company ,Position Status, Name, ID ,File Number,Home Department Description,Hire Date,Rehire Date,Termination Date
    GEV,Active,"Fernandez, thalia A",XX-XX-157,358,Front End,6/20/2017,,
    GEV,Active,"Costa, Julian S",XX-XX-970,358,Liquor,3/20/2017,8/30/2017,
    GEV,Terminated,"Escobar, Taylor V",XX-XX-755,358,Liquor,7/20/2014,8/23/2015,8/12/2017

    I want import it to my Columns in datagridview each information separate in each column, I have 10 columns: local work, status, last name, first name, new ID, ID, Function, hire date, rehire date, term date.

    I want the first information in csv text go to my first column ("GEV" in "Local work" column) and I don't want the first and second line, and separate information with ","

    My command:

     If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
    
                Try
    
                    DG_Import.ColumnCount = 10
                    DG_Import.ColumnHeadersVisible = True
    
                    Dim columnHeaderStyle As New DataGridViewCellStyle()
    
                    columnHeaderStyle.Font = New Font("Arial", 8, FontStyle.Bold)
                    DG_Import.ColumnHeadersDefaultCellStyle = columnHeaderStyle
    
    
                    DG_Import.Columns(0).Name = "Local Work"
                    DG_Import.Columns(1).Name = "Status"
                    DG_Import.Columns(2).Name = "Last Name"
                    DG_Import.Columns(3).Name = "First Name"
                    DG_Import.Columns(4).Name = "new ID"
                    DG_Import.Columns(5).Name = "ADP ID"
                    DG_Import.Columns(6).Name = "Function"
                    DG_Import.Columns(7).Name = "Hire Date"
                    DG_Import.Columns(8).Name = "Rehire Date"
                    DG_Import.Columns(9).Name = "Term Date"
    
    
    
                Catch ex As Exception
                    MsgBox("Error to send list Logins!", MsgBoxStyle.Critical, "Erro")
    
                End Try
            End If

    Thank you very much guys

    • Changed type Julian J T Wednesday, September 13, 2017 2:58 PM
    Wednesday, September 13, 2017 2:29 PM

Answers

  • Hi Julian,

    According to your description, you just want to import csv file into datagridview, you can do this by the following code.

        Dim dt As DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            dt = New DataTable
            dt.Columns.Add("Local WOrk", GetType(String))
            dt.Columns.Add("Status", GetType(String))
            dt.Columns.Add("Last Name", GetType(String))
            dt.Columns.Add("First Name", GetType(String))
            dt.Columns.Add("New ID", GetType(String))
            dt.Columns.Add("ID", GetType(String))
            dt.Columns.Add("Function", GetType(String))
            dt.Columns.Add("Hire Date", GetType(String))
            dt.Columns.Add("Rehire Date", GetType(String))
            dt.Columns.Add("Term Date", GetType(String))
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim fName As String = ""
            OpenFileDialog1.InitialDirectory = "D:\TestFile"
            OpenFileDialog1.Filter = "CSV files(*.csv)|*.csv"
            OpenFileDialog1.FilterIndex = 2
            OpenFileDialog1.RestoreDirectory = True
            If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
                fName = OpenFileDialog1.FileName
            End If
            TextBox1.Text = fName
            Dim TextLine As String = ""
            Dim SplitLine() As String
    
            If System.IO.File.Exists(fName) = True Then
                Dim objReader As New System.IO.StreamReader(TextBox1.Text, Encoding.ASCII)
                Dim index As Integer = 0
                Do While objReader.Peek() <> -1
                    If index > 0 Then
                        TextLine = objReader.ReadLine()
                        SplitLine = Split(TextLine, ",")
                        dt.Rows.Add(SplitLine)
                    Else
                        TextLine = objReader.ReadLine()
                    End If
                    index = index + 1
                Loop
                DataGridView1.DataSource = dt
            Else
                MsgBox("File Does Not Exist")
            End If
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Julian J T Thursday, September 14, 2017 4:42 PM
    Thursday, September 14, 2017 8:28 AM
    Moderator

All replies

  • Wednesday, September 13, 2017 2:36 PM
  • Julian,

    A good tool to use here is the TextFieldParser. As an example:

        Private Sub GetCSVData(ByVal filePath As String)
    
            If Not String.IsNullOrWhiteSpace(filePath) Then
    
                Try
                    Dim fi As New IO.FileInfo(filePath)
    
                    If fi.Exists Then
                        Using tfp As New Microsoft.VisualBasic.FileIO.TextFieldParser(fi.FullName)
                            With tfp
                                .TextFieldType = FileIO.FieldType.Delimited
                                .Delimiters = New String() {","}
    
                                ' Set the following to true if you have fields
                                ' which are enclosed in quotation marks:
                                .HasFieldsEnclosedInQuotes = False
                            End With
    
                            Dim currentLineOfText() As String
    
                            While Not tfp.EndOfData
                                currentLineOfText = tfp.ReadFields()
                                Stop
                            End While
                        End Using
                    End If
    
                Catch ex As Exception
                    MessageBox.Show(String.Format("An error occurred:{0}{0}{1}", _
                                                  vbCrLf, ex.Message), _
                                                  "Error Reading Text File", _
                                                  MessageBoxButtons.OK, _
                                                  MessageBoxIcon.Warning)
                End Try
            End If
    
        End Sub

    Per line read, you'll need to make a decision about what you want to do for storage. It can be, as Tommy pointed out, a DataTable or you can create a class (I would) and use that.

    Whichever way you go though, you should use a "binding go between" like a BindingSource. That will then become the DataSource of the DataGridView and the BindingSource of a BindingNavigator if you choose to use one.


    "A problem well stated is a problem half solved.” - Charles F. Kettering


    • Edited by Frank L. Smith Wednesday, September 13, 2017 2:47 PM ...added links
    Wednesday, September 13, 2017 2:43 PM
  • Since you are asking a question, please change the type of your post from Comment to Question.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, September 13, 2017 2:47 PM
  • I have a couple examples at the below link as well, which demonstrate how to create the DataTable columns if you don't want to use those from the file. Then it just adds the rows (you can use a counter variable to skip the first two) and binds to a DataGridView.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/cc7d1f3f-ef0e-4b37-92f6-d4990269a166/datagrid-read-csv?forum=vbgeneral


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, September 13, 2017 5:03 PM
  • Hi Julian,

    According to your description, you just want to import csv file into datagridview, you can do this by the following code.

        Dim dt As DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            dt = New DataTable
            dt.Columns.Add("Local WOrk", GetType(String))
            dt.Columns.Add("Status", GetType(String))
            dt.Columns.Add("Last Name", GetType(String))
            dt.Columns.Add("First Name", GetType(String))
            dt.Columns.Add("New ID", GetType(String))
            dt.Columns.Add("ID", GetType(String))
            dt.Columns.Add("Function", GetType(String))
            dt.Columns.Add("Hire Date", GetType(String))
            dt.Columns.Add("Rehire Date", GetType(String))
            dt.Columns.Add("Term Date", GetType(String))
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim fName As String = ""
            OpenFileDialog1.InitialDirectory = "D:\TestFile"
            OpenFileDialog1.Filter = "CSV files(*.csv)|*.csv"
            OpenFileDialog1.FilterIndex = 2
            OpenFileDialog1.RestoreDirectory = True
            If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
                fName = OpenFileDialog1.FileName
            End If
            TextBox1.Text = fName
            Dim TextLine As String = ""
            Dim SplitLine() As String
    
            If System.IO.File.Exists(fName) = True Then
                Dim objReader As New System.IO.StreamReader(TextBox1.Text, Encoding.ASCII)
                Dim index As Integer = 0
                Do While objReader.Peek() <> -1
                    If index > 0 Then
                        TextLine = objReader.ReadLine()
                        SplitLine = Split(TextLine, ",")
                        dt.Rows.Add(SplitLine)
                    Else
                        TextLine = objReader.ReadLine()
                    End If
                    index = index + 1
                Loop
                DataGridView1.DataSource = dt
            Else
                MsgBox("File Does Not Exist")
            End If
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Julian J T Thursday, September 14, 2017 4:42 PM
    Thursday, September 14, 2017 8:28 AM
    Moderator
  • This Form is correct but I did different, I did this:

    Private Sub BT_Import_Click(sender As System.Object, e As System.EventArgs) Handles BT_Import.Click
    
    
           If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
    
                Try
                    DG_Import.Rows.Clear()
                    Dim FileImported As New System.IO.StreamReader(OpenFileDialog1.FileName)
                    Dim itemSeparate As Char
                    Dim columnHeaderStyle As New DataGridViewCellStyle()
    
                    DG_Import.ColumnCount = 10
    
                    columnHeaderStyle.Font = New Font("Arial", 8, FontStyle.Bold)
                    DG_Import.ColumnHeadersDefaultCellStyle = columnHeaderStyle
    
    
                    DG_Import.Columns(0).Name = "Local Work"
                    DG_Import.Columns(1).Name = "Status"
                    DG_Import.Columns(2).Name = "Last Name"
                    DG_Import.Columns(3).Name = "First Name"
                    DG_Import.Columns(4).Name = "New ID"
                    DG_Import.Columns(5).Name = "ID"
                    DG_Import.Columns(6).Name = "Function"
                    DG_Import.Columns(7).Name = "Hire Date"
                    DG_Import.Columns(8).Name = "Rehire Date"
                    DG_Import.Columns(9).Name = "Term Date"
    
                    itemSeparate = ","
    
    
                    While FileImported.Peek() <> -1
                        DG_Import.Rows.Add(FileImported.ReadLine().Split(itemSeparate))
                    End While
    
                    DG_Import.Rows.RemoveAt(0)
                    DG_Import.Rows.RemoveAt(0)
    
                Catch ex As Exception
                    MsgBox("Import File Error", MsgBoxStyle.Critical, "Erro")
    
                End Try
            End If
    
        End Sub

    Thursday, September 14, 2017 4:45 PM
  • Julian,

    If you're going to use a StreamReader, you need to dispose it at the end or the file will likely be locked.

    Optionally, and better in my opinion, put it in a Using Block. Doing that will dispose the stream even if an exception is thrown in the meantime.

    Also, per line, check that the line isn't malformed. Trying to "split" an empty line will throw an exception. A TextFieldParser knows to skip blank lines.


    "A problem well stated is a problem half solved.” - Charles F. Kettering

    Thursday, September 14, 2017 9:25 PM
  • So, CSV to Datagridview?  Try this.......

    Public Sub GetCsvData()
    
                Dim csvFileFolder As String = "your_path_here"
                Dim csvFileName As String = "YOUR_CSV.csv"
                Dim connString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
                    & csvFileFolder & ";Extended Properties=""Text;HDR=No;FMT=Delimited"""
                Dim conn As New Odbc.OdbcConnection(connString)
                'Open a data adapter, specifying the file name to load
                Dim da As New Odbc.OdbcDataAdapter("SELECT * FROM [" & csvFileName & "]", conn)
                'Then fill a data table, which can be bound to a grid
                Dim dt As New DataTable
                da.Fill(dt)
                DataGridView1.DataSource = dt
                With DataGridView1
                '.AutoGenerateColumns = True
                    .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                    .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                End With
    End Sub

    MY BOOK



    • Edited by ryguy72 Friday, September 15, 2017 3:27 AM
    Friday, September 15, 2017 3:25 AM