将带有日期的文件加载到netezza [英] loading files with dates into netezza

问题描述

我正在尝试将文本文件导入netezza.举个简单的例子，我使用的文件只有一列.

I'm trying to import text file into netezza. As simplifie example I use the file with one column.

文件

01/04/2011
01/01/2099
01/01/2011

我有桌子

create table test_data
(f date)

我尝试通过

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?

推荐答案

我知道我正在回答一个非常陈旧的问题，但是google将其放在"netezza datestyle"的顶部附近，因此它应该有正确的答案.

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.

您的数据文件和测试表DDL都很好. INSERT语句具有许多不需要的参数.最小语法为:

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
);

我刚刚使用示例文件测试了此语法，并且数据正确加载并排序(例如，它知道1/4/2011是4月1日，而不是1月4日).有效的datestyle值为'YMD'，'MDY'，' DMY"，"MONDY".默认值为"YMD"

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'

您的最高maxerrors(没有LOGDIR)意味着记录在错误时被静默丢弃. 除非您需要考虑不良的数据记录，否则将MAXERRORS设置得尽可能低(例如，1以说明可选的标题行)

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)

这篇关于将带有日期的文件加载到netezza的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！