Excel as database using automation anywhere

S

sunitha nookala

New Member
Hi all,

I am trying to use excel as database to read data from an excel for automation anywhere version 10.5. Could anyone help me with the connection string code.

Thanks in advance.
 
Sukesh Kumaru

Sukesh Kumaru

Active Member
Hi Sunitha,

(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.

(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.
 
V

VJR

Well-Known Member
syed said:
How I can add new column usind Oledb?
Click to expand...
Hi syed,

This is the Automation Anywhere forum and you have the same question on the Blue Prism thread.
Do post back if you need the same solution in AA too.
 
R

RobertJ

New Member
Sukesh Kumaru said:
Hi Sunitha,

(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.

(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.
Click to expand...

I am new to Blue Prism and setting up the connection strings are still not clear to me. Do you list the spreadsheet in the database name? Is the provider value filled even tho' it is part of the connection string? And adding in the properties parameter with its own set of quotes may be giving me a problem. So a concrete example like the attached file would be really helpful. Just as I was posting this question, I noticed that the thread was tagged the Automation Anywhere so I may be in the wrong section.

Thanks.
 

Attachments

  • OLEDB Connection Screen.png
    35.4 KB · Views: 56
Sukesh Kumaru

Sukesh Kumaru

Active Member
RobertJ said:
I am new to Blue Prism and setting up the connection strings are still not clear to me. Do you list the spreadsheet in the database name? Is the provider value filled even tho' it is part of the connection string? And adding in the properties parameter with its own set of quotes may be giving me a problem. So a concrete example like the attached file would be really helpful.

Thanks.
Click to expand...
Please this post in Blue Prism Forum, this is Automation Anywhere forum.
 
You must log in or register to reply here.
Top