• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 628
  • Last Modified:

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

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?

Regards,
T
0
taromor
Asked:
taromor
  • 3
  • 2
1 Solution
 
Reza RadConsultant, TrainerCommented:
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?
0
 
taromorAuthor Commented:
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.

However,
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.  

Cheers,
T




SSIS-Sample.xls
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Reza RadConsultant, TrainerCommented:
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:
http://bensullins.com/adding-a-row-number-to-an-excel-source-in-ssis/

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?

0
 
taromorAuthor Commented:
Thanks for you time and pointing me the directions.  
Great comments with clear explanations.  
Cheers,
T
0
 
Reza RadConsultant, TrainerCommented:
Glad to help
Regards,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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