由于oracle控制文件的强大功能,使得sql*loader可以如所愿的对数据文件进行处理,然后导入oracle数据库中。想想也是,数据库的存在无非是为了:1,更好的管理数据。2,能更轻松的和软件衔接,明确数据流的走向。这时,很多格式的数据文件如果要导入到oracle中就遇到一定的问题。
一,处理是以空格断值得数据文件
编辑如图所示的文件,保存在根文件夹下
将这样的数据文件导入到昨天创建的test表中,如何操作?查找oracle的控制文件语法,得知在引述列明时使用position 关键字。编写控制文件
这里我把infile 文件写法用绝对路径,执行结果
报出了异常,这里就是昨天我在博客中说的,一定要在执行的目录里添加数据文件。还是老老实修改数据文件,放在根目录下,在根目录下执行。
正确的控制文件的写法是:
SQL> select * from test;
ID NAME
---------- ------------------------------
100 y
200
300 n
2 lily
SQL>
SQL>
通过思考,我总结出了规律,在写控制文件时一定要像写程序一样按着格式一步一步的写,排列成规矩的格式,这样才不会出错,出了错也好坚持。这真是一个IT者的好习惯。position(1:3)表示取第一个到第三个字符中间的值。这样的文件也成为定长字符串。第8个字符一定是y,n,所以后头的值没有取完。
举一反三,通过postion关键字可以实现将定长字符串的文件隔行插入。如下图文件展示的那样
很显然,数据库中没有性别这一行,我直接插入时一定会报错,并产生日志,错误文件。所以,我修改控制文件。
SQL>
SQL>
SQL> select * from test;
ID NAME
---------- ------------------------------
100 y
200
300 n
100 y
200
300 n
1000 王
2000 莉
3000 丽
1000 王歌
2000 莉莉
3000 丽丽
2 lily
13 rows selected
SQL>
因为,我插入了2次,所以append出了许多结果。因此可见控制文件的作用是非常强大的,处理的方法也是非常灵活的。
二:处理大批的数据,如多个数据文件同时导入到一个表中。解决方法:
还是修改控制文件,需要这样改变
load data
infile data1.dat /*第一个数据文件
infile data2.dat /*第二个数据文件
infile data3.dat
append into table tab1
fields terminated by ","
这样处理就行。方法灵活多变。同样如果不想插入数据文件中的第n到第n+3行。就需要关键字filler了。所以,方法多种多样。在用到时先试验,然后仔细使用就好。
很重要的一个问题,oracle中对空值的定义是null,当插入的值为空时,就会出现错误。我们实验一下。还以以前创的test表为例。定义值如图
写控制文件:
load data
infile dat_new.csv
append into table test
fields terminated by ","
(id,name)
sql*loader中执行,提示成功,但是出现bad文件。打开bad文件
4,
id ,name
这时由于空值引起的。注意.bad 文件和控制文件会在同一个目录里,bad文件会自动生成在控制文件的目录中。这样的文件在oracle中出现的可能性很大。因为oracle中的null值和excel中的空是不一样的存贮方式。为了执行的正确,需要修改控制文件,添加关键字TRAILING NULLCOLS。
LOAD DATA
INFILE dat_new.csv
TRUNCATE INTO TABLE TEST
FIELDS TERMINATED BY "," TRAILING NULLCOLS
(ID,NAME)
这次执行,没有问题了。查看一下
在实际工作中,TRAILING NULLCOLS 应用非常广泛。
工作了一天,好疲惫了。晚上头脑还在运转,更是对自己的调整。告诉自己,我能坚持,告诉自己,我一定行。