All geek questions in one place

How to join 3 files using awk?

Below are three files -

emp.txt
7839|BLAKE|PRESIDENT||17-NOV-81|5000||10
7698|KING|MANAGER|7839|01-MAY-81|2850||10
7782|CLARK|MANAGER|7839|09-JUN-81|2450||10
7566|JONES|MANAGER|7839|02-APR-81|2975||40
7788|SCOTT|ANALYST|7566|19-APR-87|3000||50
7902|FORD|ANALYST|7566|03-DEC-81|3000||20


dept.txt
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON

citycode.txt
1123|NEW YORK
1124|DALLAS
1125|CHICAGO
1126|BOSTON
1127|WASHINGTON

expected output: $ 0 (from emp.txt, where $ 6> = 2850) + $ 2 (from dept.txt) + $ 1 (from citycode.txt)

7839|BLAKE|PRESIDENT||17-NOV-81|5000||10|ACCOUNTING|1123
7698|KING|MANAGER|7839|01-MAY-81|2850||10|ACCOUNTING|1123
7566|JONES|MANAGER|7839|02-APR-81|2975||40|OPERATIONS|1126
7788|SCOTT|ANALYST|7566|19-APR-87|3000||50|NULL|NULL
7902|FORD|ANALYST|7566|03-DEC-81|3000||20|RESEARCH|1124

Explanation - join these files using the shared key emp.txt ($ NF: external connection on emp.txt - record number 50 has no common, so it will print NULL) and dept.txt ($ 1), where emp ($ 6> = 2850) and use the common column dept.txt ($ NF) and citycode.txt ($ 1). merging two files is an easy task using a common key, but how can we combine three files together.

+4
source share
3 answers
awk 'BEGIN{ FS=OFS="|" }
     FNR==1{ f++ }
     f==1{ d[$1]=$2; c[$NF]=$1; next }
     f==2 && $NF in c{ c[c[$NF]]=$1; next }
     $6 >=2850{
       print $0, ($NF in d ? d[$NF] : "NULL" ),( $NF in c ? c[$NF] : "NULL")
   }' dept.txt citycode.txt emp.txt

Explanation

awk '                               # Call awk
      BEGIN{          
              FS=OFS="|"            # Set input and output separator
      }
      FNR==1{                       # FNR will be 1 for each file when awk reads first line
            f++                     # File counter
      }

      # use can also do FILENAME == "dept.txt" here but f==1 is easy

      f==1{                         # for first file dept.txt
            d[$1]=$2;               # set array d where index is $1 and value is $2 of file dept.txt 
            c[$NF]=$1;              # set array c where index is last field and value is first field of file dept.txt
            next                    # stop processing go to next line
      }

      # when f equal 2 that is when we read second file citycode.txt 
      # and last field of current file exists in array c
      # there is no point in reading extra line exists in citycode.txt
      # we just need whichever city exists in dept.txt
      # so $NF in c

      f==2 && $NF in c{             

           # modify array c by empid 
           # that is index will be the value of array c 
           # corresponding to last field of current file and 
           # array c value will be citycode
           # Its because to make it easy to access array c value by empid while
           # reading emp.txt file

            c[c[$NF]]=$1;           

           # gawk user can delete array element here like below
           # if you have gawk uncomment below line
           # delete c[$NF]

            next                   # stop processing go to next line
      }

      $6 >=2850{                   # here we read emp.txt if 6th field is greater than or equal to 2850

            # Print current record/row/line from emp.txt

            # if last field of current file that is 
            # empid exists in array d then print department else NULL

            # if last field of current file that is
            # empid exists in array c then print citycode else NULL

            print $0,($NF in d?d[$NF]:"NULL"),($NF in c?c[$NF]:"NULL")
      }
   ' dept.txt citycode.txt emp.txt

Enter

$ cat emp.txt 
7839|BLAKE|PRESIDENT||17-NOV-81|5000||10
7698|KING|MANAGER|7839|01-MAY-81|2850||10
7782|CLARK|MANAGER|7839|09-JUN-81|2450||10
7566|JONES|MANAGER|7839|02-APR-81|2975||40
7788|SCOTT|ANALYST|7566|19-APR-87|3000||50
7902|FORD|ANALYST|7566|03-DEC-81|3000||20

$ cat dept.txt 
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON

$ cat citycode.txt 
1123|NEW YORK
1124|DALLAS
1125|CHICAGO
1126|BOSTON
1127|WASHINGTON

Output

$ awk 'BEGIN{FS=OFS="|"}FNR==1{f++}f==1{d[$1]=$2;c[$NF]=$1;next}f==2 && $NF in c{c[c[$NF]]=$1;next}$6 >=2850{print $0,($NF in d?d[$NF]:"NULL"),($NF in c?c[$NF]:"NULL")}' dept.txt citycode.txt emp.txt
7839|BLAKE|PRESIDENT||17-NOV-81|5000||10|ACCOUNTING|1123
7698|KING|MANAGER|7839|01-MAY-81|2850||10|ACCOUNTING|1123
7566|JONES|MANAGER|7839|02-APR-81|2975||40|OPERATIONS|1126
7788|SCOTT|ANALYST|7566|19-APR-87|3000||50|NULL|NULL
7902|FORD|ANALYST|7566|03-DEC-81|3000||20|RESEARCH|1124
+3
source

FILENAME , .

:

BEGIN {FS=OFS="|"}
function chknull(s) { return s ? s : "NULL"}
FILENAME == "citycode.txt" {citycode[$2]=$1}
FILENAME == "dept.txt" {depname[$1]=$2; depcity[$1]=$3}
FILENAME == "emp.txt" && $6 >= 2850 {
    print $0, chknull(depname[$NF]), chknull(citycode[depcity[$NF]])
}


$ awk -f j3.awk citycode.txt dept.txt emp.txt 
7839|BLAKE|PRESIDENT||17-NOV-81|5000||10|ACCOUNTING|1123
7698|KING|MANAGER|7839|01-MAY-81|2850||10|ACCOUNTING|1123
7566|JONES|MANAGER|7839|02-APR-81|2975||40|OPERATIONS|1126
7788|SCOTT|ANALYST|7566|19-APR-87|3000||50|NULL|NULL
7902|FORD|ANALYST|7566|03-DEC-81|3000||20|RESEARCH|1124

( , chknull, "NULL" , , , , .)

+4

it is simple in awk:

  • read the 2 files you need to add at the end of the line (dept.txt and citycode.txt)
  • and add them when analyzing the main file (emp.txt)

the code:

awk -F'|'  -v dptfile="dept.txt" -v citycodefile="citycode.txt" -v from="2850" '
   BEGIN { OFS=FS; 
           rem="build 2 arrays, dpt[] associates number with department,";
           rem="and dptcity[] associate same number with department city";
           while ((getline line<dptfile) > 0) {
              split(line,a,OFS);dpt[a[1]]=a[2]; dptcity[a[1]]=a[3]
           }
           close(dptfile)
           rem="build 3rd array, city[cityname] associates a city name to its number";
          while ((getline line<citycodefile)>0) {
              split(line,a,OFS); city[a[2]]=a[1] ; 
           }
           close(citycodefile); 
         }

  ( $6>=from ) { print $0 OFS ($8 in dpt? dpt[$8]:"NULL") OFS (dptcity[$8] in city? city[dptcity[$8]]:"NULL") ;}
  ' emp.txt

Given the data you entered (and borrowing @ akshay-hegde is a good presentation):

Enter

$ cat emp.txt
7839|BLAKE|PRESIDENT||17-NOV-81|5000||10
7698|KING|MANAGER|7839|01-MAY-81|2850||10
7782|CLARK|MANAGER|7839|09-JUN-81|2450||10
7566|JONES|MANAGER|7839|02-APR-81|2975||40
7788|SCOTT|ANALYST|7566|19-APR-87|3000||50
7902|FORD|ANALYST|7566|03-DEC-81|3000||20

$ cat dept.txt 
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON

$ cat citycode.txt
1123|NEW YORK
1124|DALLAS
1125|CHICAGO
1126|BOSTON
1127|WASHINGTON

Output

$ awk -F'|' -v dptfile="dept.txt" -v citycodefile="citycode.txt" -v from="2850" 'BEGIN { OFS=FS; rem="build 2 arrays, dpt[] associates number with department,";rem="and dptcity[] associate same number with department city"; while ((getline line<dptfile) > 0) { split(line,a,OFS);dpt[a[1]]=a[2]; dptcity[a[1]]=a[3];} ; close(dptfile) ; rem="build 3rd array, city[cityname] associates a city name to its number"; while ((getline line<citycodefile)>0) { split(line,a,OFS); city[a[2]]=a[1] ; }; close(citycodefile); } ( $6>=from ) { print $0 OFS ($8 in dpt? dpt[$8]:"NULL") OFS (dptcity[$8] in city? city[dptcity[$8]]:"NULL") ;}' emp.txt
7839|BLAKE|PRESIDENT||17-NOV-81|5000||10|ACCOUNTING|1123
7698|KING|MANAGER|7839|01-MAY-81|2850||10|ACCOUNTING|1123
7566|JONES|MANAGER|7839|02-APR-81|2975||40|OPERATIONS|1126
7788|SCOTT|ANALYST|7566|19-APR-87|3000||50|NULL|NULL
7902|FORD|ANALYST|7566|03-DEC-81|3000||20|RESEARCH|1124
+1
source

Source: https://habr.com/ru/post/1670768/

More articles:


All Articles