SQL output Csv
mike poyser
Does any one know the command line options to output an sql ouput into a comma seperated file from the command line using ingres. at present the files are outputted formatted with Pipes | as this seems to be the default ouput.
thanks for your help in advance
Mike Poyser
Legal Disclaimer:-
Internet communications are not secure and therefore Oxford, Swindon & Gloucester Co-Op does not accept legal responsibility for the contents of this message. Any views or opinions presented are solely those of the author and do not necessarily represent those of the above Organisation unless otherwise specifically stated.
Gale, Peter NESL-IT
You can't do this straight from the OS command line but the SQL copy
statement allows you to output data in just about any format. To do comma
separation the copy statement would look like this
COPY <table> (
<col1> = c0comma,
<col2> = c0comma,
:
:
<coln> = c0nl)
INTO '<filename>'
The nl on the last column is a newline
Peter Gale
+44 (0)191 210 2658
peter...@npowernorthern.com
Pinn, Hans - D05.2.3
Use simple command to convert:
grep '|' sql.out > /tmp/tmp.out
sed s/'|'/';'/g /tmp/tmp.out > sql.csv
It fails only in that cases, where the pipe is also a character within
a field. For my own, a have written a small program, which convert IngresII-
Ascii-Output-Files into csv-Files, additionally makes a change to mysql
Dateformat. If you like, I send you the source.
Hans
-----Ursprüngliche Nachricht-----
Von: mike poyser [mailto:mike....@osg.coop]
Gesendet: Donnerstag, 19. September 2002 11:48
An: info-...@ams.org
Betreff: SQL output Csv
Karl & Betty Schendel
>
>Does any one know the command line options to output an sql ouput into a comma seperated file from the command line using ingres. at present the files are outputted formatted with Pipes | as this seems to be the default ouput.
The COPY statement as suggested by Peter Gale is the best and most
flexible solution. You can however change the | separator by using
the -v option to the sql command, e.g. sql -v',' dbname.
You don't really get a "csv" file this way, though, as sql outputs a
leading separator char on each line.
--
Karl R. Schendel, Jr. sche...@kbcomputer.com
K/B Computer Associates www.kbcomputer.com
Ingres, Unix, VMS Consulting and Training
Ian Millard
>
>Does any one know the command line options to output an sql ouput into a comma >seperated file from the command line using ingres. at present the files are outputted >formatted with Pipes | as this seems to be the default ouput.
Below might help if you're in a unix environment. I've found it useful for outputting the results of temp views created for the purpose, and it takes off the leading and trailing blanks - also you can specifiy any delimiter you like, or even a multi-character delimiter (that last thing perhaps not that useful but....). NB Not all my own work as it was inspired by a posting a couple of years ago. Also, I've only used it on Ingres 6.4.
Ian
#! /bin/sh
#set -x
# sqlx - Utility to download data from database tables specifying field
# delimiter uses whole tables or views. Takes out leading and trailing
# blanks from fields.
# Assumes user has select permission on the table/view
#
# Ian Millard - 2 Oct 2000
if [ $# -ne 4 ]
then
echo "Usage: $0 <userid> <database> <table/view name> <delimiter>"
exit 1
fi
USER=-u${1}
DBASE=$2
TABLE=$3
DL=$4
SQL_OUT=./$3sql.out
SQL_ERR=./$3sql.err
COLUMNS="t.*"
sql iidbdb << ! > $SQL_OUT
set lockmode session where readlock = nolock \g
select name
from iidatabase
where name = '$DBASE'
\g
!
db_found=`grep $DBASE $SQL_OUT | wc -l`
if [ "$db_found" -ne 1 ]
then
echo "$DBASE is not a valid ingres database"
exit 1
fi
sql $DBASE << ! > $SQL_OUT
set lockmode session where readlock = nolock \g
select table_name
from iitables
where table_name = '$TABLE'
\g
!
table_ok=`grep $TABLE $SQL_OUT | wc -l`
if [ "$table_ok" -ne 1 ]
then
echo "$TABLE not an existing table on database $DBASE"
exit 1
fi
rm -f $SQL_OUT $SQL_ERR
if ! sql $USER $DBASE <<! | nawk -F"|" '
{
if (substr($0,2,1) == "_");
else
{
for(i=3;i<=NF;i++)
if(length($i)>0 )
printf("%s ",$i)
}
{print "\n"}
}'|\
nawk -v DL="$DL" '{ gsub(/\$/,"@") }
{ gsub(/\^( *)/,"") }
{ gsub(/ ( *)/," ") }
{ gsub(/ \@\$/,"") }
{ gsub(/ /,DL) }
{print $0 }' |\
sed -e '/^$/d' -e "s/^/$DL/" -e "s/\( *\)$DL/$DL/g" -e "s/$DL\( *\)/$DL/g" -e s/"$DL"\$// -e "s/^$DL//" | \
tee ${TABLE}.out 1> /dev/null 2> ${TABLE}.err
set lockmode session where readlock = nolock \g
select ' ' as _,
$COLUMNS
from $TABLE
t
\p\g
!
then
echo "Data extraction unsuccessful."
exit 1
else
if ! [ -s ${TABLE}.out ]
then
echo "${TABLE}.out empty. Possible error in outputting data."
else
echo "Data extraction successful. (Probably)"
fi
fi
----
This email was sent from an email address under the control of Babtie Group Ltd, a company registered in Scotland, registration number SC141100 and having its registered office at 95 Bothwell Street, Glasgow - known hereafter as the Company.
Privileged/confidential information may be contained in this email. If you have received this email in error, please destroy all copies in your possession or control and notify the sender by reply email. Recipients may not forward, disclose or copy this email to any third party without the prior consent of the Company. The Company does not accept liability for any changes made to this email after it was sent.
Opinions, conclusions and other information in this message that do not relate to the official business of the Company are neither given nor endorsed by the Company.
The Company monitors email sent to or from email addresses under its control