-1

I am importing .xlsx to SQL but whenever I am importing from a large .xlsx file it gives me error :

"External Table is not in expected format"

and whenever I am deleting some rows it runs fine. I have changed the Run64bit runtime to False. Still issue persists.

I am using SQL 2008R2.

Also when I change it to .xls and build package in SSIS 2005 it works.

The connection string I am using is Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @path + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";

2

Since you state it works when saved as an .xls file I would guess you are using the wrong connection string/provider.

You would typically use

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\path\to\file.xls;Extended Properties=Excel 8.0;

for a .xls file but you should use

Provider=Microsoft.ACE.OLEDB.12.0;Data Source\\path\to\file.xlsx;Extended Properties=Excel 12.0;

The Microsoft Jet OLE DB 4.0 provider supports excel up to version 2003.

The Microsoft ACE OLEDB 12.0 supports new versions of Excel, but also old versions by specifying the version in Extended Properties.

In SSIS this is (I believe) specified by choosing the excel version in the connection manager dialog

enter image description here

-1

After some time i found the system was missing drivers, so once i install it it was working fine

  • Would you care to elaborate on what drivers were missing? As it stands, your answer is likely to be deleted as it provides no details that would help a future visitor. – Max Vernon May 16 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.