Changing last entries in a comma delimited list
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.
awk approach with sprintf function(to add leading zeros):
awk -F, -v OFS=',' '$8=sprintf("MI-%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
-F, – set comma , as field separator
$8 – points to the eighth field
%02d – format which treats function argument as 2-digit number
Note, the last field in a record can be presented by $NF.
NF is a predefined variable whose value is the number of fields in the current record
So, $NF is the same as $8(for your input)
awk -F, -v OFS=',' '$(NF)=sprintf("MI-%02d", $(NF))' file
You can try using awk:
awk 'BEGIN { FS = OFS = "," } { $NF = sprintf("MI-%02d", $NF); } 1' file
With input data like:
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
in text.csv
the code below
awk -F"," '{ i = 0;
MyOutLine = "";
j = NF - 1;
while ( i < j ) {
i++;
MyOutLine = MyOutLine""$i",";
}
i++;
x = sprintf( "%.2i", $i );
y = "MI-"x;
MyOutLine = MyOutLine""y;
print MyOutLine; }' ./text.csv
produces output like:
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
Here’s perl solution:
$ perl -F',' -lane '$last=$#F;$F[$last]=sprintf("MI-%02d",$F[$last]);print join ",", @F' input.txt
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
The -a flag allows us to treat input as array, based on separator specified with -F. Basically we alter last item in that array, and rebuild it via join command.
Tcl
Here is my solution, done using Tcl which reads from input.csv file and puts the result in output.csv file
set in [open input.csv]
set out [open output.csv w]
while {![eof $in]} {
set line [gets $in]
set last_comma_pos [string last , $line]
puts $out [string range $line 0 $last_comma_pos][format MI-%02d [string range $line $last_comma_pos+1 end]]
}
close $in
close $out