【DB笔试面试446】如何将文本文件或Excel中的数据导入数据库?

题目部分

如何将文本文件或Excel中的数据导入数据库?

答案部分

有多种方式可以将文本文件的数据导入到数据库中,例如,利用PLSQL Developer软件进行复制粘贴,利用外部表,利用SQL*Loader等方式。至于EXCEL中的数据可以另存为csv文件(csv文件其实是逗号分隔的文本文件),然后导入到数据库中。

下面简单介绍一下SQL*Loader的使用方式。

SQL*Loader是一个Oracle工具,能够将数据从外部数据文件装载到数据库中。SQL*Loader必须包含一个控制文件,该控制文件是SQL*Loader的中枢核心,控制文件能够控制外部数据文件中的数据如何映射到Oracle的表和列。通常与SPOOL导出文本数据方法配合使用。SQL*Loader能够接收多种不同格式的数据文件。文件可以存储在磁盘或磁带上,或记录本身可以被嵌套到控制文件中。记录格式可以是定长的或变长的,定长记录是指这样的记录:每条记录具有相同的固定长度,并且每条记录中的数据域也具有相同的固定长度、数据类型和位置。

SQL*Loader的数据导入比较专业,有各种参数及选项可供选择,经常是作为数据仓库中大型数据的导入方法选择。

SQL*Loader的优点:

1、可将导入命令写入BAT文件直接批量处理

2、导入处理比较专业,提供各种参数选择

3、无需操作Oracle所在服务器

SQL*Loader也有缺点,例如,Excel文件需要另存为txt或csv格式才能导入到数据库中。

总得来说这种方法是最值得采用的,可以自动建立操作系统的批处理文件执行SQL*Loader命令,将数据导入原始接收表,并在数据库中设置触发器进行精细操作。

SQL*Loader有两种使用方法:

1、只使用一个控制文件,在这个控制文件中包含数据

2、使用一个控制文件和一个数据文件

SQL*Loader工具使用的命令为sqlldr,其常用参数的含义如下表所示:

参数

含义解释

userid

Oracle用户名/口令

control

控制文件名

log

记录的日志文件名

bad

错误文件名,记录错误的未加载数据

data

数据文件名,data参数只能指定一个数据文件。如果控制文件通过infile参数指定了数据文件,并且指定多个,那么在执行sqlldr命令时,先加载data参数指定的数据文件,控制文件中第一个infile指定的数据文件被忽略,但后续的infile指定的数据文件继续有效

rows

每次提交的记录数,默认情况下,常规路径为64

direct

使用直通路径方式导入,不使用buffer cache。通过direct path api发送数据到服务器端的加载引擎,加载引擎按照数据块的格式处理数据并直接写入数据文件,因此效率较高。该参数默认为FALSE。注意:含序列时不能设置direct=true

parfile

参数文件:包含参数说明的文件的名称

parallel

执行并行加载(默认为FALSE)

bindsize

常规路径绑定数组的大小(以字节计,默认为256000)

discard

废弃文件名,默认情况不产生

discardmax

允许废弃的文件的数目

skip

要跳过的逻辑记录的数目(默认为0),如:skip=3,表示数据文件的前三行不导入库

load

要加载的逻辑记录的数目如:load=5,表示要加载5条记录

errors

允许的错误的数目(默认为50)

silent

运行过程中隐藏消息

file

要从以下对象中分配区的文件

skip_unusable_indexes

不允许/允许使用无用的索引或索引分区(默认为FALSE)

skip_index_maintenance

没有维护索引,将受到影响的索引标记为无用(默认为FALSE)

commit_discontinued

提交加载中断时已加载的行(默认为FALSE),该参数默认为FALSE,表示当load被异外中止后,已load的数据是不是自动提交

readsize

读取缓冲区的大小(默认为1048576,单位为字节,即1M)

external_table

使用外部表进行加载

columnarrayrows

直接路径列数组的行数(默认为5000)

streamsize

直接路径流缓冲区的大小(以字节计,默认为256000)

multithreading

在直接路径中使用多线程

resumable

启用或禁用当前的可恢复会话(默认为FALSE)

resumable_name

有助于标识可恢复语句的文本字符串

resumable_timeout

RESUMABLE的等待时间(以秒计,默认7200)

date_cache

日期转换高速缓存的大小(以条目计,默认为1000)

下面给出SQL*Loader控制文件的一个示例:

options(SKIP=1,errors=1000) --options(SKIP=1,ROWS=1000,errors=1000)

UNRECOVERABLE --不产生日志

load data

--CHARACTERSET utf8 --ZHS16GBK

LENGTH CHARACTER

infile 'E:\sql\sqlldr\test.csv'

APPEND into table zh_lhr --insert/APPEND/replace

fields terminated by ',' optionally enclosed by '"' ---②terminated by x'09' 一个制表符(TAB)

trailing nullcols

(

position(*:16) "TRIM(:COL_5)" "SEQ.NEXTVAL" date 'YYYY/MM/DD HH24:MI:SS',

rn ,

IMIX_TAG char(4000) "trim(:IMIX_TAG)"

)

其中,CHARACTERSET指定文件的编码格式,infile指定导入的文件。

接下来就是执行导入命令了,如下所示:

sqlldr 用户名/用户名密码@数据库名称 control= 控制文件名.ctl parallel=y log='log.txt' bad='bad.bad' direct=true readsize=4194304

当要加载的数据文件比较大的时候该如何提高SQL*Loader的性能呢?可以从以下几个方面考虑:

① ROWS的默认值为64,可以根据需要指定更合适的ROWS参数来指定每次提交记录数。

② 采用DIRECT=TRUE导入可以跳过数据库的相关逻辑,直接将数据导入到数据文件中,可以提高导入数据的性能。

③ 通过指定UNRECOVERABLE选项,可以写少量的日志,而从提高数据加载的性能。不过,推荐在加载完成后立即对数据库或至少对表空间备份。

当加载大量数据时,最好抑制日志的产生:

ALTER TABLE RESULTXT NOLOGGING;

将表修改为NOLOGGING,可以只产生少量的Redo日志,从而提高导入效率。在CONTROL文件中的load data前边加一行:UNRECOVERABLE,此选项必须要与DIRECT共同使用。对于超大数据文件的导入就要用并发操作了,即同时运行多个导入任务:

sqlldr userid=/ control=result1.ctl direct=true parallel=true

下表给出在使用SQL*Loader的过程中,一些常用的需求实现方法:

序号

问题描述

数据举例

控制文件写法举例

解决方法

1

数据文件里的数据是定长,没有分隔符

SMITH CLEAK 3904ALLEN SALESMAN 2891WARD SALESMAN 3128KING PRESIDENT 2523

TRUNCATE INTO TABLE BONUS(ENAME position(1:5),JOB position(7:15),SAL position(17:20))

position关键字用来指定列的开始和结束位置,如JOB position(7:15)是指从第7个字符开始截止到第15个字符作为JOB列的列值。position的写法也很灵活,要实现上述功能还可以换成下列几种形式:● position(*+2:15):直接指定数值的方式叫作绝对偏移量,如果使用*号,专业名词叫相对偏移量,表示上一个字段从哪里结束,这次就从哪里开始。相对偏移量也可以再做运算,比如position(*+2:15)就表示从上次结束的位置+2的地方开始。● position(*) char(9):这种相对偏移量+类型和长度的优势在于,只需要为第一列指定开始位置,其他列只需要指定列长度就可以了,实际使用中比较省事

2

数据文件中的列比要导入的表的列少,且空列又必须赋值

SMITH CLEAK 3904ALLEN SALESMAN 2891WARD SALESMAN 3128KING PRESIDENT 2523

TRUNCATE INTO TABLE BONUS(ENAME position(1:5),JOB position(7:15),SAL position(17:20),comm '0')

COMM "SUBSTR(:SAL,1,1)" 这里的COMM列可以取SAL值的第一列,并赋值给COMM列,当然也可以用PL/SQL编写自定义的函数来赋值

3

要加载的数据中包含分隔符

SMITH,CLEAK,3904ALLEN,"SALER,M",2891WARD,"SALER,""S""",3128KING,PRESIDENT,2523

TRUNCATE INTO TABLE BONUS FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (ENAME,JOB,SAL)

OPTIONALLY ENCLOSED BY参数指明定界符为双引号。CSV格式文件默认定界符就是双引号,可以根据实际情况修改OPTIONALLY的参数值

4

数据文件中的列比要导入的表中列多

SMITH CLEAK 3904ALLEN SALESMAN 2891WARD SALESMAN 3128KING PRESIDENT 2523

truncate table bonusfields terminated by ","(ename,job,sal,fcol filler)

1、将数据文件中多的列删除2、在控制文件中采用filler关键字过滤从而不录入这列数据

5

多个数据文件导入同一张表

infile kkk.datinfile kkk2.datinfile kkk3.dat

load datainfile kkk.datinfile kkk2.datinfile kkk3.dattruncate table bonusfields terminated by ","(ename , job ,sal )

多个数据文件导入同一张表,条件就是这些数据文件的格式要相同,在控制文件中可以写多个文件

6

同一个数据文件要导入不同的表

bon smithbon allenmgr kingmgr smm

load datainfile kkk.datdiscardfile ldr_case9.dsctruncateinto table B when tab='bon' (tab filler position(1:3),ename position(5:9))into table M when tab='mgr'(tab filler position(1:3),ename position(5:9))

需求是将以MGR开头的记录导入M表,以BON开头的记录导入B表,其他记录存入废弃文件中。这种情况下,可以使用WHEN关键字。

7

数据文件前n行不导入

sqlddr scott/scott control=ldr_case1.ctl skip=3 load=6

skip=3 load=6 表示前三行不导入,导入接下来的6行,即导入第4~9行

8

要加载的数据中有换行或回车符

10,aaaabOffice in Virginia

COMMENTS "REPLACE(:COMMENTS,CHR(10),'')"

用REPLACE函数将换行和回车符替换成空值。CHR(10)表示换行,CHR(13)表示回车

9

要加载的数据中含有\n符号

10,Sales \n Office in

COMMENTS "REPLACE(:COMMENTS,'\\n','')"

将\n替换成空值

10

nullif导入

1 1020 lg

REPLACE(DEPTNO position(1:2) integer external nullif DEPTNO='1',DNAME position(3:8))

nullif DEPTNO='1'表示当导入deptno的值为'1'时,则该条记录不导入

11

某些字段有空值

trailing nullcols

trailing nullcols表示表的字段没有对应的值时允许为空

12

导入数据时需要修改数据、加入默认值

(phonenumber ,addtime sysdate, --这里是默认值remark "suit"--这里是默认值)

在列的后边直接列出

13

载入每行的行号

SEQNO RECNUM TEXT POSITION(1:1024))

载入每行的行号用RECNUM

14

如何导入日期型数据

MODIFYDATE date(18) 'YYYY/MM/DD HH24:MI:SS'

在ctl文件中,字段的后面加入DATE 'yyyy-mm-dd HH24:MI:SS'即可

15

如何加载序列

SEQNUM SEQUENCE(1,1)

SEQUENCE的算法有3种装载方法,这样数据文件中可以不用第一列1、(1,1),第一个1,此方法表示从1开始,第二个1代表步伐。2、对于第一个1,还可以被更换为COUNT,计算表中的记录数后,加1开始算SEQUENCE3、还有MAX,取表中该字段的最大值后加1开始算SEQUENCE

16

将数据文件中的数据当做表中的一列进行加载

trailing nullcols(id SEQUENCE(1,1),text char(4000) "TRIM(:text)")

数据文件不用分列,所有的数据均导入数据库中的表中一列

17

如何限制错误数量

在控制文件头加上OPTIONS (ERRORS=50),表示最多允许出现50条错误

18

合并多行记录为一行记录

10,Sales,Virginia,1/5/2000

CONCATENATE 3 INTO TABLE DEPTTRUNCATE(COL1,COL2...)

其实这3行看成一行 10,Sales,Virginia,1/5/2000。通过关键字CONCATENATE可以把几行的记录看成一行记录。

下表给出了在使用SQL*Loader的过程中,经常会遇到的一些错误及其解决方法:

序号

报错

原因

解决

1

没有第二个定界字符串

csv文件中含有多个换行符

如果csv是单个换行符的话,那么加入OPTIONALLY ENCLOSED BY '"' 即可,若是有多个,则可以用微软的Excel打开文件,替换掉Excel中的强制换行符

2

ORA-26002: 表 RISK.TLHR上有定义的索引

SQL*Loader命令中含有PARALLEL这个关键字

当被导入的表中含有索引的时候,这个时候需要去掉PARALLEL这个关键字就可以了

3

SQL*Loader-510: 数据文件 (E:\lhr\sql\sqlldr\1000W-1200W.csv) 中物理记录超过最大限制 (4194304)

readsize设置过小

修改readsize为较大的值,比如设置成4M,默认为1048576,单位为字节,即1M

4

Record 1: Rejected - Error on table SQM.SQM_LHR_USER, column USER_NUM.ORA-01722: invalid number

因为换行符的问题,如果INTEGER或者NUMBER类型的栏位位于表的最后,那么在Windows下加载数据的时候,最后其实会有CR/LF的换行符,在用sqlldr导入时会把换行符也算作那个数字的一部分,从而出错

加integer或者加“TERMINATED BY WHITESPACE”

5

导入数据库出现乱码

数据库字符集:zhs16gbk客户端:zhs16gbk数据文件:al32utf8

在控制文件中加入文件的编码格式CHARACTERSET utf8

6

数据文件的字段超出最大长度

没有在每个字段后面定义数据类型,碰到此种情况,Oracle默认为该字段为VARCHAR(255)。也有可能定义了数据类型,但是数据长度的确超出4000字节长度

控制文件中对应的列后边加上CHAR(4000)

7

SQL*Loader-566

最后一行数据分隔符号后面没有回车

定义行结尾符

8

“ORA-12899: 列的值太大”错误

从文本中读取的字段值超过了数据库表字段的长度

用函数截取,如“ab CHAR(4000) "SUBSTRB(:ab,1,2000)",”

9

ORA-01461: 仅能绑定要插入LONG列的LONG值

字符类型在PL/SQL中作为变量存在,最大可支持32767个字节,但在SQL中通常只能够支持到4000字节(NCHAR为2000),因此如果声明的变量长度超出了SQL中类型长度,并且变量实际值也超出类型可接受最大值时,就会触发ORA-01461错误

当数据文件中的字段值真实长度超过4000长度时,只有一个方法:将数据表字段类型改为CLOB类型或者新增加一个临时CLOB字段,sqlldr中的“CHAR(32767)”对于CLOB字段有效。导入后再通过SQL语句更新到真实字段中

10

Illegal combination of non-alphanumeric characters

非法非字母数字字符的组合

环境变量NLS_LANG的设置和文件字符集保持一致

11

ORA-26006-Incorrect bind variable in column

SQL*Loader版本问题

使用和数据库版本一致的SQL*Loader

12

SQL*Loader-2026: 加载因 SQL 加载程序无法继续而被终止

数据文件格式问题,行数据后没有回车,或行结尾符不确定

修改控制文件的格式或数据文件的内容

关于SQL*Loader还有很多参数本书不再详述,具体可以参考官方文档。有关如何导出数据到EXCEL中,本书也不再详述。读者若有需要可以关注作者的微信公众号来阅读。

& 说明:

有关SQL*Loader(sqlldr)的更多内容可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2146660/

About Me:小麦苗

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 作者博客地址:http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

● 题目解答若有不当之处,还望各位朋友批评指正,共同进步

本文分享自微信公众号 - DB宝(xiaomaimiaolhr)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-11-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券