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
# 15  
Old 09-28-2011
Thanks Franklin, I'll try your suggestion.

Many thanks again.

Best regards

---------- Post updated at 05:00 PM ---------- Previous update was at 03:00 AM ----------

Hi again Fraklin,

It looks it works, but one issue I didn't notice.

In real file some fields appear incomplete because in file2, sometimes there are commas within some fields but they are surounded by double quotes to know that is the same field as below in red.
Code:
CodeA1,Data1,4,"Text1,AKS,DDDD",Guide1,"FRT, YYSFF, TERE",Name1,Prod1,Cat1,TYYF-981,105
CodeA2,Data2,24,Text2,Guide2,CVDA,Name2,Prod2,Cat2,RT-223,23
CodeA3,Data3,13,Text3,Guide3,"FRT,TTY, GERT",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 current script treats that fields as different fields.

How can be fixed this?

Many thanks again.

Regards.
# 16  
Old 09-29-2011
Hmm... it becomes more complicated, you could use the scenario below:

Change the field separator temporary to ";"
Code:
awk -F "\"" ' {
  for(i=1;i<=NF;i+=2) {
    gsub(",", ";", $i)
  }
}1' OFS="\"" file2 > temp

Do your stuff here with the new field separator:
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=, temp file1 > file3

And change the field separator to ","
Code:
awk -F "\"" ' {
  for(i=1;i<=NF;i+=2) {
    gsub(";", ",", $i)
  }
}1' OFS="\"" file3 > newfile

rm file3

This User Gave Thanks to Franklin52 For This Post:
# 17  
Old 09-29-2011
Hi Franklin,

Nice code to change field separator. I modificated file1 and file2 created 2 temp files, then I run the 2nd code and the results now are correct and I stored in temp3.

Finally I passed a sed script (sed 's/;/,/g' temp3 > Output ) to replace field separator that remains only for first line.

Great help you provided me Franklin, many thanks for all.

Best regards
# 18  
Old 09-29-2011
Quote:
Originally Posted by cgkmal
Hi Franklin,

Nice code to change field separator. I modificated file1 and file2 created 2 temp files, then I run the 2nd code and the results now are correct and I stored in temp3.

Finally I passed a sed script (sed 's/;/,/g' temp3 > Output ) to replace field separator that remains only for first line.

Great help you provided me Franklin, many thanks for all.

Best regards
No problem man, you're welcome!
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