topic RE: OLEDB Excel - Invalid Sql Statement in Product Forum

OLEDB Excel - Invalid Sql Statement

JonathanHolstin — Tue, 24 Dec 2019 14:49:00 GMT

I'm trying to pull a simple ---
Select * From [Excel Sheet]

Could not execute code stage because exception thrown by code stage: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I'm trying to use various versions of the following statement. With [Excel Sheet] data item being the excel sheet MCDR with a $ after. So [MCDR$]

"select"&NewLine()&
"*"&NewLine()&
"from"&NewLine()&
"&"'"&[Excel Sheet]&"'"&"""

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: OLEDB Excel - Invalid Sql Statement

sutirtha.gupta — Thu, 26 Dec 2019 03:30:00 GMT

Hi Jonathan,

Can you please do the get worksheet names first and check is there any difference in worksheet names?

Regards

Sutirtha Gupta

TCS,Kolkata

------Original Message------

I'm trying to pull a simple ---
Select * From [Excel Sheet]

Could not execute code stage because exception thrown by code stage: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I'm trying to use various versions of the following statement. With [Excel Sheet] data item being the excel sheet MCDR with a $ after. So [MCDR$]

"select"&NewLine()&
"*"&NewLine()&
"from"&NewLine()&
"&"'"&[Excel Sheet]&"'"&"""

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: OLEDB Excel - Invalid Sql Statement

RajathGopal — Thu, 26 Dec 2019 05:13:00 GMT

Hi Jonathan,
Good Day!!
The syntax would be "Select * from [Excel Sheet$]".
The sheet names would be suffixed with a $ symbol.

Thanks,
Rajath G
Sr. Blueprism Developer
ITC Infotech

------------------------------
Rajath Gopal
Associate IT Consultant
ITC Infotech
Asia/Kolkata
8904867411
------------------------------

RE: OLEDB Excel - Invalid Sql Statement

JonathanHolstin — Thu, 26 Dec 2019 15:25:00 GMT

Thanks Rajath. Looks like it was that simple.

When I ran it using Microsoft.Jet.OLEDB.4.0 I got it to go through but it could not find the file. I was wondering if that is because I'm on a new version and the Jet appears to be for older excel. In trying to change to the ACE connection I am running into problems of not conforming. Details below. Any ideas?

Could not execute code stage because exception thrown by code stage: Format of the initialization string does not conform to specification starting at index 130.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='P:\Libraries\FSB\AOBRobotics\MCC\Invoices - MCDR\NPL CIF MCDR - November 2019.xls';
Extended Properties='Excel 12.0 Xml;"

Any ideas on this....?

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: OLEDB Excel - Invalid Sql Statement

RajathGopal — Thu, 26 Dec 2019 23:25:00 GMT

Hi Jonathan,
Looks like a few parameters are missing in your connection string. Try using the below connection string :

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="FilePath";
Extended Properties=""Excel12.0;HDR=Yes;IMEX=1;"""

------------------------------
Rajath Gopal
Associate IT Consultant
ITC Infotech
Asia/Kolkata
8904867411
------------------------------