0

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!

1
  • Please edit your question and add i) an example (a few lines) of your file1.csv, ii) an example of file2.csv and 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.
    – terdon
    Jul 30, 2019 at 15:51

1 Answer 1

1

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.

6
  • Thansk Terdon! Definitely a move in the right direction, however with your script I only keep the matching line. I would like to keep all 410k rows but only modify the ones flagged by the script the rest leave unchanged. The way I see it only print if the condition is true what I would need and else print $0, but I do not manage to add it properly.
    – peti27
    Jul 30, 2019 at 18:09
  • @peti27 you just need to move the print outside of the if ($2 in a){ ... } conditional block I think Jul 30, 2019 at 18:11
  • That did it! THANKS!
    – peti27
    Jul 30, 2019 at 18:42
  • Just to clarify the array part ...when I am reading the first file (NR==FNR) a[$1] will be the index and $3 of first file will be loaded into a[$2] which is used in the second part of the script. Is that correct?
    – peti27
    Jul 30, 2019 at 18:48
  • @peti27 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.
    – terdon
    Jul 30, 2019 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.