sqllod如何处理导入文本带回车换行

 

 

[oracle@lenovo sqlload]$ more data_info.ctl
Load DATA
INFILE '/tmp/data_info.txt'
truncate into table data_info_varchar_sdr
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
 id
,open_id
,vacc_code
,name
,pinyin_name
,short_name
,group_code
,group_name
,type
,attribute_type
,preventable_diseases char(4000) "replace(:preventable_diseases, '\\n',chr(10))"
,disease_hazards      char(4000) "replace(:disease_hazards, '\\n',chr(10))"
,vaccination_program  char(4000) "replace(:vaccination_program, '\\n',chr(10))"
,precautions_before   char(4000) "replace(:precautions_before, '\\n',chr(10))"
,precautions_after    char(4000) "replace(:precautions_after, '\\n',chr(10))"
,replace_desc         char(4000) "replace(:replace_desc, '\\n',chr(10))"
,vaccination_effect   char(4000) "replace(:vaccination_effect, '\\n',chr(10))"
,vaccination_contraindication char(4000) "replace(:vaccination_contraindication, '\\n',chr(10))"
,vaccination_adverse_reaction char(4000) "replace(:vaccination_adverse_reaction, '\\n',chr(10))"
,vaccination_precautions      char(4000) "replace(:vaccination_precautions, '\\n',chr(10))"
,inoculate_part      char(4000) "replace(:inoculate_part, '\\n',chr(10))"
,vacc_icon           char(4000) "replace(:vacc_icon, '\\n',chr(10))"
,deleted
,version
,sync_time
,create_time
,update_time
)

 

char(4000) 指定加载的字符串长度。默认是256个字符，超过就会报错
position(1:32)  字符串截取

设置环境变量
export NLS_LANG=AMERICAN_AMERICA.UTF8
export LANG=en_US.UTF-8

 

执行sqlldr
sqlldr hxl/oracle control=/home/oracle/sqlload/test.ctl log=/home/oracle/sqlload/test.log