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

OLEDB issue - external table is not in the expected format

DaulatRam1 — Tue, 09 Nov 2021 08:07:00 GMT

I am using OLEDB in my process to read XLSX file and running the process and sometimes getting the below error while opening the OLEDB connection.
External Table is not in the expected format
I have observed that the same code works fine in next attempt.

This is happening in Prod only and not able to replicate in UAT and Dev.

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

Thanks in advance
Daulat

------------------------------
Daulat Ram
------------------------------

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

ewilson — Tue, 09 Nov 2021 12:59:00 GMT

Hi Daulat,

I came across this post on Stackoverflow that might address your issue. Looks like they're changing their connection string a bit.

https://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

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

DaulatRam1 — Fri, 26 Nov 2021 05:53:00 GMT

Thanks for your inputs Eric. We are using the Updated connection string only but issue still persists.

Thanks,
Daulat

------------------------------
Daulat Ram
------------------------------

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

devneetmohanty07 — Fri, 26 Nov 2021 06:05:00 GMT

Hi Daulat,

Can you also try to check the file format. Some times files are stored as xlsx but they refer to XML spreadsheet type and not your typical Excel Workbook type which creates issue while connecting with excel files as OLEDB. We have seen this issue in past when we directly downloaded any file from SAP and tried to read it directly using OLEDB connection. As a workaround, we first need to save the file again as a proper .xlsx (Excel Spreadsheet Format) and then continue with it.

You can check the different excel formats in the given screenshot below. The one highlighted is likely to case the issues when trying to establish an OLEDB connection. Ensure that your file is first saved as the first xlsx format which you can see in the screenshot :

------------------------------
Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
------------------------------

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

Neel1 — Fri, 27 Oct 2023 16:16:00 GMT

posting on a old thread in case if someone able to resolution on this. Received this error all of sudden while bot running in production and next day on re-run - issue did not came.

------------------------------
Neeraj Kumar
------------------------------

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

michaeloneil — Mon, 30 Oct 2023 13:51:00 GMT

Hi @Neeraj Kumar

I've seen this error come up a few times when there is a data mismatch between the columns in the excel file and collections i want to extract to. E.g. a column contains a datetime format in the excel file but the collection might be all text. Its also been an issue where the format types cant be reconciled, i think by default the oledb takes everything as text but if a column is formatted to a type that sql is not familiar with it can cause an error. I resolve this by doing a format to text all for all columns in the excel file before running the oledb action which was ok for the data i was using.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------