Merge files, reverse order and replace text

Question

I have a data file (*.js) per day with the following content:

m[mi++]="26.03.23 23:55:00|0;0;0;2720;0;0;0|488;0;3270"
m[mi++]="26.03.23 23:50:00|0;0;0;2720;0;0;0|1360;0;3220"
m[mi++]="26.03.23 23:45:00|0;0;0;2720;0;0;0|192;0;3110"
...

I would like to merge all files into one file per month. This works with the following command in Windows Powershell:

cat *.js > 2023_04.csv

But before merging, I would like to do a few operations first:

1. reverse the order of the file so the time is ascending instead of descending
2. remove the 'm[mi++]="' at the beginning of every line
3. remove the '"' at the end of every line
4. replace the '|' with ';' on every line

Is this possible in Powershell? If not, what would be the best choice to create a script for this?

I can merge multiple files into one file with the 'cat' command in Windows Powershell.

Answer 1

Done.

# remove old file
if (Test-Path -Path $filename) 
{
    try { Remove-Item $filename }
    catch { }
}

$result

# get all files in current directory with .js extension
$files = Get-ChildItem -Path .\ -Filter *.js

# loop through files
foreach ($file in $files)
{
    # read file
    $var = Get-Content $file
    
    # reverse file
    [array]::reverse($var)
    
    # remove m[mi++]=
    $var = $var -replace 'm\[mi\+\+\]\=', ''

    # remove double quotes (")
    $var = $var -replace '"', ''

    # replace | to ;
    $var = $var -replace '\|', ';'
    
    # add processed file to result
    $result += $var
}

$result | Out-File -encoding ASCII $filename```

Answer 2

You can use a combination of .Replace for literal replacement of | with ; into a regex replacement with -replace of the starting m[mi++]=" and the ending ", from there pipe into Sort-Object to sort (ascending by default) the strings by the first token obtained from splitting the strings by ;.

(Get-Content *.js).Replace('|', ';') -replace '^m\[mi\++]="|"$' |
    Sort-Object { $_.Split(';', 2)[0] } |
    Set-Content path\to\export.file

Answer 3

Had a more detailed look into Powershell scripts and came up with this so far. Just need to reverse the order of the files.

Learned 2 things to make it work:

• a Powershell script has a .ps1 extension instead of .bat
• Windows needs the correct permissions to run a Powershell script

set-executionpolicy remotesigned

$filename = "test.js"

# remove old file
if (Test-Path -Path $filename) 
{
    try { Remove-Item $filename }
    catch { }
}

# import all .js files in directory
$var = Get-Content *.js

# remove m[mi++]=
$var = $var -replace 'm\[mi\+\+\]\=', ''

# remove double quotes (")
$var = $var -replace '"', ''

# replace | to ;
$var = $var -replace '\|', ';'

# export to file
$var | Out-File -encoding ASCII $filename