Use Excel modified date in Stored Procedure

[code green]

Within a stored procedure I am creating a table from an Excel spreadsheet

/*Create tmp table from XLS*/
SELECT * INTO tmp_cons FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;
Database=\\[server]\path\to\Excel\spreadsheet.xls',
'SELECT * FROM [sheet1$]')

Then joining this table to update another with new products

INSERT INTO consum_stk (part,qty)
SELECT tmp_cons.Part,Stock FROM tmp_cons
    LEFT JOIN consum_stk 
    ON tmp_cons.Part = consum_stk.part
WHERE consum_stk.part IS NULL

consum_stk also has a last_updated column with GETDATE() as default.
Is it possible to use the Excel file 'modified' date property to populate this field

[ck9663]

Yes. A default value will be used if you did not specify any value into a column when the record was created. You just have to get the property of file from the OS side to get the value of the modified date.

Good luck!!!

--- CK

[code green]

Thanks for replying

You just have to get the property of file from the OS side to get the value of the modified date.

This is the bit I am stuck on.
Can this be done from a stored procedure?

[ck9663]

Yes. I'm sure there are a number of other ways of doing it. Until someone suggest something else try the following pseudo-code:

 
declare @tblDirCheck table (RowNum int IDENTITY(1,1),CmdResult varchar(max))
 
set @tsql = 'dir \\[server]\path\to\Excel\spreadsheet.xls /TW'
 
insert into @tblDirCheck(cmdresult)
  exec @ErrorNumber = master..xp_cmdshell @tsql         
 
 

Now you'll have a single row on your table variable. Parse the cmdresult column to get your modified date/time. Just be careful, if one of the sheets was modified, the date/time will change even if your target sheet were not modified at all.

Happy Coding!!!


--- CK

[code green]

Thanks ck9663. I will study your solution before implementing.
Particuarly the master..xp_cmdshell