Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Replace a value using a reference data from other file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replace a value using a reference data from other file
# 1  
Old 11-24-2014
Replace a value using a reference data from other file
Gents,

Can you please help me to solve this case

In my input file I have a values in column 49 which always need to be one, but sometimes the system create a value 2, in this case I need to go to search in the original file and replace the values in the row where the value 2 is and in the previous.

Example input file

Code:
X  4708      3310  69521.00  19353.001  932  9331  69668.00  19572.00  19574.001
X  4708      3310  69521.00  19353.001  934  9352  69668.00  19576.00  19576.001

The situation is when the value in column 49 changes to value = 2, then I need to change some values in row where it is value 2 and previous row.

changes need to be done in previous row , column 46 , 72
next row , column 41, 49, 52
using the values in original file.

Values in original file

Code:
X                  69523.00  19353.001  932  9341  69668.00  19572.00  19576.001
X                  69523.00  19353.001  935  9351  69680.00  19576.00  19576.001

Desire output file
Code:
X  4708      3110  69523.00  19353.001  932  9341  69668.00  19572.00  19576.001
X  4708      3110  69523.00  19353.001  935  9351  69680.00  19576.00  19576.001x

Files attached , hope can help me.Smilie

I have started to see how I can get it

Code:
awk '{if(/^X/)print $0}' input.txt | awk '{\
	 ln = substr($0,20,5);\
	 vp = substr($0,30,5);\
	 rpl = substr($0,52,5);\
	 flc = substr($0,40,9);\
	 tc = ((substr($0,45,4)- substr($0,40,4))+1);\
	 if (tc == 1) {\
			printf ("Point : %5d/%5d in RL %5d has %4d channels in swath %4d\n", ln,vp,rpl,tc,1000 )}}' > tmpab$swath

input.txt (6.0 KB) 
original_file.txt (6.0 KB) 
output.txt (6.0 KB) 
# 2  
Old 11-24-2014
The output.txt file doesn't match your description (code written to match expected output)
ie:

changes need to be done in previous row , column 48 , 76
next row , column 43, 49, 55 and 56
using the values in original file.
next row also need a "x" appended

Code:
awk '
function repstr(str,orig,pos) {
   return substr(str,1,pos-1) substr(orig,pos,1) substr(str,pos+1)
}
FNR==NR { N[NR]=$0; next }
substr(N[FNR+1],49,1) == "2" {
    N[FNR] = repstr(N[FNR],$0,48)
    N[FNR] = repstr(N[FNR],$0,76)
}
substr(N[FNR],49,1) == "2" {
    N[FNR] = repstr(N[FNR],$0,43)
    N[FNR] = repstr(N[FNR],$0,49)
    N[FNR] = repstr(N[FNR],$0,55)
    N[FNR] = repstr(N[FNR],$0,56)"x"
}
END { for(i=1;i<=FNR;i++) print N[i]"\r" }' input.txt original_file.txt

This User Gave Thanks to Chubler_XL For This Post:
jiam912
# 3  
Old 11-26-2014
Dear XL
Thanks a lot for your script works fine.
But please can you change it to let it works even if the original_file.txt and the input file have different location when it finds the value =2 in column 49

Input file
Example

Code:
X  4708      3010  69523.00  19321.001    1  1461  69524.00  19254.00  19544.001
X  4708      3010  69523.00  19321.001  147  2761  69536.00  19286.00  19544.001
X  4708      3010  69523.00  19321.001  277  3921  69548.00  19314.00  19544.001
X  4708      3010  69523.00  19321.001  393  4921  69560.00  19346.00  19544.001
X  4708      3010  69523.00  19321.001  493  5571  69572.00  19416.00  19544.001
X  4708      3010  69523.00  19321.001  558  6101  69584.00  19440.00  19544.001
X  4708      3010  69523.00  19321.001  611  6531  69596.00  19460.00  19544.001
X  4708      3010  69523.00  19321.001  654  6861  69608.00  19480.00  19544.001
X  4708      3010  69523.00  19321.001  687  7111  69620.00  19496.00  19544.001
X  4708      3010  69523.00  19321.001  712  7281  69632.00  19512.00  19544.001
X  4708      3010  69523.00  19321.001  729  7391  69644.00  19524.00  19544.001
X  4708      3110  69523.00  19353.001    1  1621  69524.00  19254.00  19576.001
X  4708      3110  69523.00  19353.001  163  3081  69536.00  19286.00  19576.001
X  4708      3110  69523.00  19353.001  309  4401  69548.00  19314.00  19576.001
X  4708      3110  69523.00  19353.001  441  5561  69560.00  19346.00  19576.001
X  4708      3110  69523.00  19353.001  557  6371  69572.00  19416.00  19576.001
X  4708      3110  69523.00  19353.001  638  7061  69584.00  19440.00  19576.001
X  4708      3110  69523.00  19353.001  707  7651  69596.00  19460.00  19576.001
X  4708      3110  69523.00  19353.001  766  8141  69608.00  19480.00  19576.001
X  4708      3110  69523.00  19353.001  815  8551  69620.00  19496.00  19576.001
X  4708      3110  69523.00  19353.001  856  8881  69632.00  19512.00  19576.001
X  4708      3110  69523.00  19353.001  889  9151  69644.00  19524.00  19576.001
X  4708      3110  69523.00  19353.001  916  9311  69656.00  19546.00  19576.001
X  4708      3110  69523.00  19353.001  932  9331  69668.00  19572.00  19574.001
X  4708      3110  69523.00  19353.001  934  9352  69668.00  19576.00  19576.001

original_file.txt
Code:
X                  69523.00  19353.001    1  1621  69524.00  19254.00  19576.001
X                  69523.00  19353.001  163  3081  69536.00  19286.00  19576.001
X                  69523.00  19353.001  309  4401  69548.00  19314.00  19576.001
X                  69523.00  19353.001  441  5561  69560.00  19346.00  19576.001
X                  69523.00  19353.001  557  6371  69572.00  19416.00  19576.001
X                  69523.00  19353.001  638  7061  69584.00  19440.00  19576.001
X                  69523.00  19353.001  707  7651  69596.00  19460.00  19576.001
X                  69523.00  19353.001  766  8141  69608.00  19480.00  19576.001
X                  69523.00  19353.001  815  8551  69620.00  19496.00  19576.001
X                  69523.00  19353.001  856  8881  69632.00  19512.00  19576.001
X                  69523.00  19353.001  889  9151  69644.00  19524.00  19576.001
X                  69523.00  19353.001  916  9311  69656.00  19546.00  19576.001
X                  69523.00  19353.001  932  9341  69668.00  19572.00  19576.001
X                  69523.00  19353.001  935  9351  69680.00  19576.00  19576.001

The key is to find the match the key substr($0,20,18).

Hope you can help me. I have tested it and works fine but needs to have the values to compare exactly in the same row in both files but sometimes I have more data can be in original or input files and the script give other information.

Thanks
# 4  
Old 11-26-2014
This should do it:

Code:
awk '
function repstr(str,orig,pos) {
   return substr(str,1,pos-1) substr(orig,pos,1) substr(str,pos+1)
}
{k=substr($0,20,18)}
FNR==NR {
   if(!(k in keyline)) keylist[++keys]=k
   keyline[k]++
   N[k keyline[k]]=$0; next }
{
   keyline_orig[k]++
   key=k keyline_orig[k]
   next_klo = k keyline_orig[k]+1
}
next_klo in N && substr(N[next_klo],49,1) == "2" {
    N[key] = repstr(N[key],$0,48)
    N[key] = repstr(N[key],$0,76)
}
substr(N[key],49,1) == "2" {
    N[key] = repstr(N[key],$0,43)
    N[key] = repstr(N[key],$0,49)
    N[key] = repstr(N[key],$0,55)
    N[key] = repstr(N[key],$0,56)"x"
}
END {
   for(key=1;key<=keys;key++)
      for(keyln=1; (keylist[key] keyln) in N; keyln++)
          print N[keylist[key] keyln]"\r"
}' input.txt original_file.txt

This User Gave Thanks to Chubler_XL For This Post:
jiam912
# 5  
Old 11-27-2014
Dear XL
You are perfect. Smilie
Thanks a lot for your support.
It works fine but only in two changes.
I have attached the original files I have and you can see that the output is different to the original one.
Sorry to write again,, looks like there is small thing to change.
Example the output should take the values in original file row 23975 and change in row 13151 in input file.. But it write somethings else in output?. I don't know from where it take the values.
Attached input original and output file after run the script (.zip ) fle
files.zip (379.2 KB) 
# 6  
Old 11-27-2014
It's doing what was specified see below:

Code:
23975:X              1   69459.00  19257.00  1090 11001  69596.00  19460.00  19480.001
13151:X  4713    140710  69459.00  19257.001 1090 10991  69596.00  19460.00  19478.001
  New:X  4713    140710  69459.00  19257.001 1090 10901  69596.00  19460.00  19470.001

See how chars 48 and 76 were replaced from line 23975 of original file. Perhaps the character positions are wrong?
This User Gave Thanks to Chubler_XL For This Post:
jiam912
# 7  
Old 11-27-2014
Dear XL
Code:
I have in my input file 10991.       19480.001
                  Original file 11001.       19478.001
                 Output file.   10901.       19470.001

Then, the values doesn't match with original file

Maybe I need to change in the range 0,76

Thanks for your help
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

awk to replace values in one file using a second reference file

Hi, I'd be grateful for your help with the following: I have a file with a single column (file1). Let's say the values are: a b c 5 d I have a second, reference file (ref_file), which is colon-delimited, and is effectively a key. Let's say the values in it are: a:1 b:2 c:3 d:4... (4 Replies)
Discussion started by: aberg
4 Replies

2. Shell Programming and Scripting

Compare data with reference from other file

Gents, Please can you help with this. I have a big file (file2) which contends many records increment every 25 rows ( column 1 ). Then I have other file as reference (file1).. column 1 to 11. I want to compare that all values in file2 (column 2 to 12.) match with values in... (2 Replies)
Discussion started by: jiam912
2 Replies

3. Shell Programming and Scripting

Replace from reference file awk

Basically want to replace any field in input file from the refernce file ... for example. clar_2400:3113 in input file will be replaced by clar_2400:3113 Input file field seperator is "," Field which is not found in reference will stay as it is ... Input File ... (3 Replies)
Discussion started by: greycells
3 Replies

4. Shell Programming and Scripting

[Solved] Replace records according to reference

Dear All, I am struggling with the following task and would appreciate some help. I have a large table (file1.txt). The first column of this table contains an ID. I would like to replace the ID with a label according to a reference file. Here is an example: cat infile.txt 0 AJ2312 310 ... (7 Replies)
Discussion started by: GDC
7 Replies

5. Shell Programming and Scripting

Replace data of a file with data from another file using shell scripting.

Dears, I'm new to shell scripting and i was wondering if you can help me with following matter. I have a file containing 400,000 records. The file contains two columns like: 00611291,0270404000005453 25262597,1580401000016155 25779812,1700403000001786 00388934,1200408000000880... (1 Reply)
Discussion started by: paniklas
1 Replies

6. Shell Programming and Scripting

Help with replace column one content based on reference file

Input file 1 testing 10 20 1 A testing 20 40 1 3 testing 23 232 2 1 testing 10 243 2 . . Reference file 1 final 3 used . . Output file (1 Reply)
Discussion started by: perl_beginner
1 Replies

7. Shell Programming and Scripting

Help with replace column one content based on reference file

Input file 1 testing 10 20 1 A testing 20 40 1 3 testing 23 232 2 1 testing 10 243 2 . . Reference file 1 final 3 used . . Output file (2 Replies)
Discussion started by: perl_beginner
2 Replies

8. 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

9. Shell Programming and Scripting

Replace character based on reference file problem asking

I got two files right now, input file (target file), reference file 1 (query file) reference file 1 (long list of data) KOLOPWMOPOPO ADASDASD ADSASDASDAD . . target file (one long liner content) ADASDASDTYUKOKOLOPWMOPOPOOPLUAADSASDASDADPOPOUYADADASDASD desired output file content ... (1 Reply)
Discussion started by: patrick87
1 Replies

10. Shell Programming and Scripting

Find and replace data in text file with data in same file

OK I will do my best to explain what I need help with. I am trying to format an ldif file so I can import it into Oracle oid. I need the file to look like this example. Keep in mind there are 3000 of these in the file. changetype: modify replace: userpassword dn:... (0 Replies)
Discussion started by: timothyha22
0 Replies
Login or Register to Ask a Question