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,[email protected],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 - [email protected], [email protected]
save it to new file.csv, with the same structure, example:

id,location_id,name,title,email,directorate 
1,1, Amy Lee,Singer,[email protected], 
2,2,Brad Pitt,Actor,[email protected],Production 
3,5,Steven Spielberg,Producer,[email protected],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.

Please share what you have tried so far.

sseLtaH
– sseLtaH

2022-11-04 08:30:13 +00:00
Commented Nov 4, 2022 at 8:30 — sseLtaH
– sseLtaH, Commented Nov 4, 2022 at 8:30
@HatLess I'm sorry I didn't do this earlier.

Art
– Art

2022-11-04 09:38:24 +00:00
Commented Nov 4, 2022 at 9:38 — Art
– Art, Commented Nov 4, 2022 at 9:38
See stackoverflow.com/questions/45420535/…

Ed Morton
– Ed Morton

2022-11-04 15:52:34 +00:00
Commented Nov 4, 2022 at 15:52 — Ed Morton
– Ed Morton, Commented Nov 4, 2022 at 15:52

sseLtaH · Accepted Answer · 2022-11-04 09:39:14Z

3

Using GNU sed

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

answered Nov 4, 2022 at 9:39

sseLtaH

11.3k5 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Art Over a year ago

Thank you. Just 1 line of script to solve this task, without any while and for loops...

Art Over a year ago

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,[email protected], 2,2,Brad Pitt,Actor,[email protected],Production 10,30,Andrew Lee,Singer,[email protected]

sseLtaH Over a year ago

@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.

Art Over a year ago

here new question stackoverflow.com/questions/74378890/…

RavinderSingh13 · Accepted Answer · 2022-11-04 10:00:42Z

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 4, 2022 at 10:00

RavinderSingh13

135k14 gold badges61 silver badges100 bronze badges

Collectives™ on Stack Overflow

Parsing and modifying csv with bash

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related