none
datagrid read csv RRS feed

  • Question

  • Can Datagridview read the semicolon or colon from csv file which separates columns (excel) and create the same style as Excel?.

    If it does read it like excel a simple streamreader could do it? I've seen some examples of others importing csv to Datagrid but its way to difficult for me to understand.
    If its not possible on simple coding please don't use your time writing a long code, i am looking for additional features for my project and will only add the code if its relatively simple/short.

    Thanks in advance

    Friday, March 26, 2010 4:09 PM

Answers

  • This code (and there is not too much of it) shows how to read a csv file into an in-memory DataTable.

    http://msmvps.com/blogs/deborahk/archive/2009/08/25/reading-comma-delimited-files.aspx

    You can then bind the DataTable to your DataGridView to show your results.

    Hope this helps.


    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    • Marked as answer by Pjort Friday, March 26, 2010 5:24 PM
    Friday, March 26, 2010 4:29 PM
  • Pjort

    in your other thread:

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/15f13965-af62-4a81-9fe7-ee76c1bfc571/#d7dc594a-a6c9-4b2d-be85-cbfb90f22aae

    I showed one way to read the CSV file using StreamReader, and splitting the lines at whatever character you want (comma, colon, etc)

    instead of backing away from the code you don't understand, try looking at it and asking questions about the parts you need help with. That is the only way you will learn and grow in your code writing abilities. There are plenty of people willing to help you learn and explain things.

    • Marked as answer by Pjort Friday, March 26, 2010 5:24 PM
    Friday, March 26, 2010 4:39 PM
  • This is relatively simple. The below example creates a DataTable, reads the rows from a semi-colon delimited file and adds them into the DataTable, and uses the DataTable as the data source for the DataGrid:

            Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Documents and Settings\...\My Documents\My Database\Text\SemiColonDelimited.txt")
    
            TextFileReader.TextFieldType = FileIO.FieldType.Delimited
            TextFileReader.SetDelimiters(";")
    
            Dim TextFileTable As DataTable = New DataTable("TextFileTable")
    
            ' Declare variables for DataColumn and DataRow objects.
            Dim Column As DataColumn
            Dim Row As DataRow
    
            ' Create DataTable with three text columns
            For ColumnCount As Int32 = 0 To 2
                Column = New DataColumn()
                Column.DataType = System.Type.GetType("System.String")
                Column.ColumnName = "Column" & ColumnCount
                Column.Caption = "Column" & ColumnCount
                Column.ReadOnly = True
                Column.Unique = False
                TextFileTable.Columns.Add(Column)
            Next
    
            Dim CurrentRow As String()
            While Not TextFileReader.EndOfData
                Try
                    CurrentRow = TextFileReader.ReadFields()
                    Row = TextFileTable.NewRow
                    Row("Column0") = CurrentRow(0).ToString
                    Row("Column1") = CurrentRow(1).ToString
                    Row("Column2") = CurrentRow(2).ToString
                    TextFileTable.Rows.Add(Row)
                Catch ex As _
                Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & _
                    "is not valid and will be skipped.")
                End Try
            End While
            TextFileReader.Dispose()
            frmMain.DataGrid1.DataSource = TextFileTable
    


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Pjort Saturday, March 27, 2010 9:44 AM
    Friday, March 26, 2010 5:29 PM
  • I rewrote the code so that it's a bit more dynamic with respect to the number of columns:

            Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Documents and Settings\...\My Documents\My Database\Text\SemiColonDelimited.txt")
    
            TextFileReader.TextFieldType = FileIO.FieldType.Delimited
            TextFileReader.SetDelimiters(";")
    
            Dim TextFileTable As DataTable = Nothing
    
            Dim Column As DataColumn
            Dim Row As DataRow
            Dim UpperBound As Int32
            Dim ColumnCount As Int32
            Dim CurrentRow As String()
    
            While Not TextFileReader.EndOfData
                Try
                    CurrentRow = TextFileReader.ReadFields()
                    If Not CurrentRow Is Nothing Then
                        'Check if DataTable has been created
                        If TextFileTable Is Nothing Then
                            TextFileTable = New DataTable("TextFileTable")
                            'Get number of columns
                            UpperBound = CurrentRow.GetUpperBound(0)
                            'Create new DataTable
                            For ColumnCount = 0 To UpperBound
                                Column = New DataColumn()
                                Column.DataType = System.Type.GetType("System.String")
                                Column.ColumnName = "Column" & ColumnCount
                                Column.Caption = "Column" & ColumnCount
                                Column.ReadOnly = True
                                Column.Unique = False
                                TextFileTable.Columns.Add(Column)
                            Next
                        End If
                        Row = TextFileTable.NewRow
                        For ColumnCount = 0 To UpperBound
                            Row("Column" & ColumnCount) = CurrentRow(ColumnCount).ToString
                        Next
                        TextFileTable.Rows.Add(Row)
                    End If
                Catch ex As _
                Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & _
                    "is not valid and will be skipped.")
                End Try
            End While
            TextFileReader.Dispose()
            frmMain.DataGrid1.DataSource = TextFileTable


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Pjort Monday, March 29, 2010 2:04 PM
    Monday, March 29, 2010 1:42 PM

All replies

  • This code (and there is not too much of it) shows how to read a csv file into an in-memory DataTable.

    http://msmvps.com/blogs/deborahk/archive/2009/08/25/reading-comma-delimited-files.aspx

    You can then bind the DataTable to your DataGridView to show your results.

    Hope this helps.


    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    • Marked as answer by Pjort Friday, March 26, 2010 5:24 PM
    Friday, March 26, 2010 4:29 PM
  • Pjort

    in your other thread:

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/15f13965-af62-4a81-9fe7-ee76c1bfc571/#d7dc594a-a6c9-4b2d-be85-cbfb90f22aae

    I showed one way to read the CSV file using StreamReader, and splitting the lines at whatever character you want (comma, colon, etc)

    instead of backing away from the code you don't understand, try looking at it and asking questions about the parts you need help with. That is the only way you will learn and grow in your code writing abilities. There are plenty of people willing to help you learn and explain things.

    • Marked as answer by Pjort Friday, March 26, 2010 5:24 PM
    Friday, March 26, 2010 4:39 PM
  • @DeborahK Thank you for the lnik, ill be looking it through.

    @jwavila Sorry i forgot all about that code, i'll try to look through that code too

     

    Friday, March 26, 2010 5:24 PM
  • This is relatively simple. The below example creates a DataTable, reads the rows from a semi-colon delimited file and adds them into the DataTable, and uses the DataTable as the data source for the DataGrid:

            Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Documents and Settings\...\My Documents\My Database\Text\SemiColonDelimited.txt")
    
            TextFileReader.TextFieldType = FileIO.FieldType.Delimited
            TextFileReader.SetDelimiters(";")
    
            Dim TextFileTable As DataTable = New DataTable("TextFileTable")
    
            ' Declare variables for DataColumn and DataRow objects.
            Dim Column As DataColumn
            Dim Row As DataRow
    
            ' Create DataTable with three text columns
            For ColumnCount As Int32 = 0 To 2
                Column = New DataColumn()
                Column.DataType = System.Type.GetType("System.String")
                Column.ColumnName = "Column" & ColumnCount
                Column.Caption = "Column" & ColumnCount
                Column.ReadOnly = True
                Column.Unique = False
                TextFileTable.Columns.Add(Column)
            Next
    
            Dim CurrentRow As String()
            While Not TextFileReader.EndOfData
                Try
                    CurrentRow = TextFileReader.ReadFields()
                    Row = TextFileTable.NewRow
                    Row("Column0") = CurrentRow(0).ToString
                    Row("Column1") = CurrentRow(1).ToString
                    Row("Column2") = CurrentRow(2).ToString
                    TextFileTable.Rows.Add(Row)
                Catch ex As _
                Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & _
                    "is not valid and will be skipped.")
                End Try
            End While
            TextFileReader.Dispose()
            frmMain.DataGrid1.DataSource = TextFileTable
    


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Pjort Saturday, March 27, 2010 9:44 AM
    Friday, March 26, 2010 5:29 PM
  • Hello Paul. Thank you very much for that code, Simple and understandable for me.
    I Used to have a textbox to show my csv in, before this datagridview so my search function need a modification too
            Dim Lines() As String = My.Computer.FileSystem.ReadAllText("C:\testfolder\test.txt").Split(vbCrLf)
            For Each S As String In Lines
                If S.Contains(Search.text) Then
                    User.Text &= S & vbCrLf
                End If
            Next

    I Would like to show the whole line where the word appear i am searching for..
    User.text is now switched with a datagridview
    I hope this one will be simple too

    Thanks in advance

    Saturday, March 27, 2010 9:44 AM
  •  

    Can i make the rows more dynamic? or create exceptions at

    Row("column0") = CurrentRow(0).ToString

    I added 5 lines of columns for my project because my csv file contains 5 splits in every line.
    I then got the idea of creating an Openfiledialog  with this code and the top of your code:

            Dim openFileDialog1 As New OpenFileDialog()
            openFileDialog1.Filter = "Data Files|*.csv"
            openFileDialog1.Title = "Select a Datafile"
    
            If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
    
            End If
    
            Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(openFileDialog1.OpenFile())

     

    But if i open an csv file with only 4 semicolon splits i get an error on:

     

          Row("Column5") = CurrentRow(5).ToString

     

    And my program will also crash if i click cancel when i select a file from the opefiledialog

     

    I Hope you understand the problem

    Thanks in advance

     

    Saturday, March 27, 2010 11:15 AM
  • I rewrote the code so that it's a bit more dynamic with respect to the number of columns:

            Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Documents and Settings\...\My Documents\My Database\Text\SemiColonDelimited.txt")
    
            TextFileReader.TextFieldType = FileIO.FieldType.Delimited
            TextFileReader.SetDelimiters(";")
    
            Dim TextFileTable As DataTable = Nothing
    
            Dim Column As DataColumn
            Dim Row As DataRow
            Dim UpperBound As Int32
            Dim ColumnCount As Int32
            Dim CurrentRow As String()
    
            While Not TextFileReader.EndOfData
                Try
                    CurrentRow = TextFileReader.ReadFields()
                    If Not CurrentRow Is Nothing Then
                        'Check if DataTable has been created
                        If TextFileTable Is Nothing Then
                            TextFileTable = New DataTable("TextFileTable")
                            'Get number of columns
                            UpperBound = CurrentRow.GetUpperBound(0)
                            'Create new DataTable
                            For ColumnCount = 0 To UpperBound
                                Column = New DataColumn()
                                Column.DataType = System.Type.GetType("System.String")
                                Column.ColumnName = "Column" & ColumnCount
                                Column.Caption = "Column" & ColumnCount
                                Column.ReadOnly = True
                                Column.Unique = False
                                TextFileTable.Columns.Add(Column)
                            Next
                        End If
                        Row = TextFileTable.NewRow
                        For ColumnCount = 0 To UpperBound
                            Row("Column" & ColumnCount) = CurrentRow(ColumnCount).ToString
                        Next
                        TextFileTable.Rows.Add(Row)
                    End If
                Catch ex As _
                Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & _
                    "is not valid and will be skipped.")
                End Try
            End While
            TextFileReader.Dispose()
            frmMain.DataGrid1.DataSource = TextFileTable


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Pjort Monday, March 29, 2010 2:04 PM
    Monday, March 29, 2010 1:42 PM
  • Hello Paul. Thank you very much for that code, Simple and understandable for me.
    I Used to have a textbox to show my csv in, before this datagridview so my search function need a modification too
            Dim
     Lines() As
     String
     = My
    .Computer.FileSystem.ReadAllText("C:\testfolder\test.txt"
    ).Split(vbCrLf)
            For
     Each
     S As
     String
     In
     Lines
                If
     S.Contains(Search.text) Then
    
                    User.Text &= S & vbCrLf
                End
     If
    
            Next
    
    

    I Would like to show the whole line where the word appear i am searching for..
    User.text is now switched with a datagridview
    I hope this one will be simple too

    Thanks in advance


    You may want to ask a new question for this.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 29, 2010 1:48 PM
  • Thats pretty awesome coding there Paul!
    Monday, March 29, 2010 2:04 PM
  • Create your own question, it is not appriciated to use somebodies else his question thread to create new questions.
    Success
    Cor
    Wednesday, April 21, 2010 11:10 AM
  • Hello Paul. Thank you very much for that code, Simple and understandable for me. 

    I added a method to name the ColumnName based on the CSV ColumnName and to not add that as a row in the Data Table

        Dim TextFileTable As DataTable = Nothing
        Private drTextFileTable As DataRow
        Dim TextFileName As String = ""
        Dim Column As DataColumn
        Dim Row As DataRow
        Dim UpperBound As Int32
        Dim ColumnCount As Int32
        Dim CurrentRow As String()
        Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Users\David\Downloads\Royal Mail\DespatchedOrdersReport.2016-12-24-13-18-52.csv")
        Dim FirstRow As String()
        Dim blnAddRow As Boolean = False
        Private fileOpened As Boolean = False
        Private openFileName As String, folderName As String
        Dim myStream As Stream = Nothing

    Private Sub BtnFindPath_Click(sender As System.Object, e As System.EventArgs) Handles BtnFindPath.Click
            ''# Set the Path for the Initial Directory & filter for file type
            OpenFileDialog1.InitialDirectory = "C:\Users\David\Downloads\Royal Mail\"
            OpenFileDialog1.Filter = "txt files (*.CSV)|*.CSV"
            OpenFileDialog1.FilterIndex = 2
            OpenFileDialog1.RestoreDirectory = True

            If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                Try
                    myStream = OpenFileDialog1.OpenFile()
                    If (myStream IsNot Nothing) Then
                        Using TextFileReader As New Microsoft.VisualBasic.FileIO.
                            TextFieldParser(OpenFileDialog1.OpenFile())

                            TextFileReader.TextFieldType = FileIO.FieldType.Delimited
                            TextFileReader.Delimiters = New String() {vbTab}
                            ''# Avoids adding title row to the data table
                            blnAddRow = False
                            While Not TextFileReader.EndOfData
                                Try
                                    CurrentRow = TextFileReader.ReadFields()
                                    If Not CurrentRow Is Nothing Then

                                        ''# Check if DataTable has been created
                                        If TextFileTable Is Nothing Then
                                            TextFileTable = New DataTable("TextFileTable")
                                            ''# Get number of columns
                                            UpperBound = CurrentRow.GetUpperBound(0)
                                            ''# FirtsRow is used for the data colum names
                                            ReDim FirstRow(UpperBound)
                                            ''# Create new DataTable
                                            For ColumnCount = 0 To UpperBound
                                                Column = New DataColumn()
                                                Column.DataType = System.Type.GetType("System.String")
                                                ''# Names Columns based on Titles in CSV headings
                                                Column.ColumnName = CurrentRow.ToArray(ColumnCount)
                                                Column.Caption = CurrentRow.ToArray(ColumnCount)
                                                FirstRow(ColumnCount) = CurrentRow.ToArray(ColumnCount)
                                                Column.ReadOnly = True
                                                Column.Unique = False
                                                TextFileTable.Columns.Add(Column)
                                            Next
                                        End If
                                        If blnAddRow Then
                                            Row = TextFileTable.NewRow
                                            For ColumnCount = 0 To UpperBound
                                                Row(FirstRow(ColumnCount)) = CurrentRow(ColumnCount).ToString
                                            Next
                                            TextFileTable.Rows.Add(Row)
                                        Else
                                            blnAddRow = True
                                        End If
                                    End If
                                Catch ex As _
            Microsoft.VisualBasic.FileIO.MalformedLineException
                                    MsgBox("Line " & ex.Message &
                "is not valid and will be skipped.")
                                End Try
                            End While

                            TextFileReader.Dispose()
                            With DataGridView1
                                .DataSource = TextFileTable
                                .AutoResizeColumns()
                                .RowHeadersWidth = 20
                                .AllowUserToAddRows = False
                                .AllowUserToDeleteRows = False
                                .AllowUserToOrderColumns = False
                                .ReadOnly = True
                                .SelectionMode = DataGridViewSelectionMode.FullRowSelect
                                .MultiSelect = False
                                .AutoGenerateColumns = False
                                For Each column As DataGridViewColumn In .Columns
                                    column.SortMode = DataGridViewColumnSortMode.NotSortable
                                Next

                                Dim font As New Drawing.Font(DataGridView1.DefaultCellStyle.Font.FontFamily, 7, FontStyle.Regular)
                                Try
                                    .ColumnHeadersDefaultCellStyle.Font = font
                                Finally
                                    font.Dispose()
                                End Try

                            End With

                        End Using
                    End If
                Catch Ex As Exception
                    MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
                Finally
                    ' Check this again, since we need to make sure we didn't throw an exception on open.
                    If (myStream IsNot Nothing) Then
                        myStream.Close()
                    End If
                End Try
            End If
        End Sub

    There maybe a better way of opening the file, but this does work


    Tuesday, December 27, 2016 1:21 PM