Sort lines of a .txt file with PowerShell

Question

I have a .txt like that below:

MasterCard 
R$ 66,12 

Hipercard 
R$ 7.501,98 

MasterCard 
R$ 48,19 

Hipercard 
R$ 7.742,46 

MasterCard 
R$ 48,19 

Hipercard 
R$ 5.846,14 

MasterCard 
R$ 48,19 

Hipercard 
R$ 4.210,65 

Visa
R$ 4.210,65 

MasterCard 
R$ 48,19 

Hipercard 
R$ 2.892,20 

MasterCard 
R$ 48,19 

Visa
R$ 4.210,65

Hipercard 
R$ 1.944,19 

Hipercard 
R$ 584,04 

Visa
R$ 4.210,65

I need a .bat file (could be in PowerShell) that organize the lines by the descrption and value, like below:

-----------------
MasterCard 
R$ 66,12 

MasterCard 
R$ 48,19 

MasterCard 
R$ 48,19 

MasterCard 
R$ 48,19 

MasterCard 
R$ 48,19 

MasterCard 
R$ 48,19 

----------------      
Hipercard 
R$ 7.501,98 

Hipercard 
R$ 7.742,46 

Hipercard 
R$ 5.846,14 

Hipercard 
R$ 4.210,65

Hipercard 
R$ 2.892,20 

Hipercard 
R$ 1.944,19 

Hipercard 
R$ 584,04 

--------------        
Visa
R$ 4.210,65 

Visa
R$ 4.210,65

Visa
R$ 4.210,65

It's possibile do it with a .bat file? I'm stuck in how to start. I was trying using a simple command: sort test.txt /o testout.txt

But it's not working.

---

I have another idea. Running this command i can put all lines in the same line:

for /f "usebackq tokens=* delims=" %%a in ("frt01.txt") do (echo(%%a)>>test.txt
for /f "usebackqdelims=" %%i in ("test.txt") do @<nul set /p"=%%i ">>"test2.txt"
del test.txt /S /Q /

So now, the description and values are all in only one line:

MasterCard  R$ 66,12  Hipercard  R$ 7.501,98  MasterCard  R$ 48,19  Hipercard  R$ 7.742,46  MasterCard  R$ 48,19  Hipercard  R$ 5.846,14  MasterCard  R$ 48,19  Hipercard  R$ 4.210,65  MasterCard  R$ 48,19  Hipercard  R$ 2.892,20  MasterCard  R$ 48,19  Hipercard  R$ 1.944,19  Hipercard  R$ 584,04  

How can i do for it be like bellow? (Type a 'enter' after each value)

MasterCard  R$ 66,12  
Hipercard  R$ 7.501,98  
MasterCard  R$ 48,19  
Hipercard  R$ 7.742,46  
MasterCard  R$ 48,19  
Hipercard  R$ 5.846,14  
MasterCard  R$ 48,19  
Hipercard  R$ 4.210,65  
MasterCard  R$ 48,19  
Hipercard  R$ 2.892,20  
MasterCard  R$ 48,19  
Hipercard  R$ 1.944,19  
Hipercard  R$ 584,04  

Because then i can sort with this command: sort test2.txt /o output.txt

I guess doing by this way is easier.

---

@michael_heath This is what i was talking:

Hipercard   R$    584,04
Hipercard   R$  1.944,19
Hipercard   R$  2.892,20
Hipercard   R$  4.210,65
Hipercard   R$  5.846,14
Hipercard   R$  7.501,98
Hipercard   R$  7.742,46
------------------------
MasterCard  R$     48,19
MasterCard  R$     48,19
MasterCard  R$     48,19
MasterCard  R$     48,19
MasterCard  R$     48,19
MasterCard  R$     66,12
------------------------
Visa        R$     48,19
Visa        R$     48,19
Visa        R$     48,19
------------------------

Answer 1

The following code parses the input file into an array of transaction objects, sorts them, then outputs the sorted array. Not that the amounts were sorted as a right-justified string.

$unsortedtransactions = @()

Get-Content .\in.txt -ReadCount 3 |
    ForEach-Object {
        $Matches = $null
        "$_" -match '([^\s]*)\s*(\S*)\s*(.*)$(.*)$' | Out-Null
        $thisrecord = [PSCustomObject] @{ Card = $Matches[1]
            Currency = $Matches[2]
            Amount = $("{0,15}" -f @($Matches[3].Trim()))
        }
        $unsortedtransactions += $thisrecord
    }

$sortedtransactions = $unsortedtransactions | Sort-Object -Property Card, Currency, Amount

foreach ($t in $sortedtransactions) {
    $("{0}`r`n{1} {2}`r`n" -f @($t.Card, $t.Currency, $t.Amount.Trim())) |
        Out-File -FilePath st.txt -Encoding ascii -Append
}

Answer 2

All batch-file code

Option 1

@echo off
setlocal enabledelayedexpansion

(
    for /f "tokens=1,2" %%A in (frt01.txt) do (
        if not "%%~A" == "R$" (
            set "card=%%~A          "
            set "card=!card:~,11!"
        ) else (
            set "value=          %%~B"
            set "value=!value:~-9!"
            echo !card! %%~A !value!
        )
    )
) > unsorted.txt

sort unsorted.txt /o output.txt

rem  Optional code below to insert ------- between rows after sort.

set "last_card="
(
    for /f "delims=" %%A in (output.txt) do (
        for /f %%B in ("%%~A") do set "card=%%~B"
        if defined last_card if not "!card!" == "!last_card!" (
            echo ------------------------
        )
        echo %%A
        set "last_card=!card!"
    )
    echo ------------------------
) > tmp.txt

move /y tmp.txt output.txt

This is the delayed expansion concept. Fixed spacing is used so sort can order better with different lengths of the cards and of the currency values.

• for /? for token descriptions etc.
• set /? for substitution syntax used i.e. set "card=!card:~,11!".

---

Option 2

@(
    @echo off
    setlocal enabledelayedexpansion
    for /f "tokens=1,2" %%A in (frt01.txt) do (
        if not "%%~A" == "R$" (
            set "card=%%~A          "
            call set "card=%%card:~,11%%"
        ) else (
            set "value=          %%~B"
            call set "value=%%value:~-9%%"
            call echo "%%card%%" "%%A" "%%value%%"
        )
    )
) | sort /o output.txt

This is a unusual concept. Like previous code, uses fixed spacing. It uses no temporary file as it pipes direct to sort.

Uses setlocal enabledelayedexpansion even though no ! are used. Delayed variables would not expand. Without, delayed expansion, the call set and call echo would not work.

Works on Win7 though perhaps it's strange behavior may make the other code a better option.

---

Option 3

@echo off
setlocal

if not "%~1" == "" (
    if "%~1" == "/?" goto :help
    for %%A in (%*) do call :read "%%~A"
    exit /b
)

rem If no arguments, do this optional task instead.
> "%~dp0unsorted.txt" call :read "frt01.txt"
sort "%~dp0unsorted.txt" /o "%~dp0output.txt"
del "%~dp0unsorted.txt"

exit /b

:read
setlocal enabledelayedexpansion

for /f "usebackq tokens=1,2" %%A in ("%~1") do (
    if not "%%~A" == "R$" (
        set "card=%%~A          "
        set "card=!card:~,11!"
    ) else (
        set "value=          %%~B"
        set "value=!value:~-9!"
        echo !card! %%~A !value!
    )
)
exit /b

:help
echo Prints Hipercard, MasterCard... vertical list as 3 horizontal fixed columns.
echo If multiple files are passed as arguments, they will be printed in sequence.
echo.
echo Syntax: %~nx0 file [file [file]]
echo.
echo Examples:
echo   %~nx0 "test 1.txt"
echo   %~nx0 test1.txt test2.text ^| sort /o output.txt
exit /b

This is a standalone script or run code below the rem remark line. You could remove the optional code and place the script in PATH so you can run it from a console anywhere.

If the script is named cardsorter.cmd, then you could i.e.

cardsorter frt01.txt | sort /o output.txt

at a command prompt.

---

Output Of All Options

Hipercard   R$    584,04
Hipercard   R$  1.944,19
Hipercard   R$  2.892,20
Hipercard   R$  4.210,65
Hipercard   R$  5.846,14
Hipercard   R$  7.501,98
Hipercard   R$  7.742,46
MasterCard  R$     48,19
MasterCard  R$     48,19
MasterCard  R$     48,19
MasterCard  R$     48,19
MasterCard  R$     48,19
MasterCard  R$     66,12
Visa        R$  4.210,65
Visa        R$  4.210,65
Visa        R$  4.210,65

---

Note

Option 1 inserts -------------- to separate card types.

Answer 3

Here is a batch file solution. To stack the lines together you just use a variable to hold the previous line.

@echo off
setlocal enabledelayedexpansion

set "line1="
(FOR /F "delims=" %%G IN (input.txt) DO (
    IF NOT DEFINED line1 (
        set "line1=%%G
    ) else (
        echo !line1! %%G
        set "line1="
    )
))>temp.txt
sort temp.txt>output.txt

Answer 4

Before you can sort the data, you'll need to parse it. You can use the ConvertFrom-String cmdlet with a template to do this:

$Template = @'
{CardProvider*:MasterCard}
R$ {Amount:66,12}

{CardProvider*:Hipercard}
R$ {Amount:7.501,98}

{CardProvider*:Visa}
R$ {Amount:4.210,65}

'@

$Transactions = Get-Content path\to\file.txt |ConvertFrom-String -TemplateContent $Template

The $Transactions variable will now contain an array of objects with a CardProvider and an Amount property each. To sort by provider and then by amount we can do something like:

# Define output file name
$OutputFile = 'output.txt'
# Group by card type first
$Transactions |Group-Object CardProvider |ForEach-Object {
    # Write seperator to output file:
    '----------------' |Add-Content -Path $OutputFile 
    # Now we sort each group of cards by descending amount
    $_.Group |Sort-Object {$_.Amount -replace '\.' -replace ',','.' -as [decimal]} -Descending |ForEach-Object {
        # Finally write the contents to file again
        $_.CardProvider,"R$ $($_.Amount)","" |Add-Content -Path $OutputFile
    }
}