Print whole line if variables matches

nypreH · June 17, 2016, 7:58am

Der colleagues,

4 days I am trying to solve my issue and no success..
Maybe you can give me a clue how to achieve what I need..

So I have two files.

file1 example:

1_column1.1   1_column2.1   aaa   1_column4.1
1_column1.2   1_column2.2   ttt   1_column4.2
1_column1.3   1_column2.3   ddd  1_column4.3
1_column1.3   1_column2.3   ppp 1_column4.3

file2 example:

aaa   2_column2.1
bbb   2_column2.2
ccc   2_column2.3
ddd   2_column2.4

So what I need is to print whole line of file1 in case if values from 1st column from file2 matches with values from 3rd column of file1.
So, I want to see output like:

1_column1.1   1_column2.1   aaa   1_column4.1
1_column1.3   1_column2.3   ddd  1_column4.3

Any help highly appreaciated.

RavinderSingh13 · June 17, 2016, 8:06am

Hello nypreH,

Could you please try following and let me know if this helps.

awk 'FNR==NR{A[$1];next} ($3 in A)'  Input_file2   Input_file1

Output will be as follows.

1_column1.1   1_column2.1   aaa   1_column4.1
1_column1.3   1_column2.3   ddd  1_column4.3

Thanks,
R. Singh

nypreH · June 17, 2016, 8:19am

Hello Ravinder,

Yes, thats works like a charm!

Thanks for extremely fast answer.

---------- Post updated at 04:19 PM ---------- Previous update was at 04:12 PM ----------

Maybe you can suggest how to adapt your code in case I have random number of spaces?

real file1 looks like:

[AMR FR]_[72]_Capacity 140 NBSC01  AMR_FR
[STIRC]_[393]_Capacity 850 NBSC01  STIRC
[DTM]_[63]_Capacity 1750 NBSC01  DTM
[AMR PPC]_[578]_Capacity 1827 NBSC01  AMR_PPC
[PPC 2]_[606]_Capacity 1827 NBSC01  PPC_2

and file2 looks simply like:

So in this case I can't use space as delimiter since there can be different number of spaces in file1 and I need to compare with the bold marked values.

RavinderSingh13 · June 17, 2016, 8:35am

Hello nypreH,

Could you please try following and let me know if this helps you.

awk 'FNR==NR{split($2, A," ");B[A[1]]=$0;next} ($1 in B){print B[$1]}' FS="Capacity " Input_file1 FS=" " Input_file2

Considering here like you Input_file1 will have keyword Capacity and you want to compare values from the first field of Input_file2.

Thanks,
R. Singh

nypreH · June 17, 2016, 8:47am

I ran this command and got nothig in output.
I also changed spaces which separates each column with ^ and tried previous suggested command, but also got nothing:

awk -F"^" 'NR==FNR{c[$1$2]++;next};c[$1$2] > 0' /tmp/featureCodesAndCapacity /tmp/featureCode1stPart

file1

[105km Extended Cell for CS]_[1219]_Capacity^754^NBSC01 ^105km_Extended_Cell_for_CS
[105km Extended Cell for CS]_[1219]_Capacity^754 ^NBSC01 ^105km_Extended_Cell_for_CS
[AIS FE BTS]_[587]_Capacity^2700^NBSC01 ^AIS_FE_BTS
[AIS FE BTS]_[587]_Capacity^2700 ^NBSC01 ^AIS_FE_BTS
[Lb Interface basic]_[16]_Capacity^1^NBSC01 ^Lb_Interface_basic
[Lb Interface basic]_[16]_Capacity^1 ^NBSC01 ^Lb_Interface_basic
[Lb AGPS]_[19]_Capacity^1^NBSC01 ^Lb_AGPS
[Lb AGPS]_[19]_Capacity^1 ^NBSC01 ^Lb_AGPS
[Lb UTDOA]_[20]_Capacity^1^NBSC01 ^Lb_UTDOA
[Lb UTDOA]_[20]_Capacity^1 ^NBSC01 ^Lb_UTDOA
[L3 functionality for ESB switch]_[1234]_Capacity^6^NBSC01 ^L3_functionality_for_ESB_switch

file2

RavinderSingh13 · June 17, 2016, 8:58am

nypreh:

I ran this command and got nothig in output.
I also changed spaces which separates each column with ^ and tried previous suggested command, but also got nothing:

awk -F"^" 'NR==FNR{c[$1$2]++;next};c[$1$2] > 0' /tmp/featureCodesAndCapacity /tmp/featureCode1stPart

file1

[105km Extended Cell for CS]_[1219]_Capacity^754^NBSC01 ^105km_Extended_Cell_for_CS
[105km Extended Cell for CS]_[1219]_Capacity^754 ^NBSC01 ^105km_Extended_Cell_for_CS
[AIS FE BTS]_[587]_Capacity^2700^NBSC01 ^AIS_FE_BTS
[AIS FE BTS]_[587]_Capacity^2700 ^NBSC01 ^AIS_FE_BTS
[Lb Interface basic]_[16]_Capacity^1^NBSC01 ^Lb_Interface_basic
[Lb Interface basic]_[16]_Capacity^1 ^NBSC01 ^Lb_Interface_basic
[Lb AGPS]_[19]_Capacity^1^NBSC01 ^Lb_AGPS
[Lb AGPS]_[19]_Capacity^1 ^NBSC01 ^Lb_AGPS
[Lb UTDOA]_[20]_Capacity^1^NBSC01 ^Lb_UTDOA
[Lb UTDOA]_[20]_Capacity^1 ^NBSC01 ^Lb_UTDOA
[L3 functionality for ESB switch]_[1234]_Capacity^6^NBSC01 ^L3_functionality_for_ESB_switch

file2

Hello nypreH,

Could you please check into your Input_file if there are garbage characters in the Input_files mentioned by you by doing following.

cat -v Input_file1

If above gives garbage characters then you could do following to remove them.

tr -d '\r' < Input_file1 > new_Input_file1
tr -d '\r' < Input_file2 > new_Input_file2
mv new_Input_file1  Input_file1
mv new_Input_file2  Input_file2

Then could you please try my command in POST#4 and let us know how it goes then.

Thanks,
R. Singh

MadeInGermany · June 17, 2016, 9:05am

This is more like the post#2

awk 'BEGIN {oFS=FS} NR==FNR {A[$1]; next} {split($2, B, oFS)} (B[1] in A)' Input_file2 FS="Capacity" Input_file1

nypreH · June 17, 2016, 9:45am

Tried, even created file1 and file2 manually which contain only few strings and got no result.

cat file1
aaa_Capacity^20^NBSC01^asdfas
bbb_Capacity^4^NBSC016asdfas

cat file2
20^3
4^44

[root@azrcc3vsys-zbx1 tmp]# 
[root@azrcc3vsys-zbx1 tmp]# awk 'FNR==NR{split($2, A," ");B[A[1]]=$0;next} ($1 in B){print B[$1]}' FS="Capacity " /tmp/file1 FS=" " /tmp/file2
[root@azrcc3vsys-zbx1 tmp]#

---------- Post updated at 05:20 PM ---------- Previous update was at 05:11 PM ----------

I found space after Capacity and removed it and also changed FS=" " to FS="^" for second file and now it returns one string:

[root@azrcc3vsys-zbx1 parsing]# awk 'FNR==NR{split($2, A," ");B[A[1]]=$0;next} ($1 in B){print B[$1]}' FS="^" /tmp/featureCode1stPart FS="^" /tmp/featureCodesAndCapacity
[Lb UTDOA]_[20]_Capacity^1^NBSC01 ^Lb_UTDOA
[root@azrcc3vsys-zbx1 parsing]# 
[root@azrcc3vsys-zbx1 parsing]# cat /tmp/featureCodesAndCapacity
1234^4
7^16
4^1321
2^1321
14^389
1^1780
13^38
73^584
393^0
591^0
834^0
673^0
909^30
1015^0
1262^1807
1273^0
1219^0
1379^1196
3730^85
1826^3
16^0
19^0
20^0
[root@azrcc3vsys-zbx1 parsing]#

but it should return abouot 23 strings, seems that it checks only last value from file2

---------- Post updated at 05:26 PM ---------- Previous update was at 05:20 PM ----------

I think i found mistake, i will check and update status, most probably command work correct.

---------- Post updated at 05:45 PM ---------- Previous update was at 05:26 PM ----------

Ok, my mistake is that I mislead with position of the file

this is original output of file1:

[root@azrcc3vsys-zbx1 tmp]# cat featureCode1stPart 
[Double Power TRX for 2G BTS]_[834]_Capacity^50^NBSC01 ^Double_Power_TRX_for_2G_BTS
[Soft CH capa]_[14]_Capacity^2100^NBSC01 ^Soft_CH_capa
[Flexi Multiradio TRX HW Activation]_[1379]_Capacity^2600^NBSC01 ^Flexi_Multiradio_TRX_HW_Activation
[PCU2 app SW]_[13]_Capacity^68^NBSC01 ^PCU2_app_SW
[GPRS]_[673]_Capacity^2199^NBSC01 ^GPRS
[NCCR]_[11]_Capacity^1917^NBSC01 ^NCCR
[NACC]_[10]_Capacity^1672^NBSC01 ^NACC
[IS-NCCR]_[9]_Capacity^2100^NBSC01 ^IS-NCCR
[EDA]_[64]_Capacity^1927^NBSC01 ^EDA
[ext NACC]_[66]_Capacity^317^NBSC01 ^ext_NACC
[AMR FR]_[72]_Capacity^140^NBSC01 ^AMR_FR
[STIRC]_[393]_Capacity^850^NBSC01 ^STIRC
[DTM]_[63]_Capacity^1750^NBSC01 ^DTM
[AMR PPC]_[578]_Capacity^1827^NBSC01 ^AMR_PPC
[PPC 2]_[606]_Capacity^1827^NBSC01 ^PPC_2
[Gb over IP]_[7]_Capacity^2100^NBSC01 ^Gb_over_IP
[Load Based AMR Packing]_[1180]_Capacity^1672^NBSC01 ^Load_Based_AMR_Packing
[Radio Link Timeout parameter AMR HR]_[1181]_Capacity^1827^NBSC01 ^Radio_Link_Timeout_parameter_AMR_HR
[1TRX Basic SW licence]_[1262]_Capacity^2800^NBSC01 ^1TRX_Basic_SW_licence
[EDGE BSS Fnc]_[4]_Capacity^1837^NBSC01 ^EDGE_BSS_Fnc
[105km Extended Cell for CS]_[1219]_Capacity^754^NBSC01 ^105km_Extended_Cell_for_CS
[AIS FE BTS]_[587]_Capacity^2700^NBSC01 ^AIS_FE_BTS
[Lb Interface basic]_[16]_Capacity^1^NBSC01 ^Lb_Interface_basic
[Lb AGPS]_[19]_Capacity^1^NBSC01 ^Lb_AGPS
[Lb UTDOA]_[20]_Capacity^1^NBSC01 ^Lb_UTDOA
[L3 functionality for ESB switch]_[1234]_Capacity^6^NBSC01 ^L3_functionality_for_ESB_switch
[Downlink Dual Carrier]_[1273]_Capacity^10^NBSC01 ^Downlink_Dual_Carrier
[IMSI BH]_[8]_Capacity^1372^NBSC01 ^IMSI_BH
[HMC]_[65]_Capacity^686^NBSC01 ^HMC
[Multipoint Gb]_[67]_Capacity^1572^NBSC01 ^Multipoint_Gb
[Multipoint A-if]_[69]_Capacity^1372^NBSC01 ^Multipoint_A-if
[Q1 over TCP/IP]_[1238]_Capacity^1372^NBSC01 ^Q1_over_TCP/IP
[OSI over TCP/IP]_[1242]_Capacity^1372^NBSC01 ^OSI_over_TCP/IP
[2G Flexi Abis over IP/ Ethernet]_[1015]_Capacity^10^NBSC01 ^2G_Flexi_Abis_over_IP/_Ethernet
[FACCH and SACCH repetition]_[579]_Capacity^1372^NBSC01 ^FACCH_and_SACCH_repetition
[FACCH repetition for AMR FR]_[580]_Capacity^1372^NBSC01 ^FACCH_repetition_for_AMR_FR
[FACCH repetition for AMR HR]_[653]_Capacity^1372^NBSC01 ^FACCH_repetition_for_AMR_HR
[FACCH Power Increment]_[654]_Capacity^1372^NBSC01 ^FACCH_Power_Increment
[AMR Signalling Measurement]_[1014]_Capacity^1372^NBSC01 ^AMR_Signalling_Measurement
[GPRS CS3 and CS4]_[2]_Capacity^1816^NBSC01 ^GPRS_CS3_and_CS4
[A5/3 Ciphering]_[1235]_Capacity^1372^NBSC01 ^A5/3_Ciphering
[E-Cell GPRS]_[591]_Capacity^1000^NBSC01 ^E-Cell_GPRS
[2G Flexi Additional 2 E1,T1  IF]_[909]_Capacity^151^NBSC01 ^2G_Flexi_Additional_2_E1,T1__IF
[AMR Capacity]_[1]_Capacity^1950^NBSC01 ^AMR_Capacity
[Paging Coordination in BSC]_[1802]_Capacity^1117^NBSC01 ^Paging_Coordination_in_BSC
[PDO]_[589]_Capacity^75^NBSC01 ^PDO
[PCP]_[70]_Capacity^200^NBSC01 ^PCP
[GSM Power License for MultiRadio]_[3730]_Capacity^130^NBSC01 ^GSM_Power_License_for_MultiRadio
[Flexi EDGE BTS Operating SW]_[73]_Capacity^898^NBSC01 ^Flexi_EDGE_BTS_Operating_SW
[Extended CCCH]_[1826]_Capacity^700^NBSC01 ^Extended_CCCH
[Inter System NACC for LTE]_[1528]_Capacity^1030^NBSC01 ^Inter_System_NACC_for_LTE
[LTE System Information]_[1965]_Capacity^993^NBSC01 ^LTE_System_Information
[Fast return to LTE]_[4367]_Capacity^1030^NBSC01 ^Fast_return_to_LTE
[SGSN Selection Improvement]_[4405]_Capacity^1363^NBSC01 ^SGSN_Selection_Improvement
[MOCN Rel10 support]_[4363]_Capacity^1000^NBSC01 ^MOCN_Rel10_support
[Cell Selection]_[594]_Capacity^100^NBSC01 ^Cell_Selection
[[Capacity^^NBSC01 
[root@azrcc3vsys-zbx1 tmp]#

and this is original output of file2:

[root@azrcc3vsys-zbx1 tmp]# cat featureCodesAndCapacity
1234_4
7_16
4_1321
2_1321
14_389
1_1780
13_38
73_584
393_0
591_0
834_0
673_0
909_30
1015_0
1262_1807
1273_0
1219_0
1379_1196
3730_85
1826_3
16_0
19_0
20_0
[root@azrcc3vsys-zbx1 tmp]#

Both files generating by another script, so any changes can be done if needed.

My mistake is that I was comparing with wrong position...
I need to compare values which coming BEFORE Capacity, not after.

And I tried below command but it gives no result as well:

awk 'FNR==NR{split($2, A," ");B[A[1]]=$0;next} ($1 in B){print B[$1]}' FS="_" /tmp/featureCode1stPart FS="_" /tmp/featureCodesAndCapacity

I know that it was my mistake and I mislead you guys and I am sorry for that...

RavinderSingh13 · June 17, 2016, 10:10am

Hello nypreH,

I am not sure about your requirement as you are creating a new requirement in each post. Could you please try following and let me know if this helps.

awk 'FNR==NR{A[$2]=$0;next} {Q="["$1"]"} (Q in A){print A[Q]}' FS="_" featureCode1stPart  featureCodesAndCapacity

Output will be as follows.

[L3 functionality for ESB switch]_[1234]_Capacity^6^NBSC01 ^L3_functionality_for_ESB_switch
[Gb over IP]_[7]_Capacity^2100^NBSC01 ^Gb_over_IP
[EDGE BSS Fnc]_[4]_Capacity^1837^NBSC01 ^EDGE_BSS_Fnc
[GPRS CS3 and CS4]_[2]_Capacity^1816^NBSC01 ^GPRS_CS3_and_CS4
[Soft CH capa]_[14]_Capacity^2100^NBSC01 ^Soft_CH_capa
[AMR Capacity]_[1]_Capacity^1950^NBSC01 ^AMR_Capacity
[PCU2 app SW]_[13]_Capacity^68^NBSC01 ^PCU2_app_SW
[Flexi EDGE BTS Operating SW]_[73]_Capacity^898^NBSC01 ^Flexi_EDGE_BTS_Operating_SW
[STIRC]_[393]_Capacity^850^NBSC01 ^STIRC
[E-Cell GPRS]_[591]_Capacity^1000^NBSC01 ^E-Cell_GPRS
[Double Power TRX for 2G BTS]_[834]_Capacity^50^NBSC01 ^Double_Power_TRX_for_2G_BTS
[GPRS]_[673]_Capacity^2199^NBSC01 ^GPRS
[2G Flexi Additional 2 E1,T1  IF]_[909]_Capacity^151^NBSC01 ^2G_Flexi_Additional_2_E1,T1__IF
[2G Flexi Abis over IP/ Ethernet]_[1015]_Capacity^10^NBSC01 ^2G_Flexi_Abis_over_IP/_Ethernet
[1TRX Basic SW licence]_[1262]_Capacity^2800^NBSC01 ^1TRX_Basic_SW_licence
[Downlink Dual Carrier]_[1273]_Capacity^10^NBSC01 ^Downlink_Dual_Carrier
[105km Extended Cell for CS]_[1219]_Capacity^754^NBSC01 ^105km_Extended_Cell_for_CS
[Flexi Multiradio TRX HW Activation]_[1379]_Capacity^2600^NBSC01 ^Flexi_Multiradio_TRX_HW_Activation
[GSM Power License for MultiRadio]_[3730]_Capacity^130^NBSC01 ^GSM_Power_License_for_MultiRadio
[Extended CCCH]_[1826]_Capacity^700^NBSC01 ^Extended_CCCH
[Lb Interface basic]_[16]_Capacity^1^NBSC01 ^Lb_Interface_basic
[Lb AGPS]_[19]_Capacity^1^NBSC01 ^Lb_AGPS
[Lb UTDOA]_[20]_Capacity^1^NBSC01 ^Lb_UTDOA

If above doesn't meet your requirements then please do show us sample Input_file and expected output without editing it in future please.

Thanks,
R. Singh

RudiC · June 17, 2016, 10:29am

How about (samples from post#3)

awk 'FNR==NR {T[$1]; next} ($(NF-2) in T)'  file2  file1
[STIRC]_[393]_Capacity 850 NBSC01  STIRC
[DTM]_[63]_Capacity 1750 NBSC01  DTM

nypreH · June 20, 2016, 3:22am

Suggested command from Post #9 did the trick.

awk 'FNR==NR{A[$2]=$0;next} {Q="["$1"]"} (Q in A){print A[Q]}' FS="_" featureCode1stPart  featureCodesAndCapacity

Thanks again for your help!