pythonshellcsvnetezzanzsql

Export specific query data to CSV file from netezza database

I wanted to export data from netezza database to CSV file.

The format for the data will be:

col1,col2,col3

OR:

"col1","col2","col3"

I am using the query:

CREATE EXTERNAL TABLE 'H:\\test.csv' USING ( DELIMITER ',' REMOTESOURCE 'ODBC' ) AS
SELECT * FROM TEST_TABLE

Above query is not working when col3 has the field including comma as it is saying to export it using escape char as '\'.

Example table:

A | B | C
a | b | Germany, NA

I tried that too, but I am getting as output in csv:

a,b,Germany\, NA

or by adding quotes to each column I am getting output:

"a","b","Germany\, NA"

Here, I am getting extra '\' character in field. I am looking for the solution to resolve it using the nzsql or external table query method or writing own script methods only.

My expected output without changing field data:

"a","b","Germany, NA"

Solution

  • Desired output can be achieved by using nzsql command line statement.The only limitation of this approach is max file will limit to 2 GB .Here is the link from IBM KB Sending query results to an output file

    [nz@netezza ~]$ nzsql -d test -A -t -c  "select quote_ident(col1),quote_ident(col2), quote_ident(col3) from test" -o '/nzscratch/test.csv'

    Output :

    [nz@netezza ~]$ cat /nzscratch/test.csv
"A"|"B"|"C"
a|b|"Germany, NA"