Hi experts..

When I use the unload to text file command in informix it exports all the data to |(pipe) delimited text with no field names on that.

When I want to import this data into another database I need it as a tab seperated or Excel or comma seperated with the field names on it.

To accomplish this currently what I'm doing is

1) I use unload to command to extract the data

2) I open this text file in Excel and using the wizard the Excel transforms pipe delimited data into proper columns

3) I got the field names of the table using the command

"info columns for table"

4) I will paste these column names into the first row of the Excel file

This is tedious when I need to do for several tables. I have to accomplish this job very often

Is there any way I can directly get the tab delimited data with the filed names on the first row?

Thanks for your help
Ahmad
Hong Kong

note: there are no db administrators to support me and I don't have complete contorl on the database but I can use the queries using the sql query interface provided by the owner of the db.

Hi,

Please test the following:
OUTPUT TO FILE xxxxxx
SELECT col1 || "|" || col2 || "|" || coln || "|"
FROM table...

Gustavo.

Gustavo:

SELECT col1 || "|" || col2 || "|" || coln || "|" ... will return the column header as "(expression)" which may not be wanted by Ahmad.

OUTPUT TO 'filename.unl'
SELECT f1,f2,f3 ...
will unload the data with headings, subject to condition that the matter should fit in 80 characters.

Ahmed:
I tried to write a Unix script which does this job may be to your satisfaction, has some limitations though. Certain datatypes like Byte,Text,Intervals are not intepreted correctly, but can be improved upon. Script accepts table name as a parameter and padding option. You need to edit it to specify the database name and the select clause once only. Sample script outputs header with first 10 rows from any table.

One of the highlight of this script is that it pads the data fields according to the length of the datatype gathered from the info for table syntax.

Regards,
Shriyan

Here is the source Unix Script:

#!/bin/sh

#by v.shriyan 15-Dec-2003.
#This script creates data unload of a single table and pads data fields with space according to the field length based on option, and prints field headers.
#First parameter is table name, second parameter is 1 or 0, 1=field padding needed 0=field padding not needed. (default is 0)

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
echo "info columns for $1" | dbaccess $DB | grep -iv "column name" > 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

rm $INPFL1 $INPFL2 a.awk b.awk
echo ""
exit 0