Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Excel Worksheets Expand / Collapse
Author
Message
Posted Tuesday, October 5, 2010 6:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 5, 2010 9:29 PM
Points: 2, Visits: 23
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
Post #998884
Posted Tuesday, October 5, 2010 7:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:11 AM
Points: 5,366, Visits: 8,981
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #998893
Posted Tuesday, October 5, 2010 7:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:11 AM
Points: 5,366, Visits: 8,981
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #998894
Posted Tuesday, October 5, 2010 8:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 5, 2010 9:29 PM
Points: 2, Visits: 23
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
Post #998913
Posted Tuesday, October 5, 2010 9:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:11 AM
Points: 5,366, Visits: 8,981
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #998922
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse