469,286 Members | 2,427 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

Problem with reading delimited textfile '|' with OleDBDataAdapter

'--this code works but only reads text into one column when contains multiple
cols

Dim ds1x As New DataSet
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dir1A\;Extended
Properties=""Text;HDR=No;FMT=Delimited\"""

Dim conn1x As New OleDb.OleDbConnection(ConStr)
Dim dax1 As New OleDbDataAdapter("Select * from testabc1x.txt", conn1x)
dax1.Fill(ds1x, "tbl0")
datagridview1.DataSource = ds1x.Tables("tbl0")

-------------------------------------------------------
The above code works fine for reading text from a textfile and adding it to
a datagridview. My problem is that I have mutiple columns delimited by a
pipe '|', and the code above is not delimiting the text. I get it all in one
column. How to make it delimit the text into multiple columns?

Thanks,
Rich
Jul 2 '07 #1
6 21712
Hi Rich -

It doesn't look like you ever specified your delimiter.

Try changing FMT=Delimited to FMT=Delimited(<delimiter>) or in your
case, FMT=Delimited(|)

Good Luck,

-Mark

Jul 2 '07 #2
thanks for your reply. Anyway, I am using '|' as the delimiter. So I tried

....;Extended Properties=""Text;HDR=No;FMT=Delimited(|)\"""
and
....;Extended Properties=""Text;HDR=No;FMT=Delimited('|')\"""

But no delimiting ensued. However, I created a csv file - by hand - and
that did come in delimited with

....;Extended Properties=""Text;HDR=No;\"""

The only problem is in writing to a csv file.

Dim oWrite As StreamWriter
oWrite = File.AppendText("C:\1A\testabc1x.csv")
oWrite.WriteLine("A" & vbTab & "B")
oWrite.Close()

when I open the csv file, I don't get 2 columns with oWrite.WriteLine("A" &
vbTab & "B"). I used to be able to write VBA code like that, and it would
have 2 columns. Any ideas?

"ma*********@binaryswitch.com" wrote:
Hi Rich -

It doesn't look like you ever specified your delimiter.

Try changing FMT=Delimited to FMT=Delimited(<delimiter>) or in your
case, FMT=Delimited(|)

Good Luck,

-Mark

Jul 2 '07 #3
On Mon, 2 Jul 2007 11:02:04 -0700, Rich <Ri**@discussions.microsoft.comwrote:

'--this code works but only reads text into one column when contains multiple
cols

Dim ds1x As New DataSet
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dir1A\;Extended
Properties=""Text;HDR=No;FMT=Delimited\"""

Dim conn1x As New OleDb.OleDbConnection(ConStr)
Dim dax1 As New OleDbDataAdapter("Select * from testabc1x.txt", conn1x)
dax1.Fill(ds1x, "tbl0")
datagridview1.DataSource = ds1x.Tables("tbl0")

-------------------------------------------------------
The above code works fine for reading text from a textfile and adding it to
a datagridview. My problem is that I have mutiple columns delimited by a
pipe '|', and the code above is not delimiting the text. I get it all in one
column. How to make it delimit the text into multiple columns?

For a column delimiter other than a comma you either need to change a Registry setting or use a
schema.ini file.

[testabc1x.txt]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(|)
Paul
~~~~
Microsoft MVP (Visual Basic)
Jul 3 '07 #4
Thank you for your reply. I have seen several examples on the web suggesting
the same thing as you about using an schema.ini file. This seems the way to
go except that I can't figure out how this file is used. Ex: I write my
OleDBDataAdapter code. I place the schema.ini file in the same directory as
the text file I want to read. How does the schema.ini file get
invoked/implemented in the OleDBDataAdapter code? None of the examples I saw
on the web showed where the schema.ini file was referenced.
"Paul Clement" wrote:
On Mon, 2 Jul 2007 11:02:04 -0700, Rich <Ri**@discussions.microsoft.comwrote:

¤ '--this code works but only reads text into one column when contains multiple
¤ cols
¤
¤ Dim ds1x As New DataSet
¤ Dim ConStr As String = _
¤ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dir1A\;Extended
¤ Properties=""Text;HDR=No;FMT=Delimited\"""
¤
¤ Dim conn1x As New OleDb.OleDbConnection(ConStr)
¤ Dim dax1 As New OleDbDataAdapter("Select * from testabc1x.txt", conn1x)
¤ dax1.Fill(ds1x, "tbl0")
¤ datagridview1.DataSource = ds1x.Tables("tbl0")
¤
¤ -------------------------------------------------------
¤ The above code works fine for reading text from a textfile and adding it to
¤ a datagridview. My problem is that I have mutiple columns delimited by a
¤ pipe '|', and the code above is not delimiting the text. I get it all in one
¤ column. How to make it delimit the text into multiple columns?

For a column delimiter other than a comma you either need to change a Registry setting or use a
schema.ini file.

[testabc1x.txt]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(|)
Paul
~~~~
Microsoft MVP (Visual Basic)
Jul 3 '07 #5
On Tue, 3 Jul 2007 08:04:01 -0700, Rich <Ri**@discussions.microsoft.comwrote:

Thank you for your reply. I have seen several examples on the web suggesting
the same thing as you about using an schema.ini file. This seems the way to
go except that I can't figure out how this file is used. Ex: I write my
OleDBDataAdapter code. I place the schema.ini file in the same directory as
the text file I want to read. How does the schema.ini file get
invoked/implemented in the OleDBDataAdapter code? None of the examples I saw
on the web showed where the schema.ini file was referenced.


It's automatic. The Text ISAM driver will look for the schema.ini file and search for an entry (like
the one I posted in my prior response) that corresponds to the text file that is being opened.
Paul
~~~~
Microsoft MVP (Visual Basic)
Jul 5 '07 #6
Thanks Very much

"Paul Clement" wrote:
On Tue, 3 Jul 2007 08:04:01 -0700, Rich <Ri**@discussions.microsoft.comwrote:

¤ Thank you for your reply. I have seen several examples on the web suggesting
¤ the same thing as you about using an schema.ini file. This seems the way to
¤ go except that I can't figure out how this file is used. Ex: I write my
¤ OleDBDataAdapter code. I place the schema.ini file in the same directory as
¤ the text file I want to read. How does the schema.ini file get
¤ invoked/implemented in the OleDBDataAdapter code? None of the examples I saw
¤ on the web showed where the schema.ini file was referenced.
¤

It's automatic. The Text ISAM driver will look for the schema.ini file and search for an entry (like
the one I posted in my prior response) that corresponds to the text file that is being opened.
Paul
~~~~
Microsoft MVP (Visual Basic)
Jul 6 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Roy | last post: by
1 post views Thread by liwaste | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.