insert value from reference file

Question

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):

daily.csv

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

ref.csv

HVDS_1810,dfTVfsd
HVRS_5230,UtsfeOO
WPHV_4242,BBnnstd

Just some ideas needed on where to begin.

Answer 1

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

Explanation:
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.