CODEWITHSUNDEEP
awk
didjit
didjit

Reputation: 9

AWK to resolve CSV values

I have two CSV files. One is a dictionary, and the other is a file i want to modify. The dictionary has 3 columns:

DEPT_CODE,DEPT,DEPT_NAME
500,Billing,Billing Department
300,Accounting,Accounting Department
...

The input file has 7 columns:

TF,DOC,SECTION,XOFFSET,HEADING,YOFFSET,DEPT_CODE
TRUE,JOURNAL,Header Section,12345,Heading 1,55152,500
FALSE,BINDER,Body Section,23456,Heading 2,66425,300
...

I want to use AWK to substitute the 7th column of the input file with the 2nd column of the dictionary file when Column 7 of the input file and Column 1 of the dictionary file match.

For example:

TRUE,JOURNAL,Header Section,12345,Heading 1,55152,Billing
FALSE,BINDER,Body Section,23456,Heading 2,66425,Accounting
...

I'm using the following and not getting any results. What am I doing wrong?

awk -F',|, ' 'NR==FNR{a[$1]=$2} NR>FNR{$7=a[$7];print}' OFS=',' "$dictionary.csv" "$input.csv"

Upvotes: 0

Views: 54

Answers (2)

dawg
dawg

Reputation: 104062

Phew!

Given your example data with a header:

$ cat /tmp/f1.txt
DEPT_CODE,DEPT,DEPT_NAME
500,Billing,Billing Department
300,Accounting,Accounting Department
$ cat /tmp/f2.txt
TF,DOC,SECTION,XOFFSET,HEADING,YOFFSET,DEPT_CODE
TRUE,JOURNAL,Header Section,12345,Heading 1,55152,500
FALSE,BINDER,Body Section,23456,Heading 2,66425,300

You can do:

$ awk -F, 'BEGIN{OFS=FS} FNR<2 {next} FNR==NR{a[$1]=$2; next} {$7=a[$7]; print}'  /tmp/f1.txt /tmp/f2.txt
TRUE,JOURNAL,Header Section,12345,Heading 1,55152,Billing
FALSE,BINDER,Body Section,23456,Heading 2,66425,Accounting

Upvotes: 0

karakfa
karakfa

Reputation: 67537

awk to the rescue!

$ awk -F, -v OFS=, 'NR==FNR{a[$1]=$2;next} $7=a[$7]' dict file

Column1,Column2,Column3,Column4,Column5,Column6,Billing
Column1,Column2,Column3,Column4,Column5,Column6,Accounting

Upvotes: 1

Related Questions