Awk: Multiple Replace In Column From Two Different Files
Master_1.txt
Master_2.txt
Input File: Requirement:
Column 1 & 2 to be matched from master_1.txt file & column 3 & 4 to be matched from master_2.txt file.
Output Required
Code
Required ouput is not coming for changes in column 3 & 4 but it is working fine for column 1 & 2. Can you please suggest changes
One problem with your code is that after you change field 1, (adding a comma and sometimes making other changes), what used to be fields 2, 3, and 4 are then fields 3, 4, and 5. After that you aren't working on the fields you thought you were working on.
I also didn't understand why you were ignoring the replacement text from some entries in master_1.txt by using no more than 9 characters of the array subscript when making replacements.
You said you want:
but I don't understand where the comma shown in red in the last line is supposed to come from???
The following script almost produces the output you said you wanted, except that it doesn't produce that extra comma:
As always, if you want to try this on a Solaris/SunOS system, use /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk instead of using /bin/awk or /usr/bin/awk.
Note that this script:
uses a function (FandR [for Find and Replace]) to do the work,
the calls to FandR() are set up to work on field 4, 3, 2, and 1 and that order, and
FandR() uses index() rather than repeated calls to substr() to pick the matching data.
Thanks don for the inputs. It is almost near & extra comma mentioned by you in red is not required so code is perfect in terms of the near output.
One point, i would like to share is that, i used
to check starting characters matching in the input file, so in the code shared by you
will not be serving the criteria in case there are multiple enteries in the master_1 file as it will check in the complete string(correct me if i am wrong). for e.g entries marked in red color below(as i have these type of entries as well in the master_1 file), so the exact match in the master file(both master_1 & master_2) to be checked in starting chars of the input file. So can you please suggest what needs to be done additionaly?
Master_1 file
input file
Rest of the file remains same
The problem is that the order of the elements accessed from an array by the awk command:
is unspecified.
The index(string1, string2) function will return the position in string1 where string2 first appears (or 0 if string2 does not appear in string1). Since my code is checking that index() is returning 1, it is checking that string2 appears at the start of string1. If there are two entries in either of the master files where one key is an initial portion of another key in the same file, (such as USSDLIKE and US in your new master_1.txt), the following simple fix will work as long as the shorter key always follows the longer key in the master file. If you can't guarantee that the master files will follow this rule, we can change the script to sort the master files in reverse order before giving them to the awk script to get the desired results.
Note however, that it looks to me like your newly added line in inputfile:
should be translated to:
instead of to:
as you requested in message #3 in this thread because the field in red above should be converted due to the following entry in master_2.txt:
Anyway, the awk script:
uses list1[] (indexed by its line number in master_1.txt) and list2[] (indexed by its line number in master_2.txt) to store the keys. These lists can be used to check the keys in the order they were presented (instead of in an unspecified order).
The output from this script when given your updated input files is:
Last edited by Don Cragun; 08-02-2013 at 03:53 PM..
Reason: Remove extraneous "]".
This User Gave Thanks to Don Cragun For This Post:
Hi All,
I need to extract a position in the file and multiple the value by 1000 and the replace it .
Original
0010001200084701217637306521200000000000010010000000 ---> 000847 * 1000
0010012700086001213437404323000000000000001001000000 ---> 000860 * 1000... (2 Replies)
I have a csv which has lot of columns . I was looking for an awk script which would extract a column twice. for the first occurance the header and data needs to be intact but for the second occurance i want to replace the header name since it a duplicate and extract year value which is in ddmmyy... (10 Replies)
I have data of an excel files as given below,
file1
org1_1 1 1 2.5 100
org1_2 1 2 5.5 98
org1_3 1 3 7.2 88
file2
org2_1 1 1 2.5 100
org2_2 1 2 5.5 56
org2_3 1 3 7.2 70
I have multiple excel files as above shown.
I have to copy column 1, column 4 and paste into a new excel file as... (26 Replies)
Hi Experts,
Please bear with me, i need help
I am learning AWk and stuck up in one issue.
First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique.
Second point : For... (12 Replies)
Hi,
I want to replace the value in more than one column.
For one column ,following command is working -
sed 's/./$value_to_replace/$column number' file_name
e.g. suppose this is input 1111000000
command - sed 's/./M/5'
output= 1111M000000
For two column also command is like -
cat... (22 Replies)
i have multiple files with Batch Header, Record detail & Batch trailer data in the files like :
BH 20150225950050N8262
RD 20140918000000 99999999 unk Deferred Settlement -13950
BT01 -13950
*Above sample data donot have the spaces coorectly defined. I do have multiple batch trailer... (1 Reply)
Hi,
My input files is like this
axis1 0 1 10
axis2 0 1 5
axis1 1 2 -4
axis2 2 3 -3
axis1 3 4 5
axis2 3 4 -1
axis1 4 5 -6
axis2 4 5 1
Now, these are my following tasks
1. Print a first column for every two rows that has the same value followed by a string.
2. Match on the... (3 Replies)
Hi all,
I searched through the forum but i can't manage to find a solution. I need to join a set of files placed in a directory (~1600) by column, and obtain an output with first and second column common to each file, but following columns are taken from the file in the list (precisely the fourth... (10 Replies)
Hi
I use the following code to replace ‘.' with ‘N' in my files and keep both versions.
awk '{ gsub(/\./,"N"); print }' file_0001.txt > path/to/new/dir/file_0001.txt
I need help on how to apply the code to 100 files instead of doing them one file at a time. The files are labeled... (7 Replies)
$ cat log.txt
Name Age Sex Lcation nfld alias xsd
CC 25 M XYZ asx KK Y
BB 21 F XAS awe SS N
SD 21 M AQW rty SD A
How can I replace the column with header "Lcation" with the column with header "alias" and delete the "alias" column?
so that the final output will become:
Name Age Sex... (10 Replies)