SSIS Excel connection error "External table is not in the expected format."

Question

Problem

-I have an excel spreadsheet generated a lotus app. It smells and looks like excel but Excel Source data flow source can not recognise it. When trying to select a table(tab) i get the following error message "External table is not in the expected format.”

-Opening the excel spreadsheet and save it again helps(File also reduces in size). But as dev we ara allegic to manual processes.

-I have tried to change the connection string using a variable

from

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataImport\Lotus.xls;Extended Properties="Excel 8.0;HDR=YES";

To

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DataImport\Lotus.xls;Extended Properties="EXCEL 12.0;HDR=YES";

If i can avoid the script task that would be great

Do you know what version of Excel the Lotus generated spreadsheet targets? Is the data on multiple tabs or a single tab? Tab name(s) always the same? Safe to assume the source file is nothing we can examine to see why the driver is unhappy with it? — billinkc
– billinkc, Commented Sep 28, 2011 at 1:11

Kip Real · Accepted Answer · 2011-09-28 08:05:43Z

3

I have had to open and close the excel file manually saving. Those bugs that can't be fixed

answered Sep 28, 2011 at 8:05

Kip Real

3,4994 gold badges23 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John · Accepted Answer · 2012-11-19 22:37:46Z

1

I had the same error, your post led me to open the excel file but my excel file was corrupt. I'm posting in case this helps someone else.

answered Nov 19, 2012 at 22:37

John

1,92020 silver badges31 bronze badges

Collectives™ on Stack Overflow

SSIS Excel connection error "External table is not in the expected format."

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related