今天同事临时让我帮忙写个 sqlloader load到DB的例子。之前只是听过sqlloader可以把数据文件快速的upload到DB。 大概知道要用到 sqlldr命令,具体怎么做就只好百度了。
描述:User提供的文件是 abc.xls 类型的,用shell读取了一下发现是乱码,我这个菜鸟居然又在这部分犯错误。 Word,excel,pdf这些文本都不是直接可读的,要有专用的解析程序才可以滴。 其次data 文件时有header的。再次是data file有2列数据与数据库表的列可匹配。
额外需要,把DB中表acct_loyal_ref的 create_date和create_by两个字段的值也批量载入。
1. 首先手动把.xls格式的改为以逗号分隔的 .csv文件。
2. 写control file loader.ctl
3. 写shell scrip
如果control file(table column)与data file的column是一一对应的话,control file写法简单,如下
load data characterset ZHT16BIG5
truncate
into table acct_loyal_ref
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(
cust_id,
sales_date Date "DD-MM-YYYY HH24:MI:SS",
alrp_created_date Date "YYYYMMDD HH24:MI:SS" ,
alrp_created_by
)
如果table column与data file的column不对应。
1. data file column 少于 table column. (这次遇到的就是,但是table约束是每个column都不能为空)
在Shell scrip中用
sed 's/$/\,/g' $datafile >$exportfile
在datafile 的每一行末尾追加一个‘,’【因为是table里面共4个字段,csv 文件只有2个字段】。每行只追加1个‘,’
ctl file:
load data characterset ZHT16BIG5
truncate
into table acct_loyal_ref
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(
cust_id,
sales_date Date "DD-MM-YYYY HH24:MI:SS",
created_date Date "YYYYMMDD HH24:MI:SS" "nvl(null,sysdate)",
created_by "nvl(null,'OPERUSER')"
)
就可以解决本文引出的那个描述问题了。
看了一些资料,个人感觉应该直接用在crl文件中配置参数进行过滤就行。还有待考证。
************* 以下是4种装入表的方式
APPEND // 原先的表有数据 就加在后面
// INSERT // 装载空表 如果原先的表有数据 sqlloader会停止默认值
// REPLACE // 原先的表有数据原先的数据会全部删除
// TRUNCATE // 指定的内容和replace的相同会用truncate语句删除现存数据