Trying to copy data from vertica table into csv using vsql

Question

Was trying to export data from vertica table to CSV file, but some data contain comma "," in values which pushed to other column.

 vsql -h server_address -p 5433 -U username -w password -F $',' -A -o sadumpfile_3.csv -c 
 "select full_name from company_name;" -P footer=off

Vertica table data and expected csv:

   full_name
   ----------
   Samsun pvt, ltd
   Apple inc
   abc, pvt ltd

Ouput sadumpfile_3.csv

   full_name
   -------------        ---------
   Samsunpvt              ltd
   Apple inc
   abc                    pvt ltd

Thanks in advance

score 1 · Answer 1 · answered Jan 29 '23 at 01:58

Default behaviour (I have the four environment variables VSQL_USER, VSQL_PASSWORD, VSQL_HOST and VSQL_DATABASE set):

marco ~/1/Vertica/supp $ vsql -c "select full_name from company_name"
    full_name    
-----------------
 Apple inc
 Samsun pvt, ltd
 abc, pvt ltd
(3 rows)

The simplest way to achieve what you were trying:

marco ~/1/Vertica/supp $ vsql -F ',' -A -c "select full_name from company_name;" -Pfooter
full_name
Apple inc
Samsun pvt, ltd
abc, pvt ltd

Note that the only commas are the ones already existing in the strings. If you only export one column, there's no field delimiter in the output.

I can only suppose that you want to have the output so that you can, for example, import it into Excel as CSV. If the field delimiter exists in a string, you would need to enclose the string with (usually double) quotes.

Vertica has a function that encloses a string with double quotes: QUOTE_IDENT():

marco ~/1/Vertica/supp $ vsql -F ',' -A \
 -c "select QUOTE_IDENT(full_name) AS full_name from company_name;" -Pfooter
full_name
"Apple inc"
"Samsun pvt, ltd"
"abc, pvt ltd"

Trying to copy data from vertica table into csv using vsql

1 Answers1