Code-a-holic: CSV

Wednesday, January 11, 2006

[VBScript] CSV to variables using Execute

Nico wrote, on microsoft.public.scripting.vbscript:

I need ASP to read the CSV file to read lines 2 on-wards and display it back to the user for verification. Each line has aprx 8 "," that control user properties.

I am looking for each line to be displayed back to the user without the "," so they can verify and write to an access db.

All i am looking for is a simple way to read the text display to the user for acceptance before writing it back to the db.

I wrote a response but thought a fuller treatment was in order, thus the following:

Data, stored in DEMO.CSV

0084,DALTON,ANGELA,01-08-06,15:07:29,16:04:04,00:56:35
0085,CONNELLY,BILLY,01-08-06,15:07:29,16:04:04,00:56:35
0086,LE CHOCOLATE FACTORIE,CHARLEY,01-08-06,15:07:29,16:04:04,00:56:35
0087,INT LIONSDEN,DANIEL,01-08-06,15:07:29,16:04:04,00:56:35
0088,SMITH,EDWINA,01-08-06,15:07:29,16:04:04,00:56:35
0089,L'KING,FREDERICK,01-08-06,15:07:29,16:04:04,00:56:35

Then the code which does nothing other than read the file, store the data in variables for each line of data, and for effect, echoes the first and last names

Dim OPID
Dim LastName
Dim FirstName
Dim CallDate
Dim LogonTime
Dim LogoffTime
Dim WorkHours 

Dim aVars 
aVars = Array( "OPID", "LastName", "FirstName", "CallDate", "LogonTime", "LogoffTime", "WorkHours" ) 

Dim oFSO
Set oFSO = CreateObject( "Scripting.FileSystemobject" )

Const INPUT_FILE = "DEMO.CSV"
Dim oStream
Dim sData
Dim aData

Set oStream = oFSO.OpenTextFile( INPUT_FILE )

oStream.SkipLine
Do While oStream.AtEndOfStream = False
    sData = oStream.ReadLine
    aData = Split( sData, "," )
    For i = 0 To UBound( aData ) 
        Execute aVars( i ) & " = " & Chr( 34 ) & aData( i ) & Chr( 34 ) 
    Next 
    'At this point all the vars referred to in aVars have the values
    'of the current record
    WSCript.Echo FirstName, LastName
Loop

oStream.Close

And here's the results of the run, taken from SciTE's output window

ANGELA DALTON
BILLY CONNELLY
CHARLEY LE CHOCOLATE FACTORIE
DANIEL INT LIONSDEN
EDWINA SMITH
FREDERICK L'KING

A possible extra step would be to read the header line, strips the spaces, build the "Dim" statements and execute them as well. Or maybe, create an instance of "Scripting.Dictionary" and use that. Or ... well, there must be more than two ways to do it, even in VBScript!