question

Dave Myers avatar image
Dave Myers asked

Import multiple worksheets into SQL Server 2005

I need to move an Excel 2003 spreadsheet with 7 worksheets into a SQL Server 2005 Database and would like to know the best way to do this. I have looked at the Import/Export Wizard but see some disadvantages with the multiple worksheets. The contents of the worksheets are all the same columns (there is one tab(worksheet) for each month). I have looked at using an SSIS package to move these as well and again see the multiple worksheets as the sticking point. Ultimately I would like to have this data in two tables, so the app team can query against the historical data by vendor information or store information. Any ideas/solutions would be greatly appreciated
sql-server-2005excelimport-data
10 |1200

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

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
I'm not sure why SSIS wouldn't work with multiple work sheets. Assuming you're able to quantify their locations, you can walk the files and walk the worksheets with loops within SSIS and move the data you need to move. You will probably need to ensure that the structures of the worksheets are consistent in order to make the imports work well, especially automating them. Other than that, it ought to be pretty smooth. I'm sure if you searched on the web there are even examples available.
1 comment
10 |1200

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

WilliamD avatar image WilliamD commented ·
2 Likes 2 ·

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.