Dynamic variables by record in a Spreadsheet/CSV File

[tps14334]

I have spent a number of hours searching Google to find some information, but never enough to help me pull together all that I looking to accomplish; so I hope someone here will be able to help shed some light. Really I just need a sample script to work from and should be OK, I think.

What I looking to accomplish is to take an Excel spreadsheet which contains columns for a street address, city, state, and zip code along with a various number of rows for each record to capture each cell's content as a variable to be used in a action script and proceed to the next record to perform the same function again. I am having trouble mounting the file .csv file, and a bigger issue on how to script the loop of dynamic variables for each record.

My only programming experience stems from PHP and MySQL, so any help with this VBScript would be greatly appreciated. Thank you!

 

[SkipVought]

Hi,

What code do you have so far?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tps14334]

So far I have declared there will be variables, mounted the csv file, and turn the array into defined variables. The later two sections of code I actually got from a old post in this forum seen here:

http://www.tek-tips.com/viewthread.cfm?qid=1231007&page=1

Here is my current code:

Sub Main

'********************
'Variables statment
'********************

dim Count, StreetAddress, City, State, Zipcode


'********************
'Mount .csv spreadsheet
'********************

dim fs,objTextFile
fs=CreateObject("Scripting.FileSystemObject")
dim arrStr
set objTextFile = fs.OpenTextFile("test.csv")

Do while NOT objTextFile.AtEndOfStream
arrStr = split(objTextFile.ReadLine,",")
' arrStr is now an array that has each of your fields
' process them, whatever.....
Loop

objTextFile.Close
set objTextFile = Nothing
set fs = Nothing


'********************
'Array variables
'********************

arrStr = Split(objTextFile.ReadLine,",")
strStreetAddress = arrStr(0)
strCity = arrStr(1)
strState = arrStr(2)
strZipcode = arrStr(3)


End Sub

My first problem is syntax and it errors (stated error is "Syntax Error: =") out on the line where the .csv file is called.

My second problem, is that I the array variables may work for the first record, but I don't understand how to make it step down to the next line of the file and capture new information for the variables.

Once these two are solved, the action script to loop through is straight forward.

Thank you again for any help with this little project.

 

[HarleyQuinn]

Something like:

    '********************
    'Variables statment
    '********************

        Dim Count As Integer
        Dim strStreetAddress As String
        Dim strCity As String
        Dim strState As String
        Dim strZipcode As String
        
    
    '********************
    'Mount .csv spreadsheet
    '********************

        Dim fs, objTextFile
        [red]Set[/red] fs = CreateObject("Scripting.FileSystemObject")
        Dim arrStr
        Set objTextFile = fs.OpenTextFile([red]"C:\test.csv"[/red]) ' full path to file
        
        Do While Not objTextFile.AtEndOfStream
            arrStr = Split(objTextFile.ReadLine, ",")
            [red]strStreetAddress = arrStr(0)
            strCity = arrStr(1)
            strState = arrStr(2)
            strZipcode = arrStr(3)[/red]
            
            ' do what you want now with variables as they are populated
            
        Loop ' move to next row in .csv

        objTextFile.Close
        Set objTextFile = Nothing
        Set fs = Nothing

The loop will step through the file for you and the variables will update if you assign them in the loop (as shown in my example).

Once the variables are correctly assigned then you can do with them what you want. Just note that each iteration of the loop the variables will be overwritten with new values so it would be best to process your function within the loop the way this is coded.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[tps14334]

That looks like it would do the trick, however I still get an error when the file is being mounted

Dim fs, objTextFile
Set fs = CreateObject("Scripting.FileSystemObject")

Dim arrStr
Set objTextFile = fs.OpenTextFile("c:\20081209A.csv")

Do While Not objTextFile.AtEndOfStream
     arrStr = Split(objTextFile.ReadLine, ",")
Loop

Same error as before occurring at line 2 of the code above. The CSV file is sitting on on my local machine for the time being, but will be moved to a network drive for deployment; if it matters at all.

Thank you, yet again, for everyone's help!

 

[SkipVought]

Your code, as written above, works for me without error.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tps14334]

So, I am missing something here ... I am going to be running this script in a 3270 Terminal emulator which access a billing system command line to perform specified actions.

I am gathering that the scripting engine of the emulator can not comprehend how (or maybe where) to react to a FSO since that is where I am always getting hung up, no matter what I try.

This same emulator works to read a simple .txt file line by line with the code below for consideration:

Dim file1 as string

f1 = freefile
	
file1 = "\\network drive location\file.txt"
open file1 for input as f1

Can someone explain if my thinking is on the right path for the FSO or not, and how the script above makes any difference to the FSO?

 

[SkipVought]

"So, I am missing something here ... I am going to be running this script in a 3270 Terminal emulator ..."

Thanx for the important info!

You need to check with your Hummingbird, Attachmate etc. software, to determine if the FSO is an available object using CreateObject. Your CODE will be governed by THAT softwares features.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

and another thing. If it were me, I'd run the emulator access from Excel, where the VBA is much more robust and you CAN use the FSO on a text file and scrape the 3270 text.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tps14334]

Any information emulator access from Excel?

 

[SkipVought]

What emulator are you using?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tps14334]

The 3270 Terminal Emulator is what I have to use to run VBA scripts into our billing system.

 

[SkipVought]

I have a 3270 Attachmateemulator.

What's yours?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tps14334]

See the link below for a screen shot of the About window.

http://img143.imageshack.us/img143/39/3270terminalyk3.jpg