verticavsql

How to exclude the total row information from VSQL output

I am using VSQL to extract data from a table in CSV format using this command:

vsql -h [host_address] -d [db_name] -u [user_name] -w [password] -A -F , -t -f script_to_extract_data.sql -o output.csv

However, it outputs column headers, data rows AND an extra row that indicates the total number of rows in the table like this:

Geography,Product,Campaign,VariableName,Outlet,Creative,Period,VariableValue
Geo_00000,Product,BABY,web_sales,Total,Total,2016-10-03,24.06
Geo_00000,Product,BABY,web_sales,Total,Total,2016-10-08,67.17
Geo_00000,Product,BABY,web_sales,Total,Total,2016-10-17,404.67
(3 rows)

If I exclude the -t option, it'll output just the data like this:

Geo_00000,Product,BABY,web_sales,Total,Total,2016-10-03,24.06
Geo_00000,Product,BABY,web_sales,Total,Total,2016-10-08,67.17
Geo_00000,Product,BABY,web_sales,Total,Total,2016-10-17,404.67

I would like the column headers AND the data, but not the total row number like this:

Geography,Product,Campaign,VariableName,Outlet,Creative,Period,VariableValue
Geo_00000,Product,BABY,web_sales,Total,Total,2016-10-03,24.06
Geo_00000,Product,BABY,web_sales,Total,Total,2016-10-08,67.17
Geo_00000,Product,BABY,web_sales,Total,Total,2016-10-17,404.67

From reading through VSQL commandline options, I don't think I have a way to restrict the total number of rows not show?? Anyone who is experienced with using VSQL via commandline could help me out, I would greatly appreciate the help. Thank you!

Solution

  • There is no documented way to do this.

    You could just inject a select into your script though to print out the header while leaving tuples-only on.

    \t
\a
\f ,
\o output.csv
select 'Geography', 'Product', 'Campaign', 'VariableName', 'Outlet', 'Creative', 'Period', 'VariableValue';

select Geography, Product, Campaign, VariableName, Outlet, Creative, Period, VariableValue
from mytable;
\o

    And I guess if it really, really bothers you to list the fields twice, you could use a variable. 

    \set fields Geography,Product,Campaign,VariableName,Outlet,Creative,Period,VariableValue

    Then reference :fields in both queries (just use ' around it for the header list). In this case, the header list would just need to be a string, and the delimiter would have to be a , since it would also be used in sql. Just a thought.