Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Get values from different columns from file2 when match values of file1
# 1  
Old 09-27-2011
Get values from different columns from file2 when match values of file1
Hi everyone,

I have file1 and file2 comma separated both.

file1 is:
Code:
Header1,Header2,Header3,Header4,Header5,Header6,Header7,Header8,Header9,Header10
Code7,,,,,,,,,
Code5,,,,,,,,,
Code3,,,,,,,,,
Code9,,,,,,,,,
Code2,,,,,,,,,

file2 is:
Code:
CodeA1,Data1,4,Text1,Guide1,FRT,Name1,Prod1,Cat1,TYYF-981,105
CodeA2,Data2,24,Text2,Guide2,CVDA,Name2,Prod2,Cat2,RT-223,23
CodeA3,Data3,13,Text3,Guide3,FRT,Name3,Prod3,Cat3,HHJUI-12,42
CodeA4,Data4,6,Text4,Guide4,CVDA,Name4,Prod4,Cat4,TYYF-980,98
CodeA5,Data5,2,Text5,Guide5,FRT,Name5,Prod5,Cat5,RT-222,22
CodeA6,Data6,3,Text6,Guide6,CVDA,Name6,Prod6,Cat6,HHJUI-11,11
CodeA7,Data7,14,Text7,Guide7,FRT,Name7,Prod7,Cat7,TYYF-979,43
CodeA8,Data8,7,Text8,Guide8,CVDA,Name8,Prod8,Cat8,RT-221,65
CodeA9,Data9,3,Text9,Guide9,FRT,Name9,Prod9,Cat9,HHJUI-10,43
CodeA10,Data10,4,Text10,Guide10,CVDA,Name10,Prod10,Cat10,TYYF-978,99

I want to:
For every value in colum 1 of file1 that is in column 1 of file2 copy into corresponding line of file1 the values that are in some of the columns in file2 in the matching line in this way.

Copy into file1-Colum Value from file2 (for each matching line)
In col2 of file1- copy value from col5 of file2
In col3 of file1 - copy value from col2 of file2
In col4 of file1 - copy value from col4 of file2
In col9 of file1 - copy value from col3 of file2

so the output should be like this:
Code:
Header1,Header2,Header3,Header4,Header5,Header6,Header7,Header8,Header9,Header10
Code7,Guide7,Data7,Text7,,,,,14,
Code5,Guide5,Data5,Text5,,,,,22,
Code3,Guide3,Data3,Text3,,,,,42,
Code9,Guide9,Data9,Text9,,,,,43,
Code2,Guide2,Data2,Text2,,,,,23,

Maybe some body coul help me with this.

Many thanks in advance.

Regards
# 2  
Old 09-27-2011
Something like this?
Code:
awk -F, '
NR==FNR{idx=substr($1,1,4) substr($1,6,1); a[idx]=$5 FS $2 FS $4 ",,,,," $3 FS; next} 
FNR==1{print; next}
a[$1]{print $1 FS a[$1]}
' file2 file1

# 3  
Old 09-27-2011
It's closed, the only issue is that if the file1 contain values not only in column1, in the output the values are being deleted.

example:
if file1 contain this:
Code:
Header1,Header2,Header3,Header4,Header5,Header6,Header7,Header8,Header9,Header10
Code7,,,,,455,,Test7,,
Code5,,,,,,,,,
Code3,,,,,FGT1,,,,
Code9,,,,,,,,,
Code2,,,,,lyo,,,,

the output should be as follow:
Code:
Header1,Header2,Header3,Header4,Header5,Header6,Header7,Header8,Header9,Header10
Code7,Guide7,Data7,Text7,,455,,Test7,14,
Code5,Guide5,Data5,Text5,,,,,2,
Code3,Guide3,Data3,Text3,,FGT1,,,13,
Code9,Guide9,Data9,Text9,,,,,3,
Code2,Guide2,Data2,Text2,,lyo,,,24,

and currently the script gives this output:
Code:
Header1,Header2,Header3,Header4,Header5,Header6,Header7,Header8,Header9,Header10
Code7,Guide7,Data7,Text7,,,,,14,
Code5,Guide5,Data5,Text5,,,,,2,
Code3,Guide3,Data3,Text3,,,,,13,
Code9,Guide9,Data9,Text9,,,,,3,
Code2,Guide2,Data2,Text2,,,,,24,

Many thanks for help so far.

The last column is returning different values, I get this:
# 4  
Old 09-27-2011
@ cgkaml

question is not clear for me. please map the answer correctly
# 5  
Old 09-27-2011
Hi polineni, thanks for answer.

I mean,

file1 is:
Code:
Header1,Header2,Header3,Header4,Header5,Header6,Header7,Header8,Header9,Header10
Code7,,,,,455,,Test7,,
Code5,,,,,,,,,
Code3,,,,,FGT1,,,,
Code9,,,,,,,,,
Code2,,,,,lyo,,,,

file2 is:
Code:
CodeA1,Data1,4,Text1,Guide1,FRT,Name1,Prod1,Cat1,TYYF-981,105
CodeA2,Data2,24,Text2,Guide2,CVDA,Name2,Prod2,Cat2,RT-223,23
CodeA3,Data3,13,Text3,Guide3,FRT,Name3,Prod3,Cat3,HHJUI-12,42
CodeA4,Data4,6,Text4,Guide4,CVDA,Name4,Prod4,Cat4,TYYF-980,98
CodeA5,Data5,2,Text5,Guide5,FRT,Name5,Prod5,Cat5,RT-222,22
CodeA6,Data6,3,Text6,Guide6,CVDA,Name6,Prod6,Cat6,HHJUI-11,11
CodeA7,Data7,14,Text7,Guide7,FRT,Name7,Prod7,Cat7,TYYF-979,43
CodeA8,Data8,7,Text8,Guide8,CVDA,Name8,Prod8,Cat8,RT-221,65
CodeA9,Data9,3,Text9,Guide9,FRT,Name9,Prod9,Cat9,HHJUI-10,43
CodeA10,Data10,4,Text10,Guide10,CVDA,Name10,Prod10,Cat10,TYYF-978,99

The output should be:
Code:
Header1,Header2,Header3,Header4,Header5,Header6,Header7,Header8,Header9,Header10
Code7,Guide7,Data7,Text7,,455,,Test7,14,
Code5,Guide5,Data5,Text5,,,,,2,
Code3,Guide3,Data3,Text3,,FGT1,,,13,
Code9,Guide9,Data9,Text9,,,,,3,
Code2,Guide2,Data2,Text2,,lyo,,,24,

I mean, the values in file1 shouldn't be deleted when matched values from file2 are copied.

If you test the current script, it copies in correct columns from file2 into file1 for each matched value in column1 of both files, but deletes any original value that
is present from column2 to column10 in file1 before to run the script.

Hope be clear enough.

Thanks for any help.

Regards
# 6  
Old 09-27-2011
Try this one:
Code:
awk -F, '
NR==FNR{idx=substr($1,1,4) substr($1,6,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

# 7  
Old 09-27-2011
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.
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