[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2111
  • Last Modified:

Unable to declare .xls as SSIS datasource. ERROR: External table is not in the expected format.

I receive an xls file from a ftp process nightly.  It's generated by another process by another company.

When I try to declare this as a data source in SSIS I get the following:

TITLE: Microsoft JET Database Engine
------------------------------

External table is not in the expected format.

------------------------------
BUTTONS:

&Retry
Cancel
------------------------------

If I open the file, and save it i am prompted with the Following:

fileName.xls may contain features that are not compatible with Text (Tab delimited). Do you want to keep the workbook in this format?

*To Keep the format, which leaves out any incompatible features, click Yes.
*To Preserve the features, click No.  Then save a copy in the latest Excel format.
*To see what might be lost, click Help.

and i can click Yes, No, or Help
What can I do to convert this to a file that SSIS can use as a DataSource?

Thanks

Pete
0
priehl
Asked:
priehl
1 Solution
 
imitchieCommented:
hi priehl,

does it only have one sheet? if so, open the Excel file, Select and Copy All.
open Notepad, then paste it in there (including tabs).
Save the file

Point SSIS to the file as tab-delimited.
0
 
priehlAuthor Commented:
Great Call,

I realized yesterday when i opened the file in a text editor that it wasn't in fact a .xls file, but in fact, as you guessed a tab delimited CSV file.  So I was able to get SSIS to read it as a Flat file.

Thanks
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now