本博客主要演示如何用sqlldr将Excel中大批量的数据导入。
一、前提环境
1、安装Oracle和可视化操作工具PLSQL。
2、建好相应的表空间和表。
二、操作流程
通过可视化操作工具PLSQL向表中导入数据的方法很多,小批量数据最简单的方法是直接复制粘贴即可,但当数据到达一定的量级后,可以采用Oracle中的sqlldr方法进行导入。
1、文件准备
通常将Excel文件转换为文本格式文件或者CSV格式文件,进行导入操作。
(1)将Excel文件数据另存为txt格式(默认以制表符Tab为分隔符)或者CSV格式文件(默认为以逗号为分隔符)。
源文件Excel:
另存为txt文件:
(2)制作 .ctrl 控制文件。
制作方法如下:
load data
infile '\tmp11.txt' #需要导入的源文件
append into table gs_tmp_1215 #gs_tmp_1215为需要导入的表名称
fields terminated by X'09' #X'09'为文本文件的分隔符——制表符的ASC码
trailing nullcols( #表的列名称的集合
region_id
,region_name
...........
,last_active_time "to_date(:last_active_time,'yyyy/mm/dd hh24:mi:ss')"
#将last_active_time该列的数据已日期格式导入
)
注意:
1》、最好将源文件和控制文件放在同一个文件目录下。
2》、如果将源文件转换为CSV格式,在制作 .ctrl 文件时,fields terminated by X'09'
要替换成fields terminated by ','
。
3》、fields terminated by X'09'
中x’09’为制表符的asc码,则原txt文件的数据类型也必须为asc码的保存形式,否则分割不成功。
2、 数据导入
默认Oracle数据库中的表已经建好,表的列和Excel文件中的列一一相匹配,即列数一样,数据的属性也一样。
(1)、用cmd打开操作台窗口,并用sqlldr 语句进行数据的导入。
sqlldr 语句的语法:
sqlldr userid=username/password@servername control=aaa.ctrl data=aaa.txt log=aaa.log bad=aaa.bad
sqlldr参数说明:
username -------数据库的用户名
password -------数据库的密码
control --------控制文件.ctrl的文件路径
data -----------需要导入的数据.txt的路径
log ------------日志文件保存的存放路径
bad ------------保存未导入成功数据的文件存放路径
注意:
1》、文件路径设计到相对路径和绝对路径的问题,若提示 SQL*Loader 文件无法访问,很有可能是文件路径的问题。
2》、该过程可能会遇到很多错误,都可以在log文件中查看错误原因,然后进行解决。如有其他问题,可以留言,大家共同解决。
(2)、导入成功后的图:
查看log文件,显示全部导入,则成功完成:
可进入PLSQL中可以查看我们表中的数据,9158条数据全部导入。
结语
在导入的过程中可能会遇到,无效数字、超出范围、无法打开文件等各种问题,我都遇到过,也都找到原因并成功解决,如有问题,可以留言讨论。