如何将文本文件或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,其常用参数的含义如下表所示:

下面给出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的过程中,一些常用的需求实现方法:

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

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

&说明:

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

---------------优质麦课------------

详细内容可以添加麦老师微信或QQ私聊。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181130A00JOB00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码关注腾讯云开发者

领取腾讯云代金券