Parsing and modifying csv with bash

Question

Have a csv file with tons of rows, small example:

id,location_id,name,title,email,directorate 
1,1, Amy lee,Singer,, 
2,2,brad Pitt,Actor,,Production 
3,5,Steven Spielberg,Producer,spielberg@my.com,Production

Need to:

change first and last name to uppercase, example, Brad Pitt, Amy Lee.
create email with pattern first letter of first name + last name, all in lowercase with @google.com and value from location_id, example - ale1e@google.com, bpitt2@google.com
save it to new file.csv, with the same structure, example:

id,location_id,name,title,email,directorate 
1,1, Amy Lee,Singer,alee1@google.com, 
2,2,Brad Pitt,Actor,bpitt@google.com,Production 
3,5,Steven Spielberg,Producer,sspielberg@google.com,Production

I started from create a array and iterate through it, with bunch of sed, awk, but it gives to me random results. Please give me advice, how resolve this task.

while read -ra array; do
    for i in ${array[@]};
    do
        awk -F ',' '{print tolower(substr($3,1,1))$2$3"@google.com"}'
    done

    for i in ${array[@]};
    do
        awk -F "\"*,\"*" '{print $3}' | sed -e "s/\b\(.\)/\u\1/g"
    done

done < file.csv

awk -F ',' '{print tolower(substr($3,1,1))$2$3"@google.com"}' working not correct.

See https://stackoverflow.com/questions/45420535/whats-the-most-robust-way-to-efficiently-parse-csv-using-awk — Ed Morton, Nov 04 '22 at 15:52

score 3 · Accepted Answer · answered Nov 04 '22 at 09:39

3

Using GNU sed

$ sed -E 's/([^,]*,([^,]*),) ?(([[:alpha:]])[^ ]* +)(([^,]*),[^,]*,)[^,]*/\1\u\3\u\5\L\4\6\2@google.com/' input_file
id,location_id,name,title,email,directorate
1,1,Amy Lee,Singer,alee1@google.com,
2,2,Brad Pitt,Actor,bpitt2@google.com,Production
3,5,Steven Spielberg,Producer,sspielberg5@google.com,Production

answered Nov 04 '22 at 09:39

HatLess

10,622
5
14
32

Thank you. Just 1 line of script to solve this task, without any while and for loops... – Art Nov 04 '22 at 09:46
help me plz again? how script will be modified, if location_id need to add only when emails a equal? example: 1,1,Amy Lee,Singer,alee1@google.com, 2,2,Brad Pitt,Actor,bpitt@google.com,Production 10,30,Andrew Lee,Singer,alee30@google.com – Art Nov 09 '22 at 11:37
1

@lunnyj I am not sure I fully understand the new request. Can you open a new question and add all requirements, what you have tried and the expected output, I will try and help. – HatLess Nov 09 '22 at 11:45
here new question https://stackoverflow.com/questions/74378890/adding-for-loop-to-sed-process?noredirect=1#comment131306910_74378890 – Art Nov 09 '22 at 17:32

score 3 · Answer 2 · answered Nov 04 '22 at 10:00

3

With your shown samples please try following awk.

awk '
BEGIN{ FS=OFS="," }
{
  split($3,arr," ")
  val=(substr($3,1,1) arr[2]"@google.com,")
  $NF=tolower(val) $NF
  val=""
} 
1
' Input_file

answered Nov 04 '22 at 10:00

RavinderSingh13

130,504
14
57
93

Parsing and modifying csv with bash

2 Answers2