SQLLOADER导入列中的空格

前两天一个朋友问我这个问题，如何将文件中的空格作为列的值利用SQLLOADER加载到数据库中。

 

 

首先建立一张测试用表：

SQL> CREATE TABLE T_LOAD_SPACE (ID NUMBER, NAME VARCHAR2(30), AGE NUMBER);

表已创建。

为了简单，将数据放到控制文件中，控制文件和数据内容如下：

LOAD DATA
INFILE *
TRUNCATE INTO TABLE T_LOAD_SPACE
(
ID CHAR TERMINATED BY ',',
Name CHAR TERMINATED BY ',',
AGE CHAR TERMINATED BY WHITESPACE
)
BEGINDATA
1,A,15
2, B,25
3,C ,35
4, D ,45
5,    ,55

可以看到，记录1的NAME列不包含空格，记录2的NAME列包含空格前缀，记录3的NAME列包括空格后缀，记录4的NAME列同时包含空格前缀和后缀，而记录5的NAME列只包含4个空格。

下面执行SQLLOADER操作导入数据：

E:\>sqlldr yangtk/yangtk control=test.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 00:29:20 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 5

检查导入的数据：

SQL> SELECT * FROM T_LOAD_SPACE;

        ID NAME                                  AGE
---------- ------------------------------ ----------
         1 A                                      15
         2  B                                     25
         3 C                                      35
         4  D                                     45
         5                                        55

这样看不明显，只能看到空格前缀导入了，下面将空格转化一下：

SQL> SELECT ID, REPLACE(NAME, ' ', 'space') NAME, AGE
  2  FROM T_LOAD_SPACE;

        ID NAME                                  AGE
---------- ------------------------------ ----------
         1 A                                      15
         2 spaceB                                 25
         3 Cspace                                 35
         4 spaceDspace                            45
         5 spacespacespacespace                   55

可以看到，这种情况无论是前缀空格，还是后缀空格都是可以导入的。

下面稍微修改一下控制文件：

LOAD DATA
INFILE *
TRUNCATE INTO TABLE T_LOAD_SPACE
(
ID CHAR TERMINATED BY ',',
NAME CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
AGE CHAR TERMINATED BY WHITESPACE
)
BEGINDATA
1,A,15
2, B,25
3,C ,35
4, D ,45
5,    ,55

添加一个常用的选项，OPTIONALLY ENCLOSED BY  '"'，再次执行输入数据的载入：

E:\>sqlldr yangtk/yangtk control=test.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 00:35:15 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 5

检查表记录：

SQL> SELECT * FROM T_LOAD_SPACE;

        ID NAME                                  AGE
---------- ------------------------------ ----------
         1 A                                      15
         2 B                                      25
         3 C                                      35
         4 D                                      45
         5                                        55

SQL> SELECT ID, REPLACE(NAME, ' ', 'space') NAME, AGE
  2  FROM T_LOAD_SPACE;

        ID NAME                                  AGE
---------- ------------------------------ ----------
         1 A                                      15
         2 B                                      25
         3 Cspace                                 35
         4 Dspace                                 45
         5                                        55

可以看到，前缀空格和纯空格列都没有导入，而后缀空格得以保留。

为了解决这种情况，可以使用PRESERVE BLANKS选项：

LOAD DATA
INFILE *
TRUNCATE INTO TABLE T_LOAD_SPACE
(
ID CHAR TERMINATED BY ',',
NAME CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' PRESERVE BLANKS,
AGE CHAR TERMINATED BY WHITESPACE
)
BEGINDATA
1,A,15
2, B,25
3,C ,35
4, D ,45
5,    ,55

执行导入：

E:\>sqlldr yangtk/yangtk control=test.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 00:35:15 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 5

查看这次导入的结果：

SQL> SELECT * FROM T_LOAD_SPACE;

        ID NAME                                  AGE
---------- ------------------------------ ----------
         1 A                                      15
         2  B                                     25
         3 C                                      35
         4  D                                     45
         5                                        55

SQL> SELECT ID, REPLACE(NAME, ' ', 'space') NAME, AGE
  2  FROM T_LOAD_SPACE;

        ID NAME                                  AGE
---------- ------------------------------ ----------
         1 A                                      15
         2 spaceB                                 25
         3 Cspace                                 35
         4 spaceDspace                            45
         5 spacespacespacespace                   55

可以看到，这次的结果和第一次完全一致，无论前缀空格还是后缀空格都成功导入。

另一种解决方法就是在数据中将这种数值全部用引号引起来，这样可以达到同样的目的：

LOAD DATA
INFILE *
TRUNCATE INTO TABLE T_LOAD_SPACE
(
ID CHAR TERMINATED BY ',',
NAME CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
AGE CHAR TERMINATED BY WHITESPACE
)
BEGINDATA
1,"A",15
2," B",25
3,"C ",35
4," D ",45
5,"    ",55

执行导入操作：

E:\>sqlldr yangtk/yangtk control=test.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 00:35:15 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 5

检查表结果：

SQL> SELECT * FROM T_LOAD_SPACE;

        ID NAME                                  AGE
---------- ------------------------------ ----------
         1 A                                      15
         2  B                                     25
         3 C                                      35
         4  D                                     45
         5                                        55

SQL> SELECT ID, REPLACE(NAME, ' ', 'space') NAME, AGE
  2  FROM T_LOAD_SPACE;

        ID NAME                                  AGE
---------- ------------------------------ ----------
         1 A                                      15
         2 spaceB                                 25
         3 Cspace                                 35
         4 spaceDspace                            45
         5 spacespacespacespace                   55

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-376656/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-376656/