本节将详细介绍使用sqlldr导Excel文件到Oracle数据时涉及到的相关知识点,若要了解将EXcel文件导入Oracle数据库的步骤及其注意事项,可点击此链接了解sqlldr导Excel导到Oracle的步骤。
控制文件:该文件以.ctl结尾
load data --控制文件标识
infile 'e:\test.csv' --要导入的文件名
append into table test_order --将数据追加到表tset_order中
fields terminated by ',' --将文件中的每一行用逗号分隔
trailing nullcols( --若某些列的值为空,加上该语句则在导入数据库时会自动跳过为空的列
product_id, user_name ,phone_num, address ,order_date ,delivery_date) --涉及到各列
在控制文件中涉及到的三种导入方式:
append /insert/replace into table test_order
append:表不为空,也可以导入数据append:以追加的方式把数据导入到表test_order中
insert:以插入的方式把数据导入到表中,在数据导入前表必须为空,否则报错。
replace:以替换的方式把数据导入到表中,即相当于overwrite,表不为空也不会报错。
若涉及到列的内容很大,可在Oracle中定义为clob类型,在控制文件中把该列的字段设置得大一些。如Oracle的text_title的内容比较大,则:
text_title clob; --Oracle中的列
text_title char(100000) --控制文件中的列。
在执行sqllldr命令时:sqlldr userid=system/Linda123456 control='e:/input.ctl'
user_id:Oracle的用户名/密码
control:控制文件,里面涉及到数据导入的方式,数据涉及的列,以及列类型大小的定义等。
log:记录数据导入时的日志文件,其名字默认为控制文件(去除扩展名).log
如控制文件test.ctl其导入数据时的日志文件为test.log
bad:记录导入失败的数据,其名字默认为控制文件(去除扩展名).log。如在前面涉及到的test_order的introduction varchar2(666) 列,而控制文件不写数据类型及大小,则默认为char(255),当要导入的数据大于255时,则会报错,此时会产生一个test.bad文件,记录导入失败的数据。
errors:允许的错误记录数,可以用他来控制记录,使其记录数一条都不错过。
rows:多少条记录提交一次,默认64
skip:跳过行数,如跳过表头,或前几行。
在把之前写的test.ctl控制文件的内容改写之前,让我们先看一下,test_order的表结构:
test.csv文件内容:
改写控制文件test.ctl
options (skip=1,rows=128)
load data
infile 'e:\test.csv'
append into table test_order fields terminated by ','
Optionally enclosed by '"'
trailing nullcols
(product_id, user_name ,phone_num, address,
order_date date,delivery_date DATE,
introduction char(666))
执行过程截图:
导入数据的日志文件:test.log
查看表结果:
这里就可以避免第一行数据类型不一致而报错的情景,也避免了插入数据中有英文逗号,而导致插入数据错位的场景。(csv文件的默认把列的数据按照英文逗号分割)
该控制文件详解:
options (skip=1,rows=128) -- sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行
load data
infile 'e:\test.csv' --指定外部数据文件,可以写多 个 INFILE "another_data_file.csv" 指定多个数据文件
--这里还可以使 用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件,
append into table test_order fields terminated by ',' -- 要插入记录的表, 数据中每行记录用 "," 分隔
Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起,比如字段中有 "," 分隔符时
trailing nullcols --表的字段没有对应的值时允 许为空
(
product_id, user_name ,phone_num, address,
order_date DATE,delivery_date DATE,
introduction char(666))
这里还可以根据需要的日期类型来定义自己需要的日期格式,如:
logine_date DATE "YYYY-MM-DD HH24:MI:SS",delivery_date DATE "YYYY-MM-DD HH24:MI:SS", -- 指定接受日期的格式,相当用 to_date() 函数转换
关于sqlldr到Excel文件的步骤,可点击链接查看
这里看到了一个博主,对控制文件内容介绍得比较详解,详情可点击链接了解,该博主的内容是我目前发现写的比较全面的,感兴趣者可参考。