Solved

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

Posted on 2007-11-16
2
2,095 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 59
SQL Date from a string 4 57
SQL Select - Finding chars in a column 2 55
Error when saving to sql table a '/' 5 25
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

932 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now