Create folder from partial filename and move files into that folder

[devo]

Hi,

I have thousands of word and excel files that I want to group into folders.  The file names have the following structure:


e.g.

Chicago A01Z01 Green Car Dog Train.xls
Chicago A01Z01 Green Car Dog Train.doc
Chicago A01Z02 Green Car Dog Train.xls
Chicago A01Z02 Green Car Dog Train.doc
Chicago A01Z03 Green Car Dog Train.xls
Chicago A01Z03 Green Car Dog Train.doc....... etc

New York A08Z06 Blue Horse Jeep Boat.xls
New York A08Z06 Blue Horse Jeep Boat.doc
New York A08Z07 Blue Horse Jeep Boat.xls
New York A08Z08 Blue Horse Jeep Boat.doc
New York A08Z09 Blue Horse Jeep Boat.xls
New York A08Z09 Blue Horse Jeep Boat.doc....... etc

I want to create a folder called Chicago A01 Train and a folder called New York A08 Blue and move the corresponding files into their folders.  The numbers go from A00Z00 to A99Z99.  Some files would have longer or shorter file names.  If the folder is already created, only the files would move.  I've been playing around with this but I'm stuck, I can only get the files to move into their own folder

Code: [Select]

@echo off
:prep
:: removes old files first if pressent
del /q "List.txt"
del /q "DirList.txt"

:start
:: will add files that only exist to the list.txt
if exist "*.xls" dir /b "*.xls">>list.txt
if exist "*.doc" dir /b "*.doc">>list.txt

:: removes the extensions of the files so that when creating folders it works
SetLocal EnableDelayedExpansion
For /F "tokens=* delims=" %%A in (list.txt) Do (
Set TxtLine=%%A
Set TxtLine=!TxtLine:.xls=!
Set TxtLine=!TxtLine:.doc=!

echo !TxtLine!>>tmp.txt)
ren "tmp.txt" "DirList.txt"

:: does the hard work of creating and naming the files to match the folder
for /F "tokens=* delims=" %%a in (DirList.txt) do md "%%a"
for /f "tokens=* delims=" %%b in (DirList.txt) do set Folders=%%b
for /f "tokens=* delims=" %%c in (List.txt) do set Files=%%c

:: moves the files into there folders
move "%Files%" "%Folders%"

:: removes the files to start again
del /q "List.txt"
del /q "DirList.txt"

:: if a file of this type still exist it will try again
if exist "*.xls" goto start
if exist "*.doc" goto start

:: cleans up after its done
del /q "List.txt"

I need the folder to be created based on what's before A00, A00 and a keyword after A00Z00 ( in this example the keywords were train and blue) and ignore everything else.

Can anybody hep with this?

Thanks

[Salmon Trout]

For /F "tokens=* delims=" %%A in (list.txt) Do (

Where's the closing parenthesis of this loop?

[devo]

Where's the closing parenthesis of this loop?

echo !TxtLine!>>tmp.txt)

[Squashman]

Yeah but your keywords Train and Blue are both in different spots of the file names.  How are we suppose to know what word to use after A00Z00?

[devo]

The keywords are in different places in every file name.  After A00Z00 there could be 2 words or 20 words and the keyword could be anywhere, it depends on the individual file.  If I add the following lines to the original batch file

Code: [Select]

Set TxtLine=!TxtLine:Z01 =!
Set TxtLine=!TxtLine:Z02 =!
Set TxtLine=!TxtLine:Z03 =!
Set TxtLine=!TxtLine:Green =!
Set TxtLine=!TxtLine:Car =!
Set TxtLine=!TxtLine:Dog=!


 

Code: [Select]

@echo off
:prep
:: removes old files first if pressent
del /q "List.txt"
del /q "DirList.txt"

:start
:: will add files that only exist to the list.txt
if exist "*.xls" dir /b "*.xls">>list.txt
if exist "*.doc" dir /b "*.doc">>list.txt

:: removes the extensions of the files so that when creating folders it works
SetLocal EnableDelayedExpansion
For /F "tokens=* delims=" %%A in (list.txt) Do (
Set TxtLine=%%A
Set TxtLine=!TxtLine:.xls=!
Set TxtLine=!TxtLine:.doc=!
****************************
Set TxtLine=!TxtLine:Z01 =!
Set TxtLine=!TxtLine:Z02 =!
Set TxtLine=!TxtLine:Z03 =!
Set TxtLine=!TxtLine:Green =!
Set TxtLine=!TxtLine:Car =!
Set TxtLine=!TxtLine:Dog=!
****************************

echo !TxtLine!>>tmp.txt)
ren "tmp.txt" "DirList.txt"

:: does the hard work of creating and naming the files to match the folder
for /F "tokens=* delims=" %%a in (DirList.txt) do md "%%a"
for /f "tokens=* delims=" %%b in (DirList.txt) do set Folders=%%b
for /f "tokens=* delims=" %%c in (List.txt) do set Files=%%c

:: moves the files into there folders
move "%Files%" "%Folders%"

:: removes the files to start again
del /q "List.txt"
del /q "DirList.txt"

:: if a file of this type still exist it will try again
if exist "*.xls" goto start
if exist "*.doc" goto start

:: cleans up after its done
del /q "List.txt"
and run it.  A folder called Chicago A01 Train is created and all the corresponding files moved into that folder.  I excluded Z01, Z02, Z03, Green, Car & Dog from the folder name.  Would it be possible to do the opposite and only include the keywords (Train:Blue) and ignore everything else?

[Raven19528]

So a lot of this is testing for conditions it seems. So here's how you test for the before A00 of Chicago and New York:

Code: [Select]

for /f "tokens=*" %%A in ('dir /b *.xls') do (
  set Txtline=%%A
  if /i "!Txtline:~0,7!"=="Chicago" (
    more)
  if /i "!Txtline:~0,8!"=="New York" (
    more)

Then, if you are looking to find only certain keywords, try this:

Code: [Select]

echo !Txtline! | find "Train" >nul
if errorlevel 0 (echo Train was found)

So you can set up your commands in place of the echo. From there, you could set it so that the output name only has Train in it by either defining the output explicitly or using a set name=!Txtline:~0,13! Train or something similar to capture the first part of the name as well.

You seem like you know a little about what you are doing, which is why I'm choosing to provide more of some helpful tricks for you to use rather than a built script.

[devo]

Thank you for your help.

I didn't write this batch.  I modified it to suit my needs as best I could.  The changes I made were based on logic rather than any knowledge of DOS.  I really have no idea what I'm doing.

Every file name has a variation of A00Z00 up to A99Z99.  e.g.  A14Z52, A27Z16.  The original batch seems to handle what's before A00Z00-A99Z99 OK.  Whether there's 1 word or 10 words, they get added to the folder name.  Which is what I want.  I could even add the following 100 lines, which doesn't look pretty

Code: [Select]

Set TxtLine=!TxtLine:Z00 =!
Set TxtLine=!TxtLine:Z01 =!
Set TxtLine=!TxtLine:Z02 =!
.
.
.
Set TxtLine=!TxtLine:Z98 =!
Set TxtLine=!TxtLine:Z99 =!

to get rid of the Z00-Z99 from the folder name.


The real issue is to identify the keyword after the 6 letter/number combination and then add that keyword to the folder name.  This part of your post seems to be what I'm looking for but I have no idea where to put it or what to do with it

Then, if you are looking to find only certain keywords, try this:

Code: [Select]

echo !Txtline! | find "Train" >nul
if errorlevel 0 (echo Train was found)

So you can set up your commands in place of the echo. From there, you could set it so that the output name only has Train in it by either defining the output explicitly or using a set name=!Txtline:~0,13! Train or something similar to capture the first part of the name as well.

I would really appreciate it if you could help me come up with something that would add whatever the keyword is (Train, Blue) to the folder name.

Thanks

[Salmon Trout]

Is the six-character block A00Z00-A99Z99 the only part of the filename that contains numbers? And the part before it could be any number of words - one ("Chicago") or two ("New York") or more?

[devo]

Is the six-character block A00Z00-A99Z99 the only part of the filename that contains numbers?

No, there are numbers elsewhere.

And the part before it could be any number of words - one ("Chicago") or two ("New York") or more?

Yes, there could be any number of words.  Mostly it's one, two or three words but there could be anything up to ten words.

Thanks for replying

[Salmon Trout]

In each filename, is there only one space-delimited token that has six characters A99Z99 ... in that format? AmnZmn where A,Z are letters and m,n are numerical digits 0-9? And the letters are always A in the first place and Z in the fourth place, and the following part of the filename will only contain one of the possible keywords?

[devo]

In each filename, is there only one space-delimited token that has six characters A99Z99 ... in that format? AmnZmn where A,Z are letters and m,n are numerical digits 0-9?

Yes, there is only one per file

And the letters are always A in the first place and Z in the fourth place

Mostly, there are some where S is in the first place and E in the fourth place

and the following part of the filename will only contain one of the possible keywords?

Yes

[Salmon Trout]

I see a ray of hope... are you able to run Visual Basic Scripts using the Windows Scripting Host?

For each name root, is there always an .xls and a .doc file?

[devo]

I see a ray of hope... are you able to run Visual Basic Scripts using the Windows Scripting Host?

I don't know what that is.  I'm running Windows 7 Home Premium 64-bit, if that helps.  I would usually save as .bat and double click to run.

For each name root, is there always an .xls and a .doc file?

No, there may be one or the other or both.

[Salmon Trout]

I don't know what that is.

Copy this into Notepad and save it with any name and a .vbs extension and then double click it & tell me what happens

Code: [Select]

wscript.echo "Today is " & Date & VBcrlf & VBcrlf &  "This is a Visual Basic Script" & VBcrlf & VBcrlf & "Click OK to quit"


[devo]

A box popped up with the following:

"Windows Script Host access is disabled on this machine.  Contact your administrator for details."