Extract substring from filename and count

Question

Example below - 5 files will be located in the same folder.

Sales-fid1000-f100.dat
Revenue-fid1000-f100.dat
Sales-fid2000-f200.dat
Revenue-fid2000-f200.dat
Income-fid2000-f200.dat

I need to read the filename and get the number after "fid", in this case 1000 and 2000 and count the number of files associated with each "fid".

So for fid1000, there are 2 files and for fid2000, there are 3 files.

I need to write the output into a .txt file as below with first field being the fid number and second field being the count.

1000|2
2000|3

How can I generate output text file with fid and count using a Windows batch file?

try a `for` loop over the names with a `set` to extract the substring from the filename. If you have a more detailed question come back and we'll help you. — PA., Jan 28 '16 at 09:17

score 0 · Answer 1 · answered Jan 28 '16 at 09:17

@ECHO OFF
SETLOCAL
:: remove variables starting $
FOR  /F "delims==" %%a In ('set $ 2^>Nul') DO SET "%%a="
SET "sourcedir=U:\sourcedir"
SET "destdir=U:\destdir"
SET "outfile=%destdir%\outfile.txt"
FOR /f "delims=" %%a IN (
 'dir /b /a-d "%sourcedir%\*-fid*" '
 ) DO (
 SET "filename=%%a"
 CALL :process
)
(
 FOR  /F "tokens=1,2delims=$=" %%a In ('set $ 2^>Nul') DO ECHO(%%a^|%%b
)>"%outfile%"
GOTO :EOF

:process
SET "filename=%filename:*-fid=%"
FOR /f "delims=-" %%q IN ("%filename%") DO SET /a $%%q+=1
GOTO :eof

You would need to change the settings of sourcedir and destdir to suit your circumstances.

Produces the file defined as %outfile%

After clearing all the $ variables (for safety's sake), perform a directory listing without directorynames and in basic form of files in the source directory matching *-fid*.

For each name found, assign the name to filename and execute the :process routine, which first removes the characters up to and including -fid from filename then uses the delims=- option to assign the part originally between -fid and the following - to %%q.

setthe variable $%%q up by 1 (if $?? is undefined, assign 1)

Finally, when all the names have been processed, list the variables named $... using set which produces a report of the style

$1000=2
$2000=3

Using $ and = as delimiters puts token 1 (eg 2000) into %%a and token 2 (eg 3) into %%b. Write these to the output using echo, remembering to escape the pipe (|) with a caret (^) to suppress the interpretation as a redirector.

The parentheses around the for...$... ensures the output is directed to the destination file specified.

score 0 · Answer 2 · answered Jan 28 '16 at 09:33

Extract the numbers into a temporary file, then count the occurrences of each number in that file.

@echo off

setlocal EnableDelayedExpansion

>temp.txt type nul
set "unique_num="

for /f "tokens=2 delims=-" %%a in ('dir /b *.dat') do (
  set "fid=%%a"
  set "num=!fid:~3!"
  >>temp.txt echo !num!
  echo " !unique_num! " | find " !num! " >nul
  if !errorlevel! neq 0 set "unique_num=!unique_num! !num!"
)

for %%n in (%unique_num%) do (
  for /f "delims=: tokens=2" %%c in ('find /c "%%n" temp.txt') do (
    set "count=%%c"
    echo %%n^|!count: =!
  )
)

del /f /q temp.txt

Pipe the result into sort if you need the output sorted.

score 0 · Accepted Answer · edited May 23 '17 at 11:52

0

@echo off
setlocal EnableDelayedExpansion

rem Process all file names
for /F "tokens=2 delims=-" %%a in ('dir /B /A-D *.dat') do (

   rem Get FID from second dash-delimited token; format: "xxx-fid####-xxx.dat"
   set "fid=%%a"

   rem Accumulate it to the corresponding element of "count" array
   set /A "count[!fid:~3!]+=1"
)

rem Create the output
(for /F "tokens=2,3 delims=[]=" %%a in ('set count[') do echo %%a^|%%b) > output.txt

For further details on array management in Batch files, see: Arrays, linked lists and other data structures in cmd.exe (batch) script

edited May 23 '17 at 11:52

Community

1
1

answered Jan 28 '16 at 14:16

Aacini

65,180
12
72
108

Thank you very much Aacini. Worked perfect!! – Kiran Jan 29 '16 at 21:56

Extract substring from filename and count

3 Answers3