Find and extract values from one file and update other

Shell Programming and Scripting
1

Gents,

Kindly can you help me to update a file extracting the data from other file.

I have:
file1
The key in this file is substr($0,4,21), and I need to update the columns 6 and 7 using the information for file2.

S  21133.00  21535.00  1               0       919088.8 1843754.5  58.9229000049
S  21135.00  21535.00  1               0       919017.0 1843719.4  58.0229000232
S  21137.00  21535.00  1               0       919013.1 1843812.0  57.5229000414
S  21139.00  21535.00  1               0       919001.4 1843843.9  57.3229000554
S  21139.00  21537.00  1               0       919041.1 1843872.8  56.5229000736
S  21137.00  21537.00  1               0       919062.5 1843831.5  57.5229000918

file2
in this file the key is substr($0,1,21). Here I have the correct values that need to be updated in file1 Columns 5 and 5

21133.00  21535.00  1 919070.8 1843749.0
21387.00  21593.00  1 917152.3 1849943.2
21135.00  21535.00  1 919047.0 1843759.4
21385.00  21593.00  1 917178.4 1849896.6
21139.00  21537.00  1 917132.2 1850278.1
21137.00  21535.00  1 919023.1 1843802.0
21383.00  21593.00  1 917196.2 1849861.6
21183.00  21561.00  1 919011.1 1845127.6
21139.00  21535.00  1 917198.1 1850262.6

output
Mi output should be like this

S  21137.00  21535.00  1               0       919070.8 1843749.0  57.5229000414
S  21139.00  21535.00  1               0       917198.1 1850262.6  57.3229000554
S  21139.00  21537.00  1               0       917132.2 1850278.1  56.5229000736
S  21137.00  21535.00  1               0       919023.1 1843802.0  57.5229000918

I have try using

ask 'FNR==NR{a[NR]=$6;next}{$4=a[FNR]}1' file1 file2

but it is to replace one column,, the problem I have is that I need to find the key and replace the values , like I use idex and match in excel formulas.

Thanks for your support

2

I don't understand the requirements, and the samples don't help either. Why do you

? Where does the output line

S  21137.00  21535.00  1               0       919070.8 1843749.0  57.5229000414

come from? Why is the sample output just four lines long? If the print condition is a match between the keys you name, it should be five. Wildly guessing, I came up with

awk     '               {X1=$1 SUBSEP $2 SUBSEP $3; X2=$2 SUBSEP $3 SUBSEP $4}
         FNR==NR        {T1[X1]=$4; T2[X1]=$5; next}
         T1[X2]         {$6=T1[X2]; $7=T2[X2]; print}
        ' file2 file1
S 21133.00 21535.00 1 0 919070.8 1843749.0 58.9229000049
S 21135.00 21535.00 1 0 919047.0 1843759.4 58.0229000232
S 21137.00 21535.00 1 0 919023.1 1843802.0 57.5229000414
S 21139.00 21535.00 1 0 917198.1 1850262.6 57.3229000554
S 21139.00 21537.00 1 0 917132.2 1850278.1 56.5229000736

Adapt if need be.

3

Dear Rudi C

Yes there was a type mistake I should update the columns 5 and 6 in file1 ( sorry for that ).

The script works fine :slight_smile: but how can I keep the original format on file1

thanks again

---------- Post updated at 01:45 PM ---------- Previous update was at 09:51 AM ----------

Dear Rudi C
I got the same format for file1 like this :slight_smile:

awk     '               {X1=$1 SUBSEP $2 SUBSEP $3; X2=$2 SUBSEP $3 SUBSEP $4}\
         FNR==NR        {T1[X1]=$4; T2[X1]=$5; next}\
         T1[X2]         {$6=T1[X2]; $7=T2[X2];\
         type = $1;\
         li = $2;\
	 vp =$3;\
	 idx =$4;\
	 el =$5;\
	 x =substr($0,25,8);\
	 y =substr($0,34,9);\
	 ti =substr($0,43,14);\
         printf("%1s %9s %9s %2d %14d% 16.1f %9.1f %14s\n", type,li,vp,idx,el,x,y,ti)}'\
         eff600 tmpS