8

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.

Improve this question
0

5 Answers 5

Reset to default
14

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
Improve this answer
1
  • 1
    A word of warning (irrelevant in this exemple, but could apply in other cases) : changing a value of one of the fields (here: $8) "recomputes" the whole line's fields, and have side effets: ex1: loses 'multiple separators': echo "1   2 3    4" | awk '{$2=$2;print $0}' gives: 1 2 3 4 (only 1 space (or OFS) left between fields). ex2) echo "1,,,2,3,,,,4" | awk -F',' '{$2=$2;print $0}' gives: 1   2 3    4 (commas became spaces) . There could be other side effects. Test and take another approach (gsub on a copy variable of $0,for ex) if assiging a field have detrimental side effects.
    – Olivier Dulac
    Apr 14, 2017 at 12:38
3

You can try using awk:

awk 'BEGIN { FS = OFS = "," } { $NF = sprintf("MI-%02d", $NF); } 1' file
Improve this answer
0
2

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.

Improve this answer
2
  • Thank you for your answer. It does help if someone needs perl but still sprintf is the core idea of your answer. Not like if it's not right, just not offering something different than accepted answer. +1 anyways.
    – M--
    Apr 13, 2017 at 20:47
  • 1
    @Masoud well, main reason here is because sprintf() is used typically when writing a string of specific format to a variable, which is why it is used in many other languages. I can write it in Python as well - Python doesn't have sprintf() but the core idea will be the same regardless - writing formatted string to a variable. Alternatively, we can operate on array items directly and just print those. With this type of questions there is finite amount of solutions, basically is what I'm trying to say
    – Sergiy Kolodyazhnyy
    Apr 13, 2017 at 20:56
1

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
Improve this answer
0
1

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

demonstration

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .