amma.NETtami

.NET walkabout
posts - 11, comments - 18, trackbacks - 0

OLEDB and text files

This is a very quick example about OLEDB and read a text (.txt) file using Visual Basic 6.
The text file must be formatted as a simple CSV file with a field separator. Something like this

Test.txt

a;1;Test
b;2;Test
c;3;Test
d;4;Test

Supposing "Test.Txt" is stored in the root of the C: harddrive, the code will look like

----------------------------------------

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CreateObject("ADODB.Connection")
cn.Open
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=""text;HDR=No;FMT=Delimited'"""
Set rs = CreateObject("ADODB.Recordset")
rs.Open
"SELECT * FROM Test.txt", cn, 0, 1, 1
While Not rs.EOF
    Debug.Print rs.Fields(
0).Value, rs.Fields(1).Value, rs.Fields(2).Value
    rs.MoveNext
Wend
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

----------------------------------------

Take a look to the connection string

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=""text;HDR=No;FMT=Delimited'"""

the Data Source=c:\; is the key. If you plan to store your text file in a different folde, let's say "c:\documents and settings\Auser\Documents\myTestFiles\", you are required to change the connection string this way:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\documents and settings\Auser\Documents\myTestFiles\;Extended Properties=""text;HDR=No;FMT=Delimited'"""

Print | posted on Sunday, October 7, 2007 8:00 PM | Filed Under [ Visual Basic OLEDB ]

Powered by:
Powered By Subtext Powered By ASP.NET