OLEDB - Excel - Connections - ACE vs JET

[JonathanHolstin]

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

[RajathGopal]

Hi Jonathan,

There are two interfaces to choose for different versions of Excel (xls,xlsx,csv): Microsoft.Jet.OLEDB.4.0 and Microsoft.Ace.OLEDB.12.0

The Jet can access Microsoft Office 97-2003, but you cannot Access 2007 whereas ACE is a database connectivity component which in addition also allows connectivity to Excel.

Thanks,
Rajath G

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

[david.l.morris]

Is there any chance that the string is just not formatted properly? I see three things that could be causing the issue.

1. There's a NewLine just before Extended Properties. I've no idea if that could cause the problem here.
2. There's a semicolon ; at the end of the string, but I would think there shouldn't be one at the end since it is used to delimit/separate elements in the string.
3. There's no single quote after Excel 12.0 Xml.

The reason I think it's one of the three things above is that the error says the format of the string has an issue started at index 130, and the 130th character in the string is at that NewLine and/or it is the beginning of the Extended Properties element which has two problems in it.

So, here's the string I'd suggest to try. I only edited those few things I mentioned above: "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'"

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

[JonathanHolstin]

Hi Dave,
I think you are correct.  I put your string in and that error went away.  It now gives the following registration error.  I see a few different threads with numerous different ideas on what may cause this error.  Did you encounter this when starting out?

Could not execute code stage because exception thrown by code stage: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Thanks,

Jon

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

[david.l.morris]

Yeah I ran into that as well. It should mean that you need to install the provider, which I think comes with the install of the Microsoft Access Database Engine.

I think the link is broken that is in the OLEDB guide because Microsoft no longer supports that version of the install. So, I believe I installed this:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

I have some notes that indicate I ran into some issues with 32 vs 64 bit. It looks like I was able to install the 32bit version and it worked. But in any case, if you run into an error saying 'You cannot install the *-bit version of Microsoft Access Database Engine 2016...", then I can give more info on that.

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

[JonathanHolstin]

Hi Dave,
I've been reading that the 32 bit version is needed instead of the 64.  Are you saying that is the same issue you ran into?  If so do you happen to have a link to the 32 bit version needed?  I've created a ticket with my IT and will hopefully be able to verify a thumbs up or down shortly on if this is the problem.

Thanks,

Jon

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

[david.l.morris]

The link I posted above takes you to the download page, and when you click download, it'll ask you to choose the 64bit or 32bit version. But yeah my notes say I used the 32bit version. So I'm like...70% sure that's the case... lol

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

[JonathanHolstin]

Hey Dave,
I'm up and running!  Thanks for all your help with getting me going.  Do you have any "go to" resources for writing sql vs Excel?  I"m used to writing vs Oracle databases so just trying to figure out the nuances.

Thanks again!
Jon

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

[_Amlansahoo]

HI All,

Are you sure .csv files will also run with Microsoft.Ace.OLEDB.12.0. Because when i tried it was not working for me.

If anyone knows please let me know the answer.

------------------------------
Amlan Sahoo
RPA Consultant
Equinix
------------------------------

[RajathGopal]

Hi Amlan,

For .csv files you will have will have to make use of Jet OLEDB provider


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