Use Excel modified date in Stored Procedure

code green

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

Expand|Select|Wrap|Line Numbers

 /*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

Expand|Select|Wrap|Line Numbers

 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

Quote:

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:

Expand|Select|Wrap|Line Numbers

  
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

Use Excel modified date in Stored Procedure

re: Use Excel modified date in Stored Procedure

re: Use Excel modified date in Stored Procedure

re: Use Excel modified date in Stored Procedure

re: Use Excel modified date in Stored Procedure

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about