使用SQLLOADER將多行資料合併為一行進行載入
SQL*LOADER支援將多行資料（物理行）合併為一行（邏輯行）處理，本文進行測試驗證。
1，語法說明：
1.1，使用CONCATENATE合併固定行數為一行
format:
CONCATENATE integer
1.2，使用CONTINUEIF根據條件將多行合併為一行
format:
CONTINUEIF THIS/NEXT [PRESERVE] (pos1:pos2) {=|<>|!=} {str|X'hex_str'}
CONTINUEIF LAST [PRESERVE] {=|<>|!=} {str|X'hex_str'}
比如,如果每個邏輯記錄的第二列到第五列為“$|$|”，如下格式將資料中包含換行符的多行資料合併為一行：
CONTINUEIF NEXT PRESERVE (2:5) != '$|$|'
2,測試：
2.0測試表
create table multi_line(
x1 varchar2(100),x2 varchar2(100),
x3 varchar2(100),x4 varchar2(100),
x5 varchar2(100),x6 varchar2(100));
2.1測試
test1:
cat normal_line.ctl
LOAD DATA
INFILE *
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
x2,
x3,
x4,
x5,
x6
)
BEGINDATA
a1,b1,c1,
a2,b2,c2,
a3,b3,c3,
a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
a7,b7,c7,
a8,b8,c8,
a9,b9,c9,
set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
select * from multi_line;
SQL> select * from multi_line;
a1 b1 c1
a2 b2 c2
a3 b3 c3
a4 b4 c4
a5 b5 c5
a6 b6 c6
a7 b7 c7
a8 b8 c8
a9 b9 c9
9 rows selected.
test2:
cat fixed_num_lines.ctl
LOAD DATA
INFILE *
concatenate 2
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
x2,
x3,
x4,
x5,
x6
)
BEGINDATA
a1,b1,c1,
a2,b2,c2,
a3,b3,c3,
a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
a7,b7,c7,
a8,b8,c8,
a9,b9,c9,
set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1 b1 c1 a2 b2 c2
a3 b3 c3 a4 b4 c4
a5 b5 c5 a6 b6 c6
a7 b7 c7 a8 b8 c8
a9 b9 c9
test3:
cat conif_lines.ctl
LOAD DATA
INFILE *
continueif next (1:2) = '--'
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
x2,
x3,
x4,
x5,
x6
)
BEGINDATA
a1,b1,c1,
--a2,b2,c2,
a3,b3,c3,
--a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
--a7,b7,c7,
a8,b8,c8,
a9,b9,c9,
set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
b1 c1 a2 b2 c2
b3 c3 a4 b4 c4
b5 c5
b6 c6 a7 b7 c7
b8 c8
b9 c9
6 rows selected.
-->x1 is null!
test4:
cat conif_lines.ctl
LOAD DATA
INFILE *
continueif next preserve (1:2) = '--'
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
x2,
x3,
x4,
x5,
x6
)
BEGINDATA
a1,b1,c1,
--a2,b2,c2,
a3,b3,c3,
--a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
--a7,b7,c7,
a8,b8,c8,
a9,b9,c9,
set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1 b1 c1 --a2 b2 c2
a3 b3 c3 --a4 b4 c4
a5 b5 c5
a6 b6 c6 --a7 b7 c7
a8 b8 c8
a9 b9 c9
6 rows selected.
test5:
cat conif_lines.ctl
LOAD DATA
INFILE *
continueif next preserve (1:2) = '--'
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
x2,
x3,
x4 "replace(:x4,'--','')",
x5,
x6
)
BEGINDATA
a1,b1,c1,
--a2,b2,c2,
a3,b3,c3,
--a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
--a7,b7,c7,
a8,b8,c8,
a9,b9,c9,
set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1 b1 c1 a2 b2 c2
a3 b3 c3 a4 b4 c4
a5 b5 c5
a6 b6 c6 a7 b7 c7
a8 b8 c8
a9 b9 c9
6 rows selected.
test6:
cat conif_lines.ctl
LOAD DATA
INFILE *
continueif this preserve (1:2) = '--'
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
x2,
x3,
x4,
x5,
x6
)
BEGINDATA
a1,b1,c1,
--a2,b2,c2,
a3,b3,c3,
--a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
--a7,b7,c7,
a8,b8,c8,
a9,b9,c9,
set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1 b1 c1
--a2 b2 c2 a3 b3 c3
--a4 b4 c4 a5 b5 c5
a6 b6 c6
--a7 b7 c7 a8 b8 c8
a9 b9 c9
6 rows selected.
test7:
cat conif_lines.ctl
LOAD DATA
INFILE *
continueif last (= '-')
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
x2,
x3,
x4,
x5,
x6
)
BEGINDATA
a1,b1,c1,-
a2,b2,c2,
a3,b3,c3,-
a4,b4,c4,
a5,b5,c5,
a6,b6,c6,-
a7,b7,c7,
a8,b8,c8,
a9,b9,c9,
set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1 b1 c1 -a2 b2 c2
a3 b3 c3 -a4 b4 c4
a5 b5 c5
a6 b6 c6 -a7 b7 c7
a8 b8 c8
a9 b9 c9
6 rows selected.
-->雖沒有preserve關鍵詞，“-”仍然保留
test7:
--“-”後面加空格
cat conif_lines.ctl
LOAD DATA
INFILE *
continueif last (= '-')
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
x2,
x3,
x4,
x5,
x6
)
BEGINDATA
a1,b1,c1,-
a2,b2,c2,
a3,b3,c3,-
a4,b4,c4,
a5,b5,c5,
a6,b6,c6,-
a7,b7,c7,
a8,b8,c8,
a9,b9,c9,
set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1 b1 c1 - a2 b2 c2
a3 b3 c3 - a4 b4 c4
a5 b5 c5
a6 b6 c6 - a7 b7 c7
a8 b8 c8
a9 b9 c9
6 rows selected.
-->雖沒有preserve關鍵詞，“-”仍然保留，而且空格也保留了
test8:
--“-”後面加空格，增加preserve
cat conif_lines.ctl
LOAD DATA
INFILE *
continueif last preserve (= '-')
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
x2,
x3,
x4,
x5,
x6
)
BEGINDATA
a1,b1,c1,-
a2,b2,c2,
a3,b3,c3,-
a4,b4,c4,
a5,b5,c5,
a6,b6,c6,-
a7,b7,c7,
a8,b8,c8,
a9,b9,c9,
set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1 b1 c1 - a2 b2 c2
a3 b3 c3 - a4 b4 c4
a5 b5 c5
a6 b6 c6 - a7 b7 c7
a8 b8 c8
a9 b9 c9
6 rows selected.
-->雖沒有preserve關鍵詞，“-”仍然保留，而且空格也保留了
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-743754/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Excel 快速合併多行資料為一行Excel
- 【MySQL】MySQL如何合併多行資料，行轉列，group_concat 多行合併MySql
- [Hive]用concat_w實現將多行記錄合併成一行Hive
- 使用python進行合併資料集Python
- orcale 中查詢多行 後合併到一行顯示
- DBeaver如何刪除一行或多行資料
- 在oracle中將一行字串拆分成多行Oracle字串
- 利用shell中awk和xargs以及sed將多行多列文字中某一列合併成一行
- SQLServer中多行中的一列根據條件合併為一行中的一列SQLServer
- python合併相同行只保留一行Python
- 用一行Python進行資料收集探索Python
- Excel 如何批次將矩陣（多行多列）資料轉為單行或單列資料Excel矩陣
- 利用 Pandas 將資料集中的某列文字拆分為多行
- 如何將多行單元格資料轉變成一行？這個Excel技巧你一定不知道！Excel
- 使用 Python 進行資料分析：入門指南Python
- 辦公自動化：PDF檔案合併器，將多個PDF檔案進行合併
- 使用 preloadComponents 進行元件預載入元件
- rxjs入門6之合併資料流JS
- sql、oracle資料多行轉單行SQLOracle
- el-table合併相同資料的行
- Android Paging分頁庫的學習（一）—— 結合本地資料進行分頁載入Android
- GeoServer + PostGIS 進行圖層檔案資料匯入併發布Server
- Linux vi如何刪除一行或者多行內容Linux
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- Android Paging分頁庫的學習（二）—— 結合Room資料庫進行分頁載入AndroidOOM資料庫
- 使用Dbeaver 進行資料的匯入和匯出
- js使用waterfall橫向載入瀑布流,一行程式碼完成JS行程
- CSS: 通過CSS控制字串長度（一行或者多行）CSS字串
- R中將迴圈產生的資料框合併
- Java Stream 流如何進行合併操作Java
- Pytorch資料載入與使用PyTorch
- .NET LoongArch64 正式合併進入.NET
- 使用SQL語句將資料庫中的兩個表合併成一張表SQL資料庫
- 使用pandas進行資料分析
- 使用 7-Zip 進行檔案分割與合併（命令列示例）命令列
- 使用Prerender.io進行網站預載入網站
- 使用JMeter進行負載測試快速入門JMeter負載
- 如何將資料進行資料視覺化展現？視覺化
- vue對table的某一行的資料進行編輯，刪除操作Vue