I'm trying to import text file into netezza. As simplifie example I use the file with one column.
File
01/04/2011
01/01/2099
01/01/2011
I have the table
create table test_data
(f date)
I try to load data by
insert into test_data
select * from
EXTERNAL 'C:\\Temp\\dt.txt'
USING
(
DATESTYLE 'DMY'
DATEDELIM '/'
MAXERRORS 100000000000
Y2BASE 2000
ENCODING 'internal'
REMOTESOURCE 'ODBC'
delimiter '\t'
CRINSTRING TRUE
TRUNCSTRING TRUE
CTRLCHARS TRUE
IGNOREZERO TRUE
-- ESCAPECHAR '\'
)
and I have no data in the table after insert. Do you have any ideas about reason?
I know I'm answering a very stale question, but google puts this near the top of "netezza datestyle" so it should have a correct answer on it.
Your data file and test table DDL are both fine. The INSERT statement has a lot of parameters that you don't need. The minimal syntax is:
insert into test_data
select * from
EXTERNAL 'C:\\Temp\\dt.txt'
USING
(
DATESTYLE 'DMY'
DATEDELIM '/'
REMOTESOURCE ODBC -- note that you had this as a quoted string. Either should work
LOGDIR 'C:\\Temp' -- as someone pointed out, this will give you log files to troubleshoot
);
I just tested this syntax with your example file, and the data loads and sorts correctly (e.g. it knows that 1/4/2011 is April 1 not Jan 4) Valid datestyle values are ‘YMD’, ‘MDY’, ‘DMY’, ‘MONDY’. The default is 'YMD'
Your high maxerrors (without a LOGDIR) means that records are silently discarded on error.
Unless you need to account for bad data records, set MAXERRORS as low as possible (e.g. 1, to account for an optional header row)
The syntax of your insert looks fine from a remotesource. The nzlog generated from the insert statement should be viewable. Can you paste the error you're receiving from the log?
no need to use the using part in the query
correct example is:
insert into table_name select * from external C:\temp\dt.txt
this should load the data in the table without any issues
