Similar to COPY FROM is there anything available for COPY TO

Anand

Hi,

I was looking to create a CSV file of table output. I see multiple examples which can be executed along with vsql. However, I was looking for something similarly we use during COPY FROM command -
COPY myschema.my_table FROM LOCAL '/home/my_data.csv' DELIMITER E',' ABORT ON ERROR DIRECT;

I can execute this either from vsql command prompt or even other tools like DBVisualizer and read the external file "my_data.csv"
Similarly, is there a way to transfer the output of a select to a CSV file directly ?
Please do not mention about -
vsql -U dbadmin -w <> -F $',' -At -o tab_out.csv -c "SELECT * FROM my_schema.my_table;"

This can be executed from Linux command prompt. What I am looking for is to execute from vsql command prompt directly.

Thanks,
-Anand

Jim_Knicely

Why not use the EXPORT TO VERTICA feature? No need to to first create a csv file

See:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/CopyExportData/ExportingData.htm
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/EXPORTTOVERTICA.htm

Anand

Hi Jim,

My requirement is to generate CSV file.

For example,
SELECT emp_ID, emp_name FROM employee;

I want to direct the output to a CSV file.

Thanks.

Jim_Knicely

From the vsql prompt? You can use the \a (align), \f (field separator) and \o (output) meta-commands to create a csv file...

Example:

dbadmin=> \f ,
Field separator is ",".

dbadmin=> \a
Output format is unaligned.

dbadmin=> \o /home/dbadmin/test.csv

dbadmin=> select * from test;

dbadmin=> \o

dbadmin=> \! cat /home/dbadmin/test.csv
c1,c2,c3
1,VERTICAROCKS,2
(1 row)

See:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/ConnectingToVertica/vsql/Meta-Commands/MetaCommandReference.htm

Anand

ok. But this I can't use in DBVisualizer. Is there any other way I can use in DBVisualizer? Thanks.

Jim_Knicely

DBVisualizer has an "Exporting Query Results" feature that works like this:

@export on;
@export set filename="c:\test.csv";
SELECT * FROM test;
@export off;

See:
http://confluence.dbvis.com/display/UG100/Exporting+Query+Results