Get values from different columns from file2 when match values of file1


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Get values from different columns from file2 when match values of file1
# 8  
Old 09-27-2011
Quote:
Originally Posted by cgkmal
Yes, yes Franklin, This version works.

The last question. How to delete in file2 the lines that don't return any match between col 1 of both files, I mean, that don't are file1?

It could be added some more code below the same awk script?

Many thanks really.

Regards.
Try this:
Code:
awk -F, '
NR==1{next} 
NR==FNR{a[$1]; next} 
{idx=substr($1,1,4) substr($1,6,1)} 
idx in a' file1 file2

# 9  
Old 09-27-2011
I'll try it Franklin.

Really thanks for help and time.

Best regards
# 10  
Old 09-27-2011
Quote:
Originally Posted by cgkmal
I'll try it Franklin.

Really thanks for help and time.

Best regards
You're welcome as always cgkmal.
# 11  
Old 09-27-2011
I tested it and works, but only when the values in column A are of the form "CodeN", I don't get results if the values don't have the first 4 words in common.

e.g The values in column 1 of both files could be like this:

SVFDS-1, PPRTY, HHLJ-933.

Like serial numbers.

How can I modify the Franklin's script in order to match any string?

Thanks in advance.
# 12  
Old 09-28-2011
Quote:
Originally Posted by cgkmal
I tested it and works, but only when the values in column A are of the form "CodeN", I don't get results if the values don't have the first 4 words in common.

e.g The values in column 1 of both files could be like this:

SVFDS-1, PPRTY, HHLJ-933.

Like serial numbers.

How can I modify the Franklin's script in order to match any string?

Thanks in advance.
Difficult if some of the keys are equal and some not...
# 13  
Old 09-28-2011
Hi Franklin,

I only changed the assignation of idx as below and it works for sample file.
Code:
awk -F, ' 
NR==FNR{idx=$1; a[idx]=$5;b[idx]=$2;c[idx]=$4;d[idx]=$3; next} FNR==1{print; next} 
a[$1]{$2=a[$1]; $3=b[$1];$4=c[$1];$9=d[$1]; print} 
' OFS=, file2 file1

I'm confused because with the real files (file1 40KB and file2 with 50MB) is not working, I'm not sure if it is something with encoding within files, because the samples I've created in linux, but the real csv files I don't know if were edited in Excel previously.

can I do something to force the encoding or endlines of real files that could affect that the script doesn't work as expected as it works for sample files?

Thanks again.
# 14  
Old 09-28-2011
If the files are from a windows environment you can try to remove the CR's first with:
Code:
tr -d '\r' < dosfile > unixfile

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to match file1 and extract specific tag values

File2 is tab-delimeted and I am trying to use $2 in file1 (space delimeted) as a search term in file2. If it is found then the AF= in and the FDP= values from file2 are extracted and printed next to the file1 line. I commented the awk before I added the lines in bold the current output resulted. I... (7 Replies)
Discussion started by: cmccabe
7 Replies

2. Shell Programming and Scripting

awk to look up values in File 2 from File 1, & printingNth field of File1 based value of File2 $2

I have two files which are the output of a multiple choice vocab test (60 separate questions) from 104 people (there are some missing responses) and the question list. I have the item list in one file (File1) Item,Stimulus,Choice1,Choice2,Choice3,Choice4,Correct... (5 Replies)
Discussion started by: samonl
5 Replies

3. Shell Programming and Scripting

Mapping the values of ids of two columns of file1 from file2

I have of two space separated files: ==> File1 <== PT|np_496075.1 st|K92748.1 st|K89648.1 PT|np_001300561.1 PT|np_497284.1 st|K90752.1 st|K90279.1 PT|np_740775.1 PT|np_497749.1 st|K90752.1 st|K92038.1 PT|np_490856.1 PT|np_497284.1 st|K90752.1 st|K88095.1 PT|np_494764.1 ==> File 2 <==... (2 Replies)
Discussion started by: sammy777888
2 Replies

4. Shell Programming and Scripting

awk to search field2 in file2 using range of fields file1 and using match to another field in file1

I am trying to use awk to find all the $2 values in file2 which is ~30MB and tab-delimited, that are between $2 and $3 in file1 which is ~2GB and tab-delimited. I have just found out that I need to use $1 and $2 and $3 from file1 and $1 and $2of file2 must match $1 of file1 and be in the range... (6 Replies)
Discussion started by: cmccabe
6 Replies

5. Shell Programming and Scripting

Compare values in two files. For matching rows print corresponding values from File 1 in File2.

- I have two files (File 1 and File 2) and the contents of the files are mentioned below. - I am trying to compare the values of Column1 of File1 with Column1 of File2. If a match is found, print the corresponding value from Column2 of File1 in Column5 of File2. - I tried to modify and use... (10 Replies)
Discussion started by: Santoshbn
10 Replies

6. Shell Programming and Scripting

Match one column of file1 with that of file2

Hi, I have file1 like this aaa ggg ddd vvv eeeand file2 aaa 2 aaa 443 xxx 76 aaa 34 ggg 33 wee 99 ggg 33 ddd 1 ddd 10 ddd 98 sds 23 (4 Replies)
Discussion started by: polsum
4 Replies

7. Shell Programming and Scripting

Remove lines in file1 with values from file2

Hello, I have two data files: file1 12345 aa bbb cccc 98765 qq www uuuu 76543 pp rrr bbbbb 34567 nn ccc sssss 87654 qq ppp rrrrr file2 98765 34567 I need to remove the lines from file1 if the first field contains a value that appears in file2: output 12345 aa bbb cccc 76543 pp... (2 Replies)
Discussion started by: palex
2 Replies

8. Shell Programming and Scripting

AWK: read values from file1; search for values in file2

I have read another post about this issue and am wondering how to adapt it to my own, much simpler, issue. I have a file of user IDs like so: 333333 321321 546465 ...etc I need to take each number and use it to print records wherein the 5th field matches the user ID pulled from the... (2 Replies)
Discussion started by: Bubnoff
2 Replies

9. Shell Programming and Scripting

Search values between ranges in File1 within File2

Hi people, I have 2 files, one with a list of non consecutive ranges (File1.txt), where each range begins with the value in column 1 and finishes with the value in column 2 in the same line, as can be seen above. 215312581156279 215312581166279 215312582342558 215312582357758... (4 Replies)
Discussion started by: cgkmal
4 Replies

10. Shell Programming and Scripting

match value from file1 in file2

Hi, i've two files (file1, file2) i want to take value (in column1) and search in file2 if the they match print the value from file2. this is what i have so far. awk 'FILENAME=="file1"{ arr=$1 } FILENAME=="file2" {print $0} ' file1 file2 (2 Replies)
Discussion started by: myguess21
2 Replies
Login or Register to Ask a Question