使用 SQLLDR 加载分隔数据时跳过数据字段 [英] Skipping data fields while loading delimited data using SQLLDR

问题描述

考虑以下场景:

表T1(f1, f2, f3);

数据文件:

a|b|c|d
w|x|y|z

我想跳过第二个字段加载此数据，如下所示:

I want to load this data skipping the second field as follow:

f1    f2    f3 
---   ---   ---
a     d     c
w     z     y

非常感谢您在构建控制文件以实现这一目标时的帮助或任何指示.

Would really appreciate your help or any pointer in constructing the control file to achieve this.

推荐答案

将要跳过的列定义为 FILLER.请记住，控制文件中列的顺序通常就是它们在数据文件中的顺序.如果名称与表中的一列匹配，那么它就会出现在那里.

Define the column you want to skip as FILLER. Keep in mind the order of the columns in the control file is typically the order they are in the datafile. If the name matches a column in the table, that's where it will go.

...
(
  f1 CHAR,  -- 1st field in the file, goes to column named f1 in the table
  X FILLER, -- 2nd field in the file, ignored
  f3 CHAR,  -- 3rd field in the file, goes to column named f3 in the table
  f2 CHAR   -- 4th field in the file, goes to column named f2 in the table
)

换句话说，控制文件中列的顺序与它们在数据文件中的顺序相匹配，而不是它们在表中的顺序.这是按名称匹配的，而不是按顺序匹配的.

In other words, the order of the columns in the control file matches the order they are in the data file, not their order in the table. That is matched by name, not order.

编辑 - 我添加了一些注释以供解释，但我相信它们不能在实际文件中处于那个位置.请参阅下面的完整示例:

EDIT - I added some comments for explanation, but I believe they can't be in that position in the actual file. See below for a full example:

创建表:

CREATE TABLE T1
(
  F1  VARCHAR2(50 BYTE),
  F2  VARCHAR2(50 BYTE),
  F3  VARCHAR2(50 BYTE)
);

控制文件，example.ctl:

The control file, example.ctl:

load data 
infile *
truncate
into table t1
fields terminated by '|' trailing nullcols
(
f1 CHAR,
x FILLER,
f3 CHAR,
f2 CHAR
)
BEGINDATA
a|b|c|d
w|x|y|z

运行它:

C:	emp>sqlldr userid=login/password@database control=example.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Apr 22 11:25:49 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Commit point reached - logical record count 2

从表格中选择:

希望这会有所帮助.

这篇关于使用 SQLLDR 加载分隔数据时跳过数据字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！