Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

search all folders, and copy files with a matching text string (regardless of file ext) into a target folder.

Posted on 2013-11-21
10
Medium Priority
?
384 Views
Last Modified: 2013-12-03
I'm looking for a bat file or macro to run through an existing excel or text file listing around 57000 numbers (which consist of both numbers and text). Find the corresponding files which contains an identical text string (but which may have additional characters in the string at the end not limited to the file extension). The search function should search all sub folders within an identified root folder. The identified files should then be copied (not moved) to an identified  target folder.
0
Comment
Question by:Alistair_Mair
  • 5
  • 5
10 Comments
 
LVL 37

Expert Comment

by:Kimputer
ID: 39665795
VBScript:

Those things need to be adjusted to your own:


scan_dir = "c:\windows\" (folder to be scanned)
textfile = "c:\temp\temp.txt" (here are the parts of the file names)
copy_dir = "c:\temp\" (files will be copied here)



Option explicit

Const ForReading = 1
Const ForWriting = 2

Dim scan_dir,copy_dir,folder,fileCollection,file_read,test_text,textfile,ObjFSO,folderCollection,file,subfolder

scan_dir = "c:\windows\"
textfile = "c:\temp\temp.txt"
copy_dir = "c:\temp\"


Set ObjFSO = CreateObject("Scripting.FileSystemObject")

Set file_read = ObjFSO.OpenTextFile(textfile, ForReading)
	Do Until file_read.AtEndOfStream
		test_text = file_read.Readline
		searchsub scan_dir, test_text
	Loop	
Set file_read = nothing
			
	
	
	
	
Sub searchsub(Source, text)
	Set folder = ObjFSO.GetFolder(Source)
	Set fileCollection = folder.Files
	Set folderCollection = folder.Subfolders
	
	For Each file In fileCollection
		if instr(file.name, text) then
			ObjFSO.CopyFile file, copy_dir & file.name
		end if
	Next
	
	For Each subfolder In folderCollection
		'wscript.echo subfolder & text
		searchsub subfolder, text
	Next

End sub

	

Open in new window

0
 

Author Comment

by:Alistair_Mair
ID: 39666036
Hi Kimputer,

changed the parameters as requested and Copied your script as detailed below into the macro application on excel 2010 and I get the error "compile error, invalid outside procedure"  ??

--

Option Explicit

Const ForReading = 1
Const ForWriting = 2

Dim scan_dir, copy_dir, folder, fileCollection, file_read, test_text, textfile, ObjFSO, folderCollection, file, subfolder

scan_dir = "c:\DRAGADOS\"
textfile = "c:\temp\temp.txt"
copy_dir = "c:\temp\P3FILES\"


Set ObjFSO = CreateObject("Scripting.FileSystemObject")

Set file_read = ObjFSO.OpenTextFile(textfile, ForReading)
    Do Until file_read.AtEndOfStream
        test_text = file_read.Readline
        searchsub scan_dir, test_text
    Loop
Set file_read = Nothing
           
   
   
   
   
Sub searchsub(Source, text)
    Set folder = ObjFSO.GetFolder(Source)
    Set fileCollection = folder.Files
    Set folderCollection = folder.Subfolders
   
    For Each file In fileCollection
        If InStr(file.Name, text) Then
            ObjFSO.CopyFile file, copy_dir & file.Name
        End If
    Next
   
    For Each subfolder In folderCollection
        'wscript.echo subfolder & text
        searchsub subfolder, text
    Next

End Sub
0
 
LVL 37

Expert Comment

by:Kimputer
ID: 39666098
I said it was VBscript (because it's easier that way, inside Excel it's VBA). Run it at the command prompt. Copy all the code into a text file, then give the script a name, something like "search.vbs"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Alistair_Mair
ID: 39666346
Sorry, got it and ran it. It does seem to be working but very slowly (been running for half and hour and so far copies 8 files). It's looking through 400gb of data for these files, Does this code specify to look for each file individually and cycle through the folders and then look for the next file with a corresponding file name and cycle through again or cycles through once virtually stores the results and then copies across to the target folder?
0
 
LVL 37

Expert Comment

by:Kimputer
ID: 39666396
It could be slow indeed. I was hoping your disk and cache would kick in, but seems it doesn't. I was trying to keep the code clean. Would need a lot of time to code something to load all the files on the disk into memory and do the matching there.
0
 

Author Comment

by:Alistair_Mair
ID: 39666471
OK, it's just that at this rate it will take around 50 days to cycle through and find all the files. If it is unreasonable to expect a more workable solution I will credit you with the points?
0
 
LVL 37

Expert Comment

by:Kimputer
ID: 39666488
If you could give me a day? (Could still fail though, if memory not sufficient)
0
 

Author Comment

by:Alistair_Mair
ID: 39666530
I can certainly wait a day. I will credit you with at least half the points tomorrow one way or the other. If someone finds a more workable solution in the interim then half the points go to them, does that sound fair?
0
 
LVL 37

Accepted Solution

by:
Kimputer earned 2000 total points
ID: 39667684
Same routines, but now searches from memory (will scan folder once):

Option explicit

On Error Resume Next

Const ForReading = 1
Const ForWriting = 2

Dim scan_dir,copy_dir,folder,fileCollection,file_read,test_text,textfile,ObjFSO,folderCollection,file,subfolder
Dim FilesArray

FilesArray = Split("0")

scan_dir = "c:\DRAGADOS\"
textfile = "c:\temp\temp.txt"
copy_dir = "c:\temp\P3FILES\"

Set ObjFSO = CreateObject("Scripting.FileSystemObject")

FillArray scan_dir

wscript.echo ubound(FilesArray)

Set file_read = ObjFSO.OpenTextFile(textfile, ForReading)
	Dim j
	Do Until file_read.AtEndOfStream
		test_text = file_read.Readline
		'searchsub scan_dir, test_text
		For j = 0 to Ubound(FilesArray)
			if instr(FindName(FilesArray(j),"filename"),test_text) then
				ObjFSO.CopyFile FilesArray(j), copy_dir & FindName(FilesArray(j), "filename")
			end if
		Next
		
	Loop	
Set file_read = nothing
Wscript.echo "Done"

Sub FillArray(Source)
	Set folder = ObjFSO.GetFolder(Source)
	Set fileCollection = folder.Files
	Set folderCollection = folder.Subfolders
	
	For Each file In fileCollection
		FilesArray(Ubound(FilesArray)) = file
		redim preserve FilesArray(ubound(FilesArray) + 1)
	Next
	
	For Each subfolder In folderCollection
		FillArray subfolder
	Next

End sub

Function FindName(string, mode)
Dim a,i

if not(string = "") then

	a = split(string,"\")
	if mode = "path" then
		for i = 0 to Ubound(a) - 1
			if i = 0 then
				FindName = a(i)
			else
				if i = Ubound(a) -1 then
					FindName = FindName & "\" & a(i) & "\"
				else
					FindName = FindName & "\" & a(i)
				end if
			end if
			
		Next

	end if
	if mode = "filename" then
		FindName = a(Ubound(a))
	end if

end if
End Function

Open in new window

0
 

Author Closing Comment

by:Alistair_Mair
ID: 39692126
Sorry, I got caught up in other stuff and this slipped my mind. Not tested it but appreciate the work you've put in and have assigned you the points, I apologise for the delay in getting back to you.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A big percent of today’s marketing activity is performed through the online environment. The marketing strategies that have existed a decade ago no longer relate to what’s happening today. We’re currently facing a revolutionary era, called the digit…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question