Lesson 8 : Working with CSV files

The fun starts working with CSV files.  CSV files are comma separated value, files. Basically that means there are commas between the columns. So who creates them? Almost every application that creates logs, dumps performance data, configuration, etc. Before XML files became the craze CSV ruled the administrator’s world.

Below is a sample CSV file

abcd,12,E_123,300 bcde,13,E_124,301 cdef,14,E_125,302 efgh,15,E_126,303 ghij,16,E_127,304 hijk,17,E_128,305 lmno,18,E_129,306

Let us write a code that can display any column we like. If we pass the parameter 0 (zero) we get the entire file. If we pass a specific number we get that column. If we pass a number more than the columns we show the entire file.

First the code

'Show the selected coloumn Const ReadMode=1 dim fso dim fstext dim txtline dim lines, words dim strword dim Oput oput=int(wscript.arguments(1)) set fso=CreateObject("Scripting.FileSystemObject") set fstext=fso.OpenTextFile(wscript.arguments(0),ReadMode) do until fstext.AtEndOfStream     txtline=fstext.ReadLine     strword=split(txtline,",")     if oput=0 or oput>(ubound(strword)+1) then         wscript.echo txtline     else         wscript.echo strword(oput-1)     end if loop

And now the output

C:\scripts\file-io>02-show-contents.vbs sample.csv 0 //nologo abcd,12,E_123,300 bcde,13,E_124,301 cdef,14,E_125,302 efgh,15,E_126,303 ghij,16,E_127,304 hijk,17,E_128,305 lmno,18,E_129,306 C:\scripts\file-io>02-show-contents.vbs sample.csv 1 //nologo abcd bcde cdef efgh ghij hijk lmno C:\scripts\file-io>02-show-contents.vbs sample.csv 2 //nologo 12 13 14 15 16 17 18 C:\scripts\file-io>02-show-contents.vbs sample.csv 3 //nologo E_123 E_124 E_125 E_126 E_127 E_128 E_129 C:\scripts\file-io>02-show-contents.vbs sample.csv 4 //nologo 300 301 302 303 304 305 306 C:\scripts\file-io>02-show-contents.vbs sample.csv 5 //nologo abcd,12,E_123,300 bcde,13,E_124,301 cdef,14,E_125,302 efgh,15,E_126,303 ghij,16,E_127,304 hijk,17,E_128,305 lmno,18,E_129,306 C:\scripts\file-io>

Some exercises for you to try.

1. Suppose we need the sum of a column (like column 2 or 4) then modify the above code so that it works in the following way.

c:\>progname csvfile col

The output should a tab between the cols and not a csv and a sum below.