Solved

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

Posted on 2007-11-16
2
2,096 Views
Last Modified: 2008-12-30
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
Comment
Question by:priehl
2 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20324879
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
 

Author Comment

by:priehl
ID: 20328159
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Bubble user-defined Sql RAISERROR(...) to c# exception 14 169
Need help with a query 6 74
Grid querry results 41 78
Present Absent from working date rage 11 21
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question