Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Question Unanswered: Unload to file with field headers

    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Unload to file with field headers

    Originally posted by faheem786
    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.

  3. #3
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    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
    Last edited by vpshriyan; 12-15-03 at 08:02.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •