External table is not in the expected format.

[sarthak_86]

Hi,

I am getting the error: External table is not in the expected format while using OLEDB to read from Excel. I am unable to find the Root cause for this, as I tried to debug it in Production and observed that once the reading from the Excel file using oledb fails and when we retry it, it is successful and also if we go inside the stage then again there is no error. Tried reinstalling the OLEDB engine but no luck.

Please help me with the fix if you have encountered the same in your processes.

Regards,

Sarthak Dhasmana

[ewilson]

Hello Sarthak,

Is there a reason you're using the OLEDB VBO to read from Excel as opposed to the Excel VBO? Is it a really large worksheet or something? Can you share the connection string you're using to connect to Excel?

Cheers,

Eric

[sarthak_86]

Hi Eric,

The issue is not with the connection string or excel, it's something else. The main challenge is that it's running when we try to run that stage again . It is not coming everytime but coming randomly which is making it difficult to find the root cause.

[asilarow]

There is no such thing as random when it comes to Oledb connections... there is always a reason 🙂

Is the spreadsheet on a shared network drive?

Are there any other users/bots trying to connect to the spreadsheet at the same time (and thus creating locks)?

To debug, I can recommend the following:

1. Move the spreadsheet to a private folder, and adjust your process logic to target that path

2. Temporarily Enable full logging on the machine running the process (you can find the steps to do that here)

3. Run the process on the desired machine

Does it still behave the same?

If so, check if the Oledb connection is disposed of properly after the query is done (read more about it here).

[LeonardoSQueiroz]

Hello,

As Asilarow mentioned, normally the problem can be linked to simultaneous use, what I recommend doing is separating the file to be manipulated to a folder with third-party access restrictions, keeping the output shareable if necessary.

Regards,

[sarthak_86 · Accepted solution]

Hi All,

Thanks for your responses, we were able to fix this issue, by closing and reopening the OLEDB connection and then retrying, it seems it was something with the OLEDB driver, now it's working fine.