Export Netezza Data with Quoted Values into Flat File and Example

In general, quoted values are values which are enclosed in single or double quotes. Usually, quoted values files are system generated where each and every fields in flat files is either enclosed in SINGLE or DOUBLE quotation mark. In this article, we will check how to export Netezza data with quoted values into flat file such as CSV format.

Quoted Value File Overview

As mentioned in the previous section, quoted values files contains the values that are enclosed either in SINGLE or DOUBLE quotation mark. Generally, values are enclosed in quotation mark in case there is a embedded delimiter. For example, comma separated values file can have comma embedded within its values. One example of such values is address field. Below is the sample example of the embedded comma.

“1”, “John”, “1, baker st, London”
“2”, “Ram”, “100, 80ft road, Bangalore”

Export Netezza Data with Quoted Values into Flat File

So, the question is, if you are asked to export Netezza data with double quoted values into flat file, what would be our approach?

There are couple of options available.

You can use,

• Netezza external table
• IBM provided script – nz_select_quoted_data

Now let us see both approach with an example.

Export Netezza Data with Quoted Values into Flat File using External Tables

You can unload data from a user table into an external table and load data from an external table into a user table by using the text-delimited format.

Read:

• Netezza External tables and Examples
• IBM Netezza Unload Table using External Tables and Examples

Netezza external tables does not support QUOTEDVALUE option for unloading data.You have to use the quote_ident in the select statement to enclose column values in double or single quotes.

Below is the working example:

CREATE EXTERNAL TABLE '/export/home/nz/quoted_value_file.csv'
USING
(
 IncludeHeader 
 ENCODING 'internal'
 ESCAPECHAR '\'
 DELIMITER ','
)
AS 
SELECT *
FROM (
 select quote_ident(ID),
 quote_ident(NAME) 
 from PATIENT_1
 ) a;

Now verify the created flat for double quotation mark:

[nz@netezza ~]$ more quoted_value_file.csv 
"5","EFG" 
"1","ABd" 
"3","CDr" 
"7","BBB" 
"4","DEF" 
"2","BCa" 
"6","AAA" 
[nz@netezza ~]$

Export Netezza Data with Quoted Values into Flat File using nz_select_quoted_data

You can export Netezza data with quotes values into flat file using IBM provided nz_select_quoted_data. Usually, the script is available at /nz/support/bin location.

Below is the usage of script and examples:

nz_select_quoted_data <database> <table>;

[nz@netezza bin]$ ./nz_select_quoted_data training PATIENT_1 
"4","DEF" 
"2","BCa" 
"6","AAA" 
"5","EFG" 
"1","ABd" 
"3","CDr" 
"7","BBB" 
[nz@netezza bin]$

If you want to save the output to the file, simply redirect the results to flat file.

Hope this helps 🙂