How to read delimited and fixed-field data in VBScript with ADO

This FAQ

There are many ways you might go about handling delimited-text files in VBScript. We are talking about readable text files that have commas, tabs, and so on between the fields - along with some sort of row or line delimiter character.

Fixed-field data files are similar. Plain text generally, but instead of having field delimiters the data occurs in each record in columns of pre-determined widths.

This FAQ discusses using ADO and the "Microsoft Text Driver" to handle the dirty work.


Text Driver?

The basic idea is to use ADODB objects with a connection string like:
Connection strings can be tricky, you'll want to be sure you type everything between the "DRIVER=" and the ";DefaultDir" exactly as shown above. An extra blank space here or there or a typo, and it simply won't work.


Data File Format

One of the first things you'll want to ask is "how will the Text Driver know how to interpret my data properly?" This includes the delimiters used, the names of the fields, the data types, and so on.

The Microsoft Text Driver is pretty smart. In the absence of other information it will take a cruise through your data file and make an educated guess. In many cases this works just fine. To some extent its guesses are tempered by a number of registry settings on the machine your script will run on.

In general though you may need more control over the Microsoft Text Driver in order to get just the results you want every time.


Schema.ini

In order to get that control over the way your data files are interpreted you'll need a Schema.ini file. This file must be in the same directory as the text files you want to process. It contains section entries for each file in that directory.

Detailed information on Schema.ini file entries can be found at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp?frame=true

For additional details search the MSDN Library for "Microsoft Text Driver" and "Schema.ini" and you'll find many other articles.


An Example

Sometimes it is best to illustrate a concept with a simple example. Here is a text file called mydata.txt that is tab-delimited.

[color brown]mydata.txt[/color] I've used the symbol above to represent tab characters (vbTab, Chr(9), HT, whatever). The file has only 3 records and each has 2 fields, one text and another integer.

Now I need to create a Schema.ini file in this data file's directory... or make a new entry for mydata.txt if a Schema.ini file already exists. In this case I have a new directory, a new data file, so I'll make a new Schema.ini just to describe this one data file:

[color brown]Schema.ini[/color] We have a section header to identfy the file we're describing. Then we identify the format. Then we say the file doesn't have header record(s) that provide column names. Then we describe each column, assigning a field name and a data type.

All of this and more is described at the MSDN page cited above.


Using Text Driver From a WSH Script

So the files above set the stage. Now how do we use them to accomplish something? This is where those ADODB objects come in.

For a simple case like this all we need is an ADO Recordset object. From WSH I find that ADO programming is much easier if I use the newer, more generalized, and feature rich .WSF script format rather than the older .VBS form of desktop scripting. You don't need to use a .WSF but it has a number of advantages. The two I am taking advantage of here are:
[ul][li]Declarative object syntax (the <object> tag).
[li]Declarative reference syntax (the <reference> tag).[/ul]
These are described in detail in the Microsoft Windows Script reference. You can download this from:

http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/733/msdncompositedoc.xml&frame=true


Sample WSH Script

Enough teasing, the example is only complete if you can see an actual script.

Here I have a WSH script called ADOText.wsf in which I declare an ADO Recordset object that I will use to read the text data file. I also declare a reference to the ADODB type library in order to gain access to the predefined ADO constants. In this case I'm only making use of but in a more meaningful script you would need many other ADO constants.

[color brown]ADOText.wsf[/color] I'm using a function here called ScriptPath( ) to retrieve the path of the running script. Notice how I used this function to build my connection string. I use this here because I have placed the script in the same directory as my data and schema files. If the script were elsewhere I'd need to designate the data directory in some other manner.

The example simply opens an ADO Recordset, displays the number of records, and then displays the data contained in the Recordset. Nothing fancy, just an example. Notice that since I didn't create oRS via CreateObject( ) I also do not have to Set it to Nothing in order to decrement the object reference count. WSH takes care of this automatically for declared objects.

You can copy each of the three files above into appropriately named files in any directory to try this out. Just be sure to replace the "tab" symbols with real tab characters.

To run the script just double-click on ADOText.wsf as usual.


Conclusion

This should be enough to get you started with the Microsoft Text Driver. If you have MS Access you'll find it has a wizard for creating complex Schema.ini files. If you need to handle many files with numerous fields you might want to take a look at it.

A good article on ADO can be found at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacchbk/html/acsybex_chap6.asp?frame=true

This discussion is an introduction to ADO for VB programmers, but most of it will be extremely useful to a VBScripter as well. Even client-side DHTML scripters and ASP scripters should find it enlightening.

The "ADO + Text Driver" approach has many advantages over writing clumsy VBScript code using the FSO and the Split( ) function. Especially when your data contains things like date & time or currency fields.

It is even more handy for processing [color brown]fixed-field data[/color] files! Beats the heck out of doing a bunch of Mid( ) calls over, and over, and over again.

Plus you get the advantage of sorting and filtering if you use client-side cursors!

[color brown]ADOText2.wsf[/color] [color red] [/color] [color red] [/color]