UNIX for Dummies Questions & Answers

I have a csv file with 3 columns. Column 1 is a date "mm/dd/yyyy", column 2 is a dollar amount (e.g. "100.00") & column 3 in a description of where the transaction took place (e.g. "CHECK CRD PURCHASE 10/07 ACME INC USA")

so...
"10/01/2009","100.00", "CHECK CRD PURCHASE 10/07 ACME INC USA"

I would like to output a file like such:
20091001,100.00,CHECK CRD PURCHASE 10/07 ACME INC USA,ACME INC

Where ACME INC is derived from a "lookup" file, something like (s/.*ACME INC.*/ACME INC/).

So my questions:
1)What's the best method for maintaining the lookup file, how can I handle a vendor who appears in the input file and not in the lookup file? i.e. flag them as "undefined"
2)How do I append the 4th column with the lookup result
3)how do modify the date
4)how do I persist the FS"," such that the output is still .csv

Thanks in advance!

Try this:

Code:

awk -F, -v var="AMCE INC" '
{split($1,a,"/");$1=a[3] a[2] a[1]}
$0 ~ var {$(NF=1)=var}1
' OFS="," file

If you have "ACME INC" in a variable eg myvar then the first part should be -v var=$myvar