Append data from first column to second column in csv file

Question

I have a csv file with following details :

Userid,First Name,Last Name
jaina24,Aayush,Jain

my requirement is to add a new column in the existing file like this :

Userid,Email Address,First Name,Last Name
jaina24,jaina24@xyz.com,Aayush, Jain

2nd column should copy value from first column and then append @xyz.com to it.

score 3 · Accepted Answer · answered Jul 07 '21 at 13:20

3

You can use something like (if you have exactly 3 fields):

awk -F\, 'FNR==1 {print $1",Email Address,"$2","$3} FNR!=1{print $1","$1"@xyz.com,"$2","$3}' input_file

answered Jul 07 '21 at 13:20

Romeo Ninov

17,484

score 3 · Answer 2 · answered Jul 07 '21 at 13:56

You could use awk

awk 'BEGIN{FS=OFS=","}{$1=$1 OFS (FNR>1 ? $1 "@xyz.com" : "Email address")}1' file

Or sed

sed '1s/[^,]*/&,Email address/;1!s//&,&@xyz.com/' flie

Or bash (but don't use bash for processing text - see Why is using a shell loop to process text considered bad practice?)

sseLtaH · Answer 3 · 2021-07-08T07:45:27.797

1

Input File

Userid,First Name,Last Name
jaina24,Aayush,Jain
Rob738,Rob,Everhard
Scorch661,Samuel,Scorhezi

command

[ ~]# awk -F, 'NR==1, OFS="," {print $1, "Email Address", $2, $3 }''NR>2 { print $1, $1"@xyz.com", $2, $3 }' ex.csv

Output

Userid,Email Address,First Name,Last Name
jaina24,jaina24@xyz.com,Aayush,Jain
Rob738,Rob738@xyz.com,Rob,Everhard
Scorch661,Scorch661@xyz.com,Samuel,Scorhezi

edited Jul 08 '21 at 07:45

answered Jul 07 '21 at 13:18

sseLtaH

2,786

@RomeoNinov Please explain further so I am not providing bad advise and can understand what may be wrong with my code. It is one file being processed so the difference between NR and FNR should be minimal if at all noticeable. – sseLtaH Jul 07 '21 at 13:28
My mistake, i misread the name of variable. Nevertheless your script print only the CSV header – Romeo Ninov Jul 07 '21 at 13:46
What's the rationale behind using the range pattern NR==1, OFS=","? And i t seems NR>2 should be NR>=2, or NR>1 – rowboat Jul 07 '21 at 13:53
@RomeoNinov No problem. I have been looking into your former complaint all this time and discombobulating myself as I simply could not find the difference if the source is a single file.
I am once again slightly confused as to how the code is printing only a header. Could you possible expand your statement? Thanks
– sseLtaH Jul 07 '21 at 13:54
@rowboat Well.... The code basically has two parts. NR==1 Means only line 1 and OFS returns the field seperators to , Without it, it has spaces as the delimiter. As there is no input in line 2 in this example, there is no need for >= What is the difference with announcing Nr>=2, NR>1 or even NR!=1? – sseLtaH Jul 07 '21 at 13:59
@HatLess Ok, it seems the question was updated and the first version had a blank second line, hence NR>2. But I still don't understand why OFS should be set with a range pattern – rowboat Jul 07 '21 at 14:39
@rowboat The OFS is needed in the script to get the needed output as requested.
Copy my input file and run the command without OFS and you will see the results and why it is needed.
– sseLtaH Jul 07 '21 at 14:50
that use of NR==1, OFS="," means print range of line start from thr first line upto when condition OFS="," evaluates to true which that evaluates to true immediately at first line and then actions will be executed, but yes it sets the OFS to a comma too, so that action is only executed when it's the first line. – αғsнιη Jul 07 '21 at 15:02

score 1 · Answer 4 · answered Jul 08 '21 at 06:04

$ perl -F, -lane 'if ($. == 1) {
                    splice @F, 1, 0, "Email Address";
                  } else {
                    splice @F, 1, 0,$F[0] . "\@xyz.com";
                  };
                  print join ",", @F' input.csv
Userid,Email Address,First Name,Last Name
jaina24,jaina24@xyz.com,Aayush,Jain

score 0 · Answer 5 · answered Dec 14 '22 at 21:27

Creating the Email Address field using Miller (mlr):

$ mlr --csv put '$["Email Address"] = $Userid . "@xyz.com"' file
Userid,First Name,Last Name,Email Address
jaina24,Aayush,Jain,jaina24@xyz.com

Also moving the new field to the second column:

$ mlr --csv put '$["Email Address"] = $Userid . "@xyz.com"' then reorder -f Userid,"Email Address" file
Userid,Email Address,First Name,Last Name
jaina24,jaina24@xyz.com,Aayush,Jain

Append data from first column to second column in csv file

5 Answers5