Returning specific columns upon matching


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Returning specific columns upon matching
# 1  
Old 09-18-2014
Returning specific columns upon matching

Hi All,

Need help in this requirement.

I have fileA with one column and fileB with 26 columns.

I need to match the value from fileA with fileB, if matches I have to return that value from fileB, and the next value, 5th and 6th values.

NOTE- the matching value's position changes in FileB.

For example,

FileA:

Code:
abc
def

FileB:
Code:
abc|123|xyz|789|jkl|345|sez|435|.....|367 (26 values)

Since abc is a match, output should have
Code:
abc|123|jkl|345

If matched value's position is 'x' in fileB, output file should have
Code:
x|x+1|x+4|x+5

How can I achieve this in Unix? Please help. Thanks in advance.

Last edited by Scrutinizer; 09-18-2014 at 01:30 PM.. Reason: CODE tags
# 2  
Old 09-18-2014
Try:
Code:
awk 'NR==FNR{A[$1]; next} {for(i=1; i<=NF-5; i++) if($i in A) print $i, $(i+1), $(i+4), $(i+5)}' fileA FS=\| OFS=\| fileB

These 2 Users Gave Thanks to Scrutinizer For This Post:
# 3  
Old 09-18-2014
Thanks for the reply..

Unfortunately, this script is not giving results. The output file is empty. Here is the script I use. Please correct me if there is anything wrong.

Code:
dos2unix fileA
dos2unix fileB
awk 'NR==FNR{A[$1]; next} {for(i=1; i<=NF-5; i++) if($i in A) print $i, $(i+1), $(i+4), $(i+5)}' fileA FS=\| OFS=\| fileB > FileC

I need to get the result set to the new file FileC. Also, if the delimiter in fileB is comma instead of pipeline, where should I need to change..?

Thanks in advance.
# 4  
Old 09-18-2014
Quote:
Originally Posted by vamsikrishna928
Thanks for the reply..

Unfortunately, this script is not giving results. The output file is empty. Here is the script I use. Please correct me if there is anything wrong.

Code:
dos2unix fileA
dos2unix fileB
awk 'NR==FNR{A[$1]; next} {for(i=1; i<=NF-5; i++) if($i in A) print $i, $(i+1), $(i+4), $(i+5)}' fileA FS=\| OFS=\| fileB > FileC

I need to get the result set to the new file FileC. Also, if the delimiter in fileB is comma instead of pipeline, where should I need to change..?

Thanks in advance.
Is the text marked in red above your way of saying: "I'm sorry I gave you bad information about my input file format; the field separator is a comma instead of a vertical bar. Can you please help me fix the code you gave me because I made a mistake?"

If that is what you intended to say, you could try something like this instead:
Code:
awk '
{       gsub(/\r/, "")
}
NR==FNR{A[$1]
        next
}
{       for(i=1; i<=NF-5; i++) 
                if($i in A)
                        print $i, $(i+1), $(i+4), $(i+5)
}' fileA FS=',' OFS=',' fileB > FileC

Note that the call to gsub() takes care of the carriage return removal from both of your input files so you no longer need to invoke dos2unix twice.
This User Gave Thanks to Don Cragun For This Post:
# 5  
Old 09-18-2014
Hi Don,

The source file delimiter is changed from pipeline to comma. Thanks a lot for this code, it worked like a magic Smilie Thanks again!
# 6  
Old 09-18-2014
Quote:
Originally Posted by vamsikrishna928
Hi Don,

The source file delimiter is changed from pipeline to comma. Thanks a lot for this code, it worked like a magic Smilie Thanks again!
Thank Scrutinizer. It was his code (with very slight modifications to change to your new field separators and to get rid of the need for the two calls to dos2unix) that solved your problem!
# 7  
Old 09-18-2014
You are correct, thanks to Scrutinizer as well!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Matching first 2 columns..

Hello All, I want to make a file which will have primarily lines of file2 but when first 2 fields matches with the file1 it should have those lines of file1.. example is as below.. file1 a;b;1 c;d f;e t;r;5 file2 b;g a;b c;d v;b f;e t;r (2 Replies)
Discussion started by: ailnilanjan
2 Replies

2. UNIX for Dummies Questions & Answers

Printing lines with specific strings at specific columns

Hi I have a file which is tab-delimited. Now, I'd like to print the lines which have "chr6" string in both first and second columns. Could anybody help? (3 Replies)
Discussion started by: a_bahreini
3 Replies

3. Shell Programming and Scripting

Can't figure out how to find specific characters in specific columns

I am trying to find a specific set of characters in a long file. I only want to find the characters in column 265 for 4 bytes. Is there a search for that? I tried cut but couldn't get it to work. Ex. I want to find '9999' in column 265 for 4 bytes. If it is in there, I want it to print... (12 Replies)
Discussion started by: Drenhead
12 Replies

4. Shell Programming and Scripting

Print columns matching to specific values

Hello Friends, I have a CDR file and i need to print out 2 columns with their field position which matches to some constant values, a part of input file CZ=1|CZA=1|DIAL=415483420001|EE=13|ESF=1|ET=|FF=0|9|MNC=99|MNP=9041|MTC=0|NID=2|NOA=international|ON=1| OutPut ... (3 Replies)
Discussion started by: EAGL€
3 Replies

5. Shell Programming and Scripting

Common records after matching on different columns

Hi, I have the following files. cat 1.txt cat 2.txt output.txt The logic is as follows.... (10 Replies)
Discussion started by: jacobs.smith
10 Replies

6. UNIX for Dummies Questions & Answers

matching columns

Hello experts, I have this problem, I need to match values based on two files, this is what I have: file1 1.1 1.2 1.3 5.5 1.4 1.5 1.6 file2 1 a 2 B 3 C 4 D 5 z (7 Replies)
Discussion started by: Gery
7 Replies

7. UNIX for Dummies Questions & Answers

Matching corresponding columns in two different files

Hi to all, I have two separated files: FILE1 "V1" "V2" "V3" Mary James Nicole Robert Francisco Sophie Nancy Antony Matt Josephine Louise Rose Mark Simon Charles FILE2 "V1" "V2" "V3"... (2 Replies)
Discussion started by: eleonoral
2 Replies

8. Shell Programming and Scripting

matching columns from two files

Hey, I have two files that have exactly the same format. They are both tab-delimited and contain 12 columns. However the # of rows vary. What I want to do is match columns # 5,6 and 7 between the two files. If they do match exactly (based on numbers) then I want the whole row from file 2 to... (1 Reply)
Discussion started by: phil_heath
1 Replies

9. UNIX for Dummies Questions & Answers

Searching partial columns and returning maximum as output

Hello, I am just getting starting with awk and wondering if anyone could help with the following problem. I have a large file of data, 50,000 rows x 6 columns. I would like to search in blocks of 500 rows for a maximum value in a specific column and compile an output file that prints the... (0 Replies)
Discussion started by: xb_analysis
0 Replies

10. Shell Programming and Scripting

Returning filename and matching lines

What I'm trying to do is to search through a list of files, and output the filename, followed by the lines that matched the pattern. I'm matching the string "letters.moreletters" in any one of searched files, and the output I'm trying to get is: program_1.txt 10 dsdsd sdsd dsd... (2 Replies)
Discussion started by: smb_uk
2 Replies
Login or Register to Ask a Question