使用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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用SQLLOADER將一行資料拆分為多行進行載入SQL
- Excel 快速合併多行資料為一行Excel
- Oracle 合併行/多行合併成一行Oracle
- MySQL GROUP_CONCAT函式 將多行資料合併成一行MySql函式
- mysql將多行資料合併或按組合並MySql
- 【MySQL】MySQL如何合併多行資料，行轉列，group_concat 多行合併MySql
- 轉：SQL Server中將多行資料拼接為一行資料（一個字串）SQLServer字串
- [Hive]用concat_w實現將多行記錄合併成一行Hive
- 多行資料放到一行上
- orcale 中查詢多行 後合併到一行顯示
- SQL實現多行合併一行 wmsys.wm_concatSQL
- 使用python進行合併資料集Python
- 利用Oracle分析函式row_number和sys_connect_by_path實現多行資料合併為一行Oracle函式
- sqlldr一行記錄拆分為多行SQL
- DBeaver如何刪除一行或多行資料
- Mysql將近兩個月的記錄合併為一行顯示MySql
- 在oracle中將一行字串拆分成多行Oracle字串
- SQLServer中多行中的一列根據條件合併為一行中的一列SQLServer
- 利用shell中awk和xargs以及sed將多行多列文字中某一列合併成一行
- Oracle多行轉一行Oracle
- 使用sqlloader向oracle匯入文字資料SQLOracle
- (轉)SQL查詢案例：多行轉換為一行SQL
- 測試TOM=SQLLOADER載入製表符號資料SQL符號
- GridView多行表頭合併View
- Excel 如何批次將矩陣（多行多列）資料轉為單行或單列資料Excel矩陣
- python合併相同行只保留一行Python
- 根據特殊識別符號，將一行轉多行記錄符號
- android viewpager使用Fragment懶載入，滑到當前fragment才進行資料載入AndroidViewpagerFragment
- 用sqlloader(sqlldr)裝載LOB資料SQL
- 使用SQLLOADER將每行資料根據條件插入不同表SQL
- 用一行Python進行資料收集探索Python
- 使用Echarts和Ajax動態載入資料進行大資料視覺化Echarts大資料視覺化
- 利用 Pandas 將資料集中的某列文字拆分為多行
- 辦公自動化：PDF檔案合併器，將多個PDF檔案進行合併
- 如何將多行單元格資料轉變成一行？這個Excel技巧你一定不知道！Excel
- 採用sqlldr定時將文字檔案載入進入資料庫SQL資料庫
- 使用sqlloader的直接載入方式和傳統載入方式的效能差異SQL
- 用explode函式把json中用一層級資料一行轉換多行函式JSON