将数据从csv中导到ORACLE中。
数据库表:
CREATE TABLE USERS ( ID VARCHAR2(20), NAME VARCHAR2(20) , SEX VARCHAR2(20),
BIRTHDAY DATE )
test.csv 文件【数据源】
1,包爷,男,"2012-9-10 00:00:00" 2,飞哥,男,"2012-4-11 01:00:00" 3,"老姚",女,"2012-2-10 02:00:00"
result.ctl 控制文件
load data infile 'test.csv' truncate into table USERS fields terminated by ',' --将csv中以,号分隔成字段 optionally enclosed by '"' --这样在插入数据库时,内容就不包含""
trailing nullcols --将所有不在纪录中的指定位置的列当作空值 (id,name,sex,birthday DATE "YYYY-MM-DD HH24:MI:SS")
网上还有一种写法【写法太麻烦,没有使用】
load data infile 'test.csv' insert into table USERS (id char terminated by ',', name char terminated by ',' Optionally enclosed by '"', sex char terminated by whitespace)
导入命令:
sqlldr userid=test/test@orcl control=result.ctl log=resultthis.out
如报编码问题,解决方法如下,
首先查看数据库的编码:
select userenv('language') from dual; SIMPLIFIED CHINESE_CHINA.AL32UTF8 select * from v$nls_parameters where parameter like '%CHARACTERSET%' 1 NLS_CHARACTERSET AL32UTF8 2 NLS_NCHAR_CHARACTERSET AL16UTF16
查看命令行中的编码,如不统一需要设置一下
Linux:
[oracle@oracle oracle]$ export NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8' [oracle@oracle oracle]$ echo $NLS_LANG SIMPLIFIED CHINESE_CHINA.AL32UTF8
Windows:
E:\app>set NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8 E:\app>echo %NLS_LANG% SIMPLIFIED CHINESE_CHINA.AL32UTF8
注意 result.ctl test.csv 文件的编码要和上面的编码统一。如发生错误,可到 log=resultthis.out 对应的文件中查看具体原因,
[oracle@oracle oracle]$ sqlldr userid=test/test@orcl control=result.ctl log=resultthis.out SQL*Loader: Release 11.2.0.1.0 - Production on 星期五 11月 23 15:27:05 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 达到提交点 - 逻辑记录计数 3 [oracle@oracle oracle]$
E:\app>sqlldr userid=test/test@orcl control=result.ctl log=resultthis.out SQL*Loader: Release 11.2.0.1.0 - Production on 星期五 11月 23 15:10:52 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 达到提交点 - 逻辑记录计数 2 达到提交点 - 逻辑记录计数 3 E:\app>
说明:在操作类型 truncate 位置可用以下中的一值:
1) insert --为缺省方式,在数据装载开始时要求表为空
2) append --在表中追加新记录
3) replace --删除旧记录(用 delete from table 语句),替换成新装载的记录
4) truncate --删除旧记录(用 truncate table 语句),替换成新装载的记录
执行完 sqlldr 生成几个文件,如 resultthis.out 日志文件、test.bad 坏数据文件等。
从中可让你更好的理解 Sql Loader,里面有对控制文件的解析、列出每个字段的类型、加载记录的统计、出错原因等信息。
当加载大量数据时(大约超过10GB),最好抑制日志的产生:
SQL>ALTER TABLE USERS nologging;
6、注意事项:
①、日期格式转换:表中的最后两个字段是日期格式,这里需做 DATCREATETIME DATE "YYYY-MM-DD HH24:MI:SS", 转换,相当于to_date()。且数据文件中是2011-4-28 15:50:26格式,不是"2011-4-28 15:50:26"格式。否则会出错。(后来测试的时候发现带上也不会出错了,奇怪。)
②、插入数据库中的内容包含":如果数据文件中是"老姚",控制文件中需加上Optionally enclosed by '"',这样在插入数据库时,内容就不包含""