dynamic excel file upload - 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 2000 Forums

Import/Export (DTS) and Replication (2000)

dynamic excel file upload

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-07-24 : 13:13:33

Hi,

I have 2 sheet in excel file to be uploaded to 2 different tables.

I do this by MS-EXCEL 97-2000 and MS-OLE DB SQL server connection and separately move each sheet because sheet1$ have to be filtered with condition before upload.

All that works fine, now I have to read data from diff excel file for each day. FOR eg

Book072307
Book072407
Book072507

Any suggestion is appreciated. I am totally new to this, so any feedback will be great.

thanks in advance.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-07-27 : 09:38:19

You can do this easily without a DTS. I'd create a stored proc and either schedule that as a sql job or run on demand. This should get you pointed in the right direction.

Declare @sql nvarchar(4000)
Declare @path nvarchar(200)
Declare @file nvarchar(200)
Declare @table nvarchar(200)
Declare @sheet nvarchar(200)

--change variables or pass as input params in stored proc
Set @path = 'C:\'
Set @file = 'Book' + Replace(Convert(varchar(8), GetDate(), 1), '/', '') + '.xls'
Set @table = '##test'
Set @sheet = 'Sheet2$'

Set @sql = 'Insert Into ' + @table +
' Select * From OpenRowSet(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=' + @path + @file + ';HDR=YES'',
''Select * From [' + @sheet + ']'')'

Exec master.dbo.sp_ExecuteSql @sql

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-07-27 : 11:37:54

Thanks for your feedback.

I will try your example.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources