Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Vlookup using Ask from specific column from two files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Vlookup using Ask from specific column from two files
# 15  
Old 04-09-2018
Quote:
Originally Posted by ranjancom2000
sorry to bump any help on this
Have you read the various posts in this thread?

The code you are using says that comma is your field separator. With your new input file format, there are only two fields (or columns) in your input and the 2nd field on the first line of the file is:
Code:
000A3 Server1_Parent                  000A3 00666   R1:13  RW RW RW  A..1.        0        0 RW  WD   Consistent

which is clearly never going to be interpreted as a 5 digit hexadecimal number string.

When you change your input file format, you have to adjust field separators and field numbers to match that file format!
# 16  
Old 05-08-2018
I need to check the column 3 from file1 and compare it with Colume1 of file 1 and append and update the record if not found. But dont know how to update this if the file was not having any comma

Code:
awk 'FNR == NR {T[$2] = $0; next} $0 = $0 "|" (T[$NF]?T[$NF]:"Notfound")' FS="," file1  file2

File1
Code:
533 Server1 002D9 34526
533 Server1 002DA 34526
533 Server1 002DB 34526
533 Server1 002DC 34526
533 Server1 002DD 34526
533 Server1 002DE 34526
533 Server1 002DF 34526
533 Server1 002E1 34526
533 Server1 002E2 34526
533 Server1 002E3 34526
533 Server1 002E4 34526
533 Server1 002E5 34526
533 Server1 002E6 34526
533 Server1 002E7 34526

File2
Code:
002D9 RDFType:R1 RemoteDeviceSymmetrixName:001DD RemoteSymmetrixID:000296700555
002DA RDFType:R1 RemoteDeviceSymmetrixName:001DE RemoteSymmetrixID:000296700555
002DB RDFType:R1 RemoteDeviceSymmetrixName:001DF RemoteSymmetrixID:000296700555
002DC RDFType:R1 RemoteDeviceSymmetrixName:001E0 RemoteSymmetrixID:000296700555
002DD RDFType:R1 RemoteDeviceSymmetrixName:001E1 RemoteSymmetrixID:000296700555
002DE RDFType:R1 RemoteDeviceSymmetrixName:001E2 RemoteSymmetrixID:000296700555
002DF RDFType:R1 RemoteDeviceSymmetrixName:001E3 RemoteSymmetrixID:000296700555
002E0 RDFType:R1 RemoteDeviceSymmetrixName:001E4 RemoteSymmetrixID:000296700555
002E1 RDFType:R1 RemoteDeviceSymmetrixName:001E5 RemoteSymmetrixID:000296700555
002E2 RDFType:R1 RemoteDeviceSymmetrixName:001E6 RemoteSymmetrixID:000296700555
002E3 RDFType:R1 RemoteDeviceSymmetrixName:001E7 RemoteSymmetrixID:000296700555
002E4 RDFType:R1 RemoteDeviceSymmetrixName:001E8 RemoteSymmetrixID:000296700555
002E5 RDFType:R1 RemoteDeviceSymmetrixName:001E9 RemoteSymmetrixID:000296700555
002E6 RDFType:R1 RemoteDeviceSymmetrixName:001EA RemoteSymmetrixID:000296700555
002E7 RDFType:R1 RemoteDeviceSymmetrixName:001EB RemoteSymmetrixID:000296700555

Output required

Code:
533 Server1 002D9 34526 002D9 RDFType:R1 RemoteDeviceSymmetrixName:001DD RemoteSymmetrixID:000296700555
533 Server1 002DA 34526 002DA RDFType:R1 RemoteDeviceSymmetrixName:001DE RemoteSymmetrixID:000296700555
533 Server1 002DB 34526 002DB RDFType:R1 RemoteDeviceSymmetrixName:001DF RemoteSymmetrixID:000296700555
533 Server1 002DC 34526 002DC RDFType:R1 RemoteDeviceSymmetrixName:001E0 RemoteSymmetrixID:000296700555
533 Server1 002DD 34526 002DD RDFType:R1 RemoteDeviceSymmetrixName:001E1 RemoteSymmetrixID:000296700555
533 Server1 002DE 34526 002DE RDFType:R1 RemoteDeviceSymmetrixName:001E2 RemoteSymmetrixID:000296700555
533 Server1 002DF 34526 002DF RDFType:R1 RemoteDeviceSymmetrixName:001E3 RemoteSymmetrixID:000296700555
Notfound 002E0 RDFType:R1 RemoteDeviceSymmetrixName:001E4 RemoteSymmetrixID:000296700555
533 Server1 002E1 34526 002E1 RDFType:R1 RemoteDeviceSymmetrixName:001E5 RemoteSymmetrixID:000296700555
533 Server1 002E2 34526 002E2 RDFType:R1 RemoteDeviceSymmetrixName:001E6 RemoteSymmetrixID:000296700555
533 Server1 002E3 34526 002E3 RDFType:R1 RemoteDeviceSymmetrixName:001E7 RemoteSymmetrixID:000296700555
533 Server1 002E4 34526 002E4 RDFType:R1 RemoteDeviceSymmetrixName:001E8 RemoteSymmetrixID:000296700555
533 Server1 002E5 34526 002E5 RDFType:R1 RemoteDeviceSymmetrixName:001E9 RemoteSymmetrixID:000296700555
533 Server1 002E6 34526 002E6 RDFType:R1 RemoteDeviceSymmetrixName:001EA RemoteSymmetrixID:000296700555
533 Server1 002E7 34526 002E7 RDFType:R1 RemoteDeviceSymmetrixName:001EB RemoteSymmetrixID:000296700555

# 17  
Old 05-08-2018
Code:
awk 'FNR == NR {T[$3] = $0; next} {print (length(T[$1]) ? T[$1] : "Notfound ") $0}' file1 file2

This User Gave Thanks to rdrtx1 For This Post:
ranjancom2000
# 18  
Old 05-08-2018
Quote:
Originally Posted by rdrtx1
Code:
awk 'FNR == NR {T[$3] = $0; next} {print (length(T[$1]) ? T[$1] : "Notfound ") $0}' file1 file2

This one is working dont know how it compare the file. I tried to put
Code:
|

between append not able to do. How i can add that

---------- Post updated at 01:07 PM ---------- Previous update was at 01:04 PM ----------

Quote:
Originally Posted by ranjancom2000
This one is working dont know how it compare the file. I tried to put
Code:
|

between append not able to do. How i can add that

I Can able to solve that

Code:
awk 'FNR == NR {T[$3] = $0; next} {print (length(T[$1]) ? T[$1] " | ": "Notfound |") $0}' 1.txt 2.txt
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How do I extract specific column in multiple csv files?

file1: Name,Threshold,Curr Samples,Curr Error%,Curr ART GETHome,100,21601,0.00%,47 GETregistry,100,21592,0.00%,13 GEThomeLayout,100,30466,0.00%,17 file2: Name,Threshold,Curr Samples,Curr Error%,Curr ART GETHome,100,21601,0.00%,33 GETregistry,100,21592,0.00%,22... (6 Replies)
Discussion started by: Raghuram717
6 Replies

2. UNIX for Beginners Questions & Answers

Vlookup on 2 files - inserting vlookup command on another command

Hello, i am trying to print group name column(etc/group) on script (etc/passwd) since group name is not listed on etc/passwd columns. Im trying to do a vlookup. but i cant figure out how i can insert the vlookup command FNR==NR inside the print out command or the output. I also tried exporting... (2 Replies)
Discussion started by: joonisio
2 Replies

3. Shell Programming and Scripting

Vlookup using Ask from specific column shell script

Input file1 frame1,dummy,server1, 00C1C N/A RDF1+TDEV RW 51789 frame1,dummy,server1, 00C1D N/A RDF1+TDEV RW 51789 frame1,dummy,server1, 00C1E N/A RDF1+TDEV RW 51789 frame1,dummy,server1, 00C1F N/A RDF1+TDEV RW 51789 frame1,dummy,server1, 00C20 N/A RDF1+TDEV RW 51789 frame1,dummy,server1,... (10 Replies)
Discussion started by: ranjancom2000
10 Replies

4. Shell Programming and Scripting

Overwrite specific column in xml file with the specific column from adjacent line

I have an xml file dumped from rrd file, that I want to "patch" so the xml file doesn't contain any blank hole in the resulting graph of the rrd file. Here is the file. <!-- 2015-10-12 14:00:00 WIB / 1444633200 --> <row><v> 4.0419731265e+07 </v><v> 4.5045912770e+06... (2 Replies)
Discussion started by: rk4k
2 Replies

5. Shell Programming and Scripting

awk or sed to find specific column from different files

Hi everybody, I have a folder with many files: Files with 8 columns: X 123 A B C D E F And files with 7 columns: X1234 A B C D E F I am trying to find a way to extract the 5th column when the files have eight columns, or the 4th column when the files have... (3 Replies)
Discussion started by: Tzole
3 Replies

6. Shell Programming and Scripting

Concatenating many files based on a specific column contents

Dear all, I have many files(.csv) in a directory. I want to concatenate the files which have similar entries in a particular column and save into a new file like result_datetime.csv etc. One example file is like below. Sno,Step,Data1,Data2,Data3 etc. 1,0,2,3,4 2,1,3,4,5 3,2,0,1,1 ... (4 Replies)
Discussion started by: ks_reddy
4 Replies

7. Shell Programming and Scripting

Divide data with specific column values into separate files

hello! i need a little help from you :) ... i need to split a file into separate files depending on two conditions using scripting. The file has no delimiters. The conditions are col 17 = "P" and col 81 = "*", this will go to one output file; col 17 = "R" and col 81 = " ". Here is an example. ... (3 Replies)
Discussion started by: chanclitas
3 Replies

8. Shell Programming and Scripting

averaging specific column of multiple files

Hi all, I'm needing help again on scripting. I have weekly files with 3 columns, and I need to do monthly averaging on the values on column 3, the file naming convention is as follows: 20000105.u- 2000:year 01:month 05:day 20000112.u 20000119.u 20000126.u 20000202.u 20020209.u I need to... (15 Replies)
Discussion started by: ida1215
15 Replies

9. Shell Programming and Scripting

vlookup files

hi frnds i have 2 files. 1st is dddd and 2nd is ssss ==> dddd <==: 1,charit 2,gilhotra ==> ssss <==: 1,sajan 2,doda 3,hello and i want o/p ...mean join and vlookup both files sajan,charit (4 Replies)
Discussion started by: dodasajan
4 Replies

10. Shell Programming and Scripting

Concatenate two files after a specific column

Hi I have two files, one is 1.6 GB. I would like to add one extra column of information to the large file at a specific location. After its 2nd column. For example: File 1 has two columns more than 1000 rows like this MM009987 1 File 2 looks like this MM00098 MM00076 3 4 2 4 2 1... (1 Reply)
Discussion started by: sogi
1 Replies
Login or Register to Ask a Question