June 11, 2015 at 4:24 pm
Hello Experts
Can anyone please tell me a good script to find the details of creation date and modified date of all files located in a path please?
I wanna write few custom messages before I delete some files from a path.
Please suggest.
Thanks.
June 12, 2015 at 5:27 am
This powershell script will do that:-
$filepath = "C:\Temp"
gci $filepath | select-object name, lastwritetime, creationtime
Is that what you needed?
June 12, 2015 at 5:32 am
If you need to do some logic you could use:-
$filepath = "C:\Temp"
$date = "2015/06/06 03:50:00"
$files = gci $filepath | select-object name, lastwritetime, creationtime
foreach($file in $files)
{
$fileCreateDate = $file.creationtime
if($fileCreateDate -gt $date)
{
write-host "Do Something"
}
else
{
Write-Host "Do Something Else"
}
}
June 12, 2015 at 5:57 am
Thanks.
Anyway you can help me getting the script in xp_cmdshell?
Thanks.
June 12, 2015 at 6:29 am
I'm honestly not sure how to do that with xp_cmdshell.
What are you trying to do?
June 12, 2015 at 8:23 am
SQL-DBA-01 (6/12/2015)
Thanks.Anyway you can help me getting the script in xp_cmdshell?
Were the two methods of mine that you found on a couple of the other posts too slow (and I agree they might have been)? If so, do a search for "Listing files using PowerShell". You can call PowerShell from xp_CmdShell if you want.
I do have a script that works for just one directory and wasn't built to be recursive. If that'll suit you, let me know.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2015 at 9:15 am
I'm not sure if you have explored this option or if it would be any better of what you've already got.
IF OBJECT_ID('tempdb..#Files') IS NOT NULL DROP TABLE #Files;
CREATE TABLE #Files( output nvarchar(255));
IF OBJECT_ID('tempdb..#FilesCreateDate') IS NOT NULL DROP TABLE #FilesCreateDate;
CREATE TABLE #FilesCreateDate( output nvarchar(255));
IF OBJECT_ID('tempdb..#FilesModDate') IS NOT NULL DROP TABLE #FilesModDate;
CREATE TABLE #FilesModDate( output nvarchar(255));
INSERT INTO #Files
EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /B'
INSERT INTO #FilesCreateDate
EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /TC'
INSERT INTO #FilesModDate
EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /TW'
SELECT f.output AS [filename],
REPLACE( LEFT( cd.output, 19), ' ', ' ') + '.m.' AS CreateDate,
REPLACE( LEFT( md.output, 19), ' ', ' ') + '.m.' AS ModificationDate
FROM #Files f
JOIN #FilesCreateDate cd ON f.output = SUBSTRING( cd.output, CHARINDEX(f.output, cd.output), LEN(f.output))
JOIN #FilesModDate md ON f.output = SUBSTRING( md.output, CHARINDEX(f.output, md.output), LEN(f.output));
June 12, 2015 at 9:32 am
Thanks Everyone, Jeff,Luis, & DBACold.
I will have further look per your suggestions.
Let me tell what was the requirement and till what point I did.
Guys were not intersted to use reporting services where I could easily hand some of the reports for their visibility and then they could extract the results in any format (csv/excel/pdf) they want.
Insrtead, I was asked to automate a solution to extract the results to each .txt "good" readable format, The output file should be suffixed with datetime,hh_mm_ss so that we can retain them forany no of days per rwquirement. Moreover, guys can change the script also any number of times per the rewquirement, but the job should run daily and send the output to a location from where they can copy the output for reference and check.
Now, i have done entirely using T sQL and created job which is handling all the above matter.
What I thought is to post them before I delete any files that how many .txt are available before today's date which all can be deleted based on logic. and also this rwequirement will keep changing, so I will keep adding stuf to my TSQL.
Now, I thought to change Tsql to procedure, so that it can create a compiled plan but there are restrictions creating procs, so I'm good with Tsql
Thanks.
June 12, 2015 at 9:39 am
Do the files actually have a date and time in the file name? That would make life pretty easy. If so, please post the "general" format of the file names. If not, then understood and it can be done. Does it need to be for just one directory or does it need to be recursive?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2015 at 9:54 am
Luis Cazares (6/12/2015)
I'm not sure if you have explored this option or if it would be any better of what you've already got.
IF OBJECT_ID('tempdb..#Files') IS NOT NULL DROP TABLE #Files;
CREATE TABLE #Files( output nvarchar(255));
IF OBJECT_ID('tempdb..#FilesCreateDate') IS NOT NULL DROP TABLE #FilesCreateDate;
CREATE TABLE #FilesCreateDate( output nvarchar(255));
IF OBJECT_ID('tempdb..#FilesModDate') IS NOT NULL DROP TABLE #FilesModDate;
CREATE TABLE #FilesModDate( output nvarchar(255));
INSERT INTO #Files
EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /B'
INSERT INTO #FilesCreateDate
EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /TC'
INSERT INTO #FilesModDate
EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /TW'
SELECT f.output AS [filename],
REPLACE( LEFT( cd.output, 19), ' ', ' ') + '.m.' AS CreateDate,
REPLACE( LEFT( md.output, 19), ' ', ' ') + '.m.' AS ModificationDate
FROM #Files f
JOIN #FilesCreateDate cd ON f.output = SUBSTRING( cd.output, CHARINDEX(f.output, cd.output), LEN(f.output))
JOIN #FilesModDate md ON f.output = SUBSTRING( md.output, CHARINDEX(f.output, md.output), LEN(f.output));
Luis, I had started to play with the /TC and /TW switches, but I didn't know how to get both columns listed with a single command. Then, I got called away and didn't pick it up again. I'm glad someone else thought of the same thing.
June 12, 2015 at 10:33 am
Hi Jeff,
As of now, I m keeping output to s shared location accessible by dev n testers. Req may change in future. No specification of output names, but i m giving it as scriptname_yyyy_mm_dd_hh_mm_ss.
Wrote a logic to ignore .sql before it generates output with .txt format.
Hi Luis,
Your script is v nice. Many thnx.
Missed to mention in d prior mail
Great weekend to every1. Cheers..
Thanks.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy