cancel
Showing results for 
Search instead for 
Did you mean: 

OLEDB - Excel - Connections - ACE vs JET

JonathanHolstin
Level 5
I'm attempting my first ever Excel OLEDB.  I'm not sure exactly where the problem lies. I'm wondering if it is using JET vs ACE connection. 

When I ran it through 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
------------------------------
17 REPLIES 17

HI Rajath,

I too tried the same but it did not work for me.
Could you please try a small example and please let me know if it works or not.

Thanks in advance!

------------------------------
Amlan Sahoo
RPA Consultant
Equinix
------------------------------
Regards,
Amlan Sahoo

Hi Amlan,
I could set the connection and open the connection with a few small tweaks. 
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='................';Extended Properties='Excel 12.0 Xml;HDR=No'"

If I used:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='.................';Extended Properties='Excel 12.0 Csv;HDR=No'"

I can set connection, open connection, but then when trying to get collection I get a - "Could not execute code stage because exception thrown by code stage: Could not find installable ISAM."  So maybe search of "installable ISAM"??  Hope that helps.


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

HI Dave,
Like this error I'm getting - "Could not execute code stage because exception thrown by code stage: Data type mismatch in criteria expression."

I'm using the following sql statement:  "Select F2 From [MCDR$] Where F6 >0"

F2 column is a general column.  The F6 column values come in as custom. I've since tried formatting them to general, text, and number but the same error comes through with all of them.  Any ideas?

Thanks!
Jon

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

Try this out 
"Select F2 From [MCDR$] where F6 > ""0""
or
"Select F2 From [MCDR$] Where F6 > '0' "


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

Hi Rajath,
Thanks for the suggestions.  I tried both and getting the same error.  Would this have anything to do with putting an IMEX into connection string.  Or any other ideas?

Thanks,
Jon

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

I see when I go to evaluate expression it shows the 0 as a Text.  Any other ideas of how to get that to a number (I'm guessing that is what these attempts were trying to do).
16378.png


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

Yes How to solve the same issue. I am not getting proper solution for that.
Please help me on that

------------------------------
Amlan Sahoo
RPA Consultant
Equinix
------------------------------
Regards,
Amlan Sahoo

I'm not sure.  I'm working with an xls file but it appears from reading you need to change data source.  Here is a link with a few options of fixing issue.

https://www.codeproject.com/Questions/812056/Getting-error-as-Could-not-find-installable-ISAM-w

Good luck and please update with a resolution if you find one.

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