Text file as a datasource

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hi

I can use a text file as a datasource but am unable to get the datatable to
see the text file as having multiple columns. Everything gets
put into the first column in the datatable. Sample of code and text file
included.

Please help

Regards
Chris

Public Function ImportTextFile(ByVal Path As String, ByVal File As String)
As String

Dim txtConStr As String
Dim txtoleCon As OleDb.OleDbConnection
Dim txtoleAdapter As OleDb.OleDbDataAdapter
Dim txtdataset As DataSet
Dim txtdatarow As DataRow

Try
Dim f As System.IO.File
If f.Exists(Path & "\" & File) Then

'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\FirstRowHasNames
=00 (00=False) (01=True)
txtConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& Path & ";Extended Properties=Text;"
txtoleCon = New OleDb.OleDbConnection(txtConStr)
txtoleCon.Open()
txtoleAdapter = New OleDb.OleDbDataAdapter("Select * from "
& File, txtoleCon)
txtoleAdapter.SelectCommand.ExecuteNonQuery()
txtdataset = New DataSet
txtoleAdapter.Fill(txtdataset, "TextFile")
For Each txtdatarow In txtdataset.Tables(0).Rows
Me.lbRecords.Items.Add(txtdatarow.Item(0))
Next
txtoleCon.Close()
Return "True"
Else
Return "File Not Found"
End If
Catch ex As Exception
Return False
Finally
txtdataset = Nothing
txtoleAdapter = Nothing
txtoleCon = Nothing
End Try

End Function

'Text File Sample
11966 GAS_SMP 2003/11/27 17:06:00 000 0.0
11966 GAS_SMP 2003/11/27 17:07:00 000 0.0
11966 GAS_SMP 2003/11/27 17:08:00 000 0.0
11966 GAS_SMP 2003/11/27 17:09:00 000 0.0
11966 GAS_SMP 2003/11/27 17:10:00 000 0.0
11966 GAS_SMP 2003/11/27 17:11:00 000 0.0
11966 GAS_SMP 2003/11/27 17:12:00 000 0.0
11966 GAS_SMP 2003/11/27 17:13:00 000 0.0
11966 GAS_SMP 2003/11/27 17:14:00 000 0.0
11966 GAS_SMP 2003/11/27 17:15:00 000 0.0
11966 GAS_SMP 2003/11/27 17:16:00 000 0.0
11966 GAS_SMP 2003/11/27 17:17:00 000 0.0
 
Post your code and a section of the text file for input.

Regards - OHM

Hi

I can use a text file as a datasource but am unable to get the
datatable to see the text file as having multiple columns. Everything
gets
put into the first column in the datatable. Sample of code and text
file included.

Please help

Regards
Chris

Public Function ImportTextFile(ByVal Path As String, ByVal File As
String) As String

Dim txtConStr As String
Dim txtoleCon As OleDb.OleDbConnection
Dim txtoleAdapter As OleDb.OleDbDataAdapter
Dim txtdataset As DataSet
Dim txtdatarow As DataRow

Try
Dim f As System.IO.File
If f.Exists(Path & "\" & File) Then

'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\FirstRowHasNames
=00 (00=False) (01=True)
txtConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & Path & ";Extended Properties=Text;"
txtoleCon = New OleDb.OleDbConnection(txtConStr)
txtoleCon.Open()
txtoleAdapter = New OleDb.OleDbDataAdapter("Select *
from " & File, txtoleCon)
txtoleAdapter.SelectCommand.ExecuteNonQuery()
txtdataset = New DataSet
txtoleAdapter.Fill(txtdataset, "TextFile")
For Each txtdatarow In txtdataset.Tables(0).Rows
Me.lbRecords.Items.Add(txtdatarow.Item(0))
Next
txtoleCon.Close()
Return "True"
Else
Return "File Not Found"
End If
Catch ex As Exception
Return False
Finally
txtdataset = Nothing
txtoleAdapter = Nothing
txtoleCon = Nothing
End Try

End Function

'Text File Sample
11966 GAS_SMP 2003/11/27 17:06:00 000 0.0
11966 GAS_SMP 2003/11/27 17:07:00 000 0.0
11966 GAS_SMP 2003/11/27 17:08:00 000 0.0
11966 GAS_SMP 2003/11/27 17:09:00 000 0.0
11966 GAS_SMP 2003/11/27 17:10:00 000 0.0
11966 GAS_SMP 2003/11/27 17:11:00 000 0.0
11966 GAS_SMP 2003/11/27 17:12:00 000 0.0
11966 GAS_SMP 2003/11/27 17:13:00 000 0.0
11966 GAS_SMP 2003/11/27 17:14:00 000 0.0
11966 GAS_SMP 2003/11/27 17:15:00 000 0.0
11966 GAS_SMP 2003/11/27 17:16:00 000 0.0
11966 GAS_SMP 2003/11/27 17:17:00 000 0.0
Click to expand...

Best Regards - OHMBest Regards - OHM (e-mail address removed)
 
Thats REALLY WEIRD !!!, I could not see this on the previous post, But I see
in here ;-\

OK, i'll look.

OHM
ohm,

I can see the code and sample text source. It's part of the message.

"One Handed Man [ OHM ]"
Post your code and a section of the text file for input.

Regards - OHM
Click to expand...
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\FirstRowHasNames
Best Regards - OHMBest Regards - OHM (e-mail address removed)
Click to expand...
Click to expand...

Best Regards - OHMBest Regards - OHM (e-mail address removed)
 
"Thats REALLY WEIRD !!!"--yeah, that's a good way to describe it. I've seen
some STRANGE things in the past too.

One Handed Man said:
Thats REALLY WEIRD !!!, I could not see this on the previous post, But I see
in here ;-\

OK, i'll look.

OHM
ohm,

I can see the code and sample text source. It's part of the message.

"One Handed Man [ OHM ]"
Post your code and a section of the text file for input.

Regards - OHM


Chris wrote:
Hi

I can use a text file as a datasource but am unable to get the
datatable to see the text file as having multiple columns.
Everything gets
put into the first column in the datatable. Sample of code and text
file included.

Please help

Regards
Chris

Public Function ImportTextFile(ByVal Path As String, ByVal File As
String) As String

Dim txtConStr As String
Dim txtoleCon As OleDb.OleDbConnection
Dim txtoleAdapter As OleDb.OleDbDataAdapter
Dim txtdataset As DataSet
Dim txtdatarow As DataRow

Try
Dim f As System.IO.File
If f.Exists(Path & "\" & File) Then
Click to expand...
Click to expand...
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\FirstRowHasNames
Best Regards - OHMBest Regards - OHM (e-mail address removed)
Click to expand...
 
Anyway,
The first problem you have is that the file will not parse
on spaces, you need to delimit the file with commas. That way at least the
data rows will then be split. You can see this if you breakpoint your loop
and look at the datarow variable.

After that, its a matter of getting the data into a control. I'm not sure if
listbox ( if thats what you are using ) is the best for this. I would have
used a datagrid and bound it to the dataset personally. Much easier.

Regards - OHM



"Thats REALLY WEIRD !!!"--yeah, that's a good way to describe it.
I've seen some STRANGE things in the past too.

"One Handed Man [ OHM ]"
Thats REALLY WEIRD !!!, I could not see this on the previous post,
But I see in here ;-\

OK, i'll look.

OHM
ohm,

I can see the code and sample text source. It's part of the
message.

"One Handed Man [ OHM ]"
Post your code and a section of the text file for input.

Regards - OHM


Chris wrote:
Hi

I can use a text file as a datasource but am unable to get the
datatable to see the text file as having multiple columns.
Everything gets
put into the first column in the datatable. Sample of code and
text file included.

Please help

Regards
Chris

Public Function ImportTextFile(ByVal Path As String, ByVal File As
String) As String

Dim txtConStr As String
Dim txtoleCon As OleDb.OleDbConnection
Dim txtoleAdapter As OleDb.OleDbDataAdapter
Dim txtdataset As DataSet
Dim txtdatarow As DataRow

Try
Dim f As System.IO.File
If f.Exists(Path & "\" & File) Then
Click to expand...
Click to expand...
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\FirstRowHasNames
Best Regards - OHMBest Regards - OHM (e-mail address removed)
Click to expand...
Click to expand...

Best Regards - OHMBest Regards - OHM (e-mail address removed)
 
¤ Hi
¤
¤ I can use a text file as a datasource but am unable to get the datatable to
¤ see the text file as having multiple columns. Everything gets
¤ put into the first column in the datatable. Sample of code and text file
¤ included.
¤

<snip>

¤
¤ 'Text File Sample
¤ 11966 GAS_SMP 2003/11/27 17:06:00 000 0.0
¤ 11966 GAS_SMP 2003/11/27 17:07:00 000 0.0
¤ 11966 GAS_SMP 2003/11/27 17:08:00 000 0.0
¤ 11966 GAS_SMP 2003/11/27 17:09:00 000 0.0
¤ 11966 GAS_SMP 2003/11/27 17:10:00 000 0.0
¤ 11966 GAS_SMP 2003/11/27 17:11:00 000 0.0
¤ 11966 GAS_SMP 2003/11/27 17:12:00 000 0.0
¤ 11966 GAS_SMP 2003/11/27 17:13:00 000 0.0
¤ 11966 GAS_SMP 2003/11/27 17:14:00 000 0.0
¤ 11966 GAS_SMP 2003/11/27 17:15:00 000 0.0
¤ 11966 GAS_SMP 2003/11/27 17:16:00 000 0.0
¤ 11966 GAS_SMP 2003/11/27 17:17:00 000 0.0
¤

Custom field delimited files require a schema.ini file. Since your fields are space delimited:

[Order.txt]
ColNameHeader=False
Format=Delimited( )
CharacterSet=ANSI

The schema.ini file resides in the same location at your text file.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
-----Original Message-----
Hi

I can use a text file as a datasource but am unable to get the datatable to
see the text file as having multiple columns. Everything gets
put into the first column in the datatable. Sample of code and text file
included.

Please help

Regards
Chris

Public Function ImportTextFile(ByVal Path As String, ByVal File As String)
As String

Dim txtConStr As String
Dim txtoleCon As OleDb.OleDbConnection
Dim txtoleAdapter As OleDb.OleDbDataAdapter
Dim txtdataset As DataSet
Dim txtdatarow As DataRow

Try
Dim f As System.IO.File
If f.Exists(Path & "\" & File) Then

'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0 \Engines\Text\FirstRowHasNames
=00 (00=False) (01=True)
txtConStr
Click to expand...
= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& Path & ";Extended Properties=Text;"
txtoleCon = New OleDb.OleDbConnection (txtConStr)
txtoleCon.Open()
txtoleAdapter = New OleDb.OleDbDataAdapter ("Select * from "
& File, txtoleCon)
txtoleAdapter.SelectCommand.ExecuteNonQuery()
txtdataset = New DataSet
txtoleAdapter.Fill(txtdataset, "TextFile")
For Each txtdatarow In txtdataset.Tables (0).Rows
(0))
Next
txtoleCon.Close()
Return "True"
Else
Return "File Not Found"
End If
Catch ex As Exception
Return False
Finally
txtdataset = Nothing
txtoleAdapter = Nothing
txtoleCon = Nothing
End Try

End Function

'Text File Sample
11966 GAS_SMP 2003/11/27 17:06:00 000 0.0
11966 GAS_SMP 2003/11/27 17:07:00 000 0.0
11966 GAS_SMP 2003/11/27 17:08:00 000 0.0
11966 GAS_SMP 2003/11/27 17:09:00 000 0.0
11966 GAS_SMP 2003/11/27 17:10:00 000 0.0
11966 GAS_SMP 2003/11/27 17:11:00 000 0.0
11966 GAS_SMP 2003/11/27 17:12:00 000 0.0
11966 GAS_SMP 2003/11/27 17:13:00 000 0.0
11966 GAS_SMP 2003/11/27 17:14:00 000 0.0
11966 GAS_SMP 2003/11/27 17:15:00 000 0.0
11966 GAS_SMP 2003/11/27 17:16:00 000 0.0
11966 GAS_SMP 2003/11/27 17:17:00 000 0.0


.
Another way (maybe more laborious) is to read in each
Click to expand...
line of text as a string,
use the SPLIT command with a space delimiter to break into
substrings,
then assign relevant locations in the substring array to
the relevant collumns.

when you construct your dataset call add method to place a
new table and collumns or create it from a schema which
allows fields which correspond to your expected data.
 
Chris said:
Hi

I can use a text file as a datasource but am unable to get the datatable to
see the text file as having multiple columns. Everything gets
put into the first column in the datatable. Sample of code and text file
included.

Please help

Regards
Chris

Public Function ImportTextFile(ByVal Path As String, ByVal File As String)
As String

Dim txtConStr As String
Dim txtoleCon As OleDb.OleDbConnection
Dim txtoleAdapter As OleDb.OleDbDataAdapter
Dim txtdataset As DataSet
Dim txtdatarow As DataRow

Try
Dim f As System.IO.File
If f.Exists(Path & "\" & File) Then

'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\FirstRowHasNames
=00 (00=False) (01=True)
txtConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& Path & ";Extended Properties=Text;"
txtoleCon = New OleDb.OleDbConnection(txtConStr)
txtoleCon.Open()
txtoleAdapter = New OleDb.OleDbDataAdapter("Select * from "
& File, txtoleCon)
txtoleAdapter.SelectCommand.ExecuteNonQuery()
txtdataset = New DataSet
txtoleAdapter.Fill(txtdataset, "TextFile")
For Each txtdatarow In txtdataset.Tables(0).Rows
Me.lbRecords.Items.Add(txtdatarow.Item(0))
Next
txtoleCon.Close()
Return "True"
Else
Return "File Not Found"
End If
Catch ex As Exception
Return False
Finally
txtdataset = Nothing
txtoleAdapter = Nothing
txtoleCon = Nothing
End Try

End Function

'Text File Sample
11966 GAS_SMP 2003/11/27 17:06:00 000 0.0
11966 GAS_SMP 2003/11/27 17:07:00 000 0.0
11966 GAS_SMP 2003/11/27 17:08:00 000 0.0
11966 GAS_SMP 2003/11/27 17:09:00 000 0.0
11966 GAS_SMP 2003/11/27 17:10:00 000 0.0
11966 GAS_SMP 2003/11/27 17:11:00 000 0.0
11966 GAS_SMP 2003/11/27 17:12:00 000 0.0
11966 GAS_SMP 2003/11/27 17:13:00 000 0.0
11966 GAS_SMP 2003/11/27 17:14:00 000 0.0
11966 GAS_SMP 2003/11/27 17:15:00 000 0.0
11966 GAS_SMP 2003/11/27 17:16:00 000 0.0
11966 GAS_SMP 2003/11/27 17:17:00 000 0.0
Click to expand...

Hmm... Do you really need to access your file by ado.net? If there is no
others constraints, I think you would get a better result with this code :


'Open a stream reader to your text file
Dim sr As New IO.StreamReader("C:\file.txt")

Dim text As String = sr.ReadToEnd 'If your text is large, you might
prefer to read it line by line

'Close stream reader
sr.Close()

'Split your text into lines
Dim lines() As String = text.Split(vbLf) 'Depending on your original
file format, you might need to split on vbCR, or vbCrLf

Dim i As Integer
For i = 0 To lines.Length - 1

'Split your line into values
Dim values() As String = lines(i).Split(" ")

'Create your datarow
Dim row As DataRow = datatable.NewRow

'Set its values
row.Item("column1") = values(0)
row.Item("column1") = values(1)
row.Item("column1") = values(2)
row.Item("column1") = values(3)
row.Item("column1") = values(4)

'Add it to table
datatable.Rows.Add(row)

Next


HTH

Guillaume Babin-Tremblay
 
Back
Top