reading a csv file in vbscript

Question

I am using the below function to read a text file which has below format. I need to lookup the id (column1) and return date (column2). I cannot understand what is wrong with my code. So my code runs perfectly till readline, but some how it is not looping. So basically it just checks the incoming consumerSSN with only the first SSN from the txt file. So if I pass getDOH(213396391), it retuns blank(""), instead of its corresponding date.

sraDOH.txt:
578171533,2015-01-01 00:00:00
213396391,2015-06-01 00:00:00
077966385,2015-01-01 00:00:00
216418521,2015-01-01 00:00:00


Function getDOH(consumerSSN)
    dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
    dim doh, t, x
    For Each File In fso.GetFolder(fso.GetAbsolutePathName("..\Test Files\")).Files
        If File.Name = "sraDOH.txt" Then
            Set tsIn2 = fso.OpenTextFile(File, 1)
            Do While Not tsIn2.AtEndOfStream
                doh = tsIn2.ReadLine
                t = split(doh,",")
                If consumerSSN = t(0) Then
                    getDOH = t(1)
                Else 
                    getDOH = ""
                End If
            Loop
        End If
    Next
End Function

Kushal, there are many pitfalls in your code, perhaps contributing to your inability to troubleshoot this. Relative paths, use option explicit. Where is fso being created? Add Wscript.echo's throughout to determine what is running and what is not. VBA many times fails silently and it's a pain. Additionally, theres no example of how the function is being called, so we don't even know if the customerSSN will equal any of the values. Hope this helps. — Dan Chase, Nov 22 '15 at 06:14
I added fso. The path and everything works just fine. Its something logical, which after working on this for 6 hours I cannot see. Was hoping of getting a second pair of eyes. Sorry if it was confusing for you due to lack of information — imba22, Nov 22 '15 at 06:17
Try setting a result variable and breaking, then setting the function name to the result at the end. Perhaps setting the return value is causing the function to exit. — Dan Chase, Nov 22 '15 at 06:19

score 3 · Answer 1 · answered Nov 22 '15 at 07:54

You should exit the loop once you find the value being searched for.

Untested:

Function getDOH(consumerSSN)
    dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
    dim doh, t, x, f

    f = fso.GetAbsolutePathName("..\Test Files\") & "\sraDOH.txt"

    If fso.fileexists(f) then 

        Set tsIn2 = fso.OpenTextFile(File, 1)
        Do While Not tsIn2.AtEndOfStream
            doh = tsIn2.ReadLine
            t = split(doh,",")
            If consumerSSN = t(0) Then
                getDOH = t(1)
                exit do
            End If
        Loop

    End If

End Function

score 2 · Accepted Answer · answered Nov 22 '15 at 13:29

In addition: You should never compare values of different (sub) types in VBScript. So your call getDOH(213396391) is risky; it should be getDOH("213396391") because Split() results in Strings.

Evidence:

>> Function doCmp(v)
>>   s = "213396391,2015-06-01 00:00:00"
>>   t = Split(s, ",")
>>   doCmp = v = t(0)
>> End Function
>> WScript.Echo CStr(doCmp(213396391)), CStr(doCmp("213396391"))
>>
False True

score 0 · Answer 3 · edited Apr 18 '18 at 13:55

The function below is based on a similar function I use to lookup an old product ID and replace it with the product ID for our new system. This also requires that the input file have a header row, such as:

'sraDOH.txt:
'SSN, DOH
'578171533,2015-01-01 00:00:00
'213396391,2015-06-01 00:00:00
'077966385,2015-01-01 00:00:00
'216418521,2015-01-01 00:00:00

Function getDOH(consumerSSN)

Dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
Dim iConnection                 'Connection objects for input
Dim iRecordset                  'RecordSets for input
Dim dDOH                        ' date to look up   
Dim sInputFileDir               ' Directory containing the TXT file to process
Dim sInputTXT                   ' Name of the TXT file to open (without a pathname)

sInputFileDir = fso.GetAbsolutePathName("..\Test Files\")
sInputTXT = "sraDOH.txt"

'
'Open text file
'   

Set iConnection = CreateObject("ADODB.Connection")
Set iRecordset = CreateObject("ADODB.Recordset")

' Open a txt file using ODBC
iConnection.Provider = "MSDASQL"
iConnection.Open "Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" & sInputFileDir & "\;"


iRecordset.Open "SELECT DOH FROM sraDOH.txt Where SSN = "& consumerSSN, _
        iConnection, adOpenStatic, adLockOptimistic, adCmdText

dDOH = iRecordset("DOH")

If IsNull(dDOH) Or dDOH = "" Then
    stdout.WriteLine "ERROR: Bad date on record for: "& consumerSSN
End If

iRecordset.Close
iConnection.Close

getDOH = dDOH

End Function

Here's my original function:

Function setInvoiceLineItemRefListID(dpassedProductID)

Dim iConnection                 'Connection objects for input
Dim iRecordset                  'RecordSets for input
Dim dProductID                  'Foundry Product ID
Dim dListID                     'Quickbooks Online Product ID
Dim sInputFileDir               ' Directory containing the CSV file to process
Dim sInputCSV                   ' Name of the CSV file to open (without a pathname)

' Spreadsheet columns
'productID, sproductAbbrev, sechoDataItemNumber, sQBItemCode, slistID

sInputFileDir = "C:\ThinkwellApps\Programs"

sInputCSV = "products.csv"          'Name of the spreadsheet containing the list of ProductIDs

'
'Open spreadsheet
'   

Set iConnection = CreateObject("ADODB.Connection")
Set iRecordset = CreateObject("ADODB.Recordset")

' Open a CSV file  
iConnection.Provider = "MSDASQL"
iConnection.Open "Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" & sInputFileDir & "\;"


iRecordset.Open "SELECT ListID FROM products.csv Where ProductID = "& dpassedProductID, _
        iConnection, adOpenStatic, adLockOptimistic, adCmdText

dListID = iRecordset("ListID")

If IsNull(dListID) Or dListID = "" Then
    stdout.WriteLine "ERROR: Bad listID for: " & dpassedProductID

iRecordset.Close
iConnection.Close

setInvoiceLineItemRefListID = dListID
End Function

reading a csv file in vbscript

3 Answers3