SQL Loader ctl 文件 - 如何跳过列

发布于 2025-01-04 05:45:38 字数 441 浏览 0 评论 0原文

假设我的数据库有 4 列：

Column1, Column2, Column3, Column4

我的数据文件是 CSV 文件（逗号分隔，列长度未知）：

xxx,yyy,zzz,000  
a,bb,ccccc,ddddddd  
1,2,3,4

CTL 将是：

LOAD DATA
INTO TABLE "TABLE" 
FIELDS TERMINATED BY ',' TRAILING NULLCOLS 
(
    Column1,
    Column2,
    Column3,
    Column4     
)

我想跳过 Column3，我该怎么做？我了解 FILLER 但它不适用于旧的 Oracle 版本。

原文

Let's say in my database 4 columns:

Column1, Column2, Column3, Column4

My data file is CSV file (comma delimited, length of column is unknown):

xxx,yyy,zzz,000  
a,bb,ccccc,ddddddd  
1,2,3,4

The CTL will be:

LOAD DATA
INTO TABLE "TABLE" 
FIELDS TERMINATED BY ',' TRAILING NULLCOLS 
(
    Column1,
    Column2,
    Column3,
    Column4     
)

I want to skip Column3, how can I do that? I know about FILLER but it doesn't work for old oracle versions.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

如痴如狂 2025-01-11 05:45:38

如果可以的话，只需使用 shell 工具删除第三列即可。这就是有效。

例如，使用 awk：

awk 'BEGIN { FS="," } { print $1 FS $2 FS $4 }' INFILE > TMPOUTFILE

或使用 sed：（

sed 's/^\([^,]\+,[^,]\+,\)[^,]\+,/\1/' INFILE > TMPOUTFILE

您可以选择其他几个工具（例如 cut...）

If that's an option just drop the third column with a shell tool. That's just works.

E.g. with awk:

awk 'BEGIN { FS="," } { print $1 FS $2 FS $4 }' INFILE > TMPOUTFILE

Or with sed:

sed 's/^\([^,]\+,[^,]\+,\)[^,]\+,/\1/' INFILE > TMPOUTFILE

(and you can pick several other tools (e.g. cut...)

回复收藏 0 原文

不美如何 2025-01-11 05:45:38

我真的不知道早期版本是否支持这一点，但在以后的版本中，您可以使用 SQL 运算符：

LOAD DATA
INTO TABLE "TABLE" 
FIELDS TERMINATED BY ',' TRAILING NULLCOLS 
(
    Column1,
    Column2,
    Column3 "decode(:Column3,null,null,null)",
    Column4
)

...无论数据文件中是否有值，都会将 Column3 设置为 null 或不。

I really don't know if this is supported in early versions, but in later ones you can user an SQL operator:

LOAD DATA
INTO TABLE "TABLE" 
FIELDS TERMINATED BY ',' TRAILING NULLCOLS 
(
    Column1,
    Column2,
    Column3 "decode(:Column3,null,null,null)",
    Column4
)

... which will set Column3 to null whether it has a value in the data file or not.

回复收藏 0 原文

悍妇囚夫 2025-01-11 05:45:38

一种方法是使用中间表或加载表，如下所示：

LOAD DATA
INTO TABLE "LOADTABLE" 
FIELDS TERMINATED BY ',' TRAILING NULLCOLS 
(
    Column1,
    Column2,
    Column3,
    Column4     
)

然后调用一个存储过程来处理主表的填充，如下所示：

CREATE PROCEDURE pop_table
IS
   CURSOR cur_load_table
   IS
              SELECT column1
                   , column2
                   , column4
                FROM loadtable;
BEGIN
   FOR rec IN cur_load_table
   LOOP
      INSERT INTO table( column1
                       , column2
                       , column4 )
      VALUES ( rec.column1
             , rec.column2
             , rec.column4 );
   END LOOP;
END pop_table;

One approach would be to use an intermediate or load table, like so:

LOAD DATA
INTO TABLE "LOADTABLE" 
FIELDS TERMINATED BY ',' TRAILING NULLCOLS 
(
    Column1,
    Column2,
    Column3,
    Column4     
)

and then call a stored proc afterwards that handles the population of your main table, something like this:

CREATE PROCEDURE pop_table
IS
   CURSOR cur_load_table
   IS
              SELECT column1
                   , column2
                   , column4
                FROM loadtable;
BEGIN
   FOR rec IN cur_load_table
   LOOP
      INSERT INTO table( column1
                       , column2
                       , column4 )
      VALUES ( rec.column1
             , rec.column2
             , rec.column4 );
   END LOOP;
END pop_table;

回复收藏 0 原文

~没有更多了~