Replacing last characters after last comma with a

Question

I have a huge text file which look like this:

36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,3
36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,8
36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,14
36,53,15596,0.58454577855,0.26119,2.24878677855,0.116147072052964,12

The desired output is this:

36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,MI-03
36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,MI-08
36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,MI-14
36,53,15596,0.58454577855,0.26119,2.24878677855,0.116147072052964,MI-12

I have tried other relevant posts here and on other communities but could not exactly get what I want.

UPDATE

This is the cross-question (I wanted both Unix/perl answers and batch/powershell solutions for this.) that has interesting answers.

Answer 1

Here is a cmd batch file that relies on a nice hack to split off the last item of a comma-separated list, independent on how many commas occur in the string. The basic technique is shown in the following; note that this requires delayed expansion to be enabled:

set "x=This,is,the,original,list."
set "y=" & set "z=%x:,=" & set "y=!y!,!z!" & set "z=%" & set "y=!y:~1!"
echo ORIGINAL:  %x%
echo LAST ITEM: %z%
echo REMAINDER: %y%

So here is the code of the script, holding the above method in a sub-routine called :GET_LAST_ITEM:

@echo off
setlocal EnableExtensions DisableDelayedExpansion

rem // Define constants here:
set "_FILE=%~1" & rem // (specify the CSV file by the first argument)

for /F "usebackq delims=" %%L in ("%_FILE%") do (
    call :GET_LAST_ITEM LAST REST "%%L"
    setlocal EnableDelayedExpansion
    set "LAST=0!LAST!"
    echo(!REST!,MI-!LAST:~-2!
    endlocal
)

endlocal
exit /B


:GET_LAST_ITEM  rtn_last  rtn_without_last  val_string
::This function splits off the last comma-separated item of a string.
::Note that exclamation marks must not occur within the given string.
::PARAMETERS:
::  rtn_last            variable to receive the last item
::  rtn_without_last    variable to receive the remaining string
::  val_string          original string
setlocal EnableDelayedExpansion
set "STR=,%~3"
set "PRE=" & set "END=%STR:,=" & set "PRE=!PRE!,!END!" & set "END=%"
endlocal & set "%~1=%END%" & set "%~2=%PRE:~2%"
exit /B

Answer 2

This is the answer that @RomanPerekhrest provided at my cross-question (seeking unix/perl solutions) here:

awk approach with sprintf function(to add leading zeros):

 awk -F, -v OFS=',' '$8="MI-"sprintf("%02d",$8);' file

The output:

36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,MI-03
36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,MI-08
36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,MI-14
36,53,15596,0.58454577855,0.26119,2.24878677855,0.116147072052964,MI-12

Answer 3

The next code does what you want except for filling with zero the last token when is less than 10, hope it helps.

EDIT: I figured out a way to insert a leading zero when the last number is less than 10. A little bit ugly but does it. :)

@echo off

setlocal EnableDelayedExpansion

for /F "delims=, tokens=1-8" %%A in (f.txt) do (
    set /a "t=%%H-10"
    if "!t:~0,1!" equ "-" (set "n=0%%H") else (set "n=%%H")
    echo(%%A,%%B,%%C,%%D,%%E,%%F,%%G,MI-!n!>>f.new.txt
)

move /Y f.new.txt f.txt >nul 2>&1

For file (f.txt in this case):

36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,3
36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,8
36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,14
36,53,15596,0.58454577855,0.26119,2.24878677855,0.116147072052964,12

Produces the following result (also in f.txt): updated

36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,MI-03
36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,MI-08
36,53,90478,0.58699759849,0.33616,4.83449759849,0.0695335954050315,MI-14
36,53,15596,0.58454577855,0.26119,2.24878677855,0.116147072052964,MI-12

Answer 4

Here's a PowerShell answer in case you like PS.

Get-Content C:\Path\To\File.csv | 
    Where{$_ -match '^(.*,)([^,]*)$'} | 
    ForEach { "{0}MI-{1}" -f $Matches[1], $Matches[2].Padleft(2,'0') } |
    Set-Content C:\Path\To\NewFile.csv