How to load files according to Created Date in Windows command shell via SQL Server's xp_cmdshell

Question

In SQL Server, I am using a query below to load all ".jpg" file names from a specific directory (e.g. z:) into a table.

I want to know if there's a way to load files according to Created Date instead of Modified Date in Windows command prompt. The query below only works with Modified Date when executing xp_cmdshell.

-- Create the table to store file list
CREATE TABLE myFilesTable (myFileID INT IDENTITY, myFileName NVARCHAR(256))

-- Insert file list from directory to SQL Server
DECLARE @Command varchar(1024) = 'z: & forfiles /m *.jpg /s /d 07/16/2015 /c "cmd /c echo @fdate @ftime @path"'

INSERT INTO myFilesTable
   EXEC MASTER.dbo.xp_cmdshell @Command

-- Check the list
SELECT * FROM myFilesTable
GO

07/16/2015 in the variable @Command is the Modified Date. Obviously the command forfiles doesn't have a clue to filter files by Created Date.

Below is a few results from the query given above in which FileNames are prefixed by Modified Date.

myFileID | myFileName
----------------------
1        | NULL
2        | 8/18/2015 11:13:08 AM "Z:\LDB1 App Export\Top Star_Aluminium Frames & Furniture (B)-31267.jpg"
3        | 8/19/2015 5:44:41 PM "Z:\LDB2 App Export\Soe Tint_Hardware Merchants & Ironmongers-31435.jpg"
4        | 8/19/2015 10:37:13 AM "Z:\Cover App Export\Taw Win Tun_Electrical Goods Sales & Repairing (A) -31382.jpg"
5        | 8/24/2015 10:34:33 AM "Z:\CP1 App Export\Thiri May_Fabric Shop (B)-30646.jpg"
6        | 8/17/2015 10:08:39 AM "Z:\CP2 App Export\Ko Tin Aung_Building Materials (B)-31300.jpg"

I have also tried using dir command with timefield /t:c (the creation time) something like

EXEC MASTER.dbo.xp_cmdshell 'dir z: *.jpg /t:c /s'

It gives me the Created Date but it shows me the following result which is not as expected. I want the file names with full path/directory names as shown in the previous result.

myFileID | myFileName
----------------------
1        |  Volume in drive Z is Publication
2        |  Volume Serial Number is 3EF0-5CE4
3        | NULL
4        |  Directory of Z:\
5        | NULL
6        | 07/28/2015  06:41 PM    <DIR>          .
7        | 07/28/2015  07:06 PM    <DIR>          ..
8        | 03/05/2015  11:42 AM    <DIR>          LDB1 App Export
9        | 03/05/2015  05:31 PM    <DIR>          LDB2 App Export
10       |         0 File(s)              0 bytes
11       | NULL
12       |  Directory of Z:\LDB1 App Export
13       | NULL
14       | 03/05/2015  11:42 AM    <DIR>          .
15       | 07/28/2015  06:41 PM    <DIR>          ..
16       | 07/28/2015  06:49 PM         2,981,526 Kyaw Phay_Dental Equipment (A)-30998.jpg
17       | 08/31/2015  03:10 PM         3,126,629 Venus_Fashion Shops-31438.jpg
18       | 07/28/2015  06:49 PM         3,544,247 Marvellous_Tourism Services-30986.jpg
...      | ...

The expected result should be something like below,

myFileID | CreatedDate           | myFileName
----------------------------------------------
1        | 8/10/2015 11:24:16 AM | "Z:\LDB1 App Export\Top Star_Aluminium Frames & Furniture (B)-31267.jpg"
2        | 8/10/2015 11:24:27 AM | "Z:\LDB2 App Export\Soe Tint_Hardware Merchants & Ironmongers-31435.jpg"
3        | 8/12/2015 10:05:22 AM | "Z:\Cover App Export\Taw Win Tun_Electrical Goods Sales & Repairing (A) -31382.jpg"
4        | 8/12/2015 10:05:22 AM | "Z:\CP1 App Export\Thiri May_Fabric Shop (B)-30646.jpg"
5        | 8/12/2015 10:05:22 AM | "Z:\CP2 App Export\Ko Tin Aung_Building Materials (B)-31300.jpg"

Any help would be very appreciated :)

Answer 1

Here is one way you can parse the output of the DIR command:

--Create the table to store file list
CREATE TABLE myFilesTable (myFileID INT IDENTITY, myFileCreateDate datetime, myFileName NVARCHAR(256))

--Create temporary table to store output of DIR command
CREATE TABLE #DirectoryOutput (LineID INT IDENTITY, LineData NVARCHAR(256))

--Insert file list from directory to SQL Server
DECLARE @Command varchar(1024) = 'dir z: *.jpg /t:c /s'

INSERT INTO #DirectoryOutput
   EXEC MASTER.dbo.xp_cmdshell @Command

--Check the list
insert into myFilesTable
select 
    convert(Datetime,(left(LineData, 20))) CreateDate,
    FilePath2.FilePath + '\' + right(LineData,len(LineData)-39) Filename
from #DirectoryOutput
cross apply
    (
    select Max(LineID) LineID
    from #DirectoryOutput FilePaths
    where LEFT(LineData,14)=' Directory of '
        and FilePaths.LineID < #DirectoryOutput.LineID
    ) FilePath1
join
    (
    select LineID, RIGHT(LineData, LEN(LineData)-14) FilePath
    from #DirectoryOutput FilePaths
    where LEFT(LineData,14)=' Directory of '
    ) FilePath2
on FilePath1.LineID = FilePath2.LineID
where ISDATE(left(LineData, 20))=1
order by 1

select * from myFilesTable

GO

Answer 2

I slightly changed your table to include a separate column for the creation date:

CREATE TABLE myFilesTable ( myFileID           int IDENTITY
                          , myFileName         nvarchar(256)
                          , myFileCreationDate datetime
                          )

You can use following PowerShell script to get the directory information and write it to the SQL table:

Import-Module "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS" -DisableNameChecking
$files = Get-ChildItem "z:\" -Filter "*.jpg" -recurse | Where-Object { $_.CreationTime -ge "07/16/2015" } | Select-Object FullName, CreationTime
foreach ($file in $files) 
{
    $creationTime = $file.CreationTime -f "dd-MM-yyyy hh:mm:ss"
    $file.FullName = $file.FullName -replace "'", "''"
    Invoke-Sqlcmd -ServerInstance "YourInstance" `
              -Database "YourDatabase" `
              -Query ("INSERT INTO {0} (myFileName, myFileCreationDate) VALUES ('{1}', '{2}')" `
                            -f "myFilesTable", $file.FullName, $creationTime)
}

Replace the YourInstance value with your instance name and the YourDatabase value with the name of your database.

I strongly advise against using xp_cmdshell as it open a windows command shell with the same security context as the SQL server service account. This is a security risk and it is best is to disable the xp_cmdshell command.

You can best execute the PowerShell command on the SQL Server. Prerequisite is that the SQLPS module is available (for the Invoke-SqlCmd commandlet). This prerequisite is met when you have installed SQL Server.

EDIT:

So if you really want to do it via xp_cmdshell, you can save the PowerShell script on your SQL Server and execute it the following way (I modified the path to the SQLPS file in the PowerShell script, assuming you have SQL Server 2012):

EXEC xp_cmdshell 'powershell.exe -file "C:\FileList.ps1" -ExecutionPolicy Unrestricted'

Where C:\FileList.ps1 is your saved PowerShell script.