Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Change data in one column with data from another file's column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Change data in one column with data from another file's column
# 1  
Old 06-20-2016
Change data in one column with data from another file's column
Hello,

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

and featureCodesAndCapacity
Code:
[root@azrcc3vsys-zbx1 tmp]# 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

Columns separated by ^
What I am trying to do is to substitude column 2 at outputData with second columns from featureCodesAndCapacity

I am using below command:

Code:
awk 'FNR==NR{a[NR]=$2;next}{$2=a[FNR]}1' FS="^" /tmp/featureCodesAndCapacity FS="^" /tmp/outputData

But the only thing which is wrong here is that it removes ^ symbol and replaces it with Space which is not acceptable, can anyone give a clue how to leave ^ symbols?

Here is output (there 4 columns and 3 ^ symbols:

Code:
[root@azrcc3vsys-zbx1 parsing]# awk 'FNR==NR{a[NR]=$2;next}{$2=a[FNR]}1' FS="^" /tmp/featureCodesAndCapacity FS="^" /tmp/outputData 
[L3 functionality for ESB switch]_[1234]_Capacity 4 NBSC01_Licences L3_functionality_for_ESB_switch
[Gb over IP]_[7]_Capacity 16 NBSC01_Licences Gb_over_IP
[EDGE BSS Fnc]_[4]_Capacity 1321 NBSC01_Licences EDGE_BSS_Fnc
[GPRS CS3 and CS4]_[2]_Capacity 1321 NBSC01_Licences GPRS_CS3_and_CS4
[Soft CH capa]_[14]_Capacity 389 NBSC01_Licences Soft_CH_capa
[AMR Capacity]_[1]_Capacity 1780 NBSC01_Licences AMR_Capacity
[PCU2 app SW]_[13]_Capacity 38 NBSC01_Licences PCU2_app_SW
[Flexi EDGE BTS Operating SW]_[73]_Capacity 584 NBSC01_Licences Flexi_EDGE_BTS_Operating_SW
[STIRC]_[393]_Capacity 0 NBSC01_Licences STIRC
[E-Cell GPRS]_[591]_Capacity 0 NBSC01_Licences E-Cell_GPRS
[Double Power TRX for 2G BTS]_[834]_Capacity 0 NBSC01_Licences Double_Power_TRX_for_2G_BTS
[GPRS]_[673]_Capacity 0 NBSC01_Licences GPRS
[2G Flexi Additional 2 E1,T1  IF]_[909]_Capacity 30 NBSC01_Licences 2G_Flexi_Additional_2_E1,T1__IF
[2G Flexi Abis over IP/ Ethernet]_[1015]_Capacity 0 NBSC01_Licences 2G_Flexi_Abis_over_IP/_Ethernet
[1TRX Basic SW licence]_[1262]_Capacity 1807 NBSC01_Licences 1TRX_Basic_SW_licence
[Downlink Dual Carrier]_[1273]_Capacity 0 NBSC01_Licences Downlink_Dual_Carrier
[105km Extended Cell for CS]_[1219]_Capacity 0 NBSC01_Licences 105km_Extended_Cell_for_CS
[Flexi Multiradio TRX HW Activation]_[1379]_Capacity 1196 NBSC01_Licences Flexi_Multiradio_TRX_HW_Activation
[GSM Power License for MultiRadio]_[3730]_Capacity 85 NBSC01_Licences GSM_Power_License_for_MultiRadio
[Extended CCCH]_[1826]_Capacity 3 NBSC01_Licences Extended_CCCH
[Lb Interface basic]_[16]_Capacity 0 NBSC01_Licences Lb_Interface_basic
[Lb AGPS]_[19]_Capacity 0 NBSC01_Licences Lb_AGPS
[Lb UTDOA]_[20]_Capacity 0 NBSC01_Licences Lb_UTDOA
[root@azrcc3vsys-zbx1 parsing]#

---------- Post updated at 03:05 PM ---------- Previous update was at 02:58 PM ----------

I found the error, need to use OFS instead:

Code:
awk 'FNR==NR{a[NR]=$2;next}{$2=a[FNR]}1' FS="^" /tmp/featureCodesAndCapacity OFS="^" /tmp/outputData

# 2  
Old 06-20-2016
You can add OFS :

Code:
awk 'FNR==NR{a[NR]=$2;next}{$2=a[FNR]}1' FS="^" OFS="^" /tmp/featureCodesAndCapacity /tmp/outputData

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 to insert data into black column( Secound Column ) in excel (.XLSX) file using shell script?

Source Code of the original script is down below please run the script and try to solve this problem this is my data and I want it column wise 2019-03-20 13:00:00:000 2019-03-20 15:00:00:000 1 Operating System LAB 0 1 1 1 1 1 1 1 1 1 0 1 (5 Replies)
Discussion started by: Shubham1182
5 Replies

2. Shell Programming and Scripting

Change Data base on Column

Base of last two column i want to change may data if Last two Column have A and C then Copy Column $4 to Column $3. Input :- DD142 0_1 DD142_A DD142_B A B DD142 1_1 DD142_B DD142_C B C DD142 2_1 DD142_A DD142_C A C DD142 3_1 DD142_A A DD142 3_2 DD142_A A DD142 4_1 DD142_B B ... (4 Replies)
Discussion started by: pareshkp
4 Replies

3. UNIX for Dummies Questions & Answers

Des/awk for change format and adding integers in a column of data?

Greetings! I need a quick way to change the format in a table of data Here is an example of the input: 10 72 Value=177 VDB=0.0245 Value4=0,0,171,0 10 274 Value=238 VDB=0.0433 Value4=29,0,205,0 10 312 Value=222 VDB=0.0384 Value4=8,0,190,19 10 540 Value=405 VDB=0.0391 Value4=13,30,153,195... (3 Replies)
Discussion started by: Twinklefingers
3 Replies

4. Shell Programming and Scripting

Generate tabular data based on a column value from an existing data file

Hi, I have a data file with : 01/28/2012,1,1,98995 01/28/2012,1,2,7195 01/29/2012,1,1,98995 01/29/2012,1,2,7195 01/30/2012,1,1,98896 01/30/2012,1,2,7083 01/31/2012,1,1,98896 01/31/2012,1,2,7083 02/01/2012,1,1,98896 02/01/2012,1,2,7083 02/02/2012,1,1,98899 02/02/2012,1,2,7083 I... (1 Reply)
Discussion started by: himanish
1 Replies

5. Shell Programming and Scripting

Compare 2 files and match column data and align data from 3 column

Hello experts, Please help me in achieving this in an easier way possible. I have 2 csv files with following data: File1 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:36:09,JOB_5340 08/23/2012 12:36:14,JOB_5340 08/23/2012 12:36:22,JOB_5350 08/23/2012... (5 Replies)
Discussion started by: asnandhakumar
5 Replies

6. Shell Programming and Scripting

Replace column that matches specific pattern, with column data from another file

Can anyone please help with this? I have 2 files as given below. If 2nd column of file1 has pattern foo1@a, find the matching 1st column in file2 & replace 2nd column of file1 with file2's value. file1 abc_1 foo1@a .... abc_1 soo2@a ... def_2 soo2@a .... def_2 foo1@a ........ (7 Replies)
Discussion started by: prashali
7 Replies

7. Shell Programming and Scripting

Replace data of one column with data on other file corresponding to transaction ID matched

Hi All, I have two files one of which having some mobile numbers and corresponding value whose sample content as follows: 9058629605,8.0 9122828964,30.0 And in second file complete details of all mobile numbers and sample content as follows and delimeter used is comma(,): ... (8 Replies)
Discussion started by: poweroflinux
8 Replies

8. Shell Programming and Scripting

row to column and position data in to fixed column width

Dear friends, Below is my program and current output. I wish to have 3 or 4 column output in order to accomodate in single page. i do have subsequent command to process after user enter the number. Program COUNT=1 for MYDIR in `ls /` do VOBS=${MYDIR} echo "${COUNT}. ${MYDIR}" ... (4 Replies)
Discussion started by: baluchen
4 Replies

9. Windows & DOS: Issues & Discussions

Split Data in one column into 2 column in Excel using DOS or VBScript

Hi I have some data in my Excel File.However all the data is in one single column.I want to split it into two columns. Current Data: 1,Hi Everyone,I am 7,New To Dos,And 17,VB Script,i could 110,have tried this thing 1800,in UNIX Desired Output CELL1|CELL 2 1 |Hi... (3 Replies)
Discussion started by: dashing201
3 Replies

10. Shell Programming and Scripting

Extract data based on match against one column data from a long list data

My input file: data_5 Ali 422 2.00E-45 102/253 140/253 24 data_3 Abu 202 60.00E-45 12/23 140/23 28 data_1 Ahmad 256 7.00E-45 120/235 140/235 22 data_4 Aman 365 8.00E-45 15/65 140/65 20 data_10 Jones 869 9.00E-45 65/253 140/253 18... (12 Replies)
Discussion started by: patrick87
12 Replies
Login or Register to Ask a Question