1

Using an existing SSIS package, I was trying to import .xlsx files we received from a client. I received the error message:

External table is not in the expected format

  • These files will open in XL
  • When I use XL (currently XL2010) to Save As... the file without making any changes:
    • The new file imports just fine
    • The new file is 330% the size of the original file
    • When changing .xlsx to .zip and investigating the contents with WinZip:
      • The original file only has 4 .xml files and a _rels folder (with 2 .rels files): Original file
      • The new file has the expected .xlsx contents: New file

Does anyone know what kind of file this could be?

It would be nice to develop my SSIS package to work with these original files, without having to open and re-save each file. There are only 12 files, so if there are no other options, opening/saving each file is not that big of deal...and I could automate it with VBA going forward.

Thanks for any help anyone can provide,

CTB

InigoMontoya
  • 49
  • 5
  • Hi moderators, if you would, please don't change my tags. This isn't really an SSIS/ETL/SQL Server question. I mention SSIS as just kind of an aside. I have a work-a-round (in other words: save as true XL files) to get these to import with existing import packages. I'm much more interested in finding any info on these files' structure...are they corrupt?...are they a non-MS spreadsheet app's attempt at creating .xlsx files?...are they output from some 3rd-party system? Thx – InigoMontoya Apr 26 '17 at 04:09
  • i provided an answer and still waiting for a feedback – Hadi May 22 '17 at 23:33

1 Answers1

0

There are many Excel file formats.

The file you are trying to import may have another excel format but the extension is changed to .xlsx (it could be edited by someone else) , or it could be created with a different Excel version.

There is a Third-Part application called TridNet File Identifier which is an utility designed to identify file types from their binary signatures. you can use it to specify the real extension of the specified file.

Also after a simple search on External table is not in the expected format this error is thrown when the definition (or version) of the excel files supported in the connection string is different from the file selected. Check the connection string used in the excel connection manager. It might help to identify the version of the file.

Hadi
  • 36,233
  • 13
  • 65
  • 124