-
Dec 17th, 2013, 09:02 AM
#1
Thread Starter
Lively Member
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.
-
Dec 17th, 2013, 09:43 AM
#2
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.
-
Dec 17th, 2013, 10:34 AM
#3
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!
-
Dec 17th, 2013, 11:12 AM
#4
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
-
Dec 17th, 2013, 11:59 AM
#5
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
-
Dec 18th, 2013, 01:13 AM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|