Import Excel XLSX files into Temp Table in SQL 2012 64Bit

• james-711026

  SSC Veteran

  Points: 253

  More actions

  February 18, 2014 at 12:56 pm

  #282582

  Hi - I am hoping someone can help with this issue I am having with trying to import XLSX files into SQL 2012 64 Bit.

  I have installed the Access driver (AccessDatabaseEngine_x64.exe)

  I have configured the script to run the following SP

  sp_configure 'show advanced options', 1

  GO

  RECONFIGURE WITH OverRide

  GO

  sp_configure 'Ad Hoc Distributed Queries', 1

  GO

  RECONFIGURE WITH OverRide

  GO

  EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

  GO

  EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

  GO

  So I first create my Temp Table

  The run the SP above then I run the insert into the Temp table defined

  INSERT INTO tempdb.dbo.TempTRBZ (IsNew,CoID, Zip, City, County,StateCode,Rate,Taxable,TaxShip,TaxLab,CountryID,StateID)

  SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=C:\Temp\NotInTrbzJan.xlsx;HDR=YES','SELECT * FROM [Data$]')

  GO

  --Disable AdHoc

  sp_configure 'show advanced options', 1; RECONFIGURE;

  GO

  sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE;

  GO

  The error message I get back is

  Msg 7303, Level 16, State 1, Line 4

  Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

  Can someone tell me if or what I have set wrong on the import? Using SSIS at this point is not a real option.

  Thanks in advance.