create folders from a list and move files

[sifar786]

Hi,

i have a folder containing files of different types viz., .xls, .cls, .jpg, etc. Each file is sequentially numbered e.g.
10001_ENU.jpg
10001_COE.jpg
10001_ANU.jpg
10001_ENU.mdb
10001_COE.pdf
10001_ENU.pdf
10002_ENI.jpg
10002_EVS.cls
10002_TNU.png
10002_TAS.docx
10003_NOE.doc
10003_NIO.xlsm
10003_RAS.ppt
10004_GAR.xls

etc....where the 1st numeric prefixed code is an ID, followed by a Language code, followed by the Extension. The folder contains over 2 lac such different files which i want to group into folders created on ID.

now i have an Excel list of ClassID's and each ClassID has multiple such ID's. e.g

ClassID ID
------------------
1 10001
1 10004
2 10002
3 10003

I want to read the list in excel file, create folders for ClassID, and in them sub-folders for ID, such that all the files with the ID names will be moved into their respective ID folders.

then i want to output to an Excel CSV or delimited text file, the count of each file, based on their type (extension):

e.g.

ID jpg pdf mdb cls png docx doc xlsm ppt xls
10001 3 2 1
10002 1 1 1 1 1 1
10003 1 1 1 1


It is a daunting task to create ID folders and move in them over 2 lac files. any help would be most appreciated as i would be learning something new in the process.

Thanks.

[Endoro]

try this:

Code: Select all

@echo off&setlocal
cd /d startfolder
set "CSVdelim=   " &rem, delimiter for output, default \t
set "ext=cls doc docx jpg mdb pdf png ppt xls xlsm"  &rem extensions must be in alphabetical order

for /f "delims=_" %%i in ('dir /a-d/b') do set "$%%~i=0"
for /f "delims=$=" %%i in ('set "$"') do for %%j in (%ext%) do set /a #%%~i.%%~j=0
for /f "delims=$=" %%i in ('set "$"') do for /f "delims=" %%a in ('dir /a-d/b "%%~i_*"') do set /a #%%i%%~xa+=1
<nul set/p"=ID"
for %%i in (%ext%) do <nul set/p"=%CSVdelim%%%i"
echo(
for /f "tokens=1-3delims=#.=" %%i in ('set "#"') do (
   if "%%j" equ "cls" <nul set/p"=%%i"
   <nul set /p"=%CSVdelim%%%~k"
   if "%%j" equ "xlsm" echo(
)

.. output is:

Code: Select all

ID      cls     doc     docx    jpg     mdb     pdf     png     ppt     xls     xlsm
10001   0       0       0       3       1       2       0       0       0       0
10002   1       0       1       1       0       0       1       0       0       0
10003   0       1       0       0       0       0       0       1       0       1
10004   0       0       0       0       0       0       0       0       1       0

please specify ClassID

[foxidrive]

That's a nice way to present it, Endoro. Easy to read.

[sifar786]

This looks nice, but how do i make it work? i am getting 'file not found', 'Missing operator', 'file not found' filling my screen when i run the batch.

does it take an input file and if so, what would be the command syntax i need to type on the command prompt? it would be nice if you could explain the steps for my understanding.

Code: Select all

C:\Users\sifar786\Desktop>test11
The system cannot find the path specified.
Missing operator.
Missing operator.
Missing operator.
Missing operator.
Missing operator.
Missing operator.
Missing operator.
Missing operator.
Missing operator.
Missing operator.
Missing operator.
File Not Found
File Not Found
File Not Found
Missing operator.
File Not Found
File Not Found
File Not Found
File Not Found
IDclsdocdocxjpgmdbpdfpngpptxlsxlsm
clsdocdocxjpgmdbpdfpngpptxlsxlsmclsdocdocxjpgmdbpdfpngpptxlsxlsmFor00000000100
xlsxlsxlsxlsxlsxlsxlsxlsxlsxlsxlsmxlsmxlsmxlsmxlsmxlsmxlsmxlsmxlsmxlsmzipzipzipz
ipzipzipzipzipzipzipclsdocdocxjpgmdbpdfpngpptxlsxlsmclsdocdocxjpgmdbpdfpngpptxls
xlsmclsdocdocxjpgmdbpdfpngpptxlsxlsmclsdocdocxjpgmdbpdfpngpptxlsxlsmclsdocdocxjp
gmdbpdfpngpptxlsxlsmclsdocdocxjpgmdbpdfpngpptxlsxlsmclsdocdocxjpgmdbpdfpngpptxls
xlsmclsdocdocxjpgmdbpdfpngpptxlsxlsmclsdocdocxjpgmdbpdfpngpptxlsxlsmclsdocdocxjp
gmdbpdfpngpptxlsxlsmclsdocdocxjpgmdbpdfpngpptxlsxlsmclsdocdocxjpgmdbpdfpngpptxls
xlsmclsdocdocxjpgmdbpdfpngpptxlsxlsmTODO0000000000
1
C:\Users\sifar786\Desktop>

[Endoro]

yes, if no suitable file is found the code crashs

Code: Select all

@echo off&setlocal
cd /d startfolder
set "CSVdelim=   " &rem, delimiter for output, default \t
set "ext=cls doc docx jpg mdb pdf png ppt xls xlsm"  &rem extensions must be in alphabetical order
set "pat=*_*.cls *_*.doc *_*.docx *_*.jpg *_*.mdb *_*.pdf *_*.png *_*.ppt *_*.xls *_*.xlsm"  &rem search pattern must be in alphabetical order

dir %pat% >nul 2>&1 || (echo file not found: "%pat%"&goto:eof)
for /f "delims=_" %%i in ('dir /a-d/b %pat%') do set "$%%~i=0"
for /f "delims=$=" %%i in ('set "$"') do for %%j in (%ext%) do set /a #%%~i.%%~j=0
for /f "delims=$=" %%i in ('set "$"') do for /f "delims=" %%a in ('dir /a-d/b %pat%') do set /a #%%i%%~xa+=1
<nul set/p"=ID"
for %%i in (%ext%) do <nul set/p"=%CSVdelim%%%i"
echo(
for /f "tokens=1-3delims=#.=" %%i in ('set "#"') do (
   if "%%j" equ "cls" <nul set/p"=%%i"
   <nul set /p"=%CSVdelim%%%~k"
   if "%%j" equ "xlsm" echo(
)

The system cannot find the path specified.

this means, you have not set the folder (startfolder)

[sifar786]

how do i set the startfolder? what is the syntax i need to type at the command prompt? i am typing:

C:\Users\sifar786\Desktop>test11.bat


as you can see from the earlier command prompt dump i showed, i am trying to run the batch file in the current directory where the bat file is located, so that i can get a count of all diff types of files that are there in the current directory and then save the output to a csv.

[Squashman]

how do i set the startfolder?

Maybe the code that says cd /d startfolder.

[sifar786]

so, i set

Code: Select all

cd /d "c:\users\sifar786\desktop"

still gives same error. tried another folder, still the same error or some new errors too :

Code: Select all

File Not Found
Environment variable $ not defined
Environment variable $ not defined
IDclsdocdocxjpgmdbpdfpngpptxlsxlsm
Environment variable # not defined

[Endoro]

so your files don't meet the conditions of your first posting here.
Please show the output of

Code: Select all

dir /a-d /b "c:\users\sifar786\desktop" 

[foxidrive]

I get this when I have a file called my_batch.bat so in Win 7/8 it has the issue where set /p doesn't respect the leading whitespace.

Code: Select all

IDbatdocdocxjpgmdbpdfpngpptxlsxlsm
1000000000

[Endoro]

oh, yes, this can make an output issue, I have to put "CSVdelim" at the end of "set/p".

[sifar786]

Hi Endoro,

I think my original query was to get every extension found in a folder and display and not limit it to a fixed few.

i have pasted the screen dump i get when i run the code.

Code: Select all

C:\Users\sifar786\Desktop>dir /a-d /b "c:\users\sifar786\desktop"
advdos.pdf
Sinclair spectrum team (120.140.127.22).lnk
zxc Tracker.xlsx
Category cols_23 April 13.xls
Conc1.zip
Conc2.zip
Viability zinxtool.xlsm
Feedback Form.xlsm
Feedback Form.xlsx
desktop.ini
For_Mary.pptx
For_Jane.zip
Google Chrome.lnk
Internet Explorer.lnk
FORMAT (APR - MAY 2013).xls
Sign1.3.xls
Sign1.3.xlsm
Sign1.3.zip
test.txt
test1.bat
test10.bat
test11.bat
test12.bat
test2.bat
test3.bat
test4.bat
test5.bat
test6.bat
test7.bat
test8.bat
test9.bat
testy.bat
Tagart Quick Start Guide.docx
TazLIST.xlsx

C:\Users\sifar786\desktop>

[Endoro]

Yes, I see. If you changed your requirements you should best make a new question.

[sifar786]

Hi Endoro,

i still get the same error (missing operator) even after keeping the same fixed number of filetypes in a folder.

someone gave this solution in an earlier post. it does not rely on a fixed pattern of filetypes to be specified at the beginning of code, and gives a unique list of extensions found in a folder with their respective count. But i dont know how to plug it into your code and output it as i want (the same output as in your earlier post) due to lack of understanding.

Code: Select all

@echo off

setlocal
for /f "delims=" %%f in ('dir /b/a-d "*"') do set /a %%~xf = %%~xf + 1
set .
pause

[Endoro]

here, please:

Code: Select all

@echo off&setlocal
cd /d startfolder
set "CSVdelim=   " &rem, delimiter for output, default \t

for /f "delims=" %%f in ('dir /b/a-d "*"') do set /a %%~xf=%%~xf + 1 2>nul
(for /f "delims==." %%i in ('set "."') do <nul set/p"=%%i%CSVdelim%")&echo(
(for /f "tokens=2delims==" %%i in ('set "."') do <nul set/p"=%%i%CSVdelim%")&echo(

..output is:

Code: Select all

cls   doc   docx   jpg   mdb   pdf   png   ppt   xls   xlsm
1      1      1      4      1      2      1      1      1      1