March 12, 2009

Reading from CSV Files

Filed under: Scripting — Marcus Tettmar @ 11:47 am

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

March 11, 2009

Workflow Designer 2.1 Released: Export to SCP Files

Filed under: Announcements, Automation — Marcus Tettmar @ 10:10 am

Workflow Designer 2.1 is now available with the following changes:

  • Added: Ability to export workflow diagrams to Macro Scheduler script files
  • Added: Ability to add top/Left Rulers
  • Updated MacroScript engine to 11.1.05
  • Change: Visio style grab handles
  • Fixed: Opening Editor under Vista with UAC enabled would sometimes not work
  • Fixed: Some unicode encoding issues

MacroScript Workflow Designer 2.1

Workflow Designer allows you to create automation routines in flow chart form. With Workflow Designer you can create a flow chart of your process and then add the code to make it run. Logic is easily defined using decision boxes, and branching is as easy as joining boxes up. If code needs to be moved simply drag code blocks to where you want them to be and reconnect them to change the flow.

With Workflow Designer 2.1 we’ve added the ability to export a Workflow to a regular Macro Scheduer script file. This means Workflows can now be compiled to .EXE files using Macro Scheduler Pro, used in other Macro Scheduler installations, or easily integrated into existing scripts.

Workflow Designer is shipped with Macro Scheduler Pro Enterprise.

Registered Downloads/Upgrades | Evaluation Downloads | New License Sales

March 4, 2009

Macro Scheduler Video Trailer

Filed under: General — Marcus Tettmar @ 10:00 am

You’ve seen film trailers, now there are software trailers. Here’s a 30 second Macro Scheduler Trailer courtesy of Tucows.com:

March 2, 2009

Podcast: Seattle Children’s Hospital – Testing Under Citrix

Filed under: Announcements, Podcasts — Marcus Tettmar @ 1:56 pm

This month’s podcast interview is with Gale Dyvig of Seattle Children’s Hospital. Gale talks about why they chose Macro Scheduler for automating the testing of their systems running in a Citrix environment; why they chose to replace their existing HP WinRunner scripts with Macro Scheduler scripts; and how they are saving money on licenses and ongoing script maintenance.

Download Listen

Macro Scheduler 11.1.05 Update

Filed under: Announcements — Marcus Tettmar @ 11:50 am

Macro Scheduler 11.1.05 Update is now available with the following changes:

  • Added: RegEx function with Perl and EasyPatterns support for search and/or replacement.
  • Added: CopyFolder, DeleteFolder commands – actually just aliases for CopyFile and DeleteFile
  • Added: Option in Debug Menu to change colour of debug line (Debug/Debug Line Color).
  • Added: NULLCHAR system variable for null char – Chr(0)
  • Changed: Default colour of debug line to make text beneath more visible
  • Fixed: CTRL+S causing hang after editor had been opened then closed
  • Fixed: Editor menu shortcut keys still working after editor closed
  • Fixed: Press End inside subroutine confusing code folding (in order to fix this SRT and corresponding END must have same indent)
  • Fixed: Compiled macro set to log to \dev\nul (disable logging) would run slower than with no logging configured
  • Fixed: If a non existing destination folder specified in compiler, compiler would crash. Now gives error msg.

Registered Downloads/Upgrades | Evaluation Downloads | New License Sales

February 26, 2009

Sneak Peak: Simplified Regular Expression Support

Filed under: Announcements, Scripting — Marcus Tettmar @ 4:52 pm

I don’t know many people who find Regular Expressions easy. If the following makes no sense to you, don’t worry, you’re not alone:

([a-z0-9!#$%&’*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&’*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?)

It’s actually a regular expression pattern which will match an email address in a string. I’m sure you knew that.

At present to use Regular Expressions in Macro Scheduler you have to use VBScript’s regular expression object:

VBSTART
Function RegExpTest(sEmail)
  RegExpTest = false
  Dim regEx, retVal
  Set regEx = New RegExp

  regEx.Pattern ="([a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?)"

  regEx.IgnoreCase = true

  Set retMatches = regEx.Execute(sEmail)
  
  If retMatches.Count > 0 then
    RegExpTest = retMatches(0).Value
  End If

End Function
VBEND

VBEval>RegExpTest("My email address is: [email protected]"),theEmail
MessageModal>theEmail

In order to simplify things we’re currently working on a native Regular Expression function called, appropriately enough, RegEx. Using this, the following code will find the email address in the given string:

Let>text=My Email Address: [email protected]
Let>pattern=([a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?)
RegEx>pattern,text,0,matches,num,0
MessageModal>matches_1

A bit simpler, as you don’t need to use VBScript. But you still need to use that weird and wonderful regular expression syntax.

Luckily our friends at DataMystic have created something called EasyPatterns which maps real English to regular expression syntax. Thanks to DataMystic we are able to use EasyPatterns in Macro Scheduler. Setting the EasyPatterns flag in the new RegEx command allows us to turn the above into:

Let>text=My Email Address: [email protected]
Let>pattern=[EmailAddress]
RegEx>pattern,text,1,matches,num,0
MessageModal>matches_1

Note the second line has been simplified to Let>pattern=[EmailAddress]. Nice. Now it makes sense!

Check out the EasyPatterns Reference here to find out what else you can do.

Watch out for the next Macro Scheduler maintenance update, which, all being well, will have this function included as a bonus.

Please note this post refers to work in progress. The syntax in the released version may differ slightly. I will update with changes when they happen.

February 19, 2009

View System Windows – Windows and Objects

Filed under: Automation, Scripting — Marcus Tettmar @ 10:12 am

syswindows1

Macro Scheduler has a “View System Windows” tool which you can get to from the Tools menu. This displays a tree of all windows, their child windows and objects open in the system. For each entity you see its handle, class name and title/caption text.

The help file talks about windows and child windows. For windows you can read “objects”. In Windows a “windows control” is not necessarily a window in the sense that you are familiar with but could be a button, a status bar or edit box, or pretty much anything else you see inside a window or on the screen.

So the View System Windows tool shows the hierarchy of “windows” that currently exist on your system. You can expand one to see it’s child windows/objects and so on.

In the picture above I have highlighted the entry for what is commonly referred to as the “System Tray”. Its official title is “Notification Area”. If we right click this entry and select “Identify” a border will be drawn around the system tray icon area. This “Identify” option is a useful way to help locate a window or object and be sure that the entry you see in the list is the object you’re looking for.

A word about handles. These are the numbers that appear in the list. In the above snapshot the notification area has a handle of 131104. It will be different on your system. It will be different after you reboot. Handles are simply identification numbers that are allocated at run time when a window is first created by the system. So don’t go thinking you can grab that number and put it in your script and use it to identify your window. Next time you reboot the script won’t work.

After the handle you will see the “class name”. For the system tray this is “ToolbarWindow32”. This is static but not unique. It is just the type of object – the object class – that is used. We can use this to find an object, but it is not unique.

Macro Scheduler has a function called GetControlText which takes a window title (or handle), a class name and an index. This will return the text associated with an object of the specified class and index on the specified window. If there were two objects of that class an index of 1 would return the first and an index of 2 the second, in the order of creation (some trial and error is usually required). GetControlText is great for objects on regular windows and makes life simple. But for objects deeper in the hierarchy we might have to be a bit more clever.

Some objects also have captions and these are displayed after the class name. Here we see it is “Notification Area”. E.g. windows have captions and we’re used to seeing these in the title bar. Button objects have captions that appear on the button. Some other objects also have captions although they may not be visible (equally what you see on an object is not necessarily the caption that appears in the tree). If we have a caption we can also use this to help us find an object. If we don’t we would have to rely on indexes as in GetControlText – iterate through each instance of an object.

As an example, the following code gets us the handle of the system tray:

LibFunc>User32,FindWindowA,h1,Shell_TrayWnd,
LibFunc>User32,FindWindowExA,h2,h1,0,TrayNotifyWnd,
LibFunc>User32,FindWindowExA,h3,h2,0,SysPager,
LibFunc>User32,FindWindowExA,h4,h3,0,ToolbarWindow32,Notification Area

This code mirrors the hierarchy we see in the View System Windows tree above.

FindWindow takes a class name and caption to return a top level window. So this gives us the handle of the parent Shell_TrayWnd object. FindWindowEx finds an object on the given parent window. So the second line above looks for an object with class “TrayNotifyWnd” on the Shell_TrayWnd window (h1 returned by FindWindow) with no caption. It looks for the first match since the “child after” handle is set to zero. The next two lines continue to walk the tree. What we end up with in h4 is the handle of the system tray.

I use this code here in Activating System Tray Icons.

If all this sounds rather complicated, don’t worry. Most of us will never use code like this. I just wanted to explain more about what View System Windows shows us. There are much easier ways of identifying objects and scraping text from the screen that work in a much more human like way. Objects can be identified visually with Image Recognition and the Text Capture functions let us screen scrape by just specifying a window title or screen area. And most of the time we can automate a window by sending keystrokes to it and never have to care what an object is called.

February 13, 2009

Image Recognition Common Mistakes

Filed under: Automation — Marcus Tettmar @ 12:16 pm

This is a copy of a reply I gave in the forums recently which I thought would be useful posted here.

It sometimes seems to surprise people that the only reason FindImagePos will fail to find an image on the screen is because …. that image is NOT on the screen! I’ve had people swear blind the image they are seeking is on the screen but when I’ve opened up the screen image and the needle image in Paint I can see big differences.

Here are some common mistakes I’ve seen that can lead to not finding the image you want Macro Scheduler to find (or rather, looking for the wrong image):

Mistake 1: Not using the built in Image Capture Tool. ALWAYS use the Macro Scheduler Image Capture tool. Other image editors may save with a different colour depth etc. To ensure like-with-like comparisons ALWAYS use the Macro Scheduler Image Capture Tool.

Mistake 2: Capturing the object when it is in a different state to how it appears at run time. E.g. buttons look different when they are focused, menus look different when they are selected, tooltips appear when the mouse is over objects, sometimes even window captions appear different when the window is not focused, sometimes objects look different when the mouse is hovered over them. Take all these things into consideration and make sure you capture the image as it will appear at the point in the process where you want to find it. You may need to use the delayed capture feature of the Image Capture Tool so that you can arrange focus before you capture the object. Consider making the script move the mouse to 0,0 in case the mouse ends up hovering over the button you’re looking for at runtime. I’ve seen buttons look different if the window is focused. If you have the window focused when you captured it you’ll need to make the script focus it before looking for it. Or vice-versa. Etc etc

Mistake 3: Capturing too much of the screen. FindImagePos compares a random sampling of pixels in the needle image and target area. So if you capture an area with lots of background it’s feasible that only the background pixels get chosen and you’ll end up with lots of matches. Be more specific in your captures, and if you need to, increase FIP_SCANPIXELS to make it scan more pixels. Update: in V12 you can set FIP_SCANPIXELS to ALL.

Mistake 3a: Thinking it didn’t find the image when in fact it DID find it, but it also found other possible matches. The function returns the number of images found and an array of matches. If you are clicking on the first match and it is not the right place, look at the number found. Consider what to do if number of matches is more than 1.

Mistake 4: Looking for the image at the wrong time – getting the order of events all wrong.

Some issues that can cause problems:

Graduated or patterned backgrounds. If you capture an object that is on a non-smooth background and have to capture some of the background as well, then if the object appears at a different position at runtime you may fail to find a match, because the background is not smooth. As with Mistake 3 above you probably just need to be more specific about what you capture. Or, if that isn’t possible, see if you can change the background. There’s no harm in making life easier for ourselves by setting the environment up to be a little more predictable so that we can automate/test more reliably. Don’t forget you can also change the colour tolerance parameter to handle small differences in colours.

Sometimes it’s helpful for diagnostic purposes to use ScreenCapture to capture the screen to a file prior to your FindImagePos call. Then, later, if it didn’t work as you expected you can open up the screen image and work out why.

See also: How to use Image Recognition.

February 12, 2009

Macro Scheduler 11.1.04 Update

Filed under: Announcements — Marcus Tettmar @ 3:27 pm

Macro Scheduler 11.1.04 Update is now available with the following fixes:

  • Fixed: Autologon failing to save password correctly
  • Fixed: Backup on Save not working for standalone editor
  • Fixed: Editor status bar not always reflecting modified status
  • Fixed: Improvements to dialog block updating from “Update Dialog” menu
  • Fixed: Debugger: Breakpoint after an Include not showing Include lines/line number confusion
  • Fixed: Step Over confused by GoSub inside a subroutine
  • Change: Debugger no longer temporarily disables wordwrap – no longer required
  • Change: Editor line numbers now show at top of wrapped lines
  • Added: Clicking in editor margin selects entire line

Registered Downloads/Upgrades | Evaluation Downloads | New License Sales

« Newer Posts — Older Posts »