Solved

Read data from all sheets of an excel file using OPENROWSET

Posted on 2014-01-04

1 solution

Medium Priority

2,942 Views

Last Modified: 2016-02-10

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

Comment

Question by:Geo_Bout

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

6 Comments

LVL 52

Expert Comment

by:Rgonzo1971

ID: 397571822014-01-05

Hi,

pls refer to

http://www.mssqltips.com/sqlservertip/1674/retrieve-excel-schema-using-sql-integration-services-ssis/

Regards

LVL 51

Accepted Solution

by:Mark Wills

Mark Wills earned 2000 total points

ID: 397573492014-01-05

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

Open in new window

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

Open in new window

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')
   exec sp_dropserver 'my_Excel', 'droplogins';

Open in new window

LVL 16

Expert Comment

by:Jerry Paladino

ID: 397573652014-01-05

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
        .DisplayAlerts = False
        .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
    LstCell = Worksheets(wsMstr).Cells.SpecialCells(xlCellTypeLastCell).Address

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

finished:

    With Application
        .DisplayAlerts = False
        .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
        Sheets.Add Before:=Worksheets(1)
        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

Open in new window

LVL 43

Expert Comment

by:Eugene Z

ID: 397574582014-01-05

you should just use SSIS foreach loop

see full ready to use project , just adjust for yourself:
How to load data from multiple Excel sheets to any destination in SSIS
http://www.codeproject.com/Tips/395541/How-to-load-data-from-multiple-Excel-sheets-to-any

LVL 51

Expert Comment

by:Mark Wills

ID: 397575472014-01-05

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.

Author Closing Comment

by:Geo_Bout

ID: 397613872014-01-06

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

Featured Post

Question has a verified solution.

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

Automating Microsoft Excel from Access (Part II)

Article by: Dale Fye

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.

Custom Document Properties in Excel

Article by: Martin

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.

Building Probability Models in Excel Part 5: Modeling an Investment Using Discrete Random Variables

Video by: Toby

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…