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.

Improve this question
1
  • 1
    It is better if you read an awk book first and come back with specific separate questions for things you don't understand. Commented Feb 19, 2015 at 14:04

2 Answers 2

Reset to default
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.
Improve this answer
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.
Improve this answer
4
  • Thats probably not very readable, well... Commented Feb 19, 2015 at 14:12
  • While this does explain the code the OP posted, that code will not solve his question. Commented Feb 19, 2015 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. Commented Feb 19, 2015 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. Commented Feb 19, 2015 at 15:33

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.