Reading Text File Using 'schema.ini' & OLEDB

Reading data structured text file can be tedious. Maybe some splitting ,array creation, filtering or trimming involved. Example, if you have a dataset like below...

File my_txt_db.txt content

WORD|LENGTH|DATE
LOREM|5|03/07/08
IPSUM|5|02/16/08
DOLOR|5|04/19/08
SIT|3|04/16/08
AMET|4|04/01/08
CONSECTETUER|12|04/01/08
ADIPISCING|10|04/17/08
ELIT|4|02/02/08
DONEC|5|01/23/08
ID|2|03/19/08
PEDE|4|03/15/08
FUSCE|5|03/11/08
QUIS|4|04/10/08
MI|2|04/02/08
VESTIBULUM|10|02/20/08
ALIQUET|7|03/28/08
ALIQUAM|7|04/09/08
ERAT|4|01/31/08
VOLUTPAT|8|01/22/08
SED|3|04/23/08


The easiest way is to use OLEDB + Text driver named schema.ini.
By doing this, the data can be pulled using SQL statement which is easy to manipulate. The format of the text file is being determined by schema.ini.

schema.ini content

[my_txt_db.txt]
ColNameHeader=True
Format=Delimited(|)
MaxScanRows=0
CharacterSet=OEM
Col1=WORD Char
Col2=LENGTH Integer
Col3=DATE Date


The schema file name is fixed to schema.ini and it must be in the same directory as the text data source. Detail on this can be found here. You may use VBS example below to test the scripting.

vbs_test.vbs content

Option Explicit
Dim conn, rs

Call INIT_CONN()
Call MSGBOX_DATA()
Call END_CONN()

Function MSGBOX_DATA()
Dim SQL, MSG_BOX_STR
SQL = "select * from my_txt_db.txt"
rs.Open SQL, conn
rs.movefirst
if not (rs.eof and rs.bof) then
while not rs.eof
MSG_BOX_STR = "Word = "& rs("WORD") & chr(13)
MSG_BOX_STR = MSG_BOX_STR & "Length = "& rs("LENGTH") & chr(13)
MSG_BOX_STR = MSG_BOX_STR & "Date = "& rs("DATE")
msgbox MSG_BOX_STR
rs.movenext
wend
end if
rs.Close
End Function

Function INIT_CONN()
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.;" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
End Function

Function END_CONN()
conn.close
Set rs = Nothing
Set conn = Nothing
End Function


Just create these 3 files, place it in the same directory and run the VBS for testing.