By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

Use Excel modified date in Stored Procedure

code green
Expert 100+
P: 1,726
Within a stored procedure I am creating a table from an Excel spreadsheet
Expand|Select|Wrap|Line Numbers
  1. /*Create tmp table from XLS*/
  2. SELECT * INTO tmp_cons FROM
  3. OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;
  4. Database=\\[server]\path\to\Excel\spreadsheet.xls',
  5. 'SELECT * FROM [sheet1$]')
Then joining this table to update another with new products
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO consum_stk (part,qty)
  2. SELECT tmp_cons.Part,Stock FROM tmp_cons
  3.     LEFT JOIN consum_stk 
  4.     ON tmp_cons.Part = consum_stk.part
  5. 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
Nov 9 '09 #1

✓ answered by 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
  1.  
  2. declare @tblDirCheck table (RowNum int IDENTITY(1,1),CmdResult varchar(max))
  3.  
  4. set @tsql = 'dir \\[server]\path\to\Excel\spreadsheet.xls /TW'
  5.  
  6. insert into @tblDirCheck(cmdresult)
  7.   exec @ErrorNumber = master..xp_cmdshell @tsql         
  8.  
  9.  
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

Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Nov 9 '09 #2

code green
Expert 100+
P: 1,726
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?
Nov 9 '09 #3

ck9663
Expert 2.5K+
P: 2,878
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
  1.  
  2. declare @tblDirCheck table (RowNum int IDENTITY(1,1),CmdResult varchar(max))
  3.  
  4. set @tsql = 'dir \\[server]\path\to\Excel\spreadsheet.xls /TW'
  5.  
  6. insert into @tblDirCheck(cmdresult)
  7.   exec @ErrorNumber = master..xp_cmdshell @tsql         
  8.  
  9.  
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
Nov 9 '09 #4

code green
Expert 100+
P: 1,726
Thanks ck9663. I will study your solution before implementing.
Particuarly the master..xp_cmdshell
Nov 11 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.