sql server new users: Import an Excel spreadsheet -- sql server new users

You're in the

sql server new users

group:

Import an Excel spreadsheet

SAC
6/22/2007 5:46:51 AM

sql server new users:

I'm using 2000.

I'd like to know how to write the code to use in a stored procedure to
import a spreadsheet in a table. I've used dts to do it, but now I need to
write the code to do it.

Can you point me in the right direction or give me some generic code?

Thanks.

Re: Import an Excel spreadsheet

Hari Prasad
6/23/2007 12:00:00 AM

You could use linked servers and create a link from SQL to Excel. After that
use Insert commsnd to load data. Example:

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\filename.xls',
NULL,
'Excel 5.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT * FROM ExcelSource...Sheetname

CREATE TABLE test_load_excel
(id int,
name varchar(255))
GO

INSERT INTO test_load_excel
SELECT *
FROM ExcelSource...Sheetname

SELECT *
FROM test_load_excel

Thanks
Hari

[quoted text, click to view]

Re: Import an Excel spreadsheet

SAC
6/23/2007 11:14:19 AM

WOW! Thanks!
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.

home · blog · groups

Questions? Comments? Contact the dn