Search code examples
sqlsql-serverstored-proceduressql-server-openxml

Import Excel Data Into Temporary Table Without Using OLEDB in SQL Server

I am trying to look for a way to import excel data into a temporary table without using OLEDB since I don't have OLEDB installed in SQL Server and I can not install it due to security restrictions. I am aware of below mentioned ways of doing it but this is not going to help me

Begin Tran
If OBJECT_ID('tempdb..#tblUserImport') IS NOT NULL
Begin
Drop table #tblUserImport
end
Create Table [dbo].[#tblUserImport]
(
id nvarchar(max) NULL,
Name nvarchar(max) NULL,
Job_Title nvarchar(max) NULL,
Work_Email nvarchar(max) NULL
)
INSERT  INTO [dbo].[#tblUserImport]
SELECT  id, Name, Job Title, Work Email
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel12.0;HDR=YES;Database=C:\Users\Desktop\Book2.xlsx', 'SELECT * FROM [Sheet1$]');
select * from [#tblUserImport]
Rollback Tran

I will get the below mentioned error if I execute the openrowset.

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

Is it possible to achieve it using Stored Procedure or any other way?

Solution

  • Here are 3 options:

    1.Do the import from a computer you have admin rights on

    It sounds like you don't have the ability to install OLE or ODBC data providers on the SQL Server machine. But you don't have to run the import from the same machine. As long as you have valid credentials and a working network path to your SQL server, you can run the import from any computer. So you could install the Microsoft ACE OLEDB 12.0 data provider driver on another PC along with SQL Server Management Studio, copy the Excel file there, and then do the import through the wizard.

    1. Try an alternate data provider driver

    There are alternate data provider drivers for Excel sources that may already be installed in your environment. E.g. the Jet OLEDB 4.0 driver mentioned at https://www.connectionstrings.com/excel/. Connection string: 

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
    1. Use Excel formulas to build INSERT statements

    You can use the venerable Excel formula trick alluded to in RBell's answer: Assuming the fields you specified in your question are in A1 through D1, add this formula to cell E1:

    ="INSERT INTO #tblUserImport VALUES ('"&SUBSTITUTE(A1, "'", "''")&"', '"&SUBSTITUTE(B1, "'", "''")&"', '"&SUBSTITUTE(C1, "'", "''")&"', '"&SUBSTITUTE(D1, "'", "''")&"');"

    You then copy this formula down through all rows of your data. Excel will automatically alter the cell references for each row. Note that the SUBSTITUTE() function handles single-quotes in the data that might otherwise break the SQL syntax. Then you simply copy and paste the resulting text out into your SQL window, and run it.