0

I have been searching over the web for a week now and I can't seem to find any solution that works for the scenario I have. I have two files.

File1 contains a number of columns but the last column is the reference number. And will be used as the driving file.

File1 contents:

file1abc|file1abc|file1123|9999999    
file1def|file1def|file1456|8888888

File2 contains many columns as well but the reference number repeats and there's a required sort field

File2 contents

file2xyz|file2xyz|file2987|sort1|9999999
file2qrs|file2qrs|file2765|sort2|8888888
file2efg|file2efg|file2555|sort3|9999999
file2tuv|file2tuv|file2666|sort2|9999999
file2xyz|file2xyz|file2987|sort1|8888888

Desired Results to output.

File1 lines will be the initial line followed by file2 lines where the reference number match and sorted by the column with the sort #

output to contain

file1abc|file1abc|file1123|9999999
file2xyz|file2xyz|file2987|sort1|9999999
file2efg|file2efg|file2555|sort2|9999999
file2tuv|file2tuv|file2666|sort3|9999999
file1def|file1def|file1456|8888888
file2qrs|file2qrs|file2765|sort1|8888888
file2xyz|file2xyz|file2987|sort2|8888888

Any help and suggestion is appreciated.

1
  • 1
    That "sort" field being alphanumeric creates a problem that won't become obvious until your sample input contains values like sort10 because it won't sort correctly numerically as it starts with letters and it won't sort correctly alphabetically because alphabetically sort10 comes before sort2, not after it. It's not hard to address but the currently posted answers don't account for that because your sample input/output doesn't cover that case - you should fix your example to include that case when you add your own attempt to solve the problem yourself so we can help you with it.
    – Ed Morton
    Commented Jul 9, 2021 at 11:39

1 Answer 1

0

It's fairly straightforward if you have a suitably recent version of GNU awk, using a 2D array and PROCINFO to set the array traversal globally to use the string (i.e. lexical) value of the index in ascending order:

gawk '
  BEGIN {FS = "|"; PROCINFO["sorted_in"] = "@ind_str_asc"} 
  NR==FNR {a[$NF][$(NF-1)] = $0; next} 
  {print; for(i in a[$NF]) print a[$NF][i]}
' File2 File1
file1abc|file1abc|file1123|9999999
file2xyz|file2xyz|file2987|sort1|9999999
file2tuv|file2tuv|file2666|sort2|9999999
file2efg|file2efg|file2555|sort3|9999999
file1def|file1def|file1456|8888888
file2xyz|file2xyz|file2987|sort1|8888888
file2qrs|file2qrs|file2765|sort2|8888888

With older GNU awk, you can use asorti to achieve the same:

gawk '
  BEGIN{FS="|"} 
  NR==FNR {a[$NF][$(NF-1)] = $0; next} 
  {print; n = asorti(a[$NF],dst,"@ind_str_asc"); for(i=1;i<=n;i++) print a[$NF][dst[i]]}
' File2 File1

If you want to sort numerically based on some substring of the (NF-1)th field, then you can change @ind_str_asc to @ind_num_asc and replace $(NF-1) as the second array index by a suitably preprocessed value ex.

substr($(NF-1),5)

or using a regular expression (for more complex key extraction)

gensub(/[^0-9]*([0-9]+)$/,"\\1","1",$(NF-1))

With other awks, you could use 1D array and an external sort:

mawk '
  BEGIN {FS="|"; cmd = "sort -t \| -k4"}
  NR==FNR {a[$NF] = a[$NF] (a[$NF]=="" ? "" : ORS) $0; next}
  {print; print a[$NF] | cmd; close(cmd)}' File2 File1
file1abc|file1abc|file1123|9999999
file2xyz|file2xyz|file2987|sort1|9999999
file2tuv|file2tuv|file2666|sort2|9999999
file2efg|file2efg|file2555|sort3|9999999
file1def|file1def|file1456|8888888
file2xyz|file2xyz|file2987|sort1|8888888
file2qrs|file2qrs|file2765|sort2|8888888

Here, your sort options will be limited to the options supported by the external sort function, ex. cmd = "sort -t \| -k4.5,4n" will sort numerically based on the 5th character onward of the 4th field.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .