Moving Data from Excel to Temp Table

Question

Moving Data from Excel to Temp Table

George Damien Varkey

SSC-Addicted

Points: 440
More actions
November 8, 2006 at 5:20 am

#115067

How can I move data from Excel to a Temp Table.
Problem is I have read only access on that database, so cannot create a permanent table.
I tried using the DTS but no luck
Thanks
George

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

George

You can use the OPENROWSET function to run a query against the Excel sheet, and then dump the results in the temp table. The syntax for using the Jet OLE DB provider is a bit fiddly, but you should be able to get it to work.

John

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

This suggestion was posted previously ... but thought it might help you

Select ... FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DATABASE=\\server3\tables\Survey_Results_(10-13-06).xls', 'Select * from SurveyResults$')

The $ after the Excel sheet name is critical

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Doug Perlich SSC Journeyman Points: 76 More actions · Answer 3

Wow, I'm very interested in doing this - I am often requested to join production data, which I don't have write access to, with "outside data". If I could get a SQL like the above working, I could easily do this!

I tried:

Select *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=\\server\us\DWarehouse\Public\Book1.xls',

'Select * from Sheet1$')

Where I have an XLS file named "Book1.xls" (default Excel filename) and within it is the default sheet name "Sheet1". At least I think that's what the syntax posted above means.

But I get the following error:

Server: Msg 7415, Level 16, State 1, Line 1

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

Huh?!

thanks 🙂

George Damien Varkey SSC-Addicted Points: 440 More actions · Answer 4

Hi Doug Perlich,

What you could do is use the Global Temp Table.

Open Query Analyzer and create a Global Temp table (##TempTable)

The use the DTS (Import & Export) to load data into this Temp Table, ensure that you select the Tempdb database (in the destination screen).

I have checked it works.

George

Doug Perlich SSC Journeyman Points: 76 More actions · Answer 5

Thank you George.

I was able to use the import wizard to import the data and now join it with production data - and I don't have write permission to production! (Well, I have read/write permission to tempdb). I was also able to save it as a DTS structured-storage file for future use.

I did run into trouble because it insists on creating the table. It won't let me select the ##tablename even though it exists - I had to go back and forth in the wizard and finally tricked it into creating the table with the same name that already exists - it errors but continues during the load. I can now get rid of the create statement in the DTS the next time I run it.

I'll play around with running the resulting DTS package but for now this ought to work! Thanks!

-Doug