Accessing a MS Excel Spreadsheet from Automation Anywhere Enterprise as if it was a Database

When accessing data from MS Excel spreadsheets, using Automation Anywhere Enterprise, users will typically “Open Spreadsheet -> Get all cells”.

This method opens the spreadsheet in Excel and therefore takes time and consumes memory. In some cases this simple method is useful (e.g. when spreadsheets are small and/or accessed infrequently) but if the spreadsheet is large (or accessed frequently) then it is not the best method. The performance of this method (on a busy or lowly spec'd Windows computer) can be improved using the method explained below...

(1) Below is a connection string that helps users access a spreadsheet as if it was a database; therefore negating the need to open the spreadsheet prior to processing the data.
Use the website: https://www.connectionstrings.com/ to determine correct connection string for your Excel environment.

(2) Open the Database -> Connect dialog, enter in a session name and then use the correct connection string to your version of Excel.
Note: I am using Office 365 therefore my connection string will differ slightly from other versions of Office: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$str_MyExcel$;Extended Properties="Excel 8.0;HDR=YES”;1. 1.

(3) The code below tells the dialog to connect to a Spreadsheet and also to ignore the headers.
NOTE: $str_MyExcel$ = path and filename for the excel spreadsheet

(4) Then use the Database -> SQL Query
select * from [Sheet1$]
To get all the contents of the excel spreadsheet held into memory.
You are now able to access all the columns via the $Dataset Column$ variable.

I hope that you find this tip useful. Please comment if you have queries or suggestions.

Sign In or Register to comment.