Export to CSV File With Fields Enclosed by Quotes

Posted March 29, 2020 by James Knicely, Vertica Field Chief Technologist

Helpful Tips message on post-it note

It’s easy to export table data to a CSV file using vsql. Although there isn’t a VSQL option to enclose each output field in quotes, you can accomplish that task with the Linux sed command.


[dbadmin@SE-Sandbox-26-node1 ~]$ vsql -c "SELECT * FROM csv_example;"
 c1 |  c2   |   c3
  1 | DATA1 | 1.2734
  2 | DATA2 | 23.232
(2 rows)

[dbadmin@SE-Sandbox-26-node1 ~]$ vsql -F ',' -P footer=off -Aqc "SELECT * FROM csv_example;" | sed -e 's/[\"]/\\&/g' -e 's/,/"&"/g' -e 's/^\|$/"/g'

Once the formatting looks good, you can redirect the output to your CSV file:

[dbadmin@SE-Sandbox-26-node1 ~]$ vsql -F ',' -P footer=off -Aqc "SELECT * FROM csv_example;" | sed -e 's/[\"]/\\&/g' -e 's/,/"&"/g' -e 's/^\|$/"/g' > csv_example.csv

[dbadmin@SE-Sandbox-26-node1 ~]$ cat csv_example.csv

Helpful Links:
Exporting Data Using VSQL
VSQL Field Separator

Have fun!