Replacing first field of file2 with the second filed of file1 for matching cases

Dear All,

Need your help..

I am not regular on shell scripts..

I have 2 files..

Content of file1

cellRef 4};"4038_2_MTNL_KALAMBOLI"
 cellRef 1020};"4112_3_RAINBOW_BLDG"
 cellRef 134};"4049_2_TATA_HOSPITAL"
 cellRef 1003};"4242_3_HITESH_CONSTRUCTION"
 cellRef 744};"5035_2_SHAHI_NOORANI_MASJID"
 cellRef 622};"4614_3_SHYAM_NIVAS_DCS"
 cellRef 1080};"4303_1_LODHA_HEAVEN"
 cellRef 394};"4502_1_NAVKANTLAL"
 cellRef 1884};"4323_2_COMMERCE_CENTER"
 cellRef 888};"4260_1_BOLENATH_DAIRY"
 cellRef 945};"4240_1_SHARIFA_MANZIL"
 cellRef 922};"4231_2_NASHEMAN"
 cellRef 812};"4235_2_SAIDAVILA"
 cellRef 1068};"4613_1_BALRAM_SADAN"
 cellRef 75};"4050_1_MTNL_TALOJE"
 cellRef 250};"4170_3_SANDEEP_APT"
 cellRef 2530};"4015_3_DRONAGIRI"
 cellRef 2355};"4245_3_SAI_PRASAD_KOPRA"
 cellRef 1661};"4210_1_AFZAL_COMPOUND"
 cellRef 765};"4116_3_MARUTI_TOWER"
 cellRef 2415};"4401_2_KALYAN_RAIL_SDCS"
 cellRef 983};"4237_1_AMAL_APARTMENT"
 cellRef 580};"4344_1_CHAMUNDA_DARSHAN"
 cellRef 2437};"4600_1_YOGESHWAR_CHS"
 cellRef 2475};"4136_2_MTNL_APMC"

Content of file2

cellRef 2530};disabled;main;0;"Trx4";
 cellRef 2530};disabled;main;0;"Trx2";
 cellRef 2355};disabled;main;12;"Trx3";outer
 cellRef 765};disabled;main;11;"Trx2";
 cellRef 580};disabled;secondary;0;"Trx5";inner
 cellRef 580};disabled;secondary;0;"Trx6";inner
 cellRef 2437};disabled;main;-1;"Trx1";
 cellRef 2437};disabled;main;-1;"Trx3";
 cellRef 2437};disabled;main;-1;"Trx2";
 cellRef 2437};disabled;main;-1;"Trx4";
 cellRef 2475};disabled;main;6;"Trx3";outer
 cellRef 2305};disabled;main;6;"Trx2";
 cellRef 2368};disabled;main;0;"Trx4";outer
 cellRef 2368};disabled;secondary;0;"Trx5";inner
 cellRef 2368};disabled;secondary;0;"Trx6";inner
 cellRef 956};disabled;secondary;4;"Trx5";inner
 cellRef 956};disabled;secondary;3;"Trx6";inner
 cellRef 932};disabled;secondary;6;"Trx5";inner
 cellRef 932};disabled;secondary;5;"Trx6";inner
 cellRef 864};disabled;secondary;4;"Trx5";inner

Both the files are semicolon separated..

First fields of both the files are common..

I want a script which will replace first field of file2 with the second field of file1 when first field of both the files matches..

You could try something like:

awk '
BEGIN {	FS = OFS = ";"
}
FNR == NR {
	x[$1] = $2
	next
}
$1 in x {
	$2 = x[$1]
}
1' file[12]

which (with your sample files) produces the output:

cellRef 2530};disabled;main;0;"Trx4";
 cellRef 2530};"4015_3_DRONAGIRI";main;0;"Trx2";
 cellRef 2355};"4245_3_SAI_PRASAD_KOPRA";main;12;"Trx3";outer
 cellRef 765};"4116_3_MARUTI_TOWER";main;11;"Trx2";
 cellRef 580};"4344_1_CHAMUNDA_DARSHAN";secondary;0;"Trx5";inner
 cellRef 580};"4344_1_CHAMUNDA_DARSHAN";secondary;0;"Trx6";inner
 cellRef 2437};"4600_1_YOGESHWAR_CHS";main;-1;"Trx1";
 cellRef 2437};"4600_1_YOGESHWAR_CHS";main;-1;"Trx3";
 cellRef 2437};"4600_1_YOGESHWAR_CHS";main;-1;"Trx2";
 cellRef 2437};"4600_1_YOGESHWAR_CHS";main;-1;"Trx4";
 cellRef 2475};"4136_2_MTNL_APMC";main;6;"Trx3";outer
 cellRef 2305};disabled;main;6;"Trx2";
 cellRef 2368};disabled;main;0;"Trx4";outer
 cellRef 2368};disabled;secondary;0;"Trx5";inner
 cellRef 2368};disabled;secondary;0;"Trx6";inner
 cellRef 956};disabled;secondary;4;"Trx5";inner
 cellRef 956};disabled;secondary;3;"Trx6";inner
 cellRef 932};disabled;secondary;6;"Trx5";inner
 cellRef 932};disabled;secondary;5;"Trx6";inner
 cellRef 864};disabled;secondary;4;"Trx5";inner

Note that no change is made to the first line because there is a big difference between:

cellRef<space>2530}
        and
<space>cellRef<space>2530}

If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk .

No success..

With /usr/xpg6/bin/awk:

input file "file[12]"

With /usr/xpg6/bin/awk

line 11: /usr/xpg6/bin/awk: No such file or directory

With nawk

nawk: can't open file file[12]
 source line number 2

With awk

awk: syntax error near line 8
awk: bailing out near line 8

---------- Post updated at 02:58 PM ---------- Previous update was at 02:56 PM ----------

uname -a
SunOS omcmfnt1 5.10 Generic_142900-08 sun4u sparc SUNW,Sun-Fire-V490

You said you had two files named file1 and file2 . The error messages you're seeing tell us that file1 and file2 are not present.

Did you change the names of your files? Did you decide to run this script in a directory other than the directory that contains your data files?

Don't use double quotes around the filenames (i.e. file[12]) as they need to be expanded by the shell. Assuming a typo in your samples' first lines, I'd suggest "replacing field 1 in file 2 with field 2 in file 1 if both files' field 1 match":

awk -F";" 'FNR==NR{T[$1]=$2; next} $1 in T {$1=T[$1]} 1' OFS=";" file[12]
"4015_3_DRONAGIRI";disabled;main;0;"Trx4";
"4015_3_DRONAGIRI";disabled;main;0;"Trx2";
"4245_3_SAI_PRASAD_KOPRA";disabled;main;12;"Trx3";outer
"4116_3_MARUTI_TOWER";disabled;main;11;"Trx2";
"4344_1_CHAMUNDA_DARSHAN";disabled;secondary;0;"Trx5";inner
"4344_1_CHAMUNDA_DARSHAN";disabled;secondary;0;"Trx6";inner
"4600_1_YOGESHWAR_CHS";disabled;main;-1;"Trx1";
"4600_1_YOGESHWAR_CHS";disabled;main;-1;"Trx3";
"4600_1_YOGESHWAR_CHS";disabled;main;-1;"Trx2";
"4600_1_YOGESHWAR_CHS";disabled;main;-1;"Trx4";
"4136_2_MTNL_APMC";disabled;main;6;"Trx3";outer
 cellRef 2305};disabled;main;6;"Trx2";
 cellRef 2368};disabled;main;0;"Trx4";outer
 cellRef 2368};disabled;secondary;0;"Trx5";inner
 cellRef 2368};disabled;secondary;0;"Trx6";inner
 cellRef 956};disabled;secondary;4;"Trx5";inner
 cellRef 956};disabled;secondary;3;"Trx6";inner
 cellRef 932};disabled;secondary;6;"Trx5";inner
 cellRef 932};disabled;secondary;5;"Trx6";inner
 cellRef 864};disabled;secondary;4;"Trx5";inner

Thanks Don, RudiC.. Both worked well..

I have fixed the mistakes I was making at my end related to file names..

I misread what you were trying to do. To get the output you wanted using my suggestion, you'd need to change the line:

	$2 = x[$1]

to:

	$1 = x[$1]

In the future, please show us a sample of the output you're trying to produce as well as samples of your input files.