We are new to netezza and currently trying to import a CSV file, via script, into netezza.
What is the best way to get this working.
Ideally we would like to 1) Create the table structure we want 2) Import the CSV and map the CSV column names to the stucture we want
all in one import.
This code brings back the error: count of bad input rows reached maximum
INSERT INTO DBO.TABLE
SELECT * FROM
EXTERNAL 'C:\\log\\FILE.csv'
USING
(
DELIMITER ','
MAXROWS 300
logdir 'C:\log'
SKIPROWS 1
Y2BASE 2000
ENCODING 'internal'
REMOTESOURCE 'ODBC'
ESCAPECHAR '\'
)
Importing by the wizard works but we can not automate this
is Nzload the way to go? If so whats the best practise with this?
--EDIT We have got this issue down to commas being in some of the fields we are importing and no text qualifier being specified in the import script.
How do we specify this?
Answer
Generally speaking, when your record delimiter present in your data you will want to make sure it is escaped with a backslash. However, in your case you can load CSV data using an option to allow double quotes around column data (which Netezza does not normally require).
Alter your code to include this external table option: QUOTEDVALUE 'DOUBLE'
INSERT INTO DBO.TABLE
SELECT * FROM
EXTERNAL 'C:\\log\\FILE.csv'
USING
(
DELIMITER ','
MAXROWS 300
logdir 'C:\log'
SKIPROWS 1
Y2BASE 2000
ENCODING 'internal'
REMOTESOURCE 'ODBC'
ESCAPECHAR '\'
QUOTEDVALUE 'DOUBLE'
)