troubleshooting Question

Read data from all sheets of an excel file using OPENROWSET

Avatar of Geo_Bout
Geo_Bout asked on
Microsoft ExcelMicrosoft SQL Server 2008SSISSQL
6 Comments1 Solution4441 ViewsLast Modified:
I'am currently working on an SSIS project and I want to get  the data from all  sheets of
an Excell file (.xlsx)  that I need to import it into an SQL table periodically. I don't know each time the number of sheets its contains.
I know how to deal with one sheet  using for example
Select * from Openrowset (MicrosoftFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;IMEX=1;      Database=E:\export\file.xlsx',
                        'SELECT * FROM [sheet1$]'
Is there a clever way to get all the sheets? I suspect that somehow i must get the excelschema via script  to get the sheet name in an variable and then to use a For-Loop . If that so then could any one can help me with the script..
Thanks in Advance
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros