none
Giving dynamic sheet name while importing data from Excel in OPENROWSET query RRS feed

  • Question

  • I want to import data from Excel to table in SQL server. Sheets in Excel file can have any name. I want to get data from first sheet of the excel file. 
    CREATE PROC CopyExcelDataToTempTable
    @ExcelPath VARCHAR(1000)
    AS
    BEGIN
    	EXEC sp_configure 'show advanced options', 1;
    	RECONFIGURE;
    	
    	EXEC sp_configure 'ad hoc distributed queries', 1;
    	RECONFIGURE;
    
    	EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;
    	EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;
    
    	INSERT  INTO TempData
    		SELECT *
    		FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    		SELECT CONCAT('Excel 12.0 Xml;HDR=YES;Database=', @ExcelPath),
    		'SELECT * FROM [Sheet1$]');
    END;
    Currently I am giving Sheet1 as static name in Select statement. But my sheet name is not fixed. I want to create a dynamic query. If getting Sheet name is not possible then can we put sheet with its index in Select query? There is no front end. Whatever I want to do is in SQL server 2012. Is there any solution for this problem?
    • Edited by Ajinkya Jagtap Monday, December 15, 2014 4:48 AM Adding one more query
    Monday, December 15, 2014 4:39 AM

Answers

  • Hi Ajinkya,

    To get the sheet names from an excel, you can use the Stored Procedure SP_TABLES_EX. See the below code.

    EXEC sp_addlinkedserver 'ExcelSource', '',
       'Microsoft.ACE.OLEDB.12.0',
       'c:\worksheet.xls',
       NULL,
       'Excel 8.0'
    EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
    GO
    EXECUTE SP_TABLES_EX 'ExcelSource'

    the names in the worksheet.xls and execute result are like below.

    Table name
    a
    b
    c
    d

    Regarding your description, you need the first sheet of your excel. As the result shows, the sheet names are sorted, so there’s no guarantee to get the first one. The SP can work as a workaround when there’s only one sheet in your excel or the required sheet’s name is customized to be sorted at the top first.

    If you have any question, feel free to let me know.

    Best regards,
    Eric Zhang

    Friday, December 19, 2014 7:14 AM
    Moderator

All replies

  • You can use dynamic SQL. You can see good sample in the following link:

    OpenRowset with dynamic sql variable insert query


    T-SQL Articles
    T-SQL e-book by TechNet Wiki Community
    T-SQL blog

    Monday, December 15, 2014 5:57 AM
  • use variable for SheetName.

    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com

    Monday, December 15, 2014 6:10 AM
  • Why not use SSIS for this?

    This is supported straight out of the box in SSIS using expression builders

    see an example here

    http://visakhm.blogspot.in/2013/09/exporting-sqlserver-data-to-multiple.html

    You can even create a new sheet if you want and then load it as shown in above link


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, December 15, 2014 6:10 AM
  • I don't have any front end or business logic in any other language like C# or Java through which I can get sheet name. My aim is to create a scheduled job which will automatically select excel and copy its data to table in SQL database. Thats why I want to get sheet names of excel which is selected.

    Monday, December 15, 2014 6:21 AM
  • I don't have any front end or business logic in any other language like C# or Java through which I can get sheet name. My aim is to create a scheduled job which will automatically select excel and copy its data to table in SQL database. Thats why I want to get sheet names of excel which is selected.

    Then SSIS would be the best option

    You can have the scheduled job call the SSIS package and it will have a simple data flow task which will copy data from excel to sql database. And you can have a variable to hold the EXcel path/sheetname which can be used to set excel file path/ sheet name inside data flow task.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, December 15, 2014 8:28 AM
  • Hi Ajinkya,

    To get the sheet names from an excel, you can use the Stored Procedure SP_TABLES_EX. See the below code.

    EXEC sp_addlinkedserver 'ExcelSource', '',
       'Microsoft.ACE.OLEDB.12.0',
       'c:\worksheet.xls',
       NULL,
       'Excel 8.0'
    EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
    GO
    EXECUTE SP_TABLES_EX 'ExcelSource'

    the names in the worksheet.xls and execute result are like below.

    Table name
    a
    b
    c
    d

    Regarding your description, you need the first sheet of your excel. As the result shows, the sheet names are sorted, so there’s no guarantee to get the first one. The SP can work as a workaround when there’s only one sheet in your excel or the required sheet’s name is customized to be sorted at the top first.

    If you have any question, feel free to let me know.

    Best regards,
    Eric Zhang

    Friday, December 19, 2014 7:14 AM
    Moderator
  • Is it possible to perform the action without using the linked server?
    Monday, June 24, 2019 7:30 PM