upload excel file to database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

upload excel file to database

Author

Topic

magikminox
Starting Member

27 Posts

Posted - 2008-07-01 : 03:44:11

I have an excel file that gets its data from a feed.i want users to be able to uplaod this file into a databsase table easily.
Thi should be done either from the excel interface or from an application.
Any ideas.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-01 : 04:05:36

You could create a stored procedure along these lines, passing the filename and worksheet names in as parameters and then call this from a macro.


declare @excelfile varchar(max), @WorkSheet varchar(max), @SqlCmd varchar(max)

set @excelfile = 'C:\Test\test.xls'
set @WorkSheet = '[Sheet1$]'

set @SqlCmd = 'select * FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @excelfile + '";Extended properties=Excel 8.0'')...' + @WorkSheet + ' a'

exec (@SqlCmd)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources