Just want to ask if there is a shorter hand to doing this one liner

UNIX for Beginners Q & A
1

Hi all,

In Oracle, I am using SQL*Plus and selecting all rows in a table and spooling to a file as pipe delimited. I have to use pagesize 0 but unfortunately, using this option excludes the header and I can't get around having it to display the header fields.

So to get around this, I have to create some header file and combine the output from the non-header output and the header only output. This is working fine, I am just curious if there is a shorter version of the command that I am using to create the header line.

Here is what I am doing at the moment.

Below is the header text file doing a describe of the table:

$: cat a.txt
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30 CHAR)
 ROLENAME                                  NOT NULL VARCHAR2(30 CHAR)
 SWITCHROLE                                NOT NULL VARCHAR2(1 CHAR)

And this is the command that I am using to convert the content of a.txt to a header file

$: awk 'NR > 2' a.txt | awk '{ print $1 }' | grep -v "^$" | paste -d"|" -s - | awk -F"|" '{ printf "%-30s|%-30s|%-15s\n", $1, $2, $3 }' | tee -a a.header.txt
USERNAME                      |ROLENAME                      |SWITCHROLE

Then I do a cat a.header.txt a.out.txt > a.xls. Sample a.out.txt is as below:

MICKEY                        |XX_AR_COLLECTZZ_INQUIRY       |N
DONALD                        |YY_AA_TEAMMBR                 |N

Final output is as below:

USERNAME                      |ROLENAME                      |SWITCHROLE
MICKEY                        |XX_AR_COLLECTZZ_INQUIRY       |N
DONALD                        |YY_AA_TEAMMBR                 |N

While what am doing at the moment works fine, just want to know if there is a more shorter way of doing it :slight_smile:

2

This is possible within a single awk call:

program.awk

 (FNR == NR && NR > 2) {
        headers[NR-2]=$1
}

(FNR != NR) {
        if(FNR == 1) {
           printf "%-30s|%-30s|%-15s\n",headers[1],headers[2],headers[3]
        }
        print
}

Call it like an external program like this: awk -f program.awk header.txt data.txt or inline as ...

awk '(FNR==NR && NR > 2) { h[NR-2]=$1 } (FNR!=NR) { if(FNR==1) { printf "%-30s|%-30s|%-15s\n",h[1],h[2],h[3] } print }' header.txt data.txt
3

One could also try:

awk 'FNR==NR{if(NR>2)printf((++cnt%3)?"%-30s|":"%s\n",$1);next}1' a.txt a.out.txt >a.xls
1 Like