Netezza CSV负载 [英] Netezza CSV load

问题描述

我们是netezza的新手，目前正在尝试通过脚本将CSV文件导入netezza.

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.

理想情况下，我们希望 1)创建我们想要的表结构 2)导入CSV并将CSV列名称映射到我们想要的结构

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

全部导入.

此代码带回错误:错误的输入行数达到最大值

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

Nzload可以走吗?如果是这样，最好的做法是什么?

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.

我们如何指定呢?

推荐答案

通常来说，当记录分隔符出现在数据中时，您需要确保它以反斜杠转义.但是，在您的情况下，您可以使用允许在列数据周围使用双引号的选项来加载CSV数据(Netezza通常不需要).

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

更改您的代码以包括以下外部表选项:QUOTEDVALUE'DOUBLE'

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

这篇关于Netezza CSV负载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！