Solved

# Read data from all sheets of  an  excel file using OPENROWSET

Posted on 2014-01-04
2,592 Views
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 0 Question by:Geo_Bout 6 Comments LVL 49 Expert Comment ID: 39757182 0 LVL 51 Accepted Solution Mark Wills earned 500 total points ID: 39757349 Well, if you have a linked server, then you can list all the "tables" being the sheet names... e.g. if exists (select * from sys.servers where name = 'my_Excel') exec sp_dropserver 'my_Excel', 'droplogins'; -- now create the linked server for your spreadsheet EXEC sp_addlinkedserver my_Excel, 'Ace 12.0','Microsoft.ACE.OLEDB.12.0','c:\d820\ee\test1.xls', NULL, 'Excel 12.0;' --Set up login mappings (just ADMIN - ace wants something). EXEC sp_addlinkedsrvlogin my_Excel, FALSE, NULL, Admin, NULL -- now list the sheet... EXEC sp_tables_ex my_Excel  And that can be parameterised as needed, storing the sheet names in a table if exists (select * from sys.servers where name = 'my_Excel') exec sp_dropserver 'my_Excel', 'droplogins'; -- our spreadsheet name goes here declare @file varchar(200) set @file = 'c:\d820\ee\test1.xls' -- now create the linked server for that spreadsheet exec ('EXEC sp_addlinkedserver my_Excel, ''Ace 12.0'',''Microsoft.ACE.OLEDB.12.0'','''+@file+''', NULL, ''Excel 12.0;''') --Set up login mappings (just ADMIN - ACE wants something). EXEC sp_addlinkedsrvlogin my_Excel, FALSE, NULL, Admin, NULL -- we will create a temp table to store the worksheets names if object_id('tempdb..#t','u') is not null drop table #t create table #t (id int identity, table_cat varchar(100),table_schem varchar(100),table_name varchar(100),table_type varchar(100),remarks varchar(100)) --List the tables in the linked server (these are the worksheet names). insert #t EXEC sp_tables_ex my_Excel  and then of course, looping around you just use the 4 part identifier and open the table... --select * from #t -- now we will get the worksheet name we want to use based on "index" - well id actually... declare @n varchar(100) select @n = table_name from #t where id = 1 -- if it is a real name, then lets try to use it (probably should check for a '$' in the name
if @n is not null
exec ('select * from my_Excel...['+@n+']')

-- now this is where you do whatever you want with the data using the linked server

-- remove server, just to clean up 'nicely'
if exists (select * from sys.servers where name = 'my_Excel')

0

LVL 16

Expert Comment

ID: 39757365
Assuming all sheets have the same structure (columns), loop through the sheets with VBA to combine them into a single "master" worksheet and use the OPENROWSET statement once.  The code below creates a new worksheet in position 1 named "Master".  That name can be changed to whatever you want in the Initialize Variables section of the code.
Sub Combine_Worksheets()
Dim i As Integer, j As Integer
Dim lstRow1 As Integer, lstRow2 As Integer, lstCol As Integer
Dim ws1 As Worksheet, ws As Worksheet
Dim wsMstr As String
Dim LstCell As String
Dim EMsg As String
Dim DisplayMsg As String
Dim ColCnt As Integer
Dim CntFlg As Integer
Dim Response As String
Dim errorMsg As String

On Error GoTo errHandler
errFlag = False

With Application
.ScreenUpdating = False
.EnableEvents = False
.StatusBar = False
End With

'Intitialize Variables
wsMstr = "Master"       'Master Worksheet Name - rename as needed
EMsg = ""
CntFlg = 0
Application.StatusBar = "Creating/Updating the " & wsMstr & " Worksheet with Current Data"

Sheets(wsMstr).Activate
With Range("A1", ActiveCell.SpecialCells(xlLastCell))
.Clear
.EntireRow.Delete
End With

comeBack:
Set ws1 = Sheets(wsMstr)
lstRow2 = 1
j = 1

'Check the Number of Columns in each data worksheet
'Set CntFlg to 1 if any sheet has a different number of columns
ColCnt = Sheets(2).Cells(1, 16384).End(xlToLeft).Column
For i = 2 To Sheets.Count
If Right(Sheets(i).Name, 1) <> "+" Then
Worksheets(i).Activate
EMsg = EMsg & ActiveSheet.Cells(1, 16384).End(xlToLeft).Column & " Columns - " & Sheets(i).Name & Chr(10)
If ColCnt <> ActiveSheet.Cells(1, 16384).End(xlToLeft).Column Then errFlag = True
End If
Next

'IF CntFlag was set above, Display the Error Dialog and exit the routine
If errFlag = True Then
DisplayMsg = "The Component Sheets Do Not Have the Same Number of Columns" & Chr(10) & Chr(10)
DisplayMsg = DisplayMsg & EMsg & Chr(10)
DisplayMsg = DisplayMsg & "Correct the Sheets Before Continuing" & Chr(10) & "Routine is Exiting"
Response = MsgBox(DisplayMsg, vbCritical, "wsMstr Column Count Error")
GoTo finished
End If

'Combine the sheets
For i = 2 To Sheets.Count
If Right(Sheets(i).Name, 1) <> "+" Then
Worksheets(i).Activate
lstCol = ActiveSheet.Cells(1, 16384).End(xlToLeft).Column
lstRow1 = ActiveSheet.Cells(65536, "A").End(xlUp).Row
Range("A" & j, Cells(lstRow1, lstCol)).Copy
With ws1.Range("A" & lstRow2)
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False
lstRow2 = ws1.Cells(65536, "A").End(xlUp).Row + 1
j = 2
End If
Next

'Determine the Last Cell in the wsMstr worksheet

With Sheets(wsMstr)
.Activate
.ListObjects.Add(xlSrcRange, Range("$A$1:" & LstCell), , xlYes).Name = "Tbl_Mstr"
.Range("A1").Select
End With

finished:

With Application
.ScreenUpdating = False
.EnableEvents = False
.StatusBar = False
End With

Exit Sub

errHandler:

'If the wsMstr worksheet does not exist a Subscript out of Range error will
'occur. Create the wsMstr worksheet and return / continue
If Err.Number = 9 Then
ActiveSheet.Name = wsMstr
Resume comeBack
Else
errorMsg = "An error has occurred." & Chr(10) & Chr(10) & Err.Number & " - -" & Err.Description
MsgBox errorMsg, vbCritical, "Error Message - " & ThisWorkbook.Name
Resume finished
End If

End Sub

0

LVL 42

Expert Comment

ID: 39757458
you should just use SSIS foreach loop

How to load data from multiple Excel sheets to any destination in SSIS
0

LVL 51

Expert Comment

ID: 39757547
just be careful with some of the suggested solutions because some of the other suggestions require all sheets in the Excel file must have the same structure.
0

Author Closing Comment

ID: 39761387
Greate Feedback . I tested the two first proposals and worked fine . I do have the same structure for all sheets (represent sales from all substores of the same company. I retreived the table easily as proposed  by Mark and after using a Foreachloop it worked perfectly.
Just notice, that I tested the solution as proposed by Rgonzo i found out that  it works  in newer version than visual studio 2005 ('outputbuffer' if  Iam correct its not supported in VS2005).
Thanks all of you  for your immediate responce
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.