none
Excel 2013 data to sql server 2012 table RRS feed

  • Question

  • Hi 

    I've to export data from Excel sheet which is in xlsx format Excel 2013 to existing table in SQL server 2012 using a stored procedure.

    Please help in.

    Regards,

    Noor

    Tuesday, March 19, 2013 9:51 AM

Answers

All replies

  • Use the SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    You can save the Wizard generated SSIS package.

    You can execute an SSIS package from a stored procedure:

    How to Call SSIS Package from the Stored Procedure


    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    • Proposed as answer by Satheesh Variath Tuesday, March 19, 2013 11:06 AM
    • Unproposed as answer by SQL2012BI Tuesday, March 19, 2013 12:51 PM
    • Marked as answer by SQL2012BI Monday, March 25, 2013 10:35 AM
    Tuesday, March 19, 2013 10:10 AM
    Moderator
  • Thank you for the reply but my requirement to use in stored Procedure directly and not to use SSIS package.
    Tuesday, March 19, 2013 12:34 PM
  • You can create a Linked server on the Excel sheet and select the data or use csv instead of Excel and import data with bulk insert or bcp
    Tuesday, March 19, 2013 2:21 PM
  • Hi Christa,

    I've created linked server and now when am selecting the data from excel am getting the below error. 

    I've written like this...

    SELECT * 
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 XML;HDR=YES;Database=E:\VSTF 224263 - Existing Position Impact - Sample File.xls',
    'SELECT * FROM [Sheet1$]');

    Error:

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    Wednesday, March 20, 2013 9:49 AM
  • I've created a new SSIS package and in that I've written all logic. After much search I found that SSIS is good way to load data from excel to SQL server where all the transformation can be taken and we can make data available as we want.

    Thank you Kalman.

    Monday, March 25, 2013 10:37 AM