File 1 Column 1 value search in File 2 records, then loop?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting File 1 Column 1 value search in File 2 records, then loop?
# 1  
Old 07-19-2011
File 1 Column 1 value search in File 2 records, then loop?

Summary:

I planned on using Awk to grab a value from File 1 and search all records/fields in file 2. If there is a match in File 2, print the first column value of the record of the match of File2. Continue this search until the end of file 2. Once at the end of file 2, grab the next value in File 1 and repeat the procedure. Continue the procedure until the end of File 1.


File 1: This file contains a list of single values in column 1.
File 2: Contains records with a unknown number of fields per record. The file has a unknown number of records. It is a CSV.

File 1 example
Code:
apple
banana
pear
walnut

File 2 example
Quote:
Joe,apple,bird,x,y,z,pear
Brad,banana,pear,walnut,123,456
Phil,walnut,fork,knife
Expected output
Quote:
apple
Joe

banana
Brad

pear
Joe
Brad

walnut
Brad
Phil
I have been trying some different things, but my awk scripting is not the best.

Thank you.
# 2  
Old 07-19-2011
That'd read file2 a lot. If you're OK with storing both into arrays, this is what I came up with...

Code:
#!/usr/bin/awk -f
BEGIN { FS="," }
# put search terms (file1) into array
FNR == NR {a[$1];next}
# do this for all file2 entries
1 {
        for (i = 1; i <= NF; i++)
                if ($i in a)    # we had this term in file1
                        a[$i] = (length(a[$i]) ? (a[$i] SUBSEP) : "") $1
}

END {
        for (i in a)
        {
                print i;
                split(a[i], matches, SUBSEP)
                for (j in matches)
                        print matches[j]
                print ""
        }
}

# 3  
Old 07-19-2011
A little simpler:

Code:
awk '
NR==FNR{a[$1]=$1;out[$1]=$1;next}
{ for(i=1;i<=NF;i++)
      if($i in a){ 
        out[a[$i]]=out[a[$i]]"\n" $1
      }
}
END{
  for(i in out){
    print out[i]"\n"}
}' file1 FS="," file2

if you don't mind the order they come out.
# 4  
Old 07-19-2011
To keep the same order:
Code:
awk '
NR==FNR{a[$1]=++p;out[p]=$1;next}
{ for(i=1;i<=NF;i++)
      if($i in a){ 
        out[a[$i]]=out[a[$i]]"\n" $1
      }
}
END{
  for(i=1;i<=p;i++) {
    print out[i]"\n"}
}' file1 FS="," file2

This User Gave Thanks to Chubler_XL For This Post:
# 5  
Old 07-21-2011
Thanks guys! I'm getting good results.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Run a loop that will search for a file to thousand machine and know who owns the file

Run a loop that will search for a file to thousand machine and know who owns the file $ for i in abc{01..02} > do > echo -n $i > ssh $i "sudo find / -name .ssh -exec ls -l {} \;|grep id" > done abc01-rw-------. 1 root root 1675 Nov 10 2018 id_rsa abc01-rw-------. 1 root root 1675 Nov 14... (6 Replies)
Discussion started by: invinzin21
6 Replies

2. UNIX for Beginners Questions & Answers

Filtering records of a csv file based on a value of a column

Hi, I tried filtering the records in a csv file using "awk" command listed below. awk -F"~" '$4 ~ /Active/{print }' inputfile > outputfile The output always has all the entries. The same command worked for different users from one of the forum links. content of file I was... (3 Replies)
Discussion started by: sunilmudikonda
3 Replies

3. Shell Programming and Scripting

Nawk script to compare records of a file based on a particular column.

Hi Gurus, I am struggling with nawk command where i am processing a file based on columns. Here is the sample data file. UM113570248|24-AUG-11|4|man1|RR211 Alert: Master Process failure |24-AUG-11 UM113570624|24-AUG-11|4|man1| Alert: Pattern 'E_DCLeDAOException' found |24-AUG-11... (7 Replies)
Discussion started by: usha rao
7 Replies

4. Shell Programming and Scripting

Removing duplicate records in a file based on single column

Hi, I want to remove duplicate records including the first line based on column1. For example inputfile(filer.txt): ------------- 1,3000,5000 1,4000,6000 2,4000,600 2,5000,700 3,60000,4000 4,7000,7777 5,999,8888 expected output: ---------------- 3,60000,4000 4,7000,7777... (5 Replies)
Discussion started by: G.K.K
5 Replies

5. Shell Programming and Scripting

parallel while loop based on the file records

Hi, I need to execute parallel with while loop. Input File(source_file.csv) contains filenames the below source_file.csv file contains Customer1.txt Product1.txt Sales.txt Emp.txt Dept.txt Based on the number of rows that file I want to run the script ‘n' times. while... (2 Replies)
Discussion started by: onesuri
2 Replies

6. Shell Programming and Scripting

Find Duplicate records in first Column in File

Hi, Need to find a duplicate records on the first column, ANU4501710430989 0000000W20389390 ANU4501710430989 0000000W67065483 ANU4501130050520 0000000W80838713 ANU4501210170685 0000000W69246611... (3 Replies)
Discussion started by: Murugesh
3 Replies

7. UNIX for Dummies Questions & Answers

Extract records by column value - file non-delimited

the data in my file is has no delimiters. it looks like this: H52082320024740010PH333200612290000930 0.0020080131 D5208232002474000120070306200703060580T1502 TT 1.00 H52082320029180003PH333200702150001 30 100.0020080205 D5208232002918000120070726200707260580T1502 ... (3 Replies)
Discussion started by: jclanc8
3 Replies

8. Shell Programming and Scripting

Push records to array during implicit loop and write to file

NEWBIE ALERT! Hi, I'm 1 month into learning Perl and done reading "Minimal Perl" by Tim Maher (which I enjoyed enoumously). I'm not a programmer by profession but want to use Perl to automate various tasks at my job. I have a problem (obviously) and are looking for your much appreciated help.... (0 Replies)
Discussion started by: jospan
0 Replies

9. UNIX for Dummies Questions & Answers

Filtering records of a file based on a value of a column

Hi all, I would like to extract records of a file based on a condition. The file contains 47 fields, and I would like to extract only those records that match a certain value in one of the columns, e.g. COL1 COL2 COL3 ............... COL47 1 XX 45 ... (4 Replies)
Discussion started by: risk_sly
4 Replies

10. UNIX for Dummies Questions & Answers

Select records based on search criteria on first column

Hi All, I need to select only those records having a non zero record in the first column of a comma delimited file. Suppose my input file is having data like: "0","01/08/2005 07:11:15",1,1,"Created",,"01/08/2005" "0","01/08/2005 07:12:40",1,1,"Created",,"01/08/2005"... (2 Replies)
Discussion started by: shashi_kiran_v
2 Replies
Login or Register to Ask a Question