awk script to flatten isql output

When you drive ISQL (or dbaccess?) from the command line, you can store its output in a redirected file. isql stores - >customer.list <<@ select * from customer; @ When the output width would exceed 79 chars, ISQL goes to vertical format, producing like: customer_num 118 fname Dick lname Baxter company Blue Ribbon Sports address1 5427 College etc... This was debated recently on comp.databases.informix. The following awk program is one solution. It takes ISQL output on standard input and reformats it so each data row is a single, flat line. It provides quite a bit of power to format the result, as well. The key insight that makes this useful is, that awk's printf() will take its pattern from a variable, and a variable can be set from the command line. So this script prepares a fairly stupid printf format based on the first input record contents -- but it lets you override it to do almost any sort of formatting you want. ----------- cut here & save as "invsql" --------------------------- # # invsql: an awk program to reformat an Informix-ISQL output listing, # changing it from vertical format to horizontal report format. # # Usage: # [g]awk -f invsql [dpat="data-pattern"] [lpp=n] [hpat="head-pattern"] # # <data-pattern> is a printf() pattern to format the column values # <head-pattern> is a printf() pattern to format the column headings # <n> is the number of lines of data per page # # Headings are printed only when lpp is specified as greater than zero. # See below for default heading and data patterns. # # As written, supports only 30 columns of output. See the end of the # program for how to expand this to more columns if required. # # Requires an "awk" that matches the book by Aho et.al, that is, # System V awk or Gnu awk -- not the obsolete awk shipped by Sun, NeXT, etc. # # Author: David Cortesi (cortesi@informix.com) # # --------------------- User's Guide, so to speak -------------------------- # # Standard input to invsql is an ISQL vertical-format report like this: # # order_num 1007 # order_date 03/25/1989 # customer_num 117 # backlog n # po_num 278693 # ship_date 04/23/1989 # ship_weight 125.90 # # We change it to horizontal format with optional page headings: # # order_num order_date customer_num backlog po_num ship_date ship_weight # 1007 03/25/1989 117 n 278693 04/28/1989 125.90 # 1012 06/05/1989 117 n 278701 06/09/1989 70.80 # # The program prints each output line using a printf() like this: # printf(pattern,col1,col2,...,coln) # where each "col" is one value of that column from the input. # The default pattern is: "%nns %nns...\\n" where each "nn" is the # default width of that column, determined by the following rule: # The default width is the larger of the width of the heading text # and the width of the data value in the first input record. # # The default is often wrong, but you can specify exact widths and # control the format in other ways, by specifying a printf() # pattern string as the command-line argument dpat="pattern". # # The program can print column headings at the top of each page of # data. The default is to NOT print headings -- you can paginate # the output using the pr(1) command for example. However if you # specify lpp=n, n>0, the program will print column headings before # each group of n data lines. # # The default column heading display is: # printf("\\f%nns %nns...\\n\\n",col1,col2...coln) # where each "col" is the heading text of that column from the first # input record. The pattern is essentially the default data pattern # with formfeed before and newline after. You can supply your own # pattern using hpat="pattern" on the command line. # # When writing printf patterns as part of c-shell commands you need # only write the string in quotes, like this: dpat="%-5d\\t%20f\\n" # (The c shell does not object to backslashes in such quotes.) # # --------------------- The Program -------------------------- BEGIN { state = 0; colno = 0; recno = 0 } # Leading blank lines: ignore them state == 0 && $1 == "" { next } # First data line of first group: note the length of the prefix # on each line, the name of the first column and some spaces. # The prefix has the same length on all lines, even when data is null. state == 0 && $1 != "" { match($0,/^[^ ]+ */) pfxlen = RLENGTH+1 state = 1 # now into first data record } # Any data line of any group: save the string value of the data line, # which is the whole line less the prefix. $1 != "" { v[++colno] = substr($0,pfxlen) } # Any data line of the first data record: save the column name # as well, and set the default length of this column. state == 1 && $1 != "" { h[colno] = $1 lh = length($1) lv = length(v[colno]) dlen[colno] = (lh > lv)?lh:lv } # End of first data record: build the print patterns. state == 1 && $1 == "" { pl = 0 # no pagination if (lpp > 0) pl = lpp pd = "" for( j = 1; j <= colno; ++j ) pd = pd "%" dlen[j] "s " sub(" $","\\n",pd) ph = "\\f" pd "\\n" if (dpat != "") pd = fixup(dpat) if (hpat != "") { ph = fixup(hpat) if (pl == 0) pl = 60 } if (pl > 0) recno = pl-1 # force starting headings state = 2 # no more setting up to do } # End of any data record: print. $1 == "" { # the printf statements have to list every possible column 1..colno # Columns that do not exist generate no output (because there's no # format for them in the pattern :) and awk does not object to you # referring to empty array elements. To support more columns, add # more lines to the printf statements below, following the same pattern. if ( ++recno == pl ) { # only succeeds when pl > 0 printf ph \\ ,h[1],h[2],h[3],h[4],h[5],h[6],h[7],h[8],h[9],h[10] \\ ,h[11],h[12],h[13],h[14],h[15],h[16],h[17],h[18],h[19],h[20] \\ ,h[21],h[22],h[23],h[24],h[25],h[26],h[27],h[28],h[29],h[30] recno = 0 } printf pd \\ ,v[1],v[2],v[3],v[4],v[5],v[6],v[7],v[8],v[9],v[10] \\ ,v[11],v[12],v[13],v[14],v[15],v[16],v[17],v[18],v[19],v[20] \\ ,v[21],v[22],v[23],v[24],v[25],v[26],v[27],v[28],v[29],v[30] } # As received from the command line, the print patterns still have # literally "\\n" instead of a newline. Convert the 5 possible format # effectors to the real things. function fixup(pat) { gsub("\\\\\\\\f","\\f",pat) gsub("\\\\\\\\n","\\n",pat) gsub("\\\\\\\\r","\\r",pat) gsub("\\\\\\\\t","\\