Skip to main content

Similar to COPY FROM is there anything available for COPY TO

  • September 17, 2018
  • 5 replies
  • 10 views
Anand
Forum|alt.badge.img+1
  • Anand
  • Participating Frequently

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

    5 replies

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • September 17, 2018

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

    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
    Forum|alt.badge.img+1
    • Anand
    • Author
    • Participating Frequently
    • September 17, 2018

    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
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • September 17, 2018

    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
    Forum|alt.badge.img+1
    • Anand
    • Author
    • Participating Frequently
    • September 17, 2018

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

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • September 17, 2018

    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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement