Get Excel Sheet into Temp Table Using a Script

Get Excel sheet into temp table using a script

There are 5 possible causes for this error.

1. The jet engine must be installed on the server. Installing MS Office on the server sorts that out.
2. The path to the xls is relative to the server, not the workstation you're running the command from
3. The account that runs the SQL server service must have write access to the folder where the xls is. One possible solution is to changing the temp= and tmp= environment variables of the service startup account (eg. Administrator) to (for example) c:\temp, then enable Full Control on c:\temp to Everyone

4...

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

5....

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 0 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 0
GO 

Now I don't know why this works, especially considering that everyone else says they should be set to 1. For me however, setting them to zero, did the trick for the following SQL statement on SQL Server 2008R2 32bit and M$ Office 2007

Select * 
into [temp_table$]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\EXPENDITURE REPORT.xls;HDR=YES;IMEX=1',
'SELECT * FROM [EXPENDITURE SHEET$]')

Note: I purposely have used an example in which both the filename and the worksheet name have spaces to show that this can be done.

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

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.

Paste 10000 records from excel sheet into a temp table

Insert Into #Temp
Select *
From MySpreadsheet

Any tablename preceeded by a pound (or hashtag, for you young whippersnappers) is a temp table. Technically, though, you need to name each field instead of using the asterisk ("*"). So it's more like:

Insert Into #MyTempTable(Field1, Field2)
Select Field1, Field2
From MySpreadsheet

Query data in Excel file with SQL Server table

Seeing as you haven't provided any data model, etc. I have made some assumptions here. One way to do this would be to create a temporary table, populate it, and then join this to the tables you want to query.

The first step is to create your temporary table, so something like this:

CREATE TABLE #temp (order_number INT);

(Which assumes that you have a unique id for each order number.)

Then you need to populate this table, and how you do this depends very much on how many order numbers are in your Excel workbook. You could write an SSIS package (but this would mean materialising your temporary table first), use the export wizard, etc.

One simple method is to write an Excel formula into a new column for each row in your list, then just copy this down to the bottom, copy and paste it into SSMS and execute the script you generated. You might end up with something like this as your formula:

CONCATENATE("INSERT INTO #temp SELECT ", A1, ";")

Once you have your data in a temporary table, or maybe even a physical table, you can take your queries and JOIN them to this, e.g.:

SELECT o.* FROM MyOrders o INNER JOIN #temp t ON t.order_number = o.order_number;
SELECT r.* FROM MyRefunds r INNER JOIN #temp t ON t.order_number = r.order_number;

Using openrowset to read an Excel file into a temp table; how do I reference that table?

I don't have time to mock this up, so I don't know if it'll work, but try calling your table '##mytemptable' instead of '#mytemptable'

I'm guessing your issue is that your table isn't in scope anymore after you exec() the sql string. Temp tables preceded with two pound symbols are globally accessible.

Don't forget to drop it when you're done with it!

Problem with excel sheet upload into sqlserver

I have modified the query so, now it is able to import the data from excel.

EXEC('SELECT * INTO temp FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=' + @ba_bm_status + ';HDR=YES;IMEX=1'',' +
'''SELECT * FROM [qry_BA_Controlling (Report)$]'')');

I have included IMEX=1, which tells the driver to use import mode.

Load the data from an Excel File and load into a Temp Table

Assuming your first statement works, to put it into a temp table you need to do this:

SELECT A.*
INTO #MyTempTable
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0;Database=D:\b1.xlsx; hdr=yes',
                'SELECT * FROM [Co Contact$]') AS A;

Then you can play with #mytemptable

Select * From #mytemptable;

---

Related Topics