公司业务需要,通过sqlldr导入数据进行统计,数据来源是柜台软件导出的excel,然后转换成csv格式,通过sqlldr导入数据库。
控制文件fjf.ctl :
load data
CHARACTERSET ZHS16GBK
infile 'D:\20180927\9\20180903_20180903.csv'
append
into table hs_his.fjf
fields terminated by ','
TRAILING NULLCOLS
(
exch_type ,
futucode_type ,
hands ,
money ,
shouxf ,
fjf1 ,
fjf2 ,
jlc
)
bat文件:
@Echo ************************************************************************
@Echo *********** 导入附加费数据!!! **************************
@Echo ******** 请按回车进行数据导入!!! **************************
@Echo ************************************************************************
pause
sqlldr system/oracle@uf20 control=D:\20180927\9\fjf.ctl log=D:\20180927\9\fjf.log bad=D:\20180927\9\fjf.bad
pause
导入后,查询显示乱码。
SQL> select exch_type from hs_his.fjf;
EXCH_TYPE
--------------------
锘垮ぇ杩炰氦鏄撴墍
澶ц繛浜ゆ槗鎵€
閮戝窞浜ゆ槗鎵€
閮戝窞浜ゆ槗鎵€
澶ц繛浜ゆ槗鎵€
涓婃捣浜ゆ槗鎵€
閲戣瀺浜ゆ槗鎵€
澶ц繛浜ゆ槗鎵€
閮戝窞浜ゆ槗鎵€
澶ц繛浜ゆ槗鎵€
閲戣瀺浜ゆ槗鎵€
查询数据库字符集:select * from nls_database_parameters
SQL> show parameters nls_lang
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_language string SIMPLIFIED CHINESE
SQL> show parameters nls
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string GREGORIAN
nls_comp string BINARY
nls_currency string ¥
nls_date_format string DD-MON-RR
nls_date_language string SIMPLIFIED CHINESE
nls_dual_currency string ¥
nls_iso_currency string CHINA
nls_language string SIMPLIFIED CHINESE
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string .,
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string BINARY
nls_territory string CHINA
nls_time_format string HH.MI.SSXFF AM
nls_time_tz_format string HH.MI.SSXFF AM TZR
nls_timestamp_format string DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format string DD-MON-RR HH.MI.SSXFF AM TZR
session的字符集:
客户端字符集:
发现数据库字符集没什么问题。
怀疑是数据源的编码问题
ctl文件中修改字符集为:AL32UTF8
load data
CHARACTERSET AL32UTF8
infile 'D:\20180927\9\20180903_20180903.csv'
append
into table hs_his.fjf
fields terminated by ','
TRAILING NULLCOLS
(
exch_type ,
futucode_type ,
hands ,
money ,
shouxf ,
fjf1 ,
fjf2 ,
jlc
)
重新导入,问题解决
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29362142/viewspace-2215575/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29362142/viewspace-2215575/