Ìnsert into temp table from excel file

Question

Ìnsert into temp table from excel file

blacklabellover2003

SSC Eights!

Points: 875
More actions
September 24, 2010 at 9:19 am

#387859

Hi all,
I need to Insert into a temp table called #InsertQueues all the contents of an excel file, about 2 columns.
The Excel columns don't have names, but contain phone numbers and a text column.
Cheers,
Jim.

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

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

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

Jim, you can use the data import wizard, or lookup OPENROWSET() in BOL. With OPENROWSET, you can select directly from the spreadsheet into a temp table, or any other table for that matter.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Vinod Suthar Old Hand Points: 322 More actions · Answer 2

Jim,

You can use following query to import Excel sheet into temp table.

SELECT * into #InsertQueues FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;HDR=NO;IMEX=1;Database=D:\path\excel_file.xls', 'SELECT * FROM [sheet1$]')

Please specify complete path of your excel file and worksheet name.

Vinod Suthar

blacklabellover2003 SSC Eights! Points: 875 More actions · Answer 3

Can anyone help with this? This is the code I've got so far, but it's for Excel, whereas I need to point it to a tab delimited Notepadfile

SELECT * into #InsertQueues FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;HDR=NO;IMEX=1;Database=D:\path\excel_file.xls', 'SELECT * FROM [sheet1$]')

(Although the notepad columns arent' named, the next piece of code is expecting one to be named 'Phone', the other 'msg')

And then run this code..

insert into DBSrvr2.Queues.dbo.OutQueueFuture ( Phone,msg,datestamp,routing,Status,result,origin,service,keyword,keyid,OCKeyid)

Select Phone,msg,'2010-09-10 12:00:00.000',routing,0 as Status,result,origin,Service,Keyword,154394 as Keyid,3636748 as OCKeyid

from #InsertQueues where Phone<>'xxxxxxxxxxxxx'

Vinod Suthar Old Hand Points: 322 More actions · Answer 4

You can use following script in order to import Text file.

SELECT * FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=D:\File_path\;HDR=NO','SELECT * FROM FileName.csv')

OR

Alternatively, You can create schema file for custom delimiter in the same path where your text file kept

and then run above code.

Vinod Suthar

blacklabellover2003 SSC Eights! Points: 875 More actions · Answer 5

Hi Vinod,

I can't use OpenRowset as our servers are 64 bit. Is there a workaround for this, or another command?

Thanks,

Jim.

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

Try this site see if it helps.

http://connect.microsoft.com/SQLServer/feedback/details/581640/trying-to-use-openrowset-to-import-2003-excel-file-into-64-bit-2008-r2-sql-server

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]