How to read text-file using OLE DB provider

[Mika M]

Hi!

I'm trying to read text file like...

"Field1";"Field2";"Field3";"Field4"
"ABCD";"EFGH";"1234";"20051020"
"AABB";"CCDD";"2468";"20051021"
"CCDD";"XXYY";"4321";"20051022"

....using OLE DB-provider. Unfortunately I can't affect in what kind of
form this file is saved

I don't know how to make ConnectionString for this kind of file?

I think it should be something like...

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myFileName &
";Extended Properties='Text;HDR=YES;...'"

....but how to specify in ConnectionString the fields are separated with
";" ?

 

[Cor Ligthert [MVP]]

Mika,

Have a look at this sample on our website.

http://www.vb-tips.com/default.aspx?ID=1b644f6b-aa01-49f6-bc1f-212f9e0de193

Be aware that you have to be in a culture that uses the ; as a csv seperator
(by instance probably complete continental Europe).

I hope this helps,

Cor

 

[Mika M]

Mika,

Have a look at this sample on our website.

http://www.vb-tips.com/default.aspx?ID=1b644f6b-aa01-49f6-bc1f-212f9e0de193

Be aware that you have to be in a culture that uses the ; as a csv seperator
(by instance probably complete continental Europe).

I hope this helps,

Cor

I tried it, but got only first column with data retrieved - so how to
get all the columns retrieved?

I also checked of the "Regional and Language options" that there List
separator is really ;

I'm using english version WinXP Pro with finnish settings.

any more succestions?

 

[Carlos J. Quintero [VB MVP]]

Hi Mika,

Why don't you read the file with a text stream reader of the .NET Framework?
It would be faster and using an OLEDB Provider for that task seems to be a
bit overkill and it adds an extra dependency on the OLEDB components that
can be avoided...

--

Best regards,

Carlos J. Quintero

MZ-Tools: Productivity add-ins for Visual Studio .NET, VB6, VB5 and VBA
You can code, design and document much faster.
Free resources for add-in developers:
http://www.mztools.com

 

[Cor Ligthert [MVP]]

Mika,

The CSV file that you use is a typical non English CSV file (based on comma
as decimal point).

I don't know where that setting is, maybe is it the decimal point setting in
your country settings.

Cor

 

[Mika M]

Carlos J. Quintero [VB MVP] kirjoitti:

Hi Mika,

Why don't you read the file with a text stream reader of the .NET Framework?
It would be faster and using an OLEDB Provider for that task seems to be a
bit overkill and it adds an extra dependency on the OLEDB components that
can be avoided...

Because I already use OLE DB provider to import data from for example
Excel sheets in the same form. Then with only small changes would do it
for the text (CSV) files too.

I'm just trying to make it possible import data directly from CSV-file,
because quite often Excel is needed only to convert data from CSV-file
into desired form, so it would be good if this step could be avoided.

Looks like CSV-file reading using OLE DB provider does not care about
what is the setting in "Regional and Language options" List separator,
because I replaced in CSV-file ";" -> "," and then importing works fine
- but why like this way ???