Author |
Topic |
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-10-13 : 11:10:47
|
Im running this statement.CREATE TABLE #fileList(line varchar(2000))INSERT INTO #fileList EXEC xp_cmdshell 'dir C:\Company /B'SELECT * FROM #fileList WHERE line IS NOT NULL ORDER BY 1DROP TABLE #fileListWhat i would like to get is the date modified Thanks |
|
Kristen
Test
22859 Posts |
Posted - 2007-10-13 : 11:16:35
|
Use a SUBSTRING on the [line] column, and then convert that the DATETIME datatype. Position within the line will vary depending on version of windows, and the locale will influence the presentation of the date.Kristen |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-10-13 : 11:42:53
|
Not sure i understand how to do what your suggesting - can you advise thanks |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-10-13 : 11:44:39
|
When i run the command i do not get created date coming out just the filenamecompany1.txt and no dates for commandssay i have c:\company company1.txt 10/1/2007 company2.txt 11/1/2007When i run command i get company1 company2Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-13 : 13:36:09
|
That's the "/B" on the end of your DIR command, that means to return it "bare" - i.e. just filenames.dir C:\Companywill show you dates.Kristen |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-13 : 21:56:06
|
How can you get modified date if only queried file name? |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-10-14 : 07:46:06
|
Removed the B Now i can do the substring on it....For example im just querying C drive on one system here.Just did dir c:\*.txtGet the following: 0 Dir(s) 2,787,573,760 bytes free 1 File(s) 63 bytes Directory of C: Volume in drive C has no label. Volume Serial Number is 7C93-8DAC03/16/2007 09:48 AM 63 sysinfo.txtIs there a way just to get say03/16/2007 09:48 AM 63 sysinfo.txtWithout all the volume information.I tried to research the commands xp_cmdshell what the parameters were did not find much out there on net.Thanks |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-10-14 : 08:59:57
|
Got stuck on one part of coding.Files on C are 01 te.txt xp.txt 02 te.txt xa.txt --This gets me 01, 02CREATE TABLE COMPANY(Company nvarchar(20))INSERT INTO COMPANY EXEC xp_cmdshell 'dir x:\*.* /B'CREATE TABLE COMPANY_FILES(Company nvarchar(20), FILENAME nvarchar(20))BEGIN DECLARE @Company nvarchar(20) DECLARE @CMD nvarchar(1000) DECLARE ReadDirectory CURSOR FOR SELECT Company FROM COMPANY OPEN ReadDirectory FETCH NEXT FROM ReadDirectory INTO @Company WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO COMPANY_FILES(COMPANY, FILENAME) VALUES (@COMPANY, @FILENAME) set @cmd = 'EXEC xp_cmdshell ' + 'dir xc:\' + @COMPANY + ' /B' EXEC @cmd----Got stuck on trying to do the dynamic bit s i can put in 01 te.txt and 01 xp.txt print @FileName FETCH NEXT FROM ReadDirectory INTO @Company END CLOSE ReadDirectory DEALLOCATE ReadDirectoryEND |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-10-14 : 09:56:51
|
Almost there...DROP TABLE COMPANYDROP TABLE COMPANY_FILESCREATE TABLE COMPANY(Company nvarchar(20))INSERT INTO COMPANY EXEC xp_cmdshell 'dir x:\*.* /B'CREATE TABLE COMPANY_FILES(Company nvarchar(20), FILENAME nvarchar(20))BEGIN DECLARE @Company nvarchar(20) DECLARE @CMD nvarchar(1000) DECLARE @FILENAME nvarchar(1000) DECLARE ReadDirectory CURSOR FOR SELECT Company FROM COMPANY OPEN ReadDirectory FETCH NEXT FROM ReadDirectory INTO @Company WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @DIRECTORY NVARCHAR(100) SET @DIRECTORY = 'x:\' INSERT INTO COMPANY_FILES(COMPANY, FILENAME) VALUES (@COMPANY, @cmd) set @cmd = 'DIR ' + @DIRECTORY + @COMPANY + ' /B' EXEC XP_CMDSHELL @cmd When i do the print for @CMD it has all the files i want i just cannot get this to write into the COMPANY_FILES table as 01 te.txt etc FETCH NEXT FROM ReadDirectory INTO @Company END CLOSE ReadDirectory DEALLOCATE ReadDirectoryENDseLECT * FROM COMPANY_FILES |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-14 : 10:56:10
|
"Is there a way just to get say...Without all the volume information."We DELETE them using a WHERE [Line] LIKE '%this%' OR [Line] LIKE '%that%' ...Kristen |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-14 : 10:57:34
|
Actually a WHERE NOT LIKE "space space space ... [0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9] space ... space [^ ] ..."would do - so you only let through the lines that have date-like valuesKristen |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-10-14 : 19:23:40
|
Thanks for replying do you know whyINSERT INTO COMPANY_FILES(COMPANY, FILENAME)VALUES (@COMPANY, @cmd)set @cmd = 'DIR ' + @DIRECTORY + @COMPANY + ' /B'EXEC XP_CMDSHELL @cmd When i do above part it would not write to the table if i do print it has everything correct just not updating table...the Company is coming out just not the txt names. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-14 : 19:27:34
|
You need to use something more like the syntax you had at the outset, i.e.:INSERT INTO #fileList EXEC xp_cmdshell 'dir C:\Company' Kristen |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-10-17 : 14:05:31
|
I think im on my way....Thanks for your help. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 14:32:03
|
OK, I'll keep my fingers crossed! |
|
|
edpreston76
Starting Member
1 Post |
Posted - 2013-10-09 : 07:17:00
|
Hi,I am also trying to get the date modified of a external file into a temp file and have got the following so far using your answers....any help would be appreciated. the delete line is where I am really struggling as I only want the date modified - doesnt even have to have the time..CREATE TABLE #filedate(line varchar(2000))INSERT INTO #filedate EXEC xp_cmdshell 'dir \\FILELOCATION\FILENAME.dat'DELETE FROM #filedate where line not like "[0-9][0-9/[09][0-9]/[0-9][0-9][0-9][0-9]"SELECT * FROM #filedate WHERE line IS NOT NULL ORDER BY 1--DROP TABLE #filedate |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-09 : 07:57:31
|
please dont hijack old threads. Post the question as a new thread and chances are that you'll get solution much faster.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|