【DB笔试面试446】如何将文本文件或Excel中的数据导入数据库？
【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/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步