×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBScript FAQ

File and Data Processing

How to read delimited and fixed-field data in VBScript with ADO by dilettante
Posted: 12 Mar 03 (Edited 6 Apr 03)

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:

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:

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.

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:

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 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

    sResults = ""
    oRS.MoveFirst
    Do
      sResults = sResults & _
                 "Action: " & oRS.Fields("Action") & _
                 "  Value: " & CStr(oRS.Fields("DataValue")) & _
                 vbNewLine
      oRS.MoveNext
    Loop Until oRS.EOF

    MsgBox sResults, vbOkOnly, cMyName

    oRS.Close
  </script>
</job>

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 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

    sResults = ""
    oRS.Sort = "DataValue ASC"
    oRS.MoveFirst
    Do
      sResults = sResults & _
                 "Action: " & oRS.Fields("Action") & _
                 "  Value: " & CStr(oRS.Fields("DataValue")) & _
                 vbNewLine
      oRS.MoveNext
    Loop Until oRS.EOF

    MsgBox sResults, vbOkOnly, cMyName

    oRS.Close
  </script>
</job>

Happy scripting!


Back to VBScript FAQ Index
Back to VBScript Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close