1

I have two files as follows: file1.csv

+------------+----------+--------+---------+
| Account_ID | Asset_ID | LOT_ID | FLAG_F1 |
+------------+----------+--------+---------+
|      10000 |    20000 |  30000 | Y       |
|      10001 |    20001 |  30001 | N       |
|      10002 |    20002 |  30002 | Y       |
|      10003 |    20003 |  30003 | N       |
|      10004 |    20004 |  30004 | Y       |
|      10005 |    20005 |  30005 | N       |
|      10006 |    20006 |  30006 | Y       |
+------------+----------+--------+---------+

file2.csv

   +------------+----------+--------+---------+-----+-----+
| Account_ID | Asset_ID | LOT_ID | FLAG_F2 | XYZ | ABC |
+------------+----------+--------+---------+-----+-----+
|      10000 |    20000 |  30000 | Y       | XYZ | ABC |
|      10001 |    20001 |  30001 | Y       | XYZ | ABC |
|      10002 |    20002 |  30002 | Y       | XYZ | ABC |
|      10003 |    20003 |  30003 | Y       | XYZ | ABC |
|      10004 |    20004 |  30004 | Y       | XYZ | ABC |
|      10005 |    20005 |  30005 | Y       | XYZ | ABC |
|      10006 |    20006 |  30006 | Y       | XYZ | ABC |
|      10006 |    20006 |  30006 | Y       | XYZ | ABC |
|      10006 |    20006 |  30006 | Y       | XYZ | ABC |
+------------+----------+--------+---------+-----+-----+

I am trying to get the following output:

    +------------+----------+--------+---------+-----+-----+---------+
| Account_ID | Asset_ID | LOT_ID | FLAG_F2 | XYZ | ABC | FLAG_F1 |
+------------+----------+--------+---------+-----+-----+---------+
|      10000 |    20000 |  30000 | Y       | XYZ | ABC | Y       |
|      10001 |    20001 |  30001 | Y       | XYZ | ABC | N       |
|      10002 |    20002 |  30002 | Y       | XYZ | ABC | Y       |
|      10003 |    20003 |  30003 | Y       | XYZ | ABC | N       |
|      10004 |    20004 |  30004 | Y       | XYZ | ABC | Y       |
|      10005 |    20005 |  30005 | Y       | XYZ | ABC | N       |
|      10006 |    20006 |  30006 | Y       | XYZ | ABC | Y       |
|      10006 |    20006 |  30006 | Y       | XYZ | ABC | Y       |
|      10007 |    20007 |  30006 | Y       | XYZ | ABC |         |
|      10006 |    20003 |  30006 | Y       | XYZ | ABC |         |
+------------+----------+--------+---------+-----+-----+---------+

In the above output I am adding FLAG_F1 from file1.csv into the file2.csv on the condition of Account_ID,Asset_ID, and LOT_ID values are equal on both file1.csv and file2.csv. If condition fails, it can be blank.

I have tried the following code which is used awk by two .csv files compare using awk

awk -F',' '
    FNR == NR {
        if (FNR == 1) {next}
        a[$1] = $2;
        b[$1] = $3;
        next;
    }
    {
        if (FNR == 1) {print;next}
        if (a[$1] == $2) {
            print $1,$2,$3,b[$1];
        }
        else {
            print $1,a[$1],b[$1],b[$1];
        }
    }
  ' OFS=',' file1.csv file2.csv

It's better if any one explains me the above code line by line.

closed as too broad by Anthon, Networker, John WH Smith, jasonwryan, Zelda Feb 19 '15 at 17:43

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    It is better if you read an awk book first and come back with specific separate questions for things you don't understand. – Anthon Feb 19 '15 at 14:04
2

This is much simpler than the linked question. All you need is:

awk -F, -v OFS=, 'NR==FNR{a[$1$2$3]=$4; next}{print $0,a[$1$2$3]}' file1 file2

Explanation

  • -F, : set the input field separator to a comma.
  • -v OFS=, : set the output field separator to a comma. This is useful to print comma-separated output by default.
  • NR==FNR : NR is the current line number, FNR is the line number of the current file. The two will be identical only while the 1st file is being read.
  • a[$1$2$3]=$4; next : if this is the first file (see above), save the 4th field in an array whose key is the 1st,2nd and 3rd fields concatenated.
  • print $0,a[$1$2$3] : print the current line ($0) and the value in the a array associated with the first three fields. This is the corresponding 4th field of the first file.
2
awk -F',' ' # start awk and use comma as a field separator
    FNR == NR { # if processed so far number of rows in current file if equal to overall processed number of rows do things in block {} 
        if (FNR == 1) {next} # if it is first row then continue (skip to next row)
        a[$1] = $2; # create an array indexed with first field, with value of second field
        b[$1] = $3; # another array
        next; # go to next row
    } # end of block executed only for first file
    { # beginning of block which will be executed without any initial conditions
        if (FNR == 1) {print;next} # if first row of file then print it and go to next one
        if (a[$1] == $2) { # if array value which correspond to field first is equal to second field do something (array 'a' has been set in first file, and now we input index to file from second file knowing that first fields of those files are the same)
            print $1,$2,$3,b[$1]; # print field 1-3 and array b[$1]
        }
        else { # if array is not equal
            print $1,a[$1],b[$1],b[$1]; # print stuff
        }
    }
  ' OFS=',' file1.csv file2.csv # OFS means output field separator, so we want to have comma in result too.
  • Thats probably not very readable, well... – jimmij Feb 19 '15 at 14:12
  • While this does explain the code the OP posted, that code will not solve his question. – terdon Feb 19 '15 at 15:22
  • 1
    @terdon Actually there was no formal question, but OP asked explicitly for explanation: It's better if any one explains me the above code line by line. – jimmij Feb 19 '15 at 15:30
  • I know, which is why I haven't done anything but leave a comment :). It's just that the code is irrelevant to his actual problem. – terdon Feb 19 '15 at 15:33

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