Results 1 to 6 of 6

Thread: Load contents of a file to datatable

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Aug 2012
    Posts
    75

    Load contents of a file to datatable

    Hi!

    Never used .csv file for data source so I have a question. I have a file with contents like this:

    Mike
    John
    Evelyn
    etc.

    How do I read this file line by line and store names into a datatable so I can display the names in datagridview? as you can see every name is on separate line. There are no usual delimiters like ;,. etc.

    Thanks in advance!
    Last edited by lkallas; Dec 17th, 2013 at 09:05 AM.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Load contents of a file to datatable

    Try this:

    Code:
    Public Class Form1
        Private dt As DataTable
        Private bs As BindingSource
    
        Private Sub btn_load_Click(sender As System.Object, e As System.EventArgs) Handles btn_load.Click
            dt = txt_to_data(IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "names.txt"), _
                             False, Environment.NewLine)
    
            If dt Is Nothing Then
                MessageBox.Show("There was an error loading the data.")
            Else
                bs = New BindingSource
                bs.DataSource = dt
    
                DataGridView1.DataSource = bs
            End If
        End Sub
    
        Private Function txt_to_data(ByVal filename As String, ByVal header As Boolean, ByVal delimiter As String) As DataTable
            'New datatable
            Dim dt As DataTable = New DataTable
    
            'Read the contents of the textfile into an array
            Dim sr As IO.StreamReader = New IO.StreamReader(filename)
            Dim txtlines() As String = sr.ReadToEnd.Split({Environment.NewLine}, StringSplitOptions.RemoveEmptyEntries)
    
            'Return nothing if there's nothing in the textfile
            If txtlines.Count = 0 Then
                Return Nothing
            End If
    
            Dim column_count As Integer = 0
            For Each col As String In txtlines(0).Split({delimiter}, StringSplitOptions.None)
                If header Then
                    'If there's a header then add it by it's name
                    dt.Columns.Add(col)
                    dt.Columns(column_count).Caption = col
                Else
                    'If there's no header then add it by the column count
                    dt.Columns.Add(String.Format("Column{0}", column_count))
                    dt.Columns(column_count).Caption = String.Format("Column{0}", column_count + 1)
                End If
    
                column_count += 1
            Next
    
            If header Then
                For rows As Integer = 1 To txtlines.Count - 1 'start at one because there's a header for the first line(0)
                    'Declare a new datarow
                    Dim dr As DataRow = dt.NewRow
    
                    'Set the column count back to 0, we can reuse this variable ;]
                    column_count = 0
                    For Each col As String In txtlines(rows).Split({delimiter}, StringSplitOptions.None) 'Each column in the row
                        'The column in cue is set for the datarow
                        dr(column_count) = col
                        column_count += 1
                    Next
    
                    'Add the row
                    dt.Rows.Add(dr)
                Next
            Else
                For rows As Integer = 0 To txtlines.Count - 1 'start at zero because there's no header
                    'Declare a new datarow
                    Dim dr As DataRow = dt.NewRow
    
                    'Set the column count back to 0, we can reuse this variable ;]
                    column_count = 0
                    For Each col As String In txtlines(rows).Split({delimiter}, StringSplitOptions.None) 'Each column in the row
                        'The column in cue is set for the datarow
                        dr(column_count) = col
                        column_count += 1
                    Next
    
                    'Add the row
                    dt.Rows.Add(dr)
                Next
            End If
    
            Return dt
        End Function
    
    End Class
    Just be sure that if you have a header in your text file that you set the header parameter in txt_to_data to True.

    Edit - If you want a few more data snippets, check out my website in my signature. It linked as "My Snippet Website". On there it will also show a way how to load the text to a data table using OLEDB.
    Last edited by dday9; Dec 17th, 2013 at 09:48 AM.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: Load contents of a file to datatable

    Er ... that's a lot of mucking about to read a file line by line seeing as you can do it in a single command ...

    Dim LineByLine() As String = IO.File.ReadAllLines(filename)
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Load contents of a file to datatable

    @Dun - You're right. Here's a revised version with a little less muck

    Code:
    Public Class Form1
        Private dt As DataTable
        Private bs As BindingSource
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            dt = txt_to_data(IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "names.txt"), False)
    
            If dt IsNot Nothing Then
                bs = New BindingSource
                bs.DataSource = dt
    
                DataGridView1.DataSource = bs
            Else
                MessageBox.Show("There was an issue loading the data.")
            End If
        End Sub
    
        Private Function txt_to_data(ByVal filename As String, ByVal header As Boolean) As DataTable
            Dim dt As New DataTable
    
            If IO.File.Exists(filename) = False Then
                Return Nothing
            End If
    
            Dim LineByLine() As String = IO.File.ReadAllLines(filename)
    
            If LineByLine.Length = 0 Then
                Return Nothing
            End If
    
            If header Then
                dt.Columns.Add(New DataColumn(LineByLine(0)))
    
                For i As Integer = 1 To LineByLine.Length
                    Dim dr As DataRow = dt.NewRow
                    dr.Item(0) = LineByLine(i)
    
                    dt.Rows.Add(dr)
                Next
            Else
    
                dt.Columns.Add(New DataColumn("Column0"))
                For Each line As String In LineByLine
                    Dim dr As DataRow = dt.NewRow
                    dr.Item(0) = line
    
                    dt.Rows.Add(dr)
                Next
            End If
    
            Return dt
        End Function
    End Class
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  5. #5
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Load contents of a file to datatable

    Hello,

    The following uses a language extension which is of more use then just this solution.


    Code:
    Dim dt As DataTable =
        (
            From this In IO.File.ReadAllLines(IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "TextFile1.txt"))
            Select New With {.Name = this}
        ).ToDataTable
    DataGridView1.DataSource = dt
    Extension method (place into a code module, not a class or form)
    Code:
    <System.Diagnostics.DebuggerStepThrough()> _
    <System.Runtime.CompilerServices.Extension()> _
    Public Function ToDataTable(Of T)(ByVal sender As IEnumerable(Of T)) As DataTable
    
        Dim dt As New DataTable
        Dim HeaderRow = sender.First
    
        For Each pi In HeaderRow.GetType.GetProperties
            dt.Columns.Add(pi.Name, pi.GetValue(HeaderRow, Nothing).GetType)
        Next
    
        For Each result In sender
            Dim nr = dt.NewRow
            For Each pi In result.GetType.GetProperties
                nr(pi.Name) = pi.GetValue(result, Nothing)
            Next
            dt.Rows.Add(nr)
        Next
        Return dt
    End Function
    So for a simple example via delimited file (did not use TextFieldParser on purpose) read a delimited text file
    Code:
    Dim dtStates As DataTable =
        (
            From line In IO.File.ReadAllLines(IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "States.txt"))
            Let Items = line.Split(","c)
            Select New With {.Name = Items(0), .Code = Items(1)}
        ).ToDataTable
    
    DataGridView1.DataSource = dtStates
    States.txt
    Code:
    ALABAMA,AL
    ALASKA,AK
    AMERICAN SAMOA,AS
    ARIZONA,AZ
    ARKANSAS,AR
    CALIFORNIA,CA
    COLORADO,CO
    CONNECTICUT,CT
    DELAWARE,DE
    DISTRICT OF COLUMBIA,DC
    FEDERATED STATES OF MICRONESIA,FM
    FLORIDA,FL
    GEORGIA,GA
    GUAM,GU
    HAWAII,HI
    IDAHO,ID
    ILLINOIS,IL
    INDIANA,IN
    IOWA,IA
    KANSAS,KS
    KENTUCKY,KY
    LOUISIANA,LA
    MAINE,ME
    MARSHALL ISLANDS,MH
    MARYLAND,MD
    MASSACHUSETTS,MA
    MICHIGAN,MI
    MINNESOTA,MN
    MISSISSIPPI,MS
    MISSOURI,MO
    MONTANA,MT
    NEBRASKA,NE
    NEVADA,NV
    NEW HAMPSHIRE,NH
    NEW JERSEY,NJ
    NEW MEXICO,NM
    NEW YORK,NY
    NORTH CAROLINA,NC
    NORTH DAKOTA,ND
    NORTHERN MARIANA ISLANDS,MP
    OHIO,OH
    OKLAHOMA,OK
    OREGON,OR
    PALAU,PW
    PENNSYLVANIA,PA
    PUERTO RICO,PR
    RHODE ISLAND,RI
    SOUTH CAROLINA,SC
    SOUTH DAKOTA,SD
    TENNESSEE,TN
    TEXAS,TX
    UTAH,UT
    VERMONT,VT
    VIRGIN ISLANDS,VI
    VIRGINIA,VA
    WASHINGTON,WA
    WEST VIRGINIA,WV
    WISCONSIN,WI
    WYOMING,WY

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Load contents of a file to datatable

    Just note that, anywhere you're using the result of File.ReadAllLines as an IEnumerable(Of String), it would be more appropriate to use File.ReadLines instead. That assumes .NET 4.0 or above.

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