Visual Basic: How to Use ADO to Query Against a Text File

As I continue to migrate scripts over to other people for my inevitable departure from my current job, I am moving over old Visual Basic programs to VBScript for easier maintenance. One of the scripts that I am converting is a small one that gets a list of employees, and replaces their Employee ID’s with their SSN. Since, legally, we cannot store SSN information in our database, we only have a small window to grab the list of SSN’s from an encrypted file, decrypt it, and do the swaps, delete the feed, and send off the encrypted results to our vendor.

The script I have here demonstrates how to do the swap. It will query the parent database to get the list of employees, and then query a Text File using ADO. To speed things up, it only runs the query against the SSN file 1 time, and then pulls finds the results using the RecordSet.Find method. As I explaining yesterday, this is faster than having to do a repetitive query, and in this case, the performance gain is warranted. The only thing I kick myself on is I should have created a function for formatting those darn date variables.

There are a few caveats in querying a text file. First, you need the proper Connection string. I tried a few different things to get this to work right. The ODBC way kept giving me errors, so I abandoned it. I ended up with the following Connection String:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TEMP\;Extended Properties="text;HDR=NO;FMT=Delimited"

First, the Data Source points to the directory where the files reside, in my case TEMP. In the extended properties, I am specifying that there is no header row, and the format is delimited. The key to get this to work correctly and recognize fields is in a file that must reside in the same folder as the text files called schema.ini. My ssn.txt file looks like so:

GEID;SSN;FirstName;MiddleName;LastName

As you can see, I have semi-colon delimited fields. In order for this to work, my Schema.ini file looks like so:

[SSN_GEID.txt]
Format = Delimited(;)
Col1=GEID Text
Col2=SSN Text
Col3=FNAME Text
Col4=MNAME Text
Col5=LNAME Text

That’s it. I can now query against this text file from within my script just like it was a database. Below is the final script:

Const GETSSN = "SELECT * From ssn.txt"

    Const GETEMPS = "Query to get List of employees"

    'Variable for output file, usinga temp variab:wle instead of a constant since we need to use todays
    'date in the name
    Dim OUTPUTFILE_LOCATION
    Dim objFSO
    Dim objStream

    'The ADO objects for retrieving the data we need for employees
    Dim adEmpsConnection
    set adEmpsConnection = createobject("ADODB.Connection")
    Dim adEmpsCommand
    set adEmpsCommand = createobject("ADODB.Command")
    Dim adEmpsRS
 
    'ADO for getting SSN
    Dim adSSNConnection
    set adSSNConnection = createobject("ADODB.Connection")
    Dim adSSNCommand
    set adSSNCommand = createobject("ADODB.Command")
    Dim adSSNRS
     
    'X is a temporary counter, plus a few variables to store the numeric day and month
    Dim x
    dim numericMonth
    dim numericDay
 
    'set up the Employees connection
    adEmpsConnection.ConnectionString = "Provider=MSDAORA.1;Password=password;User ID=user;Data Source=tserve;Persist Security Info=True"
    adEmpsConnection.CursorLocation = 3
    adEmpsConnection.Open
 
    'set up the employees command
    adEmpsCommand.CommandType = 1
    adEmpsCommand.CommandText = GETEMPS
    adEmpsCommand.ActiveConnection = adEmpsConnection
     
    'set up the SSN connection
    adSSNConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TEMP\;Extended Properties=""text;HDR=NO;FMT=Delimited"""
    adSSNConnection.CursorLocation = 3
    adSSNConnection.Open
 
    'set up the SSN command for a prepared query
    adSSNCommand.CommandType = 1
    adSSNCommand.CommandText = GETSSN
    adSSNCommand.ActiveConnection = adSSNConnection

    'Get the 2 digit month and 2 digit day to use in our archive filename. VBScript does not include a function for this
    if (len(CStr(Month(Now))) < 2) then
        numericMonth = "0" & CStr(month(now))
    else
    numericMonth = CStr(month(now))
    end if
   'Get the 2 digit day. VBScript does not include a function for this
    if (len(CStr(day(Now))) < 2) then
        numericDay = "0" & CStr(day(now))
    else
    numericday = CStr(day(now))
    end if
    'set file name, append todays date in yyyy-mm-dd format so it will display in Alpha order correctly
    OUTPUTFILE_LOCATION = "C:\temp\feed-" &amp;amp;amp; year(now) & "-" &amp;amp;amp; numericMonth & "-" &amp;amp;amp; numericDay & ".txt"
 
    'open file for output
    'Open OUTPUTFILE_LOCATION For Output As #1
    Set objFSO = createobject("scripting.filesystemobject")
    Set objStream = objFSO.CreateTextFile(OUTPUTFILE_LOCATION, True)
 
    'get the employees who have completed the course and the SSN’s
    Set adEmpsRS = adEmpsCommand.Execute
    set adSSNRS = adSSNCommand.Execute
 
    'Go through the resulting recordset for each employee
    'On Error Resume Next
    Do While (adEmpsRS.EOF <> True)
        'get the employees SSN number using the Already existing recordset and just search to find the result rather than requerying.
    adSSNRS.MoveFirst
    adSSNRS.Find "GEID = '" & adEmpsRS("no_emp") & "'"
        'if no SSN is found, do not insert into file
        If adSSNRS.EOF = False Then
                'Print using Pipe delimited format, using mm-dd-yyyy for year format. First get the date format from our data record, then print
         'out using the objStream.Writeline object
          
        'Get the 2 digit month and 2 digit day to use in our archive filename. VBScript does not include a function for this
            if (len(CStr(Month(adEmpsRS("dt_ch_compdt")))) < 2) then
                numericMonth = "0" & CStr(month(adEmpsRS("dt_ch_compdt")))
            else
            numericMonth = CStr(month(adEmpsRS("dt_ch_compdt")))
            end if

        'Get the 2 digit day. VBScript does not include a function for this
            if (len(CStr(day(adEmpsRS("dt_ch_compdt")))) < 2) then
                numericDay = "0" & CStr(day(adEmpsRS("dt_ch_compdt")))
            else
            numericday = CStr(day(adEmpsRS("dt_ch_compdt")))
            end if

            objStream.Writeline adEmpsRS("nm_emp_last") & "" & adEmpsRS("nm_emp_first") & "" & adSSNRS("ssn") & "" & numericMonth & "-" &amp; numericDay & "-" & year(adEmpsRS("dt_ch_compdt"))  
        End If
     
       'Go to next record and do any pending events
        adEmpsRS.MoveNext
    Loop
 
    'close and free memory and close file
    adEmpsConnection.Close
    adSSNConnection.Close