1:导入命令
sqlldr userid=username/password@dbtest control=url.ctl errors=10000000
说明:
a:username/password@dbtest 分别为用户名,密码,服务名;
b:url.ctl 详细内容见下面;
c: errors=10000000 可以容纳的错误数,即错误数不超过此值会导入成功,超过此值不会导入记录;
2:url.ctl的内容如下
linux-zgum:/opt # cat url.ctl
OPTIONS (BINDSIZE=4000000,ROWS=1000)
load data
infile './test01.txt'
infile './test01.txt'
append into t_tab_test
TRAILING NULLCOLS
(
STATTIME char terminated by ' ',
t_1 char terminated by ' ',
t_2 char terminated by ' ',
t_3 char(1000) terminated by ' '
)
说明:
a:infile './test01.txt为需要导入的数据源,其中的字段是使用tab键分隔的;
b:如果字段的长度过长,需要指定其最大长度(默认为255);
c:t_tab_test 为表名,(此处公开在网上,表名为临时取的);
d:TRAILING NULLCOLS 当文件中某些行的字段个数少啦时,导入null值;
3:sqldr导入中文乱码问题
如果导入数据源中有中文时,如果导入的是乱码,请按照如下设置:
$ export NLS_LANG=AMERICAN_AMERICA.UTF8
或者
url.ctl加入CHARACTERSET ZHS16GBK
linux-zgum:/opt # cat url.ctl
OPTIONS (BINDSIZE=4000000,ROWS=1000)
load data
CHARACTERSET ZHS16GBK
infile './test01.txt'
infile './test01.txt'
append into t_tab_test
TRAILING NULLCOLS
(
STATTIME char terminated by ' ',
t_1 char terminated by ' ',
t_2 char terminated by ' ',
t_3 char(1000) terminated by ' '
)
在我的环境上面使用
export NLS_LANG=AMERICAN_AMERICA.UTF8 才有效;
----------------------------------------------------------------------------
----------------------------------------------------------------------------
我的数据库字符集:
select value
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
结果为:
ZHS16GBK
设置为如下导入还是乱码:
export NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
select * from nls_database_parameters
结果:
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.1.0