Bulk import Excel file into SQL Server

Question

I am trying to bulk import an Excel file into SQL Server. I have tried a lot of effort but not get proper result. I have ran following query but got an error. Please try to help me I am stuck on this. I have SQL Server 2012 and Excel 2013 installed

SELECT * 
INTO temptable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=D:\File.xls;IMEX=1',
                'SELECT * FROM [Data$]')

Error:

Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

is this a one-off import, or something you will want to run over and over again ? — sarin, Aug 04 '15 at 10:57
You could try it using the built-in Wizard. You could also try using a .csv file instead. Is there a reason you need an Excel file implicitly? — Joeri, Aug 04 '15 at 10:58
@sarin: Yes I want to run it over an over again using stored procedure — S. S. Rawat, Aug 04 '15 at 11:02
Sounds you got a problem with the provider. Be sure that provider and the SQL Server you are using are both 64-64 or 32-32 bits — jean, Aug 04 '15 at 11:05

score 1 · Answer 1 · answered Aug 04 '15 at 11:10

Here are examples for excel versions 97-2010, which should have you covered :

--Excel 2007-2010
SELECT * INTO #temptable
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\File.xls',
    'SELECT * FROM [ExcelTabName$]'); -- Replace ExcelTabName with actual tab name

--Excel 97-2003
SELECT * INTO #temptable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;Database=D:\File.xls',
    'select * from [ExcelTabName$]'); -- Replace ExcelTabName with actual tab name

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

I think is office versions issue just try this code

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                       'Excel 8.0;Database=C:\Export.xls;', 
                       'SELECT id_sale FROM [ExportSheet$]')

More help

edited May 23 '17 at 10:26

Community

1
1

answered Aug 04 '15 at 11:07

Nimesh Gami

361
1
2
18

Bulk import Excel file into SQL Server

2 Answers2