Query to get file name and date modified from a directory

Question

Post reply

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!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Nagaram SSCrazy Points: 2110 More actions · Answer 1

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 · Answer 2

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: 1000539 More actions · Answer 3

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)
Intro to Tally Tables and Functions