Exporting Data from the Database Tables to the CSV Format

You can export data from the database tables to the CSV format by using VSQL and its output format options. These options can be set either from within an interactive vsql session, or through command-line arguments to the vsql command (making the export process suitable for automation through scripting). After setting VSQL options so it outputs the data in a format your target system can read, you can run a query and capture the result in a CSV file. The procedure mentioned here is for some of the VSQL output format options. To know more about the available output format options, see the Database Documentation.

To export data from the database tables:

Launch a terminal session and log in to a database node.
Change to the following directory:
```
cd /opt/vertica/bin/
```
Log in as a dbadmin:
```
su dbadmin
```
(Conditional) To create an output file directly from the command line by passing parameters to vsql, execute the following commands:
```
vsql -U username -F ',' -At -o <outputfile_name> -c "SELECT * FROM <table_name>;"
```
where
-F is used to set the field separator. In this case, because the output is a CSV file, the field separator is ','.
-At is used to disable the padding and show only the table's tuples in the output file. If you want to show the table headings and the row counts, do not specify t.
-o is used to send the output to the output file.
<outputfile_name> is the output file name you need to provide to save the data to, for example, test.csv. Ensure that you have write permissions to the output file.
-c is used to run the SQL query and
<table_name> is the table whose data you want to export, for example, default_secops_adm.events.
```
[password prompt]
```
(Conditional) To create an output file within an interactive vsql session, do the following.
1. Log in to vsql and specify the password when prompted.
```
vsql
```
```
[password prompt]
```
2. Execute the following command to disable padding so as to align the output:
```
\a
```
3. (Optional) Execute the following command to export only the table tuples to the output file:
```
\t
```
4. Execute the following command to set the field separator to export data in the CSV format:
```
\pset fieldsep ','
```
5. Execute the following command to save the output to a file:
```
\o <outputfile_name>
```
  where <outputfile_name> is the output file name you need to provide to save the data to, for example, test.csv. Ensure that you have write permissions to the output file.
6. Execute the following command to export data from a database table to the output file you specified in the previous step:
```
select * from <table_name>;
```
  where <table_name> is the table whose data you want to export, for example, default_secops_adm.events.
7. Execute the following command to view the data in the output file:
```
\! cat <outputfile_name>
```