Importing the  Excel file into SQL Server via SSIS - Excel data is not in table format

Posted on 2010-01-06
Last Modified: 2013-11-10
Hi Experts,
I am new to the SSIS.  
Could you please point me a right direction as I do not know what resources or ways are available to solve the issue.  

I am trying to import the Excel data ( not table format) into the SQL Server via SSIS.
The error pop up message shows up as, "External table is not in the expected format".  
Do I need to change the Excel data writing code in VB(A) before implementing the SSIS importing?

Question by:taromor
    LVL 15

    Expert Comment

    LVL 30

    Expert Comment

    by:Reza Rad
    did you configure data source and data destination correctly?
    I mean did you select table data in source?  and did you select an excel sheet in destination ?
    if it doesn't helped provide screen shots from configuration of source and destination or upload your package here?

    Author Comment

    Hi reza rad,
    Thanks for your comment.  

    Yes I configure the data souce and data destination correctly.  
    I tested with the data includes a column heading in row1 and data from row2 onward.  In this case, I could succesfully import the Excel data into the SQL Server table.  (Please see the xls attachement "Testing Case_OK" worksheet.

    the second worksheet case, "Case with not table formatted" - the table is not beginning from Range("A1").  I want to skip the row 1 to row 4.  


    LVL 30

    Accepted Solution

    If you want to select specific rows you have two option:

    1- write a query instead of selecting sheet name in excel data source like this:

    SELECT        F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12
    FROM            ['Case with not table formated$']
    WHERE        (F2 IS NOT NULL)

    in this way you need to specify your rows by a where clause, in this example all rows that has F2 not null was fetched. but you must find a way to specify your data in where clause.

    2- use an OLE DB Data source instead of excel, and connect to sql server
    write a query with OPENROWSET() and fetch data from excel.
    like this:

    remember you can add row_number() to your query results and then select any row_number you want from the results.

    Does it make sense to you?


    Author Closing Comment

    Thanks for you time and pointing me the directions.  
    Great comments with clear explanations.  
    LVL 30

    Expert Comment

    by:Reza Rad
    Glad to help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    779 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

    12 Experts available now in Live!

    Get 1:1 Help Now