1

I've tried a few different ways to load data from Excel into SQL Server, as shown here:

Use [TestDatabase]

SELECT * 
INTO dbo.tbl_SST_Personnel
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml;HDR=YES;Database=C:\Users\rshuell001\Desktop\DATA - INPUT FILES\STT_Personnel.xlsx',
       'SELECT * FROM [tbl_SST_Personnel]')
GO

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\rshuell001\Desktop\DATA - INPUT FILES\STT_Personnel.xlsx;', 'SELECT * FROM [tbl_SST_Personnel]')

select * from tbl_SST_Personnel

Insert into tbl_SST_Personnel 
    Select * 
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Users\rshuell001\Desktop\DATA - INPUT FILES\STT_Personnel.xlsx;HDR=YES', 'SELECT * FROM [tbl_SST_Personnel]')

I keep getting this error message for the first 2 scripts.

Msg 7438, Level 16, State 1, Line 13
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

The 3rd script throws this error:

Msg 7302, Level 16, State 1, Line 14
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Any idea how I can get this working?

Thanks to all.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ASH
  • 20,759
  • 19
  • 87
  • 200
  • 1
    Have you took a look at this link? https://blogs.msdn.microsoft.com/farukcelik/2010/06/04/accessing-excel-files-on-a-x64-machine/ – FLICKER May 19 '16 at 22:20
  • Have looked at http://stackoverflow.com/questions/29567548/microsoft-ace-oledb-12-0-cannot-be-loaded-in-process-on-a-64-bit-sql-server – Jim Hewitt May 20 '16 at 04:04
  • Try to use the 64 bit version of OLEDB [AccessDatabaseEngine_X64.exe](https://www.microsoft.com/en-us/download/details.aspx?id=13255). You must uninstall the 32 bit version and install the new one. – Roberto Ferraris May 11 '23 at 13:59

1 Answers1

0

There is no way you can invoke a 32-provider from 64-bit SQL Server or vice versa.

So, you have any of these options:

  1. 64-bit Office
  2. 32-bit SQL Server 2008
  3. 32-bit SQL Server 2012

But, personally, I'm really not a fan to import the excel file data into SQL server table by using T-SQL.

In the real world, the SSIS is far more flexible to handle the Excel file import than T-SQL. If you are using the SSIS to import the data from Excel to SQL server, then it is no problem that if you have a 32- bit Office and 64-bit SQL server.

You can run the SSIS in a 32-bit debugging mode of setup an SQL agent job to run the SSIS job in 32-bit mode.

Pouria Sharif
  • 156
  • 1
  • 15