topic RE: OLEDB issue - external table is not in the expected format in Product Forum

OLEDB issue - external table is not in the expected format

SameerJoshi1 — Fri, 30 Oct 2020 16:49:00 GMT

I am using OLEDB in my process to read XLSX file and running the process in different bots and sometimes getting the below error while opening the OLEDB connection.
External Table is not in the expected format
Due to this bot returns empty collection even if excel contains data.

I have observed that the same code works fine in next attempt.

We are using office 365, and Microsoft ACE OLEDB 12.0 is already installed.

Connection String used is, Provider=Microsoft.ACE.OLEDB.12.0; Data Source = {File_Path}; Extended Properties='Excel.12.0;

Any suggestions to fix this intermittent issue where bot fails to open OLEDB connection?

------------------------------
Sameer Joshi
------------------------------

RE: OLEDB issue - external table is not in the expected format

JohnCowell — Tue, 03 Nov 2020 09:18:00 GMT

Hi Sameer,

You mention that the same code works fine in the next attempt but is this using exactly the same input file on the same machine? Your approach to finding the problem would be to try and isolate this to specific files and machines. If it fails it should then fail consistently across repeated executions of the same steps with the same inputs. Once you reach this stage you can examine the differences and compare working files against none working ones.

Some suggestions I've seen on this topic include making sure you have lower case sheet names, the workbook is not read-only, having no hidden worksheet.

------------------------------
John Cowell
Senior Software Support Analyst
Blue Prism
------------------------------

RE: OLEDB issue - external table is not in the expected format

SameerJoshi1 — Tue, 10 Nov 2020 11:54:00 GMT

Hi John, yes the same code works for the same file and on the same machine. For now I have decided to implement environment lock before reading excel file using OLEDB(which I think works in background).

------------------------------
Sameer Joshi
State Street
------------------------------

RE: OLEDB issue - external table is not in the expected format

Kiran_P — Fri, 09 Apr 2021 08:45:00 GMT

Hi Sameer,

Hope you are having a good day.

Did you get this issue resolved or found out any alternatives? We are facing the same issue after migrating to Windows10. Hoping for a quick reply.

------------------------------
PONAKALADINNE KIRAN KUMAR REDDY
SENIOR ANALYST
Northern Trust
Asia/Kolkata
------------------------------

RE: OLEDB issue - external table is not in the expected format

SameerJoshi1 — Fri, 09 Apr 2021 09:08:00 GMT

Kiran, we used environment locks before OLEDB attempts to read the excel file, this seem to be working fine for us. You also can try the same.

------------------------------
Sameer Joshi
State Street
------------------------------

RE: OLEDB issue - external table is not in the expected format

Kiran_P — Fri, 09 Apr 2021 09:22:00 GMT

Hello Sameer,

Thanks for the info, am trying to understand the RCA for this issue. What do you feel the root cause of this error if environment locking is helping ? Is it that multiple bots were trying to read data using OLEDB at the same time from same file but different sheets? or is there anything else that you want to add?

Also, are you saying that you did not see this issue repeating again after using env locks?

------------------------------
Kiran P Associate Consultant
SENIOR ANALYST
Northern Trust
Bangalore
------------------------------

RE: OLEDB issue - external table is not in the expected format

SameerJoshi1 — Fri, 09 Apr 2021 10:13:00 GMT

Hi Kiran,

In our case, multiple bots were trying to read data from same sheet, same excel file from shared location.

------------------------------
Sameer Joshi
State Street
------------------------------

RE: OLEDB issue - external table is not in the expected format

Kiran_P — Fri, 09 Apr 2021 10:23:00 GMT

Hi Sameer,

I have applied the locks and added some wait times let me migrate and observe, will get back in case of any challenges. Thank you for your inputs.

------------------------------
Kiran P Associate Consultant
SENIOR ANALYST
Northern Trust
Bangalore
------------------------------