SQLLOADER匯入列中的空格

yangtingkun發表於2008-07-11

前兩天一個朋友問我這個問題,如何將檔案中的空格作為列的值利用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

可以看到,記錄1NAME列不包含空格,記錄2NAME列包含空格字首,記錄3NAME列包括空格字尾,記錄4NAME列同時包含空格字首和字尾,而記錄5NAME列只包含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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章