在sql Server中,我使用下面的查询将特定目录(例如z :)中的所有“.jpg”文件名加载到表中.
我想知道是否有办法在Windows命令提示符下根据创建日期而不是修改日期加载文件.以下查询仅在执行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
2015年7月16日变量@Command是修改日期.显然,命令forfiles没有通过Created Date过滤文件的线索.
以下是上面给出的查询的一些结果,其中FileNames以修改日期作为前缀.
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"
我也尝试过使用dir命令和timefield / t：c(创建时间)之类的东西
EXEC MASTER.dbo.xp_cmdshell 'dir z: *.jpg /t:c /s'
它给了我创建日期,但它向我显示了以下不符合预期的结果.我希望文件名具有完整路径/目录名称,如上一个结果所示.
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 ... | ...
预期结果应如下所示,
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"
任何帮助将非常感激:)
解决方法
以下是解析DIR命令输出的一种方法：
--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
