loading files with dates into netezza

Question

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?

Answer 1

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?

Answer 2

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)

Answer 3

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