Excel 2013 data to sql server 2012 table

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

Answer 1

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

Answer 2

Thank you for the reply but my requirement to use in stored Procedure directly and not to use SSIS package.

Answer 3

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

Answer 4

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)".

Answer 5

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.