SQLTeam.com | Weblogs | Forums

Looping through folders


I have to go through about a hundred (or more) folders.
In each folder can be any number of files.
I need to loop through the folders and compress and move all the files to another folder until I reach 250, at that point go on to to the next 250 and compress and move all the files to a different folder; and so on.

The problem is that when I get to the 250 mark in a folder I do not know how to cut it off an start again. Let's say that I hit 250 on file number 5 of 23. How do I tell it to stop there that it already did 1-5 and ow should pick up again on number 6 and go on from there?

Thank you

I would be tempted to use powershell.
Here's a script to zip files one by one

You can put a wrapper round that which loops through the folders and include a counter to change destination when it gets to 250.
There's a more elaborate version also using command line here

Another option would be to import the list of files into a table in a database then mark them with the destination folder and use that to control the zipping.

Thanks for the reply.
I never used Powershell before so I am not sure if I should start from scrach here.
I do Like the last on you have here, but how would get the lsit of files in a databse to mark them?

Thank you

You can use something like this

Run it for each of the folders and
	create table #Dir (s varchar(8000))
	select	@cmd = 'dir /B ' + @FilePath + @FileMask
	insert #Dir exec master..xp_cmdshell @cmd
	delete #Dir where s is null or s like '%not found%'
insert Mytbl select @FilePath + '\' + s

Oh Okay
Thank you

Until you reach 250... 250 what? You give the example of "I hit 250 on file number 5 of 23". What is the 250 based on?

the number of files. So go into folder #1 (maybe it has 5 files) I zip them and move them to Folder A.

I go to the next folder (# 2 which has 10 files) I do the same thing as in folder #1. and I go on until I get to 250.

Again the problem is that I am most likely going to be in the last folder in the middle of the number of files in that folder
and will need a way to cut off and start again for the next 250; which will then go into folder B for the next 250.

Thank you

Virus-free. www.avg.com

Ah... got it. Your previous example with the 5 of 23 thing threw me off.

NigelRevett's response is on the right track. The /B switch he uses on the DIR command returns the full path of each file. If you also use the /S switch, it will do the DIR command recursively (and very quickly) for all of the sub-directories below the starting directory. You can capture the output of xp_CmdShell (the thing that issues the DIR command from SQL Server) into a table with a self-incrementing IDENTITY column (start it at "0"). The table should also have a couple of other columns where you use integer division to identify which group of 250 you're working on and, if you need it, a modulo column to tell you what the file number is for each group.

Once you have that data, then just run each file name through a loop with your other OS level commands (again using xp_CmdShell) in order by the file group and file number columns you created.

Unfortuantle when I tryed running the code from the page he recomended, it did nto work.
It comes back that it can not find the stord procedure (and it gives the file path) for the procedure name. Really strange.

Actually I found what I was doing wrong and now it says that it cannot find the
'The system cannot find the file specified.'

You need to post the code you're using or we'd just be guessing here.