Copying the fields of one record into the fields of another record in a second file with awk

Question

GNU Awk 5.1.0. zsh, Ubuntu 22.04 I have file containing 1 record record_grab.tmp

XJZT+ML+SD         | 9979 | 4¾    | 14

and file table.txt

| formula               | no. | dose     | days    |
| --------------------- | --- | -------- | ------- |
|                       |     |          |         |

I want to place fields 1,2,3,4 from record_grab.tmp in fields 1,2,3,4 of record 3 of table.txt. Preserving the formatting and record separators of table.txt.

I have used the chain of commands in the terminal as follows:

awk '{a[FNR]=$0}' /tmp/record_grab.tmp 
|awk -v FS='|' -v OFS='|'  'FNR==NR{a[FNR]=$0; next} {print $1,$2,$3,$4 a[FNR], $3}' /tmp/record_grab.tmp /home/table.txt

The result was:

| formula               | no. | dose      XJZT+ML+SD          | 9979 | 4¾    | 14    | no. 
| --------------------- | --- | -------- | --- 
|                       |     |   

The expected result is:

| formula               | no.  | dose     | days    |
| --------------------- | ---  | -------- | ------- |
| XJZT+ML+SD            | 9979 | 4¾       | 14      |

I realise there is definitely a more elegant way to do this, that aside I have made a mistake in my use of arrays it looks like it's put the entire line from the temp file into field 3 of table.txt rather than record 3 somehow and I can't work out how to correct it. Any help is appreciated.

Answer 1

I would harness GNU AWK for this task following way, let file.txt content be

19-03-2024
Name            | formula           | no.  | dose | days | cost  | msg | em | notes | consult | WYLQ
John Doe        | XJZT+ML+SD        | 9979 | 4¾   | 14   | xx.xx | x   | 2  | xx    | ph      |
Jane Doe        | XJZT-BS+CS+MDP+FL | 9980 | 5    | 10   | xx.xx | x   | 1  | xx    | ph      | 

then

awk 'BEGIN{FS=OFS="|"}NR==2{print "|" $2,$3,$4,$5 "|";for(i=2;i<=5;i+=1){$i=sprintf("%*s",length($i),"");gsub(/ /,"-",$i)};print "|" $2,$3,$4,$5 "|"}NR>2&&$1~/John Doe/{print "|" $2,$3,$4,$5 "|"}' file.txt

gives output

| formula           | no.  | dose | days |
|-------------------|------|------|------|
| XJZT+ML+SD        | 9979 | 4¾   | 14   |

Explanation: I inform GNU AWK that pipe is both field separator (FS) and output field separator (OFS), for 2nd line I print desired columns encased in |. Then for each selected column I use dynamic sprintf to get string consisting of as many spaces as there characters in given column, which I then globally substitute using dashes. I print it in some manner as headers line. For each line after 2nd line and having John Doe inside 1st field I print desired column encased in |.

(tested in GNU Awk 5.1.0)

Answer 2

This, using any awk, is an untested guess (since no example of table.txt in the question) at what you might be trying to do:

awk '
    BEGIN { FS=OFS="|" }
    NR == FNR {
        if ( $1 ~ /John Doe/ ) {
            split($0,a)
            nextfile
        }
        next
    }
    FNR == 3 {
        for ( i=1; i<=4; i++ ) {
            $i = a[i+1]
        }
    }
    { print }
' source_file.txt table.txt

If your awk supports the non-POSIX extension nextfile the above will execute faster than if it doesn't, but it'll work either way.

Answer 3

Assumptions:

• you want to create a new file (table.txt) that consists of fields 2-5 from the input file source_file.txt
• you're only interested in source lines reprsenting the column headers, or lines where the Name column includes the string John Doe
• each new line of output is to include a prefix/suffix of |

One awk idea:

awk '
BEGIN              { FS=OFS="|" }
$1~/Name|John Doe/ { print OFS $2,$3,$4,$5 OFS}
' source_file.txt > table.txt

This generates:

$ cat table.txt
| formula           | no.  | dose | days |
| XJZT+ML+SD        | 9979 | 4¾   | 14   |

NOTES:

• /Name|John Does/ will also match on strings like John No Name Given and John Does Dallas; if this could be an issue then we could expand the code to match exact strings
• expected output includes a line of hyphens (to separate header from data) but there's no attempt in OP's current code to create said line of hyphens so I've opted to not add code to create said line; if the line of hyphens is required we'd need to add more code