Import data from Excel file into SQL Server

Question

i am trying to import data from an EXCEL file into SQL SERVER. When i run this query

SELECT * INTO EXCEL_IMPORT
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\Users\af\Desktop\Reports\Jan_2016_Ships.xlsx; HDR=YES; IMEX=1',
'SELECT * FROM [Sheet1$]');

I get this error

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

So i needed to install this driver but after doing some searches i found this :

Install Microsoft.ACE.OLEDB.12.0 driver

To import Excel 2007-2016 files to SQL Server Microsoft.ACE.OLEDB.12.0 driver should be installed.

To download the driver use the following link:

Microsoft Access Database Engine 2010 Redistributable

Don't worry about "Access" in the name.

Warning! x64 driver can not be installed if Microsoft Office 2007-2016 x86 is already installed!

So there is no way to import Excel data to SQL Server x64 using OPENROWSET/OPENDATASOURCE functions on a machine with Microsoft Office x86!

So i couldn't install this driver because i have SQL SERVER x64 and Microsoft Office x86. I don't want to use the simple import wizard because i have many tables and i need to check the data type of the columns... it will be time consuming in my case.

I believe there should be a solution to run my query on SQL SERVER x64 and Microsoft Office x86.

Any suggestions please ? Thank you very much

convert file Jan_2016_Ships.xlsx to Jan_2016_Ships.xls then check — Mansoor
– Mansoor, Commented Nov 4, 2016 at 12:19

Ronak Patel · Accepted Answer · 2016-11-04 14:30:29Z

1

Best way of Import/Export data into Sql Server is Import/Export Wizard facility in SQL Server .

Please refer below link .

http://searchsqlserver.techtarget.com/feature/The-SQL-Server-Import-and-Export-Wizard-how-to-guide

Thanks .

answered Nov 4, 2016 at 14:30

Ronak Patel

6405 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JuniorDev Over a year ago

thank you for your advice but i am looking for a solution how to run it directly from a query not through the import wizard

tgr · Accepted Answer · 2016-11-04 12:20:30Z

0

If you run it only once you may can run the query in a SSIS package and change the solution to 32bit-mode (right-click the solution, properties, debugging, run64bitRuntime = false)

answered Nov 4, 2016 at 12:20

tgr

2541 silver badge9 bronze badges

Comments

Ali Ashkar Ct · Accepted Answer · 2016-11-04 14:17:30Z

0

Is microsoft excel installed in your system?. this might solve your problem.

Also you can import to a table manually by SQL management studio.

right Click on database > Tasks > Import Data

answered Nov 4, 2016 at 14:17

Ali Ashkar Ct

505 bronze badges

3 Comments

JuniorDev Over a year ago

thank you for your suggestion but i am looking for a solution how to run it directly from a query

Ali Ashkar Ct Over a year ago

get driver from this link. install it and try query again. microsoft.com/en-us/download/details.aspx?id=13255

JuniorDev Over a year ago

Thanks for your suggestion but i receive the same error when i try to install it "Can not install 64 bit version of MS Database Engine because i have 32 bit office products"

ASH · Accepted Answer · 2016-12-05 16:59:34Z

0

It looks like you are mixing 32-bit drivers with 64-bit drivers. This is a big nono. These two resources are probably your best place to start.

http://www.cnblogs.com/anorthwolf/archive/2012/04/25/2470250.html

http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm

answered Dec 5, 2016 at 16:59

ASH

20.5k28 gold badges115 silver badges247 bronze badges

Collectives™ on Stack Overflow

Import data from Excel file into SQL Server

4 Answers 4

1 Comment

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related