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:
DRIVER={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\Data\MyDir;
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:
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.
mydata.txt Add -> 32 Delete -> 345 Add -> 1
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:
Schema.ini [mydata.txt] Format = TabDelimited ColNameHeader = False Col1 = Action Text Col2 = DataValue Long
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:
Declarative object syntax (the <object> tag).
Declarative reference syntax (the <reference> tag).
These are described in detail in the Microsoft Windows Script reference. You can download this from:
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 adOpenKeyset but in a more meaningful script you would need many other ADO constants.
ADOText.wsf <job> <object id = oRS progid = "ADODB.Recordset"/> <reference object = "ADODB.Recordset"/> <script language = "VBScript"> Function ScriptPath() Dim strSFN
strSFN = WScript.ScriptFullName ScriptPath = Left(strSFN, InStrRev(strSFN, "\") - 1) End Function
Dim sConn, sSource, sResults Const cMyName = "Fetching Text Data With ADO"
sSource = "SELECT * FROM [mydata.txt]" sConn = "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _ "DefaultDir=" & ScriptPath & ";"
MsgBox "Records will be counted on your OK!", _ vbOkOnly, cMyName
'Use of adOpenKeyset is CRITICAL or RecordCount returns -1! oRS.Open sSource, sConn, adOpenKeyset MsgBox "File contains " & CStr(oRS.RecordCount) & _ " records.", vbOkOnly, cMyName
MsgBox "Data will be processed on your OK!", _ vbOkOnly, cMyName
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.
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 fixed-field data 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!
ADOText2.wsf <job> <object id = oRS progid = "ADODB.Recordset"/> <reference object = "ADODB.Recordset"/> <script language = "VBScript"> Function ScriptPath() Dim strSFN
strSFN = WScript.ScriptFullName ScriptPath = Left(strSFN, InStrRev(strSFN, "\") - 1) End Function
Dim sConn, sSource, sResults Const cMyName = "Fetching/Sorting Text Data With ADO"
sSource = "SELECT * FROM [mydata.txt]" sConn = "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _ "DefaultDir=" & ScriptPath & ";"
MsgBox "Records will be counted on your OK!", _ vbOkOnly, cMyName
oRS.CursorLocation = adUseClient 'Sorting. 'Use of adOpenKeyset is CRITICAL or RecordCount returns -1! oRS.Open sSource, sConn, adOpenKeyset MsgBox "File contains " & CStr(oRS.RecordCount) & _ " records.", vbOkOnly, cMyName
MsgBox "Data will be processed on your OK!", _ vbOkOnly, cMyName
Talk To Other Members