0

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"

RohanB
  • 294
  • 2
  • 5
  • 19

2 Answers2

2

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"
NzGuy
  • 382
  • 3
  • 14
  • is there a way to do it if the file size is more than 2GB? – RohanB Mar 13 '18 at 18:23
  • You can export the table using external table with delimiter other than ‘,’ and once the export is completed replace the delimiter with the original one . – NzGuy Mar 14 '18 at 00:00
1

You can use ESCAPECHAR '@' to use an '@' rather than a backslash only a backlash as an escape character. The problem is that a csv file needs a way to say "this comma is data, not a delimiter" - which is what the escape char is used for. The code that consumes your csv file should be configured to expect that same escape character.

This is one of the quirks you run into when using a loosely defined data format like csv. As long as you define a delimiter and an escape character that the producer and consumer both expect - and you're using the same character encoding - you'll be fine.

This isn't changing your data, it's just making the interchange format more specific. If you provide some detail on what is consuming the data, I can update this answer to provide detail on informing the consumer that backslashes are used as an escape character.

-- EDIT -- Apparently some implementations of netezza only support backslash as a delimiter.

IBM References:

  • Actually, i am not expecting '@' nor any other character as this is changing my data. I want output as "a","b","Germany, NA" – RohanB Mar 12 '18 at 17:46
  • 1
    It is not changing your data, it is *encoding it* as a comma-separated value file. Without an escape character, the consumer wouldn't know if a comma was separating fields or part of the data *within* a field –  Mar 12 '18 at 18:08
  • Yes, i agree that it works when we inform the consumer that backslashes are used as an escape character. but i am looking for the way to implement it without informing the escape char. I am adding double quotes for each field then it should not escape ',' character. Is there anyway which i can get the solution without getting escape char? – RohanB Mar 12 '18 at 19:10
  • 1
    I see that you tried the [RequireQuotes](https://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.load.doc/r_load_requirequotes.html) option, but it still emitted an escape char. I'm not sure what else you can try! Apologies –  Mar 12 '18 at 19:39