As you probably know Macro Scheduler scripts can include Microsoft VBScript. Not only can you CALL VBScript code you can also pass values into it and retrieve values out of it (I’ve seen many competitors claim you can use VBScript in their macros when what they really mean is you can only call external VBScripts and not get results and data back).

This means you can take VBScript code and embed it into your macros. However, you may need to make some small modifications to VBScript code samples you find out there in the wild, and I’m often being asked how to do this.

Usually the only changes necessary involve the fact that many sample scripts are designed to run as standalone .VBS files and make use of the WScript object. This object is instantiated automatically by the Windows Scripting Host and offers methods like “Echo” and “Sleep” which you’ll often see in sample scripts. But in the case of Macro Scheduler VBScript is being hosted by Macro Scheduler, not the Windows Scripting Host, so WScript is not available.

Therefore anything starting with WScript will need to be removed or replaced with something else.

The most common one is WScript.Echo. This simply displays a message. When the VBS file is run on the command line the message is output to the command line. Otherwise it appears in a pop up message box. Most of the time in sample scripts it is just there as an example, so you can see the code working. You’d probably end up not wanting the script popping lots of message boxes anyway. But if you do you could replace it with MsgBox.

So, remove references to WScript and you should find you’re good to go.

Tweet This

In More on Reading and Writing CSV Files and various other posts I have demonstrated how CSV data can be read via a SQL SELECT statement using DBQuery.

In order to do this we used an ADO connection string in the DBConnect line:

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents;Extended Properties='text;HDR=NO;FMT=Delimited'
DBConnect>ConStr,dbH

“Proper” CSV files use the double quote character to delimit strings. But there are many CSV files that do not contain these delimiters. When string delimiters aren’t used this should normally mean that the field is numeric, but that may not always be the case. So ADO sometimes has to take a guess as to whether a column is a string or a number. By default it does this by looking at the first 8 rows of data. This may not always be enough.

Watch what happens if we try to read in this file, which contains IP addresses:

abc.pluto.server.com,18.140.1.2
zxy.saturn.server.com,18.120.2.1
fyg.pluto.server.com,18.5.6.2

We’ll read it in with:

DBQuery>dbH,SELECT * FROM servers.csv,fields,num_recs,num_fields

Something weird happens. Look in the watch list and you’ll see the IP addresses have become:

18.1401
18.1202
18.562

ADO has decided that the IP addresses are numbers. It didn’t see any quote marks and after analysing the rows decided they are meant to be numbers. Bonus points go to anyone who can tell me why it has transformed the numbers in that way.

As mentioned before, ADO looks at the first 8 rows. If more of those 8 are more obviously strings, or contain string delimiters, it will treat them as strings. Try delimiting just one row of the above three. You’ll still get numbers. Delimit two and the problem is solved. Where the data type is mixed it will choose the type of the majority.

But we can’t go editing the CSV files to correct them.

What we can do is use a Schema.ini file. Using a Schema.ini file we can tell ADO what data types should be used for each column. So for our Servers.csv example above we could make a Schema.ini file as follows:

[Servers.csv]
Col1=ServerName Text
Col2=IPAddress Text

This file should be in the same folder as the CSV file.

Now, when we run our query we get the correct values, because we’ve forced ADO to treat the columns as text. Another advantage of this is that we have given our columns names, which is useful because our file doesn’t have a header column. This means we could set the Fieldnames parameter of our DBQuery line to 1 to return our array using the field names.

For more information on Schema.ini options for text files see:
http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

Most of the time you’ll probably be working with CSV files that you know about, so manually creating a schema.ini file once is no issue. But you could have the macro create it for you using WriteLn and/or EditIniFile.

What if your macro needs to read in a CSV file and you don’t already know how many fields there are, but you want to force everything to text? Well, how about doing this:

//Create schema.ini file
DBQuery>dbH,select * from servers.csv,fields,num_recs,num_fields,0
WriteLn>%SCRIPT_DIR%\schema.ini,res,[servers.csv]
Let>k=0
Repeat>k
  Let>k=k+1
  EditIniFile>%SCRIPT_DIR%\schema.ini,servers.csv,Col%k%,Col%k% Text
Until>k=num_fields

//now requery using schema.ini file
DBQuery>dbH,select * from servers.csv,fields,num_recs,num_fields,1
...

So this code uses a DBQuery to get the column count, and creates a schema.ini with each column called Coln where n is the column number and setting it to Text.

Side note: There’s another option called MaxScanRows which is supposed to help by telling ADO how many rows to scan, so that instead of looking at only 8 rows you can tell it to look at more, or less. According to the docs setting it to zero will cause it to scan all rows. Given that in our IP address experiment it took the IP addresses as numbers and we only had 3 which is less than the default 8 rows, I would have expected that setting MaxScanRows to 0 would make no difference. In fact it causes the IP addresses to come back as Text. So it seems to solve our initial problem., but I’m not sure why. Googling MaxScanRows reveals a number of forum and news group posts where others have had problems and there seems to be some confusion over exactly what it does. If we use MaxScanRows without ColNameHeader it also causes ADO to see the first line as a header line. And then setting ColNameHeader to false, while ensuring the first line is not seen as a header, brings back the original problem, where the IP addresses are converted to numbers. All seems a bit strange. Hence my preference for explicitly setting the data type of each column.

Tweet This

If you have a database table with an auto-generated numerical index field, you may need to retrieve the value of this field after an INSERT statement.

In Microsoft Access such a field uses an “AutoNumber” data type. In MySQL it’s known as an “auto_increment” field.

We often see these types of fields where a relationship exists between two tables, for example between a “customers” table and “customer_orders” table. The “customers” table may have an AutoNumber field which the system automatically increments when a record is inserted, and in the “customer_orders” table you’ll see a “CustomerID” field which uses this same value to map one or more orders to one customer.

It’s a convenient way to have the database itself automatically create a unique identifier.

If you ever need to use Macro Scheduler to transfer data into such a database you can use the built-in database functions to handle the INSERTS. But when it comes to inserting the data into the second table you’re going to need a way to get the ID that was automatically generated for the first.

Some database technologies give you a function to do just that. MySQL has a function called LAST_INSERT_ID() which you can call with a SELECT statement:

DBExec>dbID,INSERT into customers (name,address1,address2) VALUES ("Mr Customer","..",".."),res
DBQuery>dbID,SELECT LAST_INSERT_ID(),last_id,nr,nf,0

This will return the ID in LAST_ID_1_1 and you can then use it in your next INSERT for the detail records.

See: MySQL: last_insert_id

Microsoft Access does the same with a function called @@identity:

DBExec>dbID,INSERT into customers (name,address1,address2) VALUES ("Mr Customer","..",".."),res
DBQuery>dbID,SELECT @@identity,last_id,nr,nf,0

See: Retrieving Identity or Autonumber Values

Your particular flavour of database might have something similar. If not you could take a risk and do this:

DBQuery>mdbH,SELECT TOP 1 ID FROM customers ORDER BY ID DESC,last_id,nr,nf,0

This will return the largest ID – the latest one. But if you’re working on a database that has many users updating it simultaneously there’s a danger you could retrieve the ID generated by someone else’s INSERT. If it’s just you, then it’s probably OK, but you’ve been warned.

For more information on Macro Scheduler’s database functions see:
Using Macro Scheduler’s Database Functions

Tweet This

A quick and easy way to remove HTML tags from a string:

Let>Html=
Hello World
Test link

Let>pattern=<(.|\n)*?>
RegEx>pattern,Html,0,matches,numMatches,1,,plainText

MessageModal>plainText

Tweet This

I was recently asked how to get a list of files from a folder, and then randomly shuffle that list. The following code retrieves the list of files to an array using GetFileList and Separate and then loops through the array shuffling it randomly:

GetFileList>c:\docs\*.*,file_list
Separate>file_list,;,files_array
If>files_array_count>0
  //randomize the array
  Let>k=0
  Repeat>k
    Let>k=k+1
    Random>{%files_array_count%-1},random_index
    Let>random_index=random_index+1
    If>random_index<>k
        Let>Temp=files_array_%k%
        Let>files_array_%k%=files_array_%random_index%
        Let>files_array_%random_index%=Temp
    Endif
  Until>k=files_array_count
Endif

Edit: As pointed out in the comments this is somewhat naive and could cause an element to be swapped back to its original position. A better method is given in the comments.

Tweet This

More on Reading from CSV Files

In Reading from CSV Files I showed you how you could read an entire CSV file into an array of rows and columns. It used a SELECT * SQL statement to retrieve the entire table.

If you just want to read in a specific set of data you could use a more selective SQL statement. But before we can do that we need to make a slight change to the connection string. The connection string used in my last article includes the text HDR=NO which tells the database driver that the CSV file does not have a header record. If we want to be selective in what data we wish to retrieve we would need to know how to reference that data. So we would need a header record. The following example CSV file starts with a header record:

forename,age,fav_color
“sally”,28,blue
“fred”,32,green
“john”,28,yellow

We now have a way to identify each column of data. We can now change our connection string to use HDR=YES. So our code to connect to the database becomes:

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents;Extended Properties='text;HDR=YES;FMT=Delimited'
DBConnect>ConStr,dbH

We could now create a selective query. For example to select all people who are 28 years old we would use the following SQL:

SELECT * FROM csvfile.csv WHERE age=28

That would retrieve all fields. We may just want to retrieve their names:

SELECT forename FROM csvfile.csv WHERE age=28

In Macro Scheduler the code to do this is:

Let>SQL=SELECT forename FROM csvfile.csv WHERE age=28
DBQuery>dbH,SQL,fields,num_recs,num_fields

We then get an array called fields containing the results. See Reading from CSV files for example code that loops through the results.

Writing to CSV Files

As I mentioned in Reading from CSV Files, CSV files are just text files, so you could just use the WriteLn function to add a record of data to the end of the file:

WriteLn>”sally”,28,”blue”

But if you only want to add specific fields to the end of the line and want to save yourself the bother of having to correctly delimit the text you might want to treat the CSV file as a database table and use Macro Scheduler’s database functions instead.

In Reading from CSV Files we looked at using an ADO connection string and DBQuery to connect to the CSV file as if it were a database table and then using SQL to pull out the data. Well, we can do the same thing and use SQL “Update” and “Insert” queries in order to modify and insert data into the CSV file.

Here’s the code we used to connect to the CSV file as a data source:

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents;Extended Properties='text;HDR=NO;FMT=Delimited'
DBConnect>ConStr,dbH

To insert a row of data to the end of the file we can use the SQL INSERT statement as follows:

INSERT INTO csvfile.csv VALUES (”george”,19,”purple”)

In Macro Scheduler we need to use the DBExec command to execute this SQL:

Let>SQL=INSERT INTO csvfile.csv VALUES ("george",19,"purple")
DBExec>dbH,SQL,res

If you look at the connection string we used you’ll see the directive HDR=NO. This tells the database driver that the CSV file does NOT contain a header record. If we DO have a header record we should change this to HDR=YES. This also means we can specify individual column names in the INSERT statement. E.g. we may have the following CSV file:

forename,age,fav_color
“sally”,28,blue
“fred”,32,green

So now we can insert data for only two fields and because we have HDR=YES we can determine which of those fields should be set:

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents;Extended Properties='text;HDR=NO;FMT=Delimited'
DBConnect>ConStr,dbH
Let>SQL=INSERT INTO csvfile.csv (forename, fav_color) VALUES ("george","purple")
DBExec>dbh

Unfortunately it would appear that it is not possible to perform DELETE or UPDATE queries on CSV files. I’ve spent a few hours trying to find a definitive reference for JET’s text file driver but cannot find anything. All I know is that when I tried a DELETE or UPDATE query nothing happened. This is a shame as this possibility would be extremely powerful. As it is there’s not really much of a benefit over using DBExec instead of WriteLn.

If anyone knows of a definitive reference or a way to be able to perform DELETE or UPDATE queries using the Microsoft CSV driver, please let me know.

Tweet This