如果 OPTIONALLY ENCLOSED 处于打开状态，SQL 加载器会跳过由 \t 包围的空白字段

发布于 2025-01-11 05:51:25 字数 1616 浏览 0 评论 0原文

我正在尝试使用 SQL*Loader 将数据从文件加载到 Oracle DB

：版本 12.1.0.2.0

表：

CREATE TABLE TEST_TABLE (
    ID NUMBER(38) DEFAULT NULL,
    X VARCHAR2(4000) DEFAULT NULL,
    NUM NUMBER(38) DEFAULT NULL,
    Y VARCHAR2(4000) DEFAULT NULL
);

数据 testdata.txt：

ID  X   NUM Y    
1   x1  0   y1
2   x2  0   "y2
."
3       0   y3
4       0   "y4
."
5   x5  0   y5

并用制表符替换为 \t：

ID\tX\tNUM\tY    
1\tx1\t0\ty1
2\tx2\t0\t"y2
."
3\t \t0\ty3
4\t \t0\t"y4
."
5\tx5\t0\ty5

因此重要的是第 3 行和第 4 行（计数标头为 0) 包含一个简单的空格字段，第 2 行和第 4 行包含一个带引号的字段，其中包含行分隔符 \n

控制文件：

OPTIONS (SKIP=1)LOAD DATA
CHARACTERSET we8iso8859p1
INFILE 'testdata.txt' "STR '\n'"
PRESERVE BLANKS
INTO TABLE TEST_TABLE
FIELDS CSV WITH EMBEDDED TERMINATED BY "\t" OPTIONALLY ENCLOSED BY '"'
(
    ID,
    X,
    NUM,
    Y
)

结果：

Record 3: Rejected - Error on table TEST_TABLE, column Y.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table TEST_TABLE, column Y.
Column not found before end of logical record (use TRAILING NULLCOLS)

我尝试过的：

使用 |因为字段分隔符可以正确加载数据 - 不幸的是我无法控制数据。

删除 NUM 列可以使问题消失 - 这不是一个选项。

使用 TRAILING NULLCOLS 隐藏错误并加载错误数据

不使用 PRESERVE BLANKS 不能解决问题，还会破坏数据

在控制文件中用 X'09' 替换 \t 不会改变任何内容

不使用 CSV WITH EMBEDDED 或 OPTIONALLY ENCLOSED 会使问题失败在引用的字段上

使用控制文件中的数据类型不会执行任何

操作使用 NULLIF X=BLANKS 不能解决问题，而且如果解决了，还会破坏数据。

问题：

如何使 SQL Loader 读取数据文件中仅包含空白的字段，其中字段由 TAB 分隔，并且可以选择用 '"' 括起来

原文

I am trying to load data from a file to Oracle DB using

SQL*Loader: Release 12.1.0.2.0

Table :

CREATE TABLE TEST_TABLE (
    ID NUMBER(38) DEFAULT NULL,
    X VARCHAR2(4000) DEFAULT NULL,
    NUM NUMBER(38) DEFAULT NULL,
    Y VARCHAR2(4000) DEFAULT NULL
);

Data testdata.txt:

ID  X   NUM Y    
1   x1  0   y1
2   x2  0   "y2
."
3       0   y3
4       0   "y4
."
5   x5  0   y5

and written with tabs replaced with \t:

ID\tX\tNUM\tY    
1\tx1\t0\ty1
2\tx2\t0\t"y2
."
3\t \t0\ty3
4\t \t0\t"y4
."
5\tx5\t0\ty5

So it is important that lines 3 and 4 (counting header as 0) contain a field that is simply a SPACE and lines 2 and 4 contain a quoted field containing the line separator \n

Control file:

OPTIONS (SKIP=1)LOAD DATA
CHARACTERSET we8iso8859p1
INFILE 'testdata.txt' "STR '\n'"
PRESERVE BLANKS
INTO TABLE TEST_TABLE
FIELDS CSV WITH EMBEDDED TERMINATED BY "\t" OPTIONALLY ENCLOSED BY '"'
(
    ID,
    X,
    NUM,
    Y
)

Result:

Record 3: Rejected - Error on table TEST_TABLE, column Y.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table TEST_TABLE, column Y.
Column not found before end of logical record (use TRAILING NULLCOLS)

What I tried:

Using | as field separator makes the data load correctly - unfortunately I don't control the data.

Removing the NUM column makes the problem go away - this is not an option.

Using TRAILING NULLCOLS hides the error and loads faulty data

Not using PRESERVE BLANKS does not solve the problem and also ruins data

Replacing \t with X'09' in the control file changes nothing

Not using CSV WITH EMBEDDED or OPTIONALLY ENCLOSED moves the problem to failing on the quoted fields

Using data types in controlfile does nothing

Using NULLIF X=BLANKS does not solve the problem and would ruin data if it did.

Question:

How do I make SQL Loader read fields containing only BLANKS in a data file with fields separated by TAB and optionally enclosed by '"'

分享到QQ