1

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?

Omi
  • 427
  • 7
  • 21
  • 42
  • What about using the SQL Server Management Studio Import Data wizard? It has a driver for Excel (or CSV, if you can convert the data to that format) and a SQL Native Client driver that doesn't rely on OLEDB. Is this a one-time requirement, or do you want to automate it as much as possible? – Jordan Rieger Jun 18 '19 at 19:13
  • Can you try first converting the Excel files to CSV and then use Bulk Import? https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line – level3looper Jun 18 '19 at 19:17
  • @Jordan: I can’t since I don’t have privilege to do any such stuff on server. – Omi Jun 19 '19 at 04:04
  • @level3looper: Again I don’t have access to do bulk import through sql server wizard. I am trying to achieve this through procedure or plain sql queries. Is there a way to do that? – Omi Jun 19 '19 at 04:12

3 Answers3

1

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.

Jordan Rieger
  • 3,025
  • 3
  • 30
  • 50
0

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');
Rbell
  • 46
  • 1
  • 10
  • Are you asking me to hardcode those values for inserting data. There are many many records in the excel sheet. I am just looking for a way to retrieve all the data from excel sheet without using OLEDB or OPENROWSET() function. – Omi Jun 19 '19 at 04:07
0

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.

  1. 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');

  2. 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 :-)

RaturiMic
  • 73
  • 1
  • 8