How to print headers & space pad the fields in an unload data file?

How to print headers & space pad the fields in an unload data file?

How to get field names in your unloaded data file? How to pad field data with spaces so that they look symmetrical, pleasing and in easy to read format? You must have wanted to these in Informix environment. However, these facilities are missing, and that means these has to be created at user's front.

A SQL syntax:
output to pipe "pg" select tabname, owner, partnum, tabid, rowsize, ncols, nindexes from systables where tabid=100 ; will show the data with headings horizontally, however, subject to condition that the printed data matter should fit within 80 characters.

output to pipe "pg" select tabname, owner, partnum, tabid, rowsize, ncols, nindexes, nrows from systables where tabid=100 ; will show the data with headings in vertical format.

The Unix script below gathers column headings, data type and field length using info columns for <tabname> SQL statement, prints headers and pads data with space if padding option is chosen.

Make changes in the script for database name and select statement (it selects first 10 rows) and make the file executable using chmod 755 headNpad.sh

Execute examples:
headNpad.sh systables #header is printed, no space padding.
headNpad.sh systables 1 #header is printed, space padding is done.
headNpad.sh customer 1 #header is printed, space padding is done.

Source listing:

#!/bin/sh

#v.shriyan 13-Dec-2003
#This script creates data unload of a table and pads data fields with space according to the field length via an option, and prints field headers.

if test "x$1" = "x"
then
echo pass table name as a parameter.
exit 1
fi

DB="testdb"

echo "unload to '1.x' delimiter '|' select first 10 * from $1" | dbaccess $DB 2>/dev/null
echo "info columns for $1" | dbaccess $DB | grep -iv "column name" 2>/dev/null > 2.x

echo 'BEGIN{FS=" "}' > a.awk
echo "{" >> a.awk

if [ "9$2" -eq "9" ]; then
let PAD=0
else
let PAD=$2
fi

DELI="|"
INPFL1="1.x"
INPFL2="2.x"

echo "Working, Please wait..."
cat /dev/null > 3.x
cat ${INPFL2} |&
while read -p
do
COL=`echo $REPLY | cut -d" " -f1`
COL2=`echo $REPLY | cut -d" " -f2`
if test "x$COL" = "x"
then
continue
fi
LEN2=0
echo $COL2 | grep "," 1> /dev/null
if [ $? -eq 0 ]; then
LEN=`echo $COL2 | cut -d"(" -f2 | cut -d"," -f1`
LEN2=`echo $COL2 | cut -d")" -f1 | cut -d"," -f2`
let LEN2=`expr $LEN2+1`
else
LEN=`echo $COL2 | cut -d")" -f1 | cut -d"(" -f2`
echo $COL2 | egrep -i "date|serial|integer" 1> /dev/null
if [ $? -eq 0 ]; then
LEN=10
fi
if test "x$COL2" = "xsmallint"
then
LEN=5
fi
fi
let LEN=`expr $LEN+$LEN2`

CLEN=`expr length $COL`
if [ $LEN -gt $CLEN ]; then
echo $COL"|"$LEN >> 3.x
let CLEN=`expr $LEN-$CLEN`
else
echo $COL"|"$CLEN >> 3.x
let CLEN=`expr $CLEN-$CLEN`
fi
let CLEN=`expr $CLEN+1`
if [ $PAD -eq 1 ]; then
echo "printf(\"$COL%-${CLEN}s$DELI\",\$1);" >> a.awk
else
echo "printf(\"$COL$DELI\",\$1);" >> a.awk
fi
done

echo 'print ""' >> a.awk
echo "}" >> a.awk

echo " " | awk -f a.awk > $1.unl

rm $INPFL2
INPFL2="3.x"

if [ $PAD -eq 1 ]; then
echo 'BEGIN{FS="|"}' > b.awk
echo "{" >> b.awk

cat ${INPFL2} |&
while read -p
do
let NO=$NO+1
COL=`echo $REPLY | cut -d"|" -f1`
LEN=`echo $REPLY | cut -d"|" -f2`
let LEN=`expr $LEN+1`
echo "printf(\"%-${LEN}s${DELI}\",\$${NO});" >> b.awk
done

echo 'print ""' >> b.awk
echo "}" >> b.awk

cat $INPFL1 | awk -f b.awk >> $1.unl
else
cat $INPFL1 >> $1.unl
fi
echo ""

rm $INPFL1 $INPFL2 a.awk b.awk
echo "Data file $1.unl is ready."
exit 0