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

Question

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?

Answer 1

Its quite a bit of work , but feasible

I am assuming you have read-only access to prod db with temp table creation and insert into temp table statements. No access to sql wizards or any elevated privileges.

Below 4 steps we have to preform in our dev sql box. Then generate the create #temptable script and auto generated insert statements and run them in production temp database

1. save the excel as csv file to dev machine
2. create a #temptable/ ##temptable ( because usually developers wont have create table permissions in production database ) Note : if the table has more columns use wizard to generate the auto crate table statement. How ? Answer is : Right click on Database --> Task --> Import Data --> on sql server import and export wizard, select the data source as flat file source and browse the csv file what you created in step1. --> Next --> on the choose a destination select destination as sql server native client 11.0 --> next --> on select source table and views screen choose edit mappings --> on column mappings screen click edit sql button which gives auto generated create table statement.

Copy the auto generated script --> edit the name to ##TempTable_CSV --> Create the temp table.

3. Now table is ready. For data , Use Bulk insert by giving the csv path BULK INSERT ##TempTable_CSV FROM 'C:\Users<username>\Downloads\temp.csv' WITH ( FIRSTROW = 2, FORMAT = 'CSV');

4. To auto generate the insert statements from the temp table ( for example assuming the temp table has id , varchar columns ) select 'insert ##TempTable_CSV values ('+Cast(Id as varchar(5))+','''+SomeVarcharColumn+''') from ##TempTable_CSV

which will generate all the insert into statements Note : Make sure to handle null values

Finally you can copy the create temp table script from step 2 + auto generated insert statements from step 4 , run it in prod temp db.

Enjoy :-)

Answer 2

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.

2. 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";

3. 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.

Answer 3

super mcguyver way using table variables. use the concat function on your excel sheet to prepare the data for the insert part.

declare @temp1 table (id int identity(1,1),name varchar(255),job_title varchar(255),work_title varchar(255),work_email varchar(255));
insert into @temp1 (name,job_title,work_title,work_email)
values
('John','Electrician','level 3 lightning wizard','john@ex.com'),
('amy','Java Developer','Cyber Coffee Slinger','amy@ex.com');