Export to CSV File With Fields Enclosed by Quotes

Posted March 29, 2020 by Jim 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.

Example:

[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'
"c1","c2","c3"
"1","DATA1","1.2734"
"2","DATA2","23.232"

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
"c1","c2","c3"
"1","DATA1","1.2734"
"2","DATA2","23.232"

Helpful Links:
Exporting Data Using VSQL
VSQL Field Separator

Have fun!