Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Find and extract values from one file and update other

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Find and extract values from one file and update other
# 1  
Old 08-20-2014
Find and extract values from one file and update other
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.
Code:
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
Code:
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

Code:
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
Code:
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  
Old 08-21-2014
I don't understand the requirements, and the samples don't help either. Why do you
Quote:
need to be updated in file1 Columns 5 and 5
? Where does the output line
Code:
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
Code:
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  
Old 08-21-2014
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 Smilie 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 Smilie

Code:
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

Last edited by jiam912; 08-21-2014 at 11:57 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Assign Values to a Variable in While Loop and Update the File

Hello, Could anyone please help me with Assigning a value to variable and then updating the value in the original file IFS='|' while read -r Serial_ID JOB_NAME STATUS do if then echo "Perform Fuctions" ???Assign STATUS to COMPLETED and Update File??? done <File (7 Replies)
Discussion started by: infernalhell
7 Replies

2. Shell Programming and Scripting

How to retrieve values from XML file and update them in the same position! PLEASE HELP?

Good Day All Im quiet new to ksh scripting and need a bit of your help. I am attempting to write a script that reads in an XML and extracts certain field values from an XML file. The values are all alphanumeric and consist of two components: e.g "Test 1". I need to to create a script that... (2 Replies)
Discussion started by: JulioAmerica
2 Replies

3. Shell Programming and Scripting

Extract values froma line in file

p.txt T|DCNT=100|RECCHK=22222.2|PERCHK=32323|# I want to extract the value of 100 22222.2 and 32323 and assign it to variable x1,y1,z1 x=`cut -f2 -d "=" p.txt` x1=`echo $x | cut -f1 -d "|" ` y=`cut -f3 -d "=" p.txt` y1=`echo $y | cut -f1 -d "|" ` z=`cut -f4 -d "=" p.txt` z1=`echo $z... (3 Replies)
Discussion started by: w020637
3 Replies

4. Shell Programming and Scripting

Update the table using values from a csv file

i want to run update query for oracle which is in up.sql taking values from a.csv. I have implemented shell script to do it. extn="perl" ls -1 | while read file do echo "$file,$extn" > a.csv done up.sql contains update file_list set filename=$1 where extn=$2; The code to update is... (2 Replies)
Discussion started by: millan
2 Replies

5. Shell Programming and Scripting

Extract values from an XML File

Hi, I need to capture all the attributes with delete next to it. The source XML file is attached. The output should contain something like this below: Attributes = legacyExchangeDN Action = Delete Username = Hero Joker Loginid = joker09 OU =... (4 Replies)
Discussion started by: prvnrk
4 Replies

6. Shell Programming and Scripting

How to find spaces and update values on the same line.

Dear all, I'm trying to write a script where: A file contains more or less 2000 lines. To some of those lines, in a specific position, let's say 89-92 there are spaces. So, this script should find these spaces on specific position and update a value (from 2 to 1) to another position of the... (4 Replies)
Discussion started by: paniklas
4 Replies

7. UNIX for Dummies Questions & Answers

Extract Unique Values from file

Hello all, I have a file with following sample data 2009-08-26 05:32:01.65 spid5 Process ID 86:214 owns resources that are blocking processes on Scheduler 0. 2009-08-26 05:32:01.65 spid5 Process ID 86:214 owns resources that are blocking processes on Scheduler 0. 2009-08-26... (5 Replies)
Discussion started by: simonsimon
5 Replies

8. Shell Programming and Scripting

to extract specific values twice in a file

Hi Friends, I have a file with the following values.. xyz.txt,12345.xml abc.txt,04567.xml cde.txt,12134.xml I would like to extract all the 2nd column values twice as shown in the example like 12345,12345.xml 04567,04567.xml 12134,12134.xml Please advice!! In the formus one of... (7 Replies)
Discussion started by: techmoris
7 Replies

9. Shell Programming and Scripting

update a file with values from other file in shell bash

Hello I need to write a shell script to update properties between files. I have 2 files as follow: file1 urlWebserviceCheckAddress^=McoConfigGlobal.commonGLOBALUrlWebservice file 2 urlWebserviceCheckAddress=http://localhost:8080/tags Both files containt the same properties... (1 Reply)
Discussion started by: teodora
1 Replies

10. Shell Programming and Scripting

Extract values from log file

I would like to write a shell script that will parse through a file similar to the sample below. The data in the file is redirected from rsync into a log file. I would like to call a shell script to parse through and pick out the number beside the percent sign inside the parentheses in the last... (5 Replies)
Discussion started by: wdympcf
5 Replies
Login or Register to Ask a Question