Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Lookup field values in two fixed format file in UNIX - not working

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Lookup field values in two fixed format file in UNIX - not working
# 1  
Old 02-25-2014
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

Input #2
Code:
    510       77432201111010000       2     1        1ChK          100111000001    121000248           123456789            20111101.510.77432.20001C                              510       77432201111010000       2     1        2INv                                                                   20111101.510.77432.20001D                              510       77432201111010000       2     1        3INv                                                                   20111101.510.77432.20002D                              510       77432201111010000       2     1        4INv                                                                   20111101.510.77432.20003D                              510       77432201111010000       2     1        5INv                                                                   20111101.510.77432.20004D                              510       77432201111010000       2     2        1ChK          200111000002    121000248           123456789            20111101.510.77432.20002C                              510       77432201111010000       2     2        2INv                                                                   20111101.510.77432.20005D                              510       77432201111010000       2     2        3INv                                                                   20111101.510.77432.20006D                              510       77432201111010000       2     2        4INv                                                                   20111101.510.77432.20007D                              510       77432201111010000       2     2        5INv                                                                   20111101.510.77432.20008D                              510       77432201111010000       2     3        1ChK          300111000003    121000248           123456789            20111101.510.77432.20003C                              510       77432201111010000       2     3        2INv                                                                   20111101.510.77432.20009D                              510       77432201111010000       2     3        3INv                                                                   20111101.510.77432.20010D                              510       77432201111010000       2     3        4INv                                                                   20111101.510.77432.20011D                              510       77432201111010000       2     6        1ChK          600111000006    121000248           123456789            20111101.510.77432.20006C                              510       77432201111010000       2     6        2INv                                                                   20111101.510.77432.20021D                              510       77432201111010000       2     6        3INv                                                                   20111101.510.77432.20022D                              510       77432201111010000       2     6        4INv                                                                   20111101.510.77432.20023D                              510       77432201111010000       2     6        5INv                                                                   20111101.510.77432.20024D

Desired output:
Code:
     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:
Mod Comment 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.
# 2  
Old 02-25-2014
The input you provided is next to impossible to understand.
Can you paste it neatly? Also, extract at least one value which is in the range 37-50 for our reference.

--ahamed
# 3  
Old 02-25-2014
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


I am trying to format the output to put it here
# 4  
Old 02-26-2014
Giving us your code again (this time with no line breaks) doesn't help.

Your code uses three input files. You have shown us two lines of data.

Please show us all THREE of your sample input files in a readable format with CODE tags.
Since both of your input files are extremely long single lines with no matches in the 14 characters starting in column 37 between those lines, we can't guess at what you're trying to do!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to print field from lookup file in output

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)
Discussion started by: cmccabe
4 Replies

2. Shell Programming and Scripting

UNIX command -Filter rows in fixed width file based on column values

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)
Discussion started by: ashok.k
2 Replies

3. Linux

How do I format a Date field of a .CSV file with multiple commas in a string field?

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)
Discussion started by: dhruuv369
3 Replies

4. UNIX for Dummies Questions & Answers

Lookup field in map file

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)
Discussion started by: genehersh
1 Replies

5. Shell Programming and Scripting

File field to replace lookup from another file

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)
Discussion started by: ckwong99
0 Replies

6. UNIX for Dummies Questions & Answers

Help with AWK - Compare a field in a file to lookup file and substitute if only a match

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)
Discussion started by: venalla_shine
4 Replies

7. Shell Programming and Scripting

Lookup two values per line (from a second file) and write the smaller value to another file

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)
Discussion started by: genehunter
3 Replies

8. Shell Programming and Scripting

Fixed width file with newline field separators

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)
Discussion started by: vtischuk@yahoo.
0 Replies

9. Shell Programming and Scripting

Fixed Format File

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)
Discussion started by: div_Neev
2 Replies

10. Shell Programming and Scripting

Changing particular field in fixed width file

I have a fixed width file and i need to change 36th field to "G" in for about random 20 records? How can I do it? (4 Replies)
Discussion started by: dsravan
4 Replies
Login or Register to Ask a Question