How to add a new line for a missing entry between two fields

Question

I have this file. As you can see in column1, entries TEST01N03, TEST01N06, and TEST01N18 are missing (total of 18 entries). Is there a way to check the fields of this column and fill in the missing entries ?

TEST01N01 40   2002    2697    2523  
TEST01N02 40   4       4       5    
TEST01N04 40   4       4       5  
TEST01N05 40   4       4       5  
TEST01N07 40   4       4       5  
TEST01N08 40   1       4       5  
TEST01N09 40   4       4       5  
TEST01N10 40   4       4       5  
TEST01N11 40   2002    2621    2617  
TEST01N13 40   1       4       5  
TEST01N14 40   1       4       5    
TEST01N15 40   1       4       5  
TEST01N16 40   1       4       5  
TEST01N17 40   2002    2576    2515

The output should look like this:

TEST01N01 40   2002    2697    2523  
TEST01N02 40   4       4       5    
TEST01N03 empty  
TEST01N04 40   4       4       5  
TEST01N05 40   4       4       5  
TEST01N06 empty  
TEST01N07 40   4       4       5  
TEST01N08 40   1       4       5  
TEST01N09 40   4       4       5  
TEST01N10 40   4       4       5  
TEST01N11 40   2002    2621    2617  
TEST01N12 empty  
TEST01N13 40   1       4       5  
TEST01N14 40   1       4       5    
TEST01N15 40   1       4       5  
TEST01N16 40   1       4       5  
TEST01N17 40   2002    2576    2515  
TEST01N18 empty  

Really appreciated for any suggestion of a script to accomplish this.

Answer 1

Here is a perl solution:

$ perl -anle 'BEGIN {$pattern = "TEST01N"}
    $curr = $F[0] =~ s/$pattern//r;
    if ($prev and ++$prev != $curr) {
        printf "%s%02d empty\n", $pattern, $prev;
        redo;
    } 
    $prev = $curr;
    print;
' file
TEST01N01 40 2002 2697 2523
TEST01N02 40 4 4 5
TEST01N03 empty
TEST01N04 40 4 4 5
TEST01N05 40 4 4 5
TEST01N06 empty
TEST01N07 40 4 4 5
TEST01N08 40 1 4 5
TEST01N09 40 4 4 5
TEST01N10 40 4 4 5
TEST01N11 40 2002 2621 2617
TEST01N12 empty
TEST01N13 40 1 4 5
TEST01N14 40 1 4 5
TEST01N15 40 1 4 5
TEST01N16 40 1 4 5
TEST01N17 40 2002 2576 2515

Explanation

• We get current column index, save in variable $curr
• If $prev is set and its value plus 1 not equal $curr, meaning we have missing entry, print it and redo from the beginning until $prev equal $curr.
• Assign $curr value to $prev.
• Print the line.

Update

For @Babyy's comment, you can try:

$ perl -anle 'BEGIN {$pattern = "TEST01N"}
    $curr = $F[0] =~ s/$pattern//r;     
    if ($. == 1 and (0+$curr) != ++$i) {
        printf "%s%02d empty\n", $pattern, $i;
        redo;
    }
    if ($prev and ++$prev != $curr) {
        printf "%s%02d empty\n", $pattern, $prev;
        redo;
    } 
    $prev = $curr;
    print;
' file

Answer 2

In awk: parse each input line to find the current index after the TEST01N prefix. If the current output line number (n) is less than the index, print missing lines before copying the input line. Finally add more lines to reach the desired count.

{
    i = match($1,/[0-9]+$/);
    prefix = substr($1,1,i-1);
    current = substr($1,i) + 0;
    while (++n < current) printf "%s%02d empty\n", prefix, n;
    print;
 }
 END {
    while (++n <= 18) printf "%s%02d empty\n", prefix, n;
 }