Excel Worksheets

Question

Excel Worksheets

satya_sathya

SSC Enthusiast

Points: 120
More actions
October 5, 2010 at 6:21 pm

#229124

Hi All
I am a Newbie. I am trying to extract data from multiple worksheets in an excel file using SSIS. Can Somebody guide me through this?
Satish

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

Utilize an Execute SQL Task (connected to an Excel Connection), and run a "CREATE TABLE " statement. If the xls file doesn't already exist, it will be created. It will also create a worksheet AND a named range of the name specified in the CREATE TABLE statement (the difference is the worksheet has a $ appended to the end).

Repeat for each worksheet that you want to create.

For each worksheet, use a Data Flow task, with a Source and Destination. If your source has char/varchar columns, you will need to use a derived column task to make nvarchar columns. (Alternatively, for your source use a query and convert(nvarchar()) all the char/varchar columns).

Note that the create table statement needs to use the "`" instead of the ' character, and the data types need to be what excel supports.

The easiest way to set up your initial package is to utilize the Export Wizard, and export your source to an Excel file. Save it as a package, and you'll have a good baseline to work with for all the remaining worksheets.

Hope this helps. Come on back if you have any more questions.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95409 More actions · Answer 2

And in re-reading your question, are you trying to import data from multiple worksheets of an Excel file into Sql Server?

If so, most of the previous post still applies. Use the Import Wizard to create a package that will load in one of the worksheets. Again, save as a package. Then copy the data flow tasks to load each of the worksheets.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

satya_sathya SSC Enthusiast Points: 120 More actions · Answer 3

Hi Wayne Thank You Much for the answer

I was wondering is their any way we can accomplish this task using the for each loop container ( i am not sure i might be wrong ) that will read all the worksheets??

Thank You In Advance

WayneS SSC Guru Points: 95409 More actions · Answer 4

SSIS can only work with known worksheets.

However, you CAN use a Script Task, use some Excel Automation, and get a list of worksheets. However, utilizing Excel Automation on a Server is not supported by MS.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2