Query to get file name and date modified from a directory

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  September 3, 2014 at 8:31 pm

  #290317

  Hi Experts,

  can you please give a query that will give me a file name and date modified from a directory into a sql table

  basically what we need is a query that will allow me to provide a directory and a variable for number of days for instance 1 day old and I want to be able to able to delete all files older than that date and of course I want to be able exclude files of a particular type where I would give it a wild card statement for example say i wanted to save all csv files i would have the wild card say <> '%.csv'

  Thanks!

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  September 3, 2014 at 9:28 pm

  #1743063

  I have written the query . Need to take care of few things as per requirement.

  DECLARE @PathName VARCHAR(256)='E:\abcd\' ,

  @CMD VARCHAR(512)

  IF OBJECT_ID('tempdb..#CommandShell') IS NOT NULL

  DROP TABLE #CommandShell

  CREATE TABLE #CommandShell ( Line VARCHAR(512))

  SET @CMD = 'DIR ' + @PathName + ' /TC'

  PRINT @CMD -- test & debug

  -- DIR F:\data\download\microsoft /TC

  -- MSSQL insert exec - insert table from stored procedure execution

  INSERT INTO #CommandShell

  EXEC MASTER..xp_cmdshell @CMD

  -- Delete lines not containing filename

  DELETE

  FROM #CommandShell

  WHERE Line NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'

  OR Line LIKE '%<DIR>%'

  OR Line is null

  SELECT

  Line File_details

  ,LEFT(Line,20) AS file_date

  ,Rtrim(LTRIM(substring(Line,21,18))) as file_size

  ,Rtrim(LTRIM(substring(Line,39,Len(Line)))) as file_Names

  from #CommandShell

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  September 4, 2014 at 11:28 am

  #1743287

  unless there is another reason for loading the results into a sql database, to accomplish what you want you could just use powershell.

  $path = "c:\temp\directory"

  $nodays = "-1"

  $excludes = "*.csv, *.doc"

  get-childitem $path -file -exclude $excludes | where {$_.Lastwritetime -le (get-date).adddays($nodays} |remove-item

  this will remove all files except csv and docs older than 1 day from c:\temp\directory

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• Jeff Moden

  SSC Guru

  Points: 1004589

  More actions

  September 4, 2014 at 4:52 pm

  #1743361

  Please, everyone... remember that there is such a thing as "DOS INJECTION". Take the appropriate safeguards and delouse the inputs.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)