Error: External table is not in the expected format

[NarVish]

Hi, I used the below connection string to read excel file. connectionString = "Provider=Microsoft.Jet.OLEDB.12.0;Data Source=D:\test2.xlsx;Extended Properties=\'Excel 12.0;HDR=No;\"; While opening, an exception is thrown.. "External table is not in the expected format" I explored in serveral sites and changed the connection string as mentioned there and followed other solutions. But none of them didn't work. Please guide me to solve this issue. Thanks in advance.

[Lost User]

Your driver version is not correct; you need the ACE driver and Microsoft.Ace.OLEDB.12.0 for your connection string. See Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^] for further details.

Use the best guess

[NarVish]

I used the below connection string as mentioned in the given article. Still error exists connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test.xlsx;Extended Properties=\"Excel 12.0;HDR=NO\"";

[Lost User]

You need to escape the backslash in your file name thus:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\test.xlsx;Extended Properties=\"Excel 12.0;HDR=NO\"";

Use the best guess

[NarVish]

:) I'm reading that data source value in runtime. so blackslash is not an issue.

[Lost User]

I can only assume that there is something else causing the problem. Are you able to read the file with Excel?

Use the best guess

[NarVish]

I followed below steps to make it work. Now, I'm able to read file 1. open your excel file 2. try to read that 3. Able to read the file 4. to read the file next time, no need to open the file. I don't know the reason how and why its working :)