Problem with reading delimited textfile '|' with OleDBDataAdapter

[Guest]

'--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

 

[mark.milley]

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

 

[Guest]

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?

 

[Paul Clement]

¤ '--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)

 

[Guest]

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

 

[Paul Clement]

¤ 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)

 

[Guest]

Thanks Very much