I have a daily csv file with thousands of rows.
I have been asked to concatenate two columns, then insert a new column when a value in a given column matches a table from another file. Empty values are to be expected.

simplifying the data (column 3 is concatenated):


HVDS,1810,HVDS_1810,"match value",xxxxx
HVRS,5230,HVRS_5230,"match value",xxxxx
WPHV,0,WPHV_0000,"no match",xxxxx

reference file has two columns, searching $1 and inserting $2 above



Just some ideas needed on where to begin.

  • 1
    awk: read the ref file and store in an associative array, then read the daily file: if $3 is an array key, append the value to the line and print. Plenty of examples on this site and on stackoverflow.com – glenn jackman Apr 16 '15 at 23:18

If I understand correctly your question "no match" should be replaced with values from ref.csv. A possible solution is with awk:

awk -F',' 'BEGIN { OFS = "," } FNR==NR { m[$1] = $2; next; } { if ($3 in m) { $4 = "\"" m[$3] "\""; } else { $4 = "\"" "\""; } print; }' ref.csv daily.csv

FNR==NR is true while the first file is read.
We create array m with index first column and value third column.
Then in second block we read second file (because of directive next in first block), check if column 3 is index of array m and if yes set new column value. If not we set empty value.

  • Taliezin, "no match" should eq "". (blank is good) – Mickey Apr 17 '15 at 12:06
  • @Mickey try new one. – taliezin Apr 17 '15 at 12:16
  • This worked on my OSX with minor tweaks. I will check out on my Ubuntu box later. Thanks a bunch. – Mickey Apr 17 '15 at 13:16

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.