Netezza export to csv with double quotes

Question

I want to export some data from a Netezza table into a csv file.

I want the file to have a header, be comma delimited, and have double quotes around all non numeric fields.

I'm using this syntax:

CREATE EXTERNAL TABLE 'F:\My Documents\noname.txt'
USING
(
    IncludeHeader 
    DELIMITER ','
    QUOTEDVALUE 'DOUBLE'
    REQUIREQUOTES 'TRUE'
    ENCODING 'internal'
    REMOTESOURCE 'ODBC'
    ESCAPECHAR '\'
    NULLVALUE ''
)
AS 
SELECT *
  FROM MYTABLE

However the output shows no sign of double quotes. A sample output row looks like:

04_0001_17.jpg,fr50139,Y,32

Any ideas how to obtain something like this?

"04_0001_17.jpg","fr50139","Y",32

NzGuy NzGuy · Accepted Answer · 2016-10-08T15:28:48

As of 7.2.X Netezza dosen't support QUOTEDVALUE during unload . You need to enclose double quotes individual columns or all columns and make this as part of select statement.

CREATE EXTERNAL TABLE 'C:\noname.txt'
USING
(
    IncludeHeader 
    ENCODING 'internal'
    REMOTESOURCE 'ODBC'
    ESCAPECHAR '\'
    DELIMITER ','

)
AS 

SELECT *
FROM (
    SELECT quote_ident(prod_id) AS prod_id
        ,quote_ident(CUST_ID) AS CUST_ID
    FROM TEST..IMP_SALES_20160223 limit 10
    ) a;

Extra select is included to pickup the column names .

Output :

PROD_ID,CUST_ID
"13","1660"
"13","1762"
"13","1948"
"13","2380"
"13","5590"
"13","8540"
"13","9076"
"13","35834"
"13","524"
"13","188"

Netezza export to csv with double quotes

2 Answers