June 18, 2007 at 3:42 am
Hi All,
I'm not sure if this is possible. But I have a directory that contains 100's of files, but i need to copy 1 of these files to another directory every day. There is nothing in the filename that i can use, but withing the file there are strings that can identify it. so basically i need to scan the files in this directory and if i can identify them (from the string) shift them out to a new directory. Is this possible.
June 18, 2007 at 4:33 am
As simple as it is. Create a windows batch file to do the same.
1. Use FIND command to find the file that contains your input string and write to a txt file.
2. next step read the file that contains the file list from the previous step and use the COPY command to move the file to the destination.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 19, 2007 at 11:38 am
Assuming you want to accomplish the task from T-SQL, you could use Sugesh's idea of using FIND, but without using a batch file. You may have to adjust the code if your output format differs from mine. This version handles the case when there is more than one filename returned.
SET NOCOUNT ON
DECLARE @searchString varchar(50)
, @filespec varchar(80)
, @destination varchar(80)
SET @searchString = 'your search string'
SET @filespec = 'your fullpath spec'
SET @destination = 'your destination path' -- Ex. e:\newlocation\*.*
DECLARE @cmd varchar(255)
CREATE TABLE #output (output varchar(256))
SET @cmd = 'find /I /C "' + @searchString + '" ' + @filespec
INSERT #output EXEC master..xp_cmdshell @cmd
DELETE #output WHERE output IS NULL OR CharIndex(':', output, 3) = 0
UPDATE #output SET output = Substring(output, 12, 256)
DELETE #output WHERE CONVERT(int, Substring(output, CharIndex(':', output, 3) + 1, 256)) = 0
/*
SELECT Left(output, CharIndex(':', output, 3) - 1) AS [FileName]
, CONVERT(int, Substring(output, CharIndex(':', output, 3) + 1, 256)) AS NumOccurs
FROM #output
WHERE CONVERT(int, Substring(output, CharIndex(':', output, 3) + 1, 256)) > 0
*/
WHILE (SELECT Count(*) FROM #output) > 0
BEGIN
SET ROWCOUNT 1
SELECT @cmd = 'copy ' + RTrim(Left(output, CharIndex(':', output, 3) - 1)) + ' ' + @destination
FROM #output
EXEC master..xp_cmdshell @cmd -- , NO_OUTPUT
DELETE #output
SET ROWCOUNT 0
END --WHILE
DROP TABLE #output
June 20, 2007 at 2:19 am
thanks Sugesh
June 20, 2007 at 2:20 am
yes i did want to keep it all in sql, thats brill. thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply