Access data in a commasep. text file

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I need to write a program that accesses data in
commaseparated text file. Instead of just reading the
lines and chopping out the text in field values, I
thought that I could use ODBC to just access it as a
table.

I have created a File-DSN for the directory where the
file is located. A specific file cannot be referenced in
the DSN entry. I guess that the individual files are
considered tables for such an entry.

I have then selected an ODBC connection (ODBC connection
1)from the dataobjects tab and have set its
ConnectionString property to the DSN entry:
"SafeTransactions=0;MaxScanRows=8;DefaultDir=F:\Visual
Studio Projects\Nøgleta" & _
"l - Dataopdatering\Test Data;FILEDSN=F:\Visual
Studio Projects\Nøgletal -" & _
" Dataopdatering\Statoil Data\Test
Data.dsn;DriverId=27;UID=admin;UserCommitSy" & _
"nc=Yes;FIL=text;PageTimeout=5;Driver={Microsoft
Text Driver (*.txt; *.csv)};MaxB" & _
"ufferSize=2048;Threads=3"

(this was generated by the VB when i chose new connection
and pointed to the File-DSN)

However, I don't seem to be able to get the Dataadapter
working:

Dim dadapKunde1 As Odbc.OdbcDataAdapter

Dim str_sql As String = "SELECT * FROM Kunddat1.txt"

dadapKunde1 = New Odbc.OdbcDataAdapter(str_sql,
OdbcConnection1)
dadapKunde1.Fill(datsetKunde1, "Kunddat1.txt")

When I run the code there is an error in the last
statement:
"An unhandled exception of
type 'System.ArgumentNullException' occurred in
system.data.dll
Additional information: A Value may not be null"

Perhaps i am handling the reference to the file
(Kunddat1.txt) wrongly. I just don't see where to
reference it otherwise. The DSN entry only points to the
directory. But it should be possible.

Any help is appreciated.


With kind regards,

Frank
 
I need to write a program that accesses data in
commaseparated text file. Instead of just reading the
lines and chopping out the text in field values, I
thought that I could use ODBC to just access it as a
table.

I have created a File-DSN for the directory where the
file is located. A specific file cannot be referenced in
the DSN entry. I guess that the individual files are
considered tables for such an entry.

I have then selected an ODBC connection (ODBC connection
1)from the dataobjects tab and have set its
ConnectionString property to the DSN entry:
"SafeTransactions=0;MaxScanRows=8;DefaultDir=F:\Visual
Studio Projects\N gleta" &
"l - Dataopdatering\Test Data;FILEDSN=F:\Visual
Studio Projects\N gletal -" &
" Dataopdatering\Statoil Data\Test
Data.dsn;DriverId=27;UID=admin;UserCommitSy" &
"nc=Yes;FIL=text;PageTimeout=5;Driver={Microsoft
Text Driver (*.txt; *.csv)};MaxB" &
"ufferSize=2048;Threads=3"
<<

Have you tried using the Jet OLEDB provider instead?

Dim TextConnectionString As String
TextConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\TestData" & ";" & _
"Extended Properties=""Text;HDR=NO;"""
Dim TextConn As New System.Data.OleDb.OleDbConnection(TextConnectionString)
TextConn.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from test.txt", TextConn)

Dim ds As DataSet = New DataSet("CSVFiles")
da.Fill(ds, "test.txt")
'....
TextConn.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
¤ I still get trouble. I am now using the code:
¤
¤ Const str_datafile As String = "F:\T"
¤
¤ Private connKunde1 As OleDb.OleDbConnection
¤ Private dadapKunde1 As OleDb.OleDbDataAdapter
¤ Private dacmdKunde1 As OleDb.OleDbCommand
¤ Private datsetKunde1 As DataSet
¤
¤ Private Sub form_datatest_Load(ByVal sender As
¤ System.Object, ByVal e As System.EventArgs) Handles
¤ MyBase.Load
¤
¤ Dim str_sql As String = "SELECT * FROM
¤ KunddatTest.txt"
¤
¤ connKunde1 = New OleDb.OleDbConnection
¤ ("Provider=Microsoft.Jet.OLEDB.4.0; " _
¤ & "Data Source=" & str_datafile & ";" _
¤ & "Extended
¤ Properties=""Text;HDR=YES;FMT=Delimited""")
¤ connKunde1.Open()
¤
¤ dadapKunde1 = New OleDb.OleDbDataAdapter(str_sql,
¤ connKunde1)
¤ dadapKunde1.TableMappings.Add
¤ ("Table", "KunddatTest.txt")
¤
¤ datsetKunde1 = New DataSet
¤
¤ ' line with error
¤ dadapKunde1.Fill(datsetKunde1, "KunddatTest.txt")
¤
¤ Me.datagrid_kunde_1.DataSource = datsetKunde1.Tables
¤ ("KunddatTest.txt")
¤
¤ End Sub
¤
¤ The error is: "An unhandled exception of
¤ type 'System.Data.OleDb.OleDbException' occurred in
¤ system.data.dll"
¤
¤ In the directory F:\T I have a Schema.ini file:
¤
¤ [KunddatTest.txt]
¤ ColNameHeader=True
¤ Format=CSVDelimited
¤ MaxScanRows=100
¤ CharacterSet=ANSI
¤
¤ What is the problem here?
¤


On which line of code does the error occur? Is there any more detailed error information available?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top