replace blank field in file 2 with content of file 1

09-07-2011

Registered User

37, 0

Join Date: Mar 2009

Last Activity: 14 September 2011, 10:09 AM EDT

Posts: 37

Thanks Given: 2

Thanked 0 Times in 0 Posts

replace blank field in file 2 with content of file 1

Something like vlookup in excel, column 2 in file 2 is blank and should be replaced by column 2 in file 1 based on comparing column 1 in both files.

file1

Code:
1234~abc~b~c~d~e~f~g~h~09/10/09
5678~def~b~c~d~e~f~g~h~12/06/10
8910~hij~b~c~d~e~f~g~h~03/28/13
1023~klm~b~c~d~e~f~g~h~07/29/11
file2

Code:
1234~ ~b~c~d~e~f~g~h~09/10/09
1234~ ~b~c~d~e~f~g~h~11/12/10
1234~ ~b~c~d~e~f~g~h~12/06/10
5678~ ~b~c~d~e~f~g~h~01/11/11
5678~ ~b~c~d~e~f~g~h~03/28/13
8910~ ~b~c~d~e~f~g~h~07/29/11
1023~ ~b~c~d~e~f~g~h~10/22/09
1023~ ~b~c~d~e~f~g~h~04/22/14
desired output

Code:
1234~abc~b~c~d~e~f~g~h~09/10/09
1234~abc~b~c~d~e~f~g~h~11/12/10
1234~abc~b~c~d~e~f~g~h~12/06/10
5678~def~b~c~d~e~f~g~h~01/11/11
5678~def~b~c~d~e~f~g~h~03/28/13
8910~hij~b~c~d~e~f~g~h~07/29/11
1023~klm~b~c~d~e~f~g~h~10/22/09
1023~klm~b~c~d~e~f~g~h~04/22/14

sigh2010

View Public Profile for sigh2010

Find all posts by sigh2010

09-07-2011

Registered User

2,100, 402

Join Date: Apr 2009

Last Activity: 11 February 2020, 10:24 AM EST

Posts: 2,100

Thanks Given: 26

Thanked 402 Times in 360 Posts

Code:

$
$ cat file1
1234~abc~b~c~d~e~f~g~h~09/10/09
5678~def~b~c~d~e~f~g~h~12/06/10
8910~hij~b~c~d~e~f~g~h~03/28/13
1023~klm~b~c~d~e~f~g~h~07/29/11
$
$ cat file2
1234~ ~b~c~d~e~f~g~h~09/10/09
1234~ ~b~c~d~e~f~g~h~11/12/10
1234~ ~b~c~d~e~f~g~h~12/06/10
5678~ ~b~c~d~e~f~g~h~01/11/11
5678~ ~b~c~d~e~f~g~h~03/28/13
8910~ ~b~c~d~e~f~g~h~07/29/11
1023~ ~b~c~d~e~f~g~h~10/22/09
1023~ ~b~c~d~e~f~g~h~04/22/14
$
$ awk -F[~] 'BEGIN {OFS="~"} NR == FNR {x[$1] = $2} NR != FNR {$2=x[$1]; print}' file1 file2
1234~abc~b~c~d~e~f~g~h~09/10/09
1234~abc~b~c~d~e~f~g~h~11/12/10
1234~abc~b~c~d~e~f~g~h~12/06/10
5678~def~b~c~d~e~f~g~h~01/11/11
5678~def~b~c~d~e~f~g~h~03/28/13
8910~hij~b~c~d~e~f~g~h~07/29/11
1023~klm~b~c~d~e~f~g~h~10/22/09
1023~klm~b~c~d~e~f~g~h~04/22/14
$
$
$
$ perl -F[~] -lane '$ARGV eq "file1" ? $x{$F[0]}=$F[1] : print join "~",($F[0],$x{$F[0]},@F[2..$#F])' file1 file2
1234~abc~b~c~d~e~f~g~h~09/10/09
1234~abc~b~c~d~e~f~g~h~11/12/10
1234~abc~b~c~d~e~f~g~h~12/06/10
5678~def~b~c~d~e~f~g~h~01/11/11
5678~def~b~c~d~e~f~g~h~03/28/13
8910~hij~b~c~d~e~f~g~h~07/29/11
1023~klm~b~c~d~e~f~g~h~10/22/09
1023~klm~b~c~d~e~f~g~h~04/22/14
$
$

tyler_durden

Last edited by durden_tyler; 09-07-2011 at 03:52 PM..

This User Gave Thanks to durden_tyler For This Post:

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

Shell Programming and Scripting

replace blank field in file 2 with content of file 1

9 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Conditionally replace field content

Discussion started by: dovah

2. Shell Programming and Scripting

How to replace blank tab with zero in a file?

Discussion started by: amyt1234

3. Shell Programming and Scripting

Replace string in a file with some content indexed from another file.

Discussion started by: AlokKumbhare

4. Shell Programming and Scripting

Find a blank field and replace values to NA

Discussion started by: Shenbaga.d

5. Shell Programming and Scripting

Sed: replace content from file with the content from file

Discussion started by: dirkaulo

6. Shell Programming and Scripting

Adding an additional blank field to a file

Discussion started by: ramky79

7. Shell Programming and Scripting

Text file to CSV with field data separated by blank lines

Discussion started by: vestport

8. Shell Programming and Scripting

Find and replace blank in the last field

Discussion started by: GoldenFire

9. Shell Programming and Scripting

how to replace a character with blank in a file

Discussion started by: sridhusha