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."

[Salmon Trout]

Oh, well, if it's an employer's machine and the IT people have disabled scripting, that kills my idea. Sorry. (Home Premium? At work?)

[devo]

It's my machine.  I've enabled Windows Script Host now.
A square box popped up:

Today is 10/02/2012

This is a Visual Basic Script

Click OK to quit. 

There's a close button and an OK button.

[Salmon Trout]

OK we're good to go... it's evening here and I am going to watch a film... and then bed... expect a script in around 12 hours...

A question: in the 6 character block, are the letters always upper case?

[devo]

A question: in the 6 character block, are the letters always upper case?

No, sometimes upper sometimes lower. 

Thanks

[patio]

What Film ? ?

[Salmon Trout]

What Film ? ?

Actually, a TV series episode: Series 1 Episode 3 of the Danish "Borgen"... (Danish TV series are a big hit here right now)

http://en.wikipedia.org/wiki/Borgen_%28Danish_TV_series%29

... and then after that, "101" - a documentary film about the 1989 Depeche Mode concert in Pasadena

http://www.imdb.com/title/tt0094590/

You know, I had forgotten how good they were! But the hairstyles (of everybody) were so funny!

[Salmon Trout]

Batch file that calls vbs file

Code: [Select]

@echo off

REM Create Test files
echo hello > "New York A08Z06 Blue Horse Jeep Boat.doc"
echo hello > "New York A08Z06 Blue Horse Jeep Boat.xls"
echo hello > "New York A08Z07 Blue Horse Jeep Boat.doc"
echo hello > "New York A08Z08 Blue Horse Jeep Boat.xls"
echo hello > "New York A08Z09 Blue Horse Jeep Boat.doc"
echo hello > "New York A08Z09 Blue Horse Jeep Boat.xls"
echo hello > "Chicago A01Z01 Green Car Dog Train.doc"                                                       
echo hello > "Chicago A01Z01 Green Car Dog Train.xls"
echo hello > "Chicago A01Z02 Green Car Dog Train.xls"
echo hello > "Chicago A01Z02 Green Car Dog Train.doc"
echo hello > "Chicago A01Z03 Green Car Dog Train.xls"
echo hello > "Chicago A01Z03 Green Car Dog Train.doc"

REM These are bad file names
echo hello > "FailAlphaNumeric AB1Z03 Green Car Dog Train.xls"
echo hello > "FailAlphaNumeric AB1Z03 Green Car Dog Train.doc"
echo hello > "FailKeyWord A01Z03 Green Car Dog Bus.xls"
echo hello > "FailKeyWord A01Z03 Green Car Dog Bus.doc"
echo hello > "FailBoth AB1Z03 Green Car Dog Bus.xls"
echo hello > "FailBoth AB1Z03 Green Car Dog Bus.doc"


setlocal enabledelayedexpansion
for /f "delims=" %%A in ( 'dir /b *.doc; *.xls' ) do (
for /f "delims=" %%B in ('cscript //nologo ParseName.vbs "%%~nA"') do Set Result=%%B
if "!Result!"=="Bad Filename" (
echo File %%A Error:  !Result!
) else (
If not exist "!Result!" md "!Result!"
Move "%%A" "!Result!"
)
)


ParseName.vbs

Code: [Select]

InputString = wscript.arguments(0)
TokensArray = split (InputString , " ")
KeyWordList = Array ("Train","Blue","Sky","Cheese","Ratio","Sanitary","Rosebush")
AlphaBlockFound = False
KeyWordFound = False
OutPutString="Bad Filename"
For j = lbound(TokensArray) to Ubound(TokensArray)
MyString=TokensArray(j)
If Len(MyString) = 6 Then
FlagString=""
For k = 1 To 6
MyChar = Mid(MyString,k,1)
If IsNumeric(MyChar) = False Then
If InStr ("ABCDEFGHIJKLMNOPQRSTUVWXYZ", MyChar) > 0 Then
FlagString = FlagString & "A"
End If
Else
FlagString = FlagString & "N"
End If
Next
If FlagString = "ANNANN" Then
'wscript.echo "Token (" & j & ") = " & TokensArray(j) & " " & Len(TokensArray(j)) & " " & FlagString
BlockNum=j
PreambleString = ""
For L = 0 To (BLocknum-1)
PreambleString = PreambleString & " " & TokensArray (L)
Next
For l = (BlockNum+1) To UBound(TokensArray)
'wscript.echo TokensArray(l)
For q = LBound(KeyWordList) To UBound(KeyWordList)
If Trim(TokensArray(l)) = Trim(KeyWordList(q)) Then
OutPutString = PreambleString & " " & Mid(TokensArray(j), 1,3) & " " & KeyWordList(q)
'wscript.echo Trim(OutPutString)
End If
Next
Next
End If
End If
Next
wscript.echo Trim(OutPutString)


[devo]

Thank you for this.  I saved the first piece of code as Batch.vbs and the second as ParseName.vbs.  When I double click on Batch.vbs I get the following error:

Line: 1
Char: 1
Error: Invalid character
Code: 800A0408
Source: Microsoft VBScript compilation error


Any ideas what I did wrong?

[Salmon Trout]

The first file is a batch, not a vbs, and therefore should be saved with the extension .bat

Since you are going to run the batch by double clicking, a good idea is to prevent the comand window from closing as soon as the batch is finished. Add these 2 final lines to the batch, as follows

Code: [Select]

Echo Processing completed
Pause


[devo]

Thank you so much.  It works perfectly.

[Salmon Trout]

You are very welcome. Thanks for the response.

[devo]

Hi,

Just an update to this.  Is it possible to have multiple keywords/phrase between the "" in the keywordlist?

e.g. in the ParseName.vbs the keyword list is currently

Code: [Select]

KeyWordList = Array ("Train","Blue","Sky","Cheese","Ratio","Sanitary","Rosebush")
Is it possible to change it to the following

Code: [Select]

KeyWordList = Array ("Train","Blue","Sky","Cheese","Ratio","Sanitary","Rosebush",      "Train Blue","Train Blue Sky","Cheese Green Ten Hotel")

If a file contained "Train" it would move to the Train folder, as it currently does
If a file contained "Train Blue" it would move to the Train Blue folder (not the Train or the Blue folder)
If a file contained "Train Blue Sky" it would move to the Train Blue Sky folder (not the Train/Blue/Train Blue folder)

I can get it working if I put a - between the keywords e.g. Train-Blue.  But I have to rename the file to include the hyphen and rename it afterwards.  The folder name also has the hyphen which I don't want, I'd like a space between each word.

Salmon Trout, is this possible.


Thanks

[Salmon Trout]

Well, my first thought, on looking at my script from February 2012 is "Did I write that?" My second thought is that I am flying to Spain tomorrow morning, so although I will give it some thought, and I am sure that what you want is possible, don't expect anything for about 1 week.

[devo]

That's great.  Whenever you can, there's no hurry.

[Salmon Trout]

.

[Salmon Trout]

Try this batch...

Code: [Select]

@echo off

REM Optional section start -------------------------------
REM Create Test files

echo hello > "New York A08Z06 Blue Train Horse Jeep Boat.doc"
echo hello > "New York A08Z06 Blue Train Horse Jeep Boat.xls"
echo hello > "New York A08Z07 Blue Train Horse Jeep Boat.doc"
echo hello > "New York A08Z07 Blue Train Horse Jeep Boat.xls"
echo hello > "New York A08Z08 Blue Train Horse Jeep Boat.doc"
echo hello > "New York A08Z08 Blue Train Horse Jeep Boat.xls"
echo hello > "New York A08Z09 Blue Train Horse Jeep Boat.doc"
echo hello > "New York A08Z09 Blue Train Horse Jeep Boat.xls"
echo hello > "New York A08Z06 Blue Horse Jeep Boat.doc"
echo hello > "New York A08Z06 Blue Horse Jeep Boat.xls"
echo hello > "New York A08Z07 Blue Horse Jeep Boat.doc"
echo hello > "New York A08Z08 Blue Horse Jeep Boat.xls"
echo hello > "New York A08Z09 Blue Horse Jeep Boat.doc"
echo hello > "New York A08Z09 Blue Horse Jeep Boat.xls"
echo hello > "Chicago A01Z01 Green Car Dog Train.doc"
echo hello > "Chicago A01Z01 Green Car Dog Train.xls"
echo hello > "Chicago A01Z02 Green Car Dog Train.xls"
echo hello > "Chicago A01Z02 Green Car Dog Train.doc"
echo hello > "Chicago A01Z03 Green Car Dog Train.xls"
echo hello > "Chicago A01Z03 Green Car Dog Train.doc"
echo hello > "Chicago A01Z01 Green Water Dog Train.doc"
echo hello > "Chicago A01Z02 Green Water Dog Train.doc"
echo hello > "Chicago A01Z03 Green Water Dog Train.doc"
echo hello > "Chicago A01Z04 Green Water Dog Train.doc"
echo hello > "Chicago A01Z05 Green Water Dog Train.doc"

REM These are bad file names
echo hello > "FailAlphaNumeric AB1Z03 Green Car Dog Train.xls"
echo hello > "FailAlphaNumeric AB1Z03 Green Car Dog Train.doc"
echo hello > "FailKeyWord A01Z03 Green Car Dog Bus.xls"
echo hello > "FailKeyWord A01Z03 Green Car Dog Bus.doc"
echo hello > "FailBoth AB1Z03 Green Car Dog Bus.xls"
echo hello > "FailBoth AB1Z03 Green Car Dog Bus.doc"
REM Optional section end -------------------------------

setlocal enabledelayedexpansion
for /f "delims=" %%A in ( 'dir /b *.doc; *.xls' ) do (
for /f "delims=" %%B in ('cscript //nologo Script.vbs "%%~nA"') do Set "Result=%%B"
if "!Result!"=="Bad Filename" (
    Echo Not moving file %%A
    ) else (
    If not exist "!Result!" md "!Result!"
    Echo Moving file     %%A to folder !Result!
    Move "%%A" "!Result!" > nul
    )
)
echo Complete
echo.
Pause
...which calls this VBscript... (Referred to as Script.vbs in the batch file above)

Note: In the keyword list, put longer phrases before shorter phrases using the same words or the words used singly thus:

"Large Red Book", "Red Book", "Book", "Red", "Blue", "Water"

Code: [Select]

InputString = wscript.arguments(0)
TokensArray = split (InputString , " ")
KeyWordList = Array ("Large Red Book", "Blue Train", "Train", "Blue", "Water")
AlphaBlockFound = False
KeyWordFound = False
OutPutString="Bad Filename"
Wscript.echo "Filename " & InputString
For j = lbound(TokensArray) to Ubound(TokensArray)
    MyString=TokensArray(j)
    If Len(MyString) = 6 Then
        FlagString=""
        For k = 1 To 6
            MyChar = Mid(MyString,k,1)
            If IsNumeric(MyChar) = False Then
                If InStr ("ABCDEFGHIJKLMNOPQRSTUVWXYZ", MyChar) > 0 Then
                    FlagString = FlagString & "A"
                End If
            Else
                FlagString = FlagString & "N"
            End If
        Next
        If FlagString = "ANNANN" Then
                AlphaBlockFound = True
                AlphaBlock = TokensArray(j)
                BlockNum=j
        End If
    End If
Next
If AlphaBlockFound = True Then
                PreambleString = ""
                For L = 0 To (BLocknum-1)
                    PreambleString = PreambleString & " " & TokensArray (L)
                Next
                PostambleString = ""
                For l = (BlockNum+1) To UBound(TokensArray)
                    PostambleString = PostambleString & " " & TokensArray (L)
                Next
                PostambleString = trim (PostambleString)
                    For q = LBound(KeyWordList) To UBound(KeyWordList)
                        Sstring=Trim(KeyWordList(q))
                        If instr (ucase(PostAmbleString), ucase(Sstring)) Then
                            OutPutString = PreambleString & " " & Mid(TokensArray(blocknum), 1,3) & " " & Sstring
                            wscript.echo trim(OutPutString)
                            wscript.quit
                        End If
                    Next
End If
wscript.echo OutPutString


[devo]

You've done it again.  It works perfectly.  Thanks for taking the time to help me out.