Lesson 8 : Solution to the question

Question was to modify the code in the last lesson, where

- the comma could be replaced by tab, for making it easy to read

- to add the columns and show the total.

In  the following code, it takes two parameters, first is csv file, and second is col. The same old file is used. The csv file first

C:\scripts\file-io>type sample.csv 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>

The output of the program

C:\scripts\file-io>03-addcols-csv.vbs sample.csv 4 //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 Sum of Col 4 is 2121

And finally the code

Const ReadMode=1 dim fso dim fstext dim txtline dim lines, words dim strword dim Oput dim sums oput=int(wscript.arguments(1)) set fso=CreateObject("Scripting.FileSystemObject") set fstext=fso.OpenTextFile(wscript.arguments(0),ReadMode) sums=0 do until fstext.AtEndOfStream         txtline=fstext.ReadLine         strword=split(txtline,",")         sums=sums+int(strword(oput-1))         txtline= Replace(txtline,",", vbTab)         wscript.echo txtline loop wscript.echo " " wscript.echo "Sum of Col " & oput & " is " & sums

What more can be done.

a. You can validate if the column selected exists and if it does exists is it a numeric value.

b. You can check if the csv file passed as a parameter exists in the first place.

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.

Lesson 7 : Working with Text files

In our routine as an administrator, we routinely work with text files. Let us try to understand how to work with them.

A usual logic would be

- Open the file, Loop line by line, display the contents (or process them)

Example 1:

In this example, we try to display the contents of the file that is passed as a parameter at the command line. I have not done validations and assume that a file name is already there. You can add three validations, first check if there is an argument. Second check if the file exists. Third check if it is a text file.  I have a sample file called “sample.txt” in the same folder as the program.

Const ReadMode=1 dim fso dim fstext dim txtline set fso=CreateObject("Scripting.FileSystemObject") set fstext=fso.OpenTextFile(wscript.arguments(0),ReadMode) do until fstext.AtEndOfStream     txtline=fstext.ReadLine     wscript.echo txtline loop

Let us check the output

C:\scripts\file-io>01-show-contents.vbs sample.txt //nologo The quick fox jumped over the lazy dog. The hare and the turtle were friends in the forest. Early bird gets the worm. C:\scripts\file-io>

Let us make it more useful. At the end, let us say we want to display the total lines in this file.

Const ReadMode=1 dim fso dim fstext dim txtline dim lines lines=0 set fso=CreateObject("Scripting.FileSystemObject") set fstext=fso.OpenTextFile(wscript.arguments(0),ReadMode) do until fstext.AtEndOfStream     txtline=fstext.ReadLine     wscript.echo txtline     lines=lines+1 loop wscript.echo "--------------------------------------end of file" wscript.echo "Lines in the file " & lines

C:\scripts\file-io>01-show-contents.vbs sample.txt //nologo The quick fox jumped over the lazy dog. The hare and the turtle were friends in the forest. Early bird gets the worm. --------------------------------------end of file Lines in the file 4 C:\scripts\file-io>

What if we needed the lines and words.

a. Use the split function, it splits a string and returns an array

b. use the ubound function to check how many elements are there in the array, and add 1

Const ReadMode=1 dim fso dim fstext dim txtline dim lines, words dim strword lines=0 words=0 set fso=CreateObject("Scripting.FileSystemObject") set fstext=fso.OpenTextFile(wscript.arguments(0),ReadMode) do until fstext.AtEndOfStream     txtline=fstext.ReadLine     strword=split(txtline)     words=words+ubound(strword) +1     wscript.echo txtline     lines=lines+1 loop wscript.echo "--------------------------------------end of file" wscript.echo "Lines in the file " & lines wscript.echo "Number of words   " & words

Sample Output of the same file

C:\scripts\file-io>01-show-contents.vbs sample.txt //nologo The quick fox jumped over the lazy dog. The hare and the turtle were friends in the forest. Early bird gets the worm. --------------------------------------end of file Lines in the file 4 Number of words   23 C:\scripts\file-io>

Let us make it more complete. We do the following changes, we pass the parameter filename with a number, 1=show file, 2=show lines, 3=show words, 4=show all

Code

Const ReadMode=1 dim fso dim fstext dim txtline dim lines, words dim strword lines=0 words=0 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)     words=words+ubound(strword) +1     if oput=1 or oput=4 then         wscript.echo txtline     end if     lines=lines+1 loop if oput=1 or oput=4 then     wscript.echo "--------------------------------------end of file" end if if oput=2 or oput=3 or oput=4 then     wscript.echo "Lines in the file " & lines end if if oput=3 or oput=4 then     wscript.echo "Number of words   " & words end if

Various Runs

C:\scripts\file-io>01-show-contents.vbs sample.txt 1 //nologo The quick fox jumped over the lazy dog. The hare and the turtle were friends in the forest. Early bird gets the worm. --------------------------------------end of file C:\scripts\file-io>01-show-contents.vbs sample.txt 2 //nologo Lines in the file 4 C:\scripts\file-io>01-show-contents.vbs sample.txt 3 //nologo Lines in the file 4 Number of words   23 C:\scripts\file-io>01-show-contents.vbs sample.txt 4 //nologo The quick fox jumped over the lazy dog. The hare and the turtle were friends in the forest. Early bird gets the worm. --------------------------------------end of file Lines in the file 4 Number of words   23 C:\scripts\file-io>

We build more on this in the subsequent chapters.

Lesson 6: Functions and Procedures (vbscript)

Functions and Procedures make your code modular. They help you to keep repeated statements as blocks of code that can be reused. Both functions and procedures do the same thing, except, Functions can return value. So as a good practice never let your functions have any statements to print(display) or get input from keyboard.

Let your functions take parameters and return the value. How does a function return a value? Function_name=value you want to return. Enough of theory.

This program finds the value of (a+b) squared.

(a+b)² =a² +2ab+b²

‘File-0105-1.vbs (this is file name I have) Function Sq_ab(a,b)     sq_ab=a*a + 2*a*b + b*b End Function Sub Print(str)     wscript.echo str End Sub 'Main program starts here a=int(wscript.arguments(0)) b=int(wscript.arguments(1)) Print("The Square of (a+b) is " & Sq_ab(a,b))

Sample Output

C:\scripts>0105-1.vbs 2 3 //nologo The Square of (a+b) is 25 C:\scripts>0105-1.vbs 7 9 //nologo The Square of (a+b) is 256 C:\scripts>

Exercises for you.

1. Write functions for (a-b)², a²-b², (a+b)³ and so on