Loop a list through awk

Question

I have two files: data.csv and list.txt. Here's an example of what they look like

data.csv:

"John","red","4"
"Basketball","orange","2"
"The Mike","blue","94"
"Lizard","purple","3"
"Johnny","pink","32"

list.txt:

Mike
John
purple
32

Now, I am trying to figure out how I can make a loop

awk -F "\"*,\"*" '/**LIST ITEM**/ {print $1}' data.csv > output.txt

where the command runs for each line of list.txt, replacing **LIST ITEM**. How can this be accomplished?

I'm running this though Terminal on MacOSX 10.5.7.

EDIT:

The desired output for the above example would be

The Mike
John
Johnny
Lizard
Johnny

EDIT2:

To be more clear, I am trying to avoid doing this:

awk -F "\"*,\"*" '/Mike/ {print $1}' data.csv
awk -F "\"*,\"*" '/John/ {print $1}' data.csv
awk -F "\"*,\"*" '/purple/ {print $1}' data.csv
awk -F "\"*,\"*" '/32/ {print $1}' data.csv

And instead, run it in one command, somehow looping through all the lines of list.txt.

Answer 1

This meets the order of your desired output:

$ awk -F, '
    NR == FNR {field1[$0] = $1; next}
    {
      for (line in field1) 
        if (line ~ $0) 
          print field1[line]
    }
  ' data.csv list.txt 
"The Mike"
"John"
"Johnny"
"Lizard"
"Johnny"

This reads the data.csv file into memory, mapping the whole line to field1. Then, each line of the list.txt file is checked against each element of the field1 array.

If the data file is much larger than the list file, then it would make more sense to hold the smaller file in memory and loop over the larger file a line at a time:

$ awk -F, '
    NR == FNR {list[$1]; next}
    {
      for (item in list) 
        if ($0 ~ item) 
          print $1
    }
  ' list.txt data.csv 
"John"
"The Mike"
"Lizard"
"Johnny"
"Johnny"

Answer 2

#!/bin/bash

 while read -r line; do 
   awk -F '^"|","|"$' '$0 ~ line{print $2}' line="$line" data.csv
 done < list.txt

Proof of Concept

$ while read -r line; do awk -F '^"|","|"$' '$0 ~ line{print $2}' line="$line" data.csv; done < list.txt
The Mike
John
Johnny
Lizard
Johnny

This field separator deals with embedded quotes and/or commas

Answer 3

I'm not entirely clear on what you're trying to do: replace LIST ITEM with what? Just looking for a match anywhere and outputting the first field? Also, your example list.txt appears to match anywhere in the line, which could potentially be problematic: what if list.txt at some point contains the line e? That would match all but the last line of your sample data.csv.

awk -F '^"?|"?,"?|"$?' 'BEGIN {
                          # read list.txt into an array
                          while (getline pat < "list.txt") {
                            pats[pat] = 1
                          }
                          close("list.txt")
                        }
                        {
                          # skip empty field before leading "
                          if ($1 == "") {
                            res = $2
                          } else {
                            res = $1
                          }
                          # scan record for patterns stored earlier,
                          # output the first real data field (res) if
                          # found
                          for (pat in pats) {
                            if ($0 ~ pat) {
                              print res
                            }
                          }
                        }' data.csv

This is a bit more complex than it could be; your field separator doesn't deal with the optional leading quotation mark on the first field or the optional trailing one on the last field. Mine does, but at the price that if it's there the first field will be empty (the empty string before ^"?). It also doesn't try to deal with embedded quotes. A dedicated CSV parser would be a better idea if you need to support random generalized CSV.