upload CSV file 注意的问题:
1. csv file format
if 本地编辑的csv file, fileformat 一般是dos.
upload 的时候,需要改为unix : set fileformat=unix
2. 中英文问题
无论csv file 是utf8 or GBK. 如果是其中有中文, 在upload的时候,如果进入DB是乱码,
只要设定其字符集即可
aa.sh
##Normal maybe
#en_US.UTF-8
##Use below for CN inputs
export NLS_LANG='AMERICAN_AMERICA.UTF8'
#export NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280'
sqlldr username/password@servername control=agg_pdt1106.ctl log=dd.log
3.control file
1)control file 每行对应csv file 的每个column,如果不用某个column,要用FILLER 忽略掉。
2)control file 每行要么用,要么用FILLER忽略,不可以跳过不管。 否则会延到control下一行被使用。
3)字段间和记录间的分隔符,要选择合适,例如字段间使用双引号",如果字段内容中也有含有",就会出问题
aa.ctl:
OPTIONS (skip=1, errors=99999)
load data
CHARACTERSET UTF8
infile 'cAGG_prod3.CSV' " str '>>>/n'" (note :记录分隔符)
append
--replace
into table agg_product
fields terminated by ',' optionally enclosed by '|||' (note:字段分隔符)
trailing nullcols
(
org_id FILLER,
source_org_id "TRIM(:source_org_id)",
source_product_id "TRIM(:source_product_id)",
source_name "TRIM(:source_name)",
source_url "TRIM(:source_url)",
source_category "TRIM(:source_category)",
description char(200) "SUBSTR(TRIM(:description), 1, 150)",
key_specification char(3000) "SUBSTR(TRIM(:key_specification), 1, 2000)",
record_status FILLER, --"TRIM(:record_status)",
create_date FILLER, --"nvl(:create_date,sysdate)",
l_upd_date FILLER, --"nvl(:l_upd_date,sysdate)",
certification char(3000) "SUBSTR(TRIM(:certification), 1, 3000)",
product_image_name char(200) "SUBSTR(TRIM(:product_image_name),1,20)"
)
1. csv file format
if 本地编辑的csv file, fileformat 一般是dos.
upload 的时候,需要改为unix : set fileformat=unix
2. 中英文问题
无论csv file 是utf8 or GBK. 如果是其中有中文, 在upload的时候,如果进入DB是乱码,
只要设定其字符集即可
aa.sh
##Normal maybe
#en_US.UTF-8
##Use below for CN inputs
export NLS_LANG='AMERICAN_AMERICA.UTF8'
#export NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280'
sqlldr username/password@servername control=agg_pdt1106.ctl log=dd.log
3.control file
1)control file 每行对应csv file 的每个column,如果不用某个column,要用FILLER 忽略掉。
2)control file 每行要么用,要么用FILLER忽略,不可以跳过不管。 否则会延到control下一行被使用。
3)字段间和记录间的分隔符,要选择合适,例如字段间使用双引号",如果字段内容中也有含有",就会出问题
aa.ctl:
OPTIONS (skip=1, errors=99999)
load data
CHARACTERSET UTF8
infile 'cAGG_prod3.CSV' " str '>>>/n'" (note :记录分隔符)
append
--replace
into table agg_product
fields terminated by ',' optionally enclosed by '|||' (note:字段分隔符)
trailing nullcols
(
org_id FILLER,
source_org_id "TRIM(:source_org_id)",
source_product_id "TRIM(:source_product_id)",
source_name "TRIM(:source_name)",
source_url "TRIM(:source_url)",
source_category "TRIM(:source_category)",
description char(200) "SUBSTR(TRIM(:description), 1, 150)",
key_specification char(3000) "SUBSTR(TRIM(:key_specification), 1, 2000)",
record_status FILLER, --"TRIM(:record_status)",
create_date FILLER, --"nvl(:create_date,sysdate)",
l_upd_date FILLER, --"nvl(:l_upd_date,sysdate)",
certification char(3000) "SUBSTR(TRIM(:certification), 1, 3000)",
product_image_name char(200) "SUBSTR(TRIM(:product_image_name),1,20)"
)