question

Naveen Kumar avatar image
Naveen Kumar asked

SSIS - Steps to dump multiple excel sheets

Hi Every one, Can some one provide me some link or document on how to dump multiple excel sheets and all the data sheets (sheet1,sheet2....) of all excel files to a table. Thanks in advance.
ssis excel
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
Microsoft Support have written an article "How to import data from Excel to SQL Server" that covers several different techniques for importing the spreadsheet - [ http://support.microsoft.com/kb/321686][1] It has some code samples if you want to do a quick import via linked server or distributed query (openrowset/opendatasource) as well as guidance for doing the import with SSIS. It does assume some basic knowledge of SSIS, so only gives direction on how to import multiple sheets rather than a working package sample. Here's a quick sample import query based on a query from the article (the first query creates a table using SELECT ... INTO. If you already have a table, just use the INSERT ... SELECT from the second query - maybe adding some column names): SELECT * INTO YourTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\path_to_your_file\your_file_name.xls', 'SELECT * FROM [YourSheetName$]') INSERT YourTable SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\path_to_your_file\your_file_name.xls', 'SELECT * FROM [AnotherSheetName$]') Just repeat for each sheet in the workbook. If you're using a newer version of excel than the sample, you can get the appropriate connection string from connectionstrings.com - [ http://www.connectionstrings.com/excel/][2] [1]: http://support.microsoft.com/kb/321686 [2]: http://www.connectionstrings.com/excel/
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Ken, Thanks for the reply, I want to achieve the task through SSIS and I am able to dump multiple excel files from a folder But I am not able to dump all the sheets of all excel files my package is executing for only sheet1. For example I have two excel files File1 and File2 each file is having 3 sheets, So my package should get the data from all the 6 sheets. Please suggest some steps to follow or an article to achieve this. Thanks in advance. :)
0 Likes 0 ·
There's a two-step approach you can take that is referenced from a previous answer to this question (a common problem, apparently)- http://ask.sqlservercentral.com/questions/87966/import-multiple-excel-worksheets-from-one-file-int.html Step 1: script task to get a list of worksheets in the excel file - http://technet.microsoft.com/en-us/library/ms403358.aspx#example4 Step 2: SSIS for each loop to read the worksheets - http://technet.microsoft.com/en-us/library/ms345182.aspx A similar approach was also referenced in this other previous answer to this question - http://ask.sqlservercentral.com/questions/19308/import-multiple-worksheets-into-sql-server-2005.html
0 Likes 0 ·
Thanks Ken... You made my day :)
0 Likes 0 ·
Glad to help.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.