Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I'm sorry if this is extremely elementary, but I just can't figure out how to do this, and my research has failed me as well.

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.

share|improve this question
    
The command should run replacing **LIST ITEM** with 'Mike', then 'John', then 'purple', then '32'. –  Julien Mar 9 '11 at 5:41
    
It would be quite helpful if your sample input included LIST ITEM somewhere (assuming it's literal) as well as providing desired output –  SiegeX Mar 9 '11 at 5:41
    
@Julien When you say **LIST ITEM**, it appears you mean the first field of your CSV, yes? Also, I believe your desired output is wrong, there is an extra Johnny line, yes? –  SiegeX Mar 9 '11 at 5:58
    
When I say **LIST ITEM** I mean a line from list.txt. Hence an item from the list, or list item. –  Julien Mar 9 '11 at 6:03
    
awk -F "\"*,\"*" '/32/ {print $1}' data.csv would yield Johnny, unless I am mistaken. –  Julien Mar 9 '11 at 6:04
show 2 more comments

3 Answers

up vote 2 down vote accepted

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"
share|improve this answer
    
What if both files are very large? Is it just whichever is bigger? –  Julien Mar 9 '11 at 6:34
    
This is great, now what if I only wanted it to match the beginning of the lines in data.csv? –  Julien Mar 9 '11 at 6:52
    
@Julien, this might work for you too: grep -f list.txt data.csv | cut -d, -f1, but you only get "Johnny" once. It's a more lightweight pipeline but I can't tell if it would meet your requirements. –  glenn jackman Mar 9 '11 at 17:35
add comment

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.

share|improve this answer
    
I see why, now, after reading glenn jackman's answer –  Julien Mar 9 '11 at 6:35
    
@Julien: Your proposed edit should have been a comment. Post it as a comment here if you still have a question about geekosaur's answer. –  Gilles Mar 9 '11 at 20:42
add comment
#!/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

share|improve this answer
    
It's not printing the second Johnny on my system... I'm not sure why. –  Julien Mar 9 '11 at 6:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.