Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. It only takes a minute to sign up.
Sign up to join this community
I have two files file1.csv (20 columns 410k rows) and data.csv (4 columns 1800 rows). What I am trying to do is if data.csv 1st column matches file1.csv 2nd column overwrite 1st column in file1.csv with value from data.csv 3rd column. If no match keep existing value...
this command will list matching lines but could not get the replacement part correctly.
awk -F"," 'BEGIN{OFS=","} {if (NR==FNR) {a[$1]=$3; next} if ($2 in a) print}' data.csv file1.csv
> file3.csv
Thanks!
If I understand you correctly, you want:
awk -F"," 'BEGIN{OFS=","}
{
if (NR==FNR) {
a[$1]=$3;
next
}
if ($2 in a){
$1=a[$2]
}
print
}' data.csv file1.csv
You were almost there. You just need to set $1 to be whatever you saved from the first file and then print the line.
print outside of the if ($2 in a){ ... } conditional block I think
Jul 30, 2019 at 18:11
a[$1]=$3 saves the 3rd field of data.csv as the value associated with the index $1 in the array a. Then, when we're reading file1.csv, if its second field is an index in a, then we set its first field to be a[$2]. Finally, moving the print out of the if clause as steeldriver suggested does indeed make it print all lines.
file1.csv, ii) an example offile2.csvand iii) the output you would want from these short examples. That way, we can understand what you need and test our solutions to ensure we give you the output you require.