Expert: Jean-Pierre Zuate Date: 6/2/2007 Subject: Ingres to flat files in unix
Question Hi,
I am not aware about ingres other than knowing that it is an database, but as I want to get few tables from Ingres exported to comma separated flat files, could you please let me know whether it is possible or not and if yes how can I do it using unix scripts? as I will be using those flat files in unix environment i would like to have a methos which can be implemented using unix.
Thanks in advance
Vivek
Answer Hello,
Of course you can interact via shell script with your data. For that, you must use the command line : sql dbname
you can run the "sql" command like following :
echo "query \g" | sql yourdatabase
OR
sql yourdatabase < yourSQLfile > yourSQLresult
OR
sql yourdatabase << %END% > yourSQLresult
QUERY
\g
%END%
"\g" stand for "go" (ie run the query)
You have also an other flag (usefull in interactive mode) : "\p" (stand for "print [the query to the output])
If you want to export data from table to file, use the SQL command : copy table yourtable (column description) into 'file'
If you want to import data from file to table, use the SQL command : copy table yourtable (column descritpion) from 'file'
To generate the "copy table" for a table you can use the following command :
copydb -c yourdatabase yourtable
You will find after that 2 files : copy.in and copy.out
copy.out containt the SQL order to export data into a flat file, but with fixed lengh columns. "-c" stand for "ASCII (or printable) format" rather than "binary format".
See the SQL documentation for exact syntax of copy table or sql or copydb.
Here is a small example on how to export data with fields separated with comma. Hope this help
1/ Create the table
create table toto (id integer, desc varchar(50))
2/ Populate the table
insert into toto values(1,'One');
insert into toto values(2,'Two');
insert into toto values(3,'Three');
3/ Generate the sql code to export data
copydb -c demodb toto
The output is :
INGRES COPYDB Copyright 2007 Ingres Corporation
Unload directory is 'jp'.
Reload directory is 'jp'.
There is one table owned by user 'jp'.
The copy.out file contain (at least) the following :
copy toto(
id= c0tab with null(']^NULL^['),
desc= varchar(0)nl with null(']^NULL^['),
nl= d0nl)
into 'jp/toto.jp'
Just add the word "comma" instead of tab and nl separators :
copy toto(
id= c0comma with null(']^NULL^['),
desc= varchar(0)nl with null(']^NULL^['),
nl= d0nl)
into 'toto.jp'
The content of the file 'toto.jp' is :
1, 3One
2, 3Two
3, 5Three
If you prefer having something like "1, One,\c" in the result replace nl by comma.
See on line Ingres documentation (http://docs.ingres.com/) :
- SQL Reference Guide (for COPY TABLE)
- Command Reference Guide (for sql and copydb)
