Command to "fill down" columns in text file, a la the Excel fill down function

Question

I have a text file with rows, and columns within those rows. I want to, essentially, replicate the Excel "fill down" function. In other words, if there is a blank "cell" on a line, it will look to the line above it and fill down the value in the corresponding field above it. Example, with "^" being used as column separator:

London^Paris^Moscow^Berlin
^^Melbourne^New York^Washington
^^^Sydney^Singapore^New Delhi
^^New York^Washington
Kuala Lumpur^Bangkok^Hong Kong
^^^^Johannesburg^Sydney^Singapore^New Delhi
Amsterdam
^^Bucharest

... becomes (filled down items in CAPS for clarity):

London^Paris^Moscow^Berlin
LONDON^PARIS^Melbourne^New York^Washington
LONDON^PARIS^MMELBOURNE^Sydney^Singapore^New Delhi
LONDON^PARIS^New York^Washington
Kuala Lumpur^Bangkok^Hong Kong
KUALA LUMPUR^BANGKOK^HONG KONG^^Johannesburg^Sydney^Singapore^New Delhi
Amsterdam
AMSTERDAM^^Bucharest

So essentially the function is to identify any blank field, and, where there is one, pull the value down from the corresponding field above it. Important to be able to specify the field/column separators. Any ideas ?

Answer 1

awk -F'^' -v OFS='^' \
   '{
        for (i = 1; i <= NF; i++) {
                if ($i == "") $i = save[i]
                else          save[i] = $i
        }
        for (i = NF+1; i <= 99; i++) save[i] = ""
        print
    }'

This uses an array called save to save the values from one line to the next (filling down).  Everything in awk is implicitly initialized to null, so the save array will be blank until data are assigned to it.  (Therefore, any blank fields in the first row will be left blank, because there’s no prior data to copy down into them.)

For each line,

• For each field that exists on this line (for (i = 1; i <= NF; i++)),
  • if it’s blank, fill it with the saved (copied) value for this column,
  • otherwise, save the current value for possible use on subsequent lines.

• Then erase the save array for all columns that aren’t present on the current line.  For example, when we get to row 4 (which has only four fields), we “forget” that columns 5 and 6 of row 3 contained “Singapore” and “New Delhi”.  And when we get to row 7 (which has only one field), we “forget” that column 2 of row 5 contained “Bangkok”, so it is not available to be entered into row 8 (where column 2 is blank).

  Replace the 99 with the largest number of fields you expect to see in one row (ever).