LinuxQuestions.org - CSV | Text manipulation

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - CSV | Text manipulation (https://www.linuxquestions.org/questions/programming-9/csv-%7C-text-manipulation-659220/)

CSV | Text manipulation

Hi,

This forum is awesome, has helped me accomplish some data manipulation things really quickly!

I have another question now, not sure if gawk can be used for this or not....

I need to convert an alpha code in a csv file to an alpha-numeric code based on the following criteria:-

Code:

If field starts with N then new code starts with 01

If field starts with C then new code starts with 02

If field starts with L then new code starts with 03



THEN



If the conditions above are true then take three characters after the first character of the original code and add this to the 01,02 or 03 above.



THEN



Add 01 on the end.



If the field starts with something other than N, C or L then leave the field intact.



An example:



NBUP would become 01BUP01

CBUP would become 02BUP01

NWAR would become 01WAR01

012322 would become 012322

The format of the original csv file would be as follows and would require an additional field for the new codes:-

Code:

ID, Amount, Old code

654, 45.00, NBUP

5432, 20.00, CBUP

42, 65.00, NWAR

442, 66.00, 012322

So the output when directed to a csv file, would look like

Code:

ID, Amount, Old code, New code

654, 45.00, NBUP, 01BUP01

5432, 20.00, CBUP, 02BUP01

42, 65.00, NWAR, 01WAR01

442, 66.00, 012322, 012322

Thanks so much for help. I have started to read about awk arrays but certainly not at the standard required to do the job above!

sed 's/, N\([A-Z]\+\)/, 01\101/; s/, C\([A-Z]\+\)/, 02\101/; s/, L\([A-Z]\+\)/, 03\101/;'

This is for GNU Awk (otherwise you should escape the new lines in the ternary operator):

Code:

awk>new.csv -F', *' 'BEGIN {

  n = split("N C L", t, OFS)

  while (++i <= n) tt[t[i]] = sprintf("%02d", i)

  c = "01"

  }

NR == 1 { $(NF + 1) = "New code"; print; next }

{ $(NF + 1) = $NF ~ /^[NCL].*/ ? 

    tt[substr($NF, 1, 1)] substr($NF, 2, 3) c : 

      $NF }

1'  OFS=', ' filename

Quote:

Originally Posted by burschik (Post 3230785)

sed 's/, N\([A-Z]\+\)/, 01\101/; s/, C\([A-Z]\+\)/, 02\101/; s/, L\([A-Z]\+\)/, 03\101/;'

Thanks. I have never used sed before, how do issue the command with the csv file.

I tried...

Code:

sed 's/, N\([A-Z]\+\)/, 01\101/; s/, C\([A-Z]\+\)/, 02\101/; s/, L\([A-Z]\+\)/, 03\101/;' input.csv > output.csv

But it didn't work.

Thanks

It should work like that. What exactly does not work?

if you have Python, here's an alternative

Code:

for line in open("file"):

    line=line.strip() #strip new line

    code=line.split(",")[2].strip() #get the code

    if code.startswith("N"):

        newcode="01"+code[1:]+"01"

        print line+", "+newcode

    elif code.startswith("C"):

        newcode="02"+code[1:]+"01"

        print line+", "+newcode

    elif code.startswith("L"):

        newcode="03"+code[1:]+"01"

        print line+", "+newcode

    else:

        newcode=""

        print line

Quote:

Originally Posted by burschik (Post 3230816)

It should work like that. What exactly does not work?

Code:

sed: -e expression #1, char 91: extra characters after command

That is slightly surprising, since the expression is less than 91 characters long. Are you sure that your placement of quotation marks is correct?

Quote:

Originally Posted by burschik (Post 3230984)

That is slightly surprising, since the expression is less than 91 characters long. Are you sure that your placement of quotation marks is correct?

I ran the following..no error message and output file is generated but looks identical to the input file.

[HTML]sed 's/, N\([A-Z]\+\)/, 01\101/; s/, C\([A-Z]\+\)/, 02\101/; s/, L\([A-Z]\+\)/, 03\101/;' '/home/ll/Desktop/Extracts/Result/Prime/in.csv' > out.csv[/HTML]

Not sure what you mean about the placement of quotation marks, I just copied and pasted your command.

Any ideas?

Thanks for helping...

No, sorry, I don't see what might be going wrong. It works for me (TM). But since the sed one-liner isn't exactly what you wanted anyhow, you would probably be better off using one of the other suggestions.

Quote:

Originally Posted by burschik (Post 3231074)

I have no idea with Python, so will need to look into this.

Thanks for your help though.

sed is definitely not a suitable tool to do your stuff.
why didn't you try radoulov's awk solution? Doesn't it work?
If you want to try the Python piece, save the code as as script and on command prompt

Code:

# python script.py

And yet another way:

Code:

$ cat data

ID, Amount, Old code

654, 45.00, NBUP

5432, 20.00, CBUP

42, 65.00, NWAR

442, 66.00, 012322



$ perl -lna -F'/,\s*/' -e 'BEGIN {$" = ", "; %C=(N =>"01",C=>"02",L=>"03")}; $F[3]=$F[0] eq "ID" ? "New code" : $F[2] =~ /^([NCL])(\w+)/ ? "$C{$1}${2}01" : $F[2]; print "@F"' data

ID, Amount, Old code, New code

654, 45.00, NBUP, 01BUP01

5432, 20.00, CBUP, 02BUP01

42, 65.00, NWAR, 01WAR01

442, 66.00, 012322, 012322

ghostdog74 wrote:

Quote:

sed is definitely not a suitable tool to do your stuff.

Would you please be so kind as to explain this pearl of wisdom.

Sed can do just about anything, but is very cumbersome to use for larger tasks. It was superb for its time, and still has plenty of value. But who the heck wants to fight with a basic two register machine!

I think this was the point being made.