Lookup field values in two fixed format file in UNIX - not working
I have 2 fixed length files input#1 & input#2. I want to match the rows based on the value in position 37-50 in both files (pos 37-50 will have same value in both files).
If any matching record is found then cut the value against company code & Invoice number from input file #1 (position 99 until end of line).
The cut string (from Input #1) need to be appended at the end of the record/line.
Below is the code I tried (not working) and the input files & desired output. Please provide your advice.
Code:
awk '
NR==FNR && NF>1 {
v=substr($0,37,14);
#print substr($0,37,14)
next
}
NR==FNR && ( /Company Code/ OR /Invoice Number/ ) {
sub(/Company Code/,"",$0);
sub(/Invoice Number/,"",$0);
a[v]=$0;
print $0
next
}
(substr($0,37,14) in a) {
print $0 a[substr($0,99)]
}' Input1.txt input2.txt input3.txt
Input #1
Code:
612 1111111111201402120000 2 1 111 211 Due Date 20140101 612 1111111111201402120000 2 1 111 311 Company Code 227 612 1111111111201402120000 2 1 111 411 Item Code 12 612 1111111111201402120000 2 1 111 511 Invoice Number 2014010 612 1111111111201402120000 2 2 111 611 Company Code 214 612 1111111111201402120000 2 2 111 711 Item Code 20 612 1111111111201402120000 2 2 111 811 Invoice Number 3014010 612 1111111111201402120000 2 3 111 911 Due Date 20140101 612 1111111111201402120000 2 3 111 111 Invoice Number 40140101 612 1111111111201402120000 2 3 111 121 user code 15563263636 612 1111111111201402120000 2 3 111 131 Amount Due 100000 612 111111111120140212000078978982123444 111 141 Due Date 20140101 612 111111111120140212000078978982123444 111 151 Invoice Number 50140101 612 111111111120140212000078978982123444 111 161 Amount Due 008000
510 77432201111010000 2 1 1ChK 100111000001 121000248 123456789 20111101.510.77432.20001C 2272014010 (company & Inv # from input 1) 510 77432201111010000 2 1 2INv 20111101.510.77432.20001D 2272014010 510 77432201111010000 2 1 3INv 20111101.510.77432.20002D 2272014010 510 77432201111010000 2 1 4INv 20111101.510.77432.20003D (company & Inv # from input 1) 510 77432201111010000 2 1 5INv 20111101.510.77432.20004D (company & Inv # from input 1) 510 77432201111010000 2 2 1ChK 200111000002 121000248 123456789 20111101.510.77432.20002C (company & Inv # from input 1) 510 77432201111010000 2 2 2INv 20111101.510.77432.20005D (company & Inv # from input 1) 510 77432201111010000 2 2 3INv 20111101.510.77432.20006D (company & Inv # from input 1) 510 77432201111010000 2 2 4INv 20111101.510.77432.20007D (company & Inv # from input 1) 510 77432201111010000 2 2 5INv 20111101.510.77432.20008D (company & Inv # from input 1) 510 77432201111010000 2 3 1ChK 300111000003 121000248 123456789 20111101.510.77432.20003C (company & Inv # from input 1) 510 77432201111010000 2 6 1ChK 600111000006 121000248 123456789 20111101.510.77432.20006C <there is no matching record in input 1, this will be blank> 510 77432201111010000 2 6 2INv 20111101.510.77432.20021D <there is no matching record in input 1, this will be blank> 510 77432201111010000 2 6 3INv 20111101.510.77432.20022D <there is no matching record in input 1, this will be blank> 510 77432201111010000 2 6 4INv 20111101.510.77432.20023D <there is no matching record in input 1, this will be blank> 510 77432201111010000 2 6 5INv 20111101.510.77432.20024D <there is no matching record in input 1, this will be blank>
Moderator's Comments:
I tried to add CODE tags in appropriate places, but I don't see any input file 3 and there seem to be a lot of missing newlines.
Please edit this post to show appropriate line breaks and sample input for the 3rd input file!
Last edited by Don Cragun; 02-25-2014 at 09:56 PM..
Reason: Try to add CODE tags.
Hi to All,
Would you please help me.
I have a issue like in fixed format file there are 80 words should be there. but if file contains greater than 80 words then creates problem. so i have to trim file having more than 80 characters line through unix shell scripting. Please let me know how... (2 Replies)
I have some huge files that are produced daily from a production system written in basic (really). The files are fixed width records, 512 bytes, with newline field separators, newlines if the field is null, and trailing newlines for null fields. The data in the fields can be any ascii... (0 Replies)
Hello Unix Gurus,
Please let me know if this is hard to understand and I apologize for my inability to explain better.
I have a file "Foo" with the following structure
CHR_A BP_A SNP_A CHR_B BP_B SNP_B R2
1 ... (3 Replies)
I have the below 2 files:
1) Third field from file1.txt should be compared to the first field of lookup.txt.
2) If match found then third field, file1.txt should be substituted with the second field from lookup.txt.
3)Else just print the line from file1.txt.
File1.txt:... (4 Replies)
Hi All,
I don't know how to fast do this field replace that need lookup from another file to form the update result:confused:
I want to do it by general shell script
Can anyone help to solve it ? Thanks for your kindly reply in advance.
CK (0 Replies)
Hi,
I have two questions which I would massively appreciate help with.
1.
I am trying to insert a field into a file similar to the vlookup function in excel. In column 2 is a gene id for which i would like to insert the full name in the adjacent column. I have a map file (map.file) which... (1 Reply)
I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below:
column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10
"12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in... (3 Replies)
Hi All,
I am trying to select the rows in a fixed width file based on values in the columns.
I want to select only the rows if column position 3-4 has the value AB
I am using cut command to get the column values. Is it possible to check if cut -c3-4 = AB is true then select only that... (2 Replies)
The below awk uses $3 and $4 in search as the min and max, then takes each $2 value in lookup and compares it. If the value in lookupfalls within the range in searchthen it prints the entire line in lookup/ICODE]. What I can't seem to figure out is how to print the matching $5 from search on that... (4 Replies)