How to ignore first and third data column on sorting and summing the text using batch file?

Question

I have made this script below:

start /w "" "C:\Program Files (x86)\CoolUtils\Total PDF Converter\PDFConverter.exe" "%userprofile%\Desktop\teste.oxps" "%userprofile%\Desktop\teste.txt"
del /f /s /q "%userprofile%\Desktop\teste.pdf"
findstr /v /r /c:"http" /c:"Banrisul" /c:"Sac" /c:":" /c:"-" /c:"SAC" /c:"OUVIDORIA" /c:"B A N R I S U L" /c:"+" "teste.txt" > "output.txt"
for /f "usebackq tokens=* delims=" %%a in ("output.txt") do (echo(%%a)>>output2.txt
move /y output2.txt output.txt

My output.txt is that below which is summarized as real file is too large:

                    01  VERO BANRICOMPRAS A PRAZO                         914709              77,56
                        VERO BANRICOMPRAS A PRAZO                         914710             322,58
                        VERO BANRICOMPRAS A VISTA                         256534             187,64
                        VERO BANRICOMPRAS A VISTA                         256539              17,62
                        VERO CARTAO CREDITO                               391534             146,22
                        VERO CARTAO CREDITO                               391535             159,26
                        VERO CARTAO CREDITO                               391536             543,16
                        VERO CARTAO CREDITO                               391537             479,37
                        VERO CARTAO CREDITO                               391538              96,00
                        REDECARD S.A CARTAO CREDITO                       415856             217,25
                        REDECARD S.A CARTAO CREDITO                       416552             152,28
                        VERO CARTAO DEBITO                                338711             144,60
                        VERO CARTAO DEBITO                                338712             378,03
                        VERO CARTAO DEBITO                                338713             134,34
                        CIELO S/A CARTAO DEBITO                           312125              57,71
                        CIELO S/A CARTAO DEBITO                           312126              23,46
                        CIELO S/A CARTAO DEBITO                           312127             232,55
                        RESGATE AUTOMATICO                                000000          17.700,00
                    02  VERO BANRICOMPRAS A PRAZO                         208538             396,99
                        VERO BANRICOMPRAS A PRAZO                         208539             217,65
                        VERO BANRICOMPRAS A VISTA                         562243             147,65
                        VERO CARTAO CREDITO                               692285             105,58
                        VERO CARTAO CREDITO                               692286              28,53
                        VERO CARTAO CREDITO                               692287             561,48
                        VERO CARTAO CREDITO                               692288             150,14
                        VERO CARTAO CREDITO                               692289             101,78
                        CIELO S/A CARTAO CREDITO                          673728             125,39
                        CIELO S/A CARTAO CREDITO                          673729              25,36
                        REDECARD S.A CARTAO CREDITO                       716879             278,83
                        REDECARD S.A CARTAO CREDITO                       720069              47,78
                        VERO CARTAO DEBITO                                637568             124,70
                        VERO CARTAO DEBITO                                637569             272,15
                        VERO CARTAO DEBITO                                637570             244,13
                        CIELO S/A CARTAO DEBITO                           609977             100,13
                    03  VERO BANRICOMPRAS A PRAZO                         501238             158,81

I'm stuck in how to sum the values of each same text, and then sum the total of all text summed. The middle data column and the initials 01, 02, etc. don't matter. Only the text and the last column should be used. It need be like that bellow:

        VERO BANRICOMPRAS A PRAZO                 20.596,26
        VERO BANRICOMPRAS A VISTA                 14.658,12
        VERO CARTAO CREDITO                        2.549,34
        (etc.)
        ---------------------------------------------------
        TOTAL OF ALL:                             37.803,72

---

Just uploaded my full output.txt in this link: https://ufile.io/uyxr1

---

About my output.txt file: There are just spaces between the data columns, but the number of charachteres of the description and the numbers will always change. But what only matter for me is the description and the last column.

---

@Mofi, look how i expected:

------------ CARDS OF MONTH -----------
CIELO S/A CARTAO CREDITO        2.147,13
CIELO S/A CARTAO DEBITO        10.867,72
REDECARD S.A CARTAO CREDITO    11.835,11
REDECARD S.A CARTAO DEBITO         87,20
VERO BANRICOMPRAS A PRAZO      17.083,70
VERO BANRICOMPRAS A VISTA       7.829,06
VERO CARTAO CREDITO            58.052,05
VERO CARTAO DEBITO             17.215,64
----------------------------------------
TOTAL OF ALL:                 125.117,61
----------------------------------------
RESGATE AUTOMATICO            152.900,00
PAGAMENTO DE FORNECEDOR        25.900,00
DOC-E                          63.487,36

Answer 1

Batch files are for executing a sequence of commands and applications, but not for text file editing or data calculations. There are many other programming and scripting languages which would be much better for this task than using Windows command processor cmd.exe. I would have never come to the idea doing such a task with a batch file using cmd.exe. For that reason I was interested in this task if it is even possible at all to do this with just commands supported on Windows command line. And yes, after hours of coding I can offer a batch file which worked for the example as posted in question. I have not run it on entire file.

@echo off
setlocal EnableExtensions DisableDelayedExpansion
set "DataCount=0"
set "MaxNameLength=19"
set "TempFile1=%TEMP%\Output.tmp"
set "TempFile2=%TEMP%\Sorted.tmp"
set "OutputFile=Output.txt"

"%ProgramFiles(x86)%\CoolUtils\Total PDF Converter\PDFConverter.exe" "%UserProfile%\Desktop\teste.oxps" "%UserProfile%\Desktop\teste.txt"
del /F "%UserProfile%\Desktop\teste.pdf"
%SystemRoot%\System32\findstr.exe /V /R /C:"http" /C:"Banrisul" /C:"Sac" /C:":" /C:"-" /C:"SAC" /C:"OUVIDORIA" /C:"B A N R I S U L" /C:"+" "%UserProfile%\Desktop\teste.txt" >"%TempFile1%"
if not exist "%TempFile1%" goto EndBatch

rem Process each line in the output file line by line with leading
rem spaces and tabs already removed by FOR, but no other characters.
for /F usebackq^ tokens^=*^ eol^= %%I in ("%TempFile1%") do (
    set "DataRow=%%~I"
    call :ProcessLine
)
goto FormatOutput


:ProcessLine
rem Remove all double quotes within the data row.
set "DataRow=%DataRow:"=%"
rem Remove all exclamation marks within the data row.
set "DataRow=%DataRow:!=%"
rem Remove all horizontal tabs within the data row by spaces.
set "DataRow=%DataRow:  = %"
rem Replace all $ by the string #DollarSign# within the data row.
set "DataRow=%DataRow:$=#DollarSign#%"
rem Replace two spaces in series by a dollar sign.
set "DataRow=%DataRow:  =$%"
rem Replace all occurrences of dollar sign plus space by dollar sign.
set "DataRow=%DataRow:$ =$%"

rem Split up the data row using dollar sign as delimiter which can result
rem in four or just three tokenized substrings depending on existence of
rem 01, 02, ... in first data column. Of interest is the name string in
rem first or second data column and the value string in third or fourth
rem data column.
for /F tokens^=1-4^ delims^=$^ eol^= %%J in ("%DataRow%") do (
    if "%%M" == "" (
        set "DataName=%%~J"
        set "DataValue=%%L"
    ) else (
        set "DataName=%%~K"
        set "DataValue=%%M"
    )
)

rem It is safe now to replace the string #DollarSign# back to dollar sign.
set "DataName=%DataName:#DollarSign#=$%"

rem If the first 2 characters of data name are two digits and third
rem character is a space then remove those 3 characters from data name.
if not "%DataName:~2,1%" == " " goto CheckName
if "%DataName:~0,1%" == ";" goto CheckName
for /F "delims=01234567890" %%J in ("%DataName:~0,2%") do goto CheckName
set "DataName=%DataName:~3%"

rem Data names not containing one of the following four strings
rem should be at end of the list and not included in the total sum.
:CheckName
if not "%DataName:DEBITO=%"  == "%DataName%" goto ReformatValue
if not "%DataName:CREDITO=%" == "%DataName%" goto ReformatValue
if not "%DataName:A VISTA=%" == "%DataName%" goto ReformatValue
if not "%DataName:A PRAZO=%" == "%DataName%" goto ReformatValue
set "DataName=z_%DataName%"

rem Floating point arithmetic is not supported by Windows command processor,
rem just signed 32-bit integer arithmetic with values in range -2147483648
rem to 2147483647. Therefore remove all dots and commas from data value.
:ReformatValue
set "DataValue=%DataValue:.=%"
set "DataValue=%DataValue:,=%"
for /F "tokens=* delims=0" %%J in ("%DataValue%") do set "DataValue=%%J"
if not defined DataValue set "DataValue=0"

rem Add the data value as integer to total sum. There is no special
rem overflow handling implemented yet in case of total sum exceeds
rem the maximum 32-bit positive signed integer value 2147483647.
if not "%DataName:~0,2%" == "z_" set /A "#TotalSum+=DataValue"

rem Find out if a data value of current data name is already in list
rem of environment variables and in this case just add the data value
rem to the existing sum for data data name and exit the subroutine.
for /F "tokens=1* delims==" %%J in ('set $\ 2^>nul') do if /I "%%K" == "%DataName%" set /A "#%%~nJ+=DataValue" & goto :EOF

rem This is a new data name with its first data value. So set
rem the appropriate environment variables for name and value.
set /A DataCount+=1
set "$\%DataCount%=%DataName%"
set "#%DataCount%=%DataValue%"

rem For a later aligned output find out the length of the data name and
rem remember its length if being greater than longest data name up to now.
rem z_ added at beginning of some data names must be always ignored for
rem length of data name.
set "NameLength=1"
:GetNameLength
set "DataName=%DataName:~1%"
if not "%DataName%" == "" set /A "NameLength+=1" & goto GetNameLength
if "%DataName:~0,2%" == "z_" set /A NameLength-=2
if %NameLength% GTR %MaxNameLength% set "MaxNameLength=%NameLength%"

rem Exit the subroutine.
goto :EOF


:FormatOutput
rem Add 3 to maximum name length to have always at least 3 spaces
rem between longest data name and the sum of the data values.
set /A MaxNameLength+=3

setlocal EnableDelayedExpansion
rem Build a string consisting of spaces according to maximum name length.
set "SpacesName="
for /L %%I in (1,1,%MaxNameLength%) do set "SpacesName=!SpacesName! "

rem Format value of total sum.
call :FormatNumber %#TotalSum%

rem For a later aligned output find out the length of the total
rem sum which is expected to be the greatest value in output.
set "ValueLength=1"
:GetValueLength
set "DataValue=%DataValue:~1%"
if not "%DataValue%" == "" set /A "ValueLength+=1" & goto GetValueLength

rem Build a string consisting of spaces according to maximum name length.
set "SpacesValue="
for /L %%I in (1,1,%ValueLength%) do set "SpacesValue=!SpacesValue! "

rem Output the data names with appropriate number of aligning spaces
rem and the data sum for each data name formatted and with aligning
rem spaces into the output file.
set /A ExtraNameLength=MaxNameLength+2
del "%TempFile1%" 2>nul
(for /F "tokens=1* delims==" %%I in ('set $\ 2^>nul') do (
    set "DataName=%%J%SpacesName%"
    if not "!DataName:~0,2!" == "z_" (
        set "DataName=!DataName:~0,%MaxNameLength%!"
    ) else set "DataName=!DataName:~0,%ExtraNameLength%!"
    call :FormatNumber !#%%~nI!
    set "DataValue=%SpacesValue%!DataValue!"
    set "DataValue=!DataValue:~-%ValueLength%!"
    echo !DataName!!DataValue!
))>>"%TempFile1%"

rem Sort the lines in output file according to name.
%SystemRoot%\System32\sort.exe "%TempFile1%" /O "%TempFile2%"
del "%TempFile1%" 2>nul

rem 16 is the length of string CARDS OF MONTH with a space on both sides.
set /A LineLength=MaxNameLength+ValueLength
set /A HeadLength=(LineLength-16) / 2

rem Build the heading for the output file with centered CARDS OF MONTH
rem and create the output file with this heading as first line.
set "HyphensHead="
for /L %%I in (1,1,%HeadLength%) do set "HyphensHead=!HyphensHead!-"
set "Heading=%HyphensHead% CARDS OF MONTH %HyphensHead%"
set /A HeadLength=HeadLength*2 + 16
if not %HeadLength% == %LineLength% set "Heading=%Heading%-"
echo %Heading%>"%OutputFile%"

set "TotalSumWrite=1"
for /F usebackq^ tokens^=*^ eol^= %%I in ("%TempFile2%") do (
    set "DataRow=%%I"
    if defined TotalSumWrite (
        if not "!DataRow:~0,2!" == "z_" (
            echo !DataRow!>>"%OutputFile%"
        ) else (
            rem Append the total summary to the output file.
            set "TotalSumWrite="
            >>"%OutputFile%" echo %SpacesName: =-%%SpacesValue: =-%
            set "DataName=TOTAL OF ALL:%SpacesName%"
            set "DataName=!DataName:~0,%MaxNameLength%!"
            call :FormatNumber %#TotalSum%
            set "DataValue=%SpacesValue%!DataValue!"
            set "DataValue=!DataValue:~-%ValueLength%!"
            >>"%OutputFile%" echo !DataName!!DataValue!
            >>"%OutputFile%" echo %SpacesName: =-%%SpacesValue: =-%
            >>"%OutputFile%" echo !DataRow:~2!
        )
    ) else echo !DataRow:~2!>>"%OutputFile%"
)

if defined TotalSumWrite (
    >>"%OutputFile%" echo %SpacesName: =-%%SpacesValue: =-%
    set "DataName=TOTAL OF ALL:%SpacesName%"
    set "DataName=!DataName:~0,%MaxNameLength%!"
    call :FormatNumber %#TotalSum%
    set "DataValue=%SpacesValue%!DataValue!"
    set "DataValue=!DataValue:~-%ValueLength%!"
    >>"%OutputFile%" echo !DataName!!DataValue!
)

del "%TempFile2%" 2>nul
endlocal
goto EndBatch


rem The subroutine below reformats 0 to 99 to 0,00 to 0,99 and inserts
rem dots on larger values after a series of 3 digits left to the comma.

:FormatNumber
set "DataValue=%1"
if "%DataValue:~1,1%" == "" set "DataValue=0%DataValue%"
if "%DataValue:~2,1%" == "" set "DataValue=0%DataValue%"
set "DataValue=%DataValue:~0,-2%,%DataValue:~-2%"
if not "%DataValue:~6,1%" == "" set "DataValue=%DataValue:~0,-6%.%DataValue:~-6%"
if not "%DataValue:~10,1%" == "" set "DataValue=%DataValue:~0,-10%.%DataValue:~-10%"
goto :EOF

:EndBatch
endlocal

Note 1: There is one line in code where a horizontal tab character must be in the line and not two spaces as the browsers display according to HTML specification. So search for the comment containing the string horizontal tab and replace the spaces in the line below between : and = by a tab character.

Note 2: Read the comments which are the lines starting with rem. The batch code removes all " as well as all ! from all lines before processing each line.

Note 3: Windows command processor does not support floating point arithmetic. It supports only 32-bit signed integer arithmetic. So each sum including the total sum which becomes greater than 21.474.836,47 during the calculation is wrong on exceeding this limit. It would be of course possible to work around this limit with extra code if that would be really needed depending on the data.

Note 4: The batch code interprets two or more spaces in series as separator between the data columns. So if any string in second data column has by chance two spaces in series, the batch file produces a wrong result for at least this data row.

Note 5: The batch file takes some time to finish the task because of Windows command processor is not really designed for such tasks.

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

• call /?
• del /?
• echo /?
• endlocal /?
• findstr /?
• for /?
• goto /?
• if /?
• rem /?
• set /?
• setlocal /?
• sort /?

See also:

• Where does GOTO :EOF return to?
• Single line with multiple commands using Windows batch file
• Microsoft article about Using command redirection operators

Edit: Please use this code which is an enhanced version of the code posted here which outputs all sums correct in case of one sum below the sum output on line TOTAL OF ALL: has a value with more characters.

Answer 2

cmd isn't good at math. There is the set /a command, but it can only handle 32Bit Integer (-2,147,483,648 through 2,147,483,647). . and , are no part of numbers, so we have to remove them for calculation and insert them again at the end.

We can get substrings with the set command.

The set commands after echo ------------- format the sum (inserting dots and commas and build a right aligned string; you may have to adapt it a bit to take care of shorter or longer numbers - I'll leave that to you)

@echo off
setlocal enabledelayedexpansion
(
for /f "delims=" %%a in (output.txt) do (
  set "line=%%a"
  set "second=!line:~28,30!"
  set "fourth=!line:~90!
  set "summand=!fourth:.=!"
  set "summand=!summand:,=!"
  set /a sum+=summand
  echo !second!!fourth!
)
echo -------------------------------------------
set "sum=!sum:~0,-2!,!sum:~-2!"
set "sum=!sum:~0,-6!.!sum:~-6!"
set "sum=                    !sum!"
set "sum=!sum:~-20!
echo TOTAL OF ALL:          !sum!
)>out.txt
type out.txt

NOTE: due to the INT32 in combination with removing the comma (multiply by 10), your result might be strange, if the sum is bigger than 21.474.836,47