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

Question 1

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

1

2

3

4

5

6

7

8

9

10

11

12

13

-- 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"

1

2

3

4

5

6

7

8

9

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'

1 2	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
...      | ...

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

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 .

7 | 07/28/2015 07:06 PM ..

8 | 03/05/2015 11:42 AM LDB1 App Export

9 | 03/05/2015 05:31 PM 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 .

15 | 07/28/2015 06:41 PM ..

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"

1

2

3

4

5

6

7

8

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 🙂

Question 2

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

--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

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

Question:

Answer:

Source:

How to load files according to Created Date in Windows command shell via SQL Server’s xp_cmdshell by stackoverflow.com licensed under CC BY-SA | With most appropriate answer!

Leave a Reply Cancel reply