Programming Praxis

# CSVtoLIS -- convert CSV file to formatted LIS

# This program takes a CSV file as input and writes a formatted LIS file to standard
# output. It is called as
#
#     LC_NUMERIC=en_US.utf8 gawk -f csvtolis.awk -f $REPORT.awk $REPORT.csv
#
# where the locale permits commas in numbers formatted with the sprintf specifications
# %'d and %'f. The user must supply a file $REPORT.awk that provides a BEGIN action
# that sets variables
#
#     pagelen   - the number of lines on a page
#     pagewidth - the number of print columns on a page
#     lenheader - the number of lines in the page header
#     lendetail - the number of lines printed for each record
#     lenfooter - the number of lines in the page footer
#
# and three functions
#
#     header() - print one or more header rows at top of page
#     detail() - print one or more detail rows; may use csv[1..n] array
#     footer() - print one or more footer rows at bottom of page
#
# as well as any auxiliary functions needed by those three functions. The program
# initializes variables
#
#     user      - Oracle user running program, passed by Banner environment
#     sid       - Oracle SID in which program runs, read from ENVIRON["ORACLE_SID"]
#     oneup     - Banner one-up number
#     today     - current date in MM/DD/YYYY format
#     pagenum   - current page number
#     nfields   - number of fields in current record
#
# that may be read by the user and also provides function
#
#     trititle(left, center, right) - return string with components at left margin,
#                                     centered in the string, and at the right margin
#
# that may be used in the header(), detail() or footer() functions. The program
# splits CSV records in which fields are separated by commas, fields surrounded by
# double-quotes may contain commas, and doubled double-quotes in a field surrounded
# by double-quotes represent a single embedded quote; fields may not contain newline
# characters. A sample $REPORT.awk file is shown below:
#
# # AZPSAWD Scholarship Awards
#
# # fieldname  len start end format
# #  1 fsyr      4    1    4
# #  2 camp      2    6    7
# #  3 pidm                  not on report
# #  4 id        9    9   17
# #  5 fname                 not on report
# #  6 mname                 not on report
# #  7 lname                 not on report
# #    name     30   19   48 substr(lname ", " fname " " mname,1,30)
# #  8 term      6   50   55
# #  9 subcode   2   57   58
# # 10 desg     10   60   69
# # 11 descrip  23   71   93
# # 12 detail   30   95  124
# # 13 amount    7  126  132 b99,990
#
# BEGIN { pagelen = 60; pagewidth = 132
#         lenheader = 5; lendetail = 1; lenfooter = 2 }
#
# function header() {
#     print trititle("AZPSAWD", "Scholarship Awards", sprintf("Page %d", pagenum))
#     print trititle("", sprintf("Fiscal Year = %s", csv[1]), "")
#     print ""
#     print "FSYR CP A-Number  Student Name                    Term  SC Desgn" \
#           "ation Description             Detail                          Amount"
#     print "---- -- --------- ------------------------------ ------ -- -----" \
#           "----- ----------------------- ------------------------------ -------" }
#
# function detail() {
#     printf "%-4.4s %-2.2s %-9.9s %-30.30s %-6.6s %-2.2s %-10.10s " \
#            "%-23.23s %-30.30s %'7d\n", csv[1], csv[2], csv[4], " \
#            "substr(csv[7] ", " csv[5] " " csv[6], 1, 30), csv[8], "\
#            "csv[9], csv[10], csv[11], csv[12], csv[13] }
#
# function footer() { print ""; print trititle(user "@" sid, oneup, today) }
#
# It is intended that CSVtoLIS is normally called by GZPCSVF to convert a CSV file to
# printed output. The following lines added to the end of gzpcsvf.shl call CSVtoLIS
# when it is requested:
#
# if [ -f $PROG_LC.awk ]
# then LIS=$H/$(basename $PROG_LC .shl)_${ONE_UP}.lis
#      LC_NUMERIC=en_US.utf8 gawk -f csvtolis.awk -f $PROG_LC.awk $CSV > $LIS
#      gurinso -n $ONE_UP -l $LIS -j $PROG -w $BANUID $BAN9UID/@${TARGETDB}
# fi

BEGIN { linenum = 0 # current line number - incremented after printing each line
        pagenum = 0 # current page number - incremented before start of each page
        user = toupper(ENVIRON["BAN9UID"]) # user name passed from Banner environment
        sid = ENVIRON["ORACLE_SID"] # Oracle SID, normally SLCC or something similar
        oneup = ENVIRON["ONE_UP"] # Banner one-up number
        "date +%m/%d/%Y" | getline today } # current date in MM/DD/YYYY format

function csvsplit(str, arr,     i,j,n,s,fs,qt) {
    # split comma-separated fields into arr; return number of fields in arr
    # fields surrounded by double-quotes may contain commas;
    #     doubled double-quotes represent a single embedded quote
    delete arr; s = "START"; n = 0; fs = ","; qt = "\""
    for (i = 1; i <= length(str); i++) {
        if (s == "START") {
            if (substr(str,i,1) == fs) { arr[++n] = "" }
            else if (substr(str,i,1) == qt) { j = i+1; s = "INQUOTES" }
            else { j = i; s = "INFIELD" } }
        else if (s == "INFIELD") {
            if (substr(str,i,1) == fs) {
                arr[++n] = substr(str,j,i-j); j = 0; s = "START" } }
        else if (s == "INQUOTES") {
            if (substr(str,i,1) == qt) { s = "MAYBEDOUBLE" } }
        else if (s == "MAYBEDOUBLE") {
            if (substr(str,i,1) == fs) {
                arr[++n] = substr(str,j,i-j-1)
                gsub(qt qt, qt, arr[n]); j = 0; s = "START" } } }
    if (s == "INFIELD" || s == "INQUOTES") { arr[++n] = substr(str,j) }
    else if (s == "MAYBEDOUBLE") {
        arr[++n] = substr(str,j,length(str)-j); gsub(qt qt, qt, arr[n]) }
    else if (s == "START") { arr[++n] = "" }
    return n }

function trititle(left, center, right,     gap1, gap2) { # assume sufficient space
    gap1 = int((pagewidth - length(center)) / 2) - length(left)
    gap2 = pagewidth - length(left) - length(center) - length(right) - gap1
    return left sprintf("%*s", gap1, "") center sprintf("%*s", gap2, "") right }

NR > 1 { nfields = csvsplit($0, csv); # print one record, with header/footer as needed
  if (pagelen - (linenum % pagelen) - lenfooter < lendetail) {
      while ((linenum + lenfooter) % pagelen != 0) { print ""; linenum++ }
      footer(); linenum += lenfooter }
  if (linenum % pagelen == 0) { pagenum++; header(); linenum += lenheader }
  detail(); linenum += lendetail
  if ((linenum + lenfooter) % pagelen == 0) { footer(); linenum += lenfooter } }

END { if (linenum % pagelen != 0) {                      # if not at top of page
          while ((linenum + lenfooter) % pagelen != 0) { # while not at bottom
              print ""; linenum++ }                      # skip to bottom
          footer() } }                                   # and print footer

Pages: 1 2 3