Marcus' Macro Scheduler Blog

VBScript has a function called IsNumeric, but as discussed in this forum post VBScript numbers can contain the letter ‘E’ (and it would seem ‘D’ too). These are valid numbers as far as VBScript is concerned but in the real world we usually don’t care for them. Numbers may also be floats. So in the forum post we came up with the following VBScript functions for IsNumber and IsInteger:

VBSTART
Function IsNumber(var)
  IsNumber = (LCase(var) = UCase(var)) and isNumeric(var)
End Function

Function IsInteger(var)
  IsInteger = IsNumber(var) and (InStr(var,".") = 0)
End Function
VBEND

Now we have Easy Patterns I thought I’d show you another neat way to validate that a string is numeric:

//IsNumeric?
Let>data=154.3
RegEx>[lineStart][oneOrMore number or "."][lineEnd],data,1,matches,num_matches,0
If>num_matches>0
  //string IS numeric
Endif

If you just want to check the string is integer, change it to:

//IsInteger?
Let>data=154
RegEx>[lineStart][oneOrMore number][lineEnd],data,1,matches,num_matches,0
If>num_matches>0
  //string IS integer
Endif

If we want to see if a string merely contains a number we can do:

//Contains a number?
Let>data=fred 224 sally 4
RegEx>[oneOrMore number],data,1,matches,num_matches,0
If>num_matches>0
  //string CONTAINS number
Endif

An interesting discussion arose in the forums yesterday on how to read data from a CSV file. The usual suggestion is to use Separate to split each line into an array using the comma as the delimiter, as explained in my post here.

The problem, as highlighted in the forum topic, is: what if the line has a field which itself contains a comma? In CSV if a field contains a comma it will be encased in double quotes. E.g.:

Sally,”2,500″,Blue

If we just use Separate using the comma as a delimiter we will end up with the following values:

Sally
“2
500”
Blue

Of course this isn’t what we want. We want to end up with:

Sally
2,500
Blue

In the forum discussion a number of suggestions were made such as to use regular expressions to find the fields inside quotes and replace the commas with something else, then use Separate and then put the commas back. Certainly we could resort to some kind of text parsing and I’ve updated the post with a RegEx solution.

However, we can avoid all this by using DBQuery and an ADO connection string that lets us connect to and query a CSV file and treat it like a database table. All the work is done for us:

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents;Extended Properties='text;HDR=NO;FMT=Delimited'
DBConnect>ConStr,dbH
DBQuery>dbH,select * from test.csv,fields,num_recs,num_fields

In the connection string specify the path of the CSV file in Data Source. We can also tell it whether or not the CSV file has a header line by setting HDR to NO or Yes.

This reads all the data from the CSV file into an array.

Here’s some code to read through the array and display each field one by one:

Let>rec=0
Repeat>rec
  Let>rec=rec+1
  Let>field=0
  Repeat>field
    Let>field=field+1
    Let>this_field=fields_%rec%_%field%
    MessageModal>this_field
  Until>field=num_fields
Until>rec=num_recs

If the CSV file has a header line change HDR to Yes and then we could also tell DBQuery to return the field names:

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents;Extended Properties='text;HDR=NO;FMT=Delimited'
DBConnect>ConStr,dbH
DBQuery>dbH,select * from test.csv,fields,num_recs,num_fields,1

Then we can access the data using the field names. E.g.:

Let>rec=0
Repeat>rec
  Let>rec=rec+1
  Let>name=fields_%rec%_name
  Let>number=fields_%rec%_number
  Let>colour=fields_%rec%_colour
  MessageModal>%name% %number% %colour%
Until>rec=num_recs

For more help with Macro Schedulers database functions see:
Using Macro Scheduler’s Database Functions