ITPub博客

首页 > 数据库 > Oracle > Oracle 巧用外部表将大量excel数据导入数据库

Oracle 巧用外部表将大量excel数据导入数据库

Oracle 作者:rtt8387 时间:2015-10-07 20:34:09 0 删除 编辑


标题:Oracle 巧用外部表将大量excel数据导入数据库 

作者:lōττéry©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]


注释:
    之前有写过 如何将.dbf/.xls/.csv文件导入oracle数据库  适用量较小的文件;
    当excel/txt/csv/dbf等文件数据大于10w,直接导入可能比较慢,如下简单使用了下oracle的外部表功能使数据库直接加载/读取系统文件(而非insert表)。


1、将excel另存为.txt格式,再传到数据库directory 路径(目录列表:select * from dba_directories
2、执行创建外部表语句:

   create table test
   (name1 varchar(4000 )/*等其他字段*/)
    organization external
   (type oracle_loader
    default directory bak
    access parameters
    ( records delimited by newline characterset UTF16
    fields terminated by "  "
    )location ( '文件名称.txt' ))
    reject limit unlimited

参数注释:
    ( records delimited by newline characterset UTF16 /*字符集要和“文件名称.txt”字符集相同,而非数据和系统文件(查看T.txt字符集命令:file T.txt)*/
    fields terminated by "  " /*描述字段的终止符*/

3、查询表即可 select * from test;
 

参数介绍:

a)ORGANIZATION EXTERNAL 关键字,必须要有。以表明定义的表为外部表。
b)外部表的类型
      ORACLE_LOADER:定义外部表的缺省方式,只能只读方式实现文本数据的装载。
      ORACLE_DATAPUMP:支持对数据的装载与卸载,数据文件必须为二进制dump文件。可以从外部表提取数据装载到内部表,也可以从内部表卸载数据作为二进制文件填充到外部表。
c)DEFAULT DIRECTORY:缺省的目录指明了外部文件所在的路径
d)LOCATION:定义了外部表的位置
e)ACCESS PARAMETERS:描述如何对外部表进行访问
     RECORDS关键字后定义如何识别数据行  
     DELIMITED BY 'XXX'——换行符,常用newline定义换行,并指明字符集。对于特殊的字符则需要单独定义,如特殊符号,
     可以使用OX'十六位值',例如tab(/t)的十六位是9,则DELIMITEDBY0X'09';
     cr(/r)的十六位是d,那么就是DELIMITEDBY0X'0D'。
     SKIP X ——跳过X行数据,有些文件中第一行是列名,需要跳过第一行,则使用SKIP 1。
     FIELDS关键字后定义如何识别字段,常用的如下:
     FIELDS:TERMINATED BY 'x'——字段分割符。
     ENCLOSED BY 'x'——字段引用符,包含在此符号内的数据都当成一个字段。
      例如一行数据格式如:"abc","a""b,""c,"。使用参数TERMINATED BY ',' ENCLOSED BY '"'后,系统会读到两个字段,第一个字段的值是abc,第二个字段值是a"b,"c,。
     LRTRIM ——删除首尾空白字符。
     MISSING FIELD VALUES ARE NULL——某些字段空缺值都设为NULL。
     对于字段长度和分割符不确定且准备用作外部表文件,可以使用UltraEdit、Editplus等来进行分析测试,如果文件较大,则需要考虑将文件分割成小文件并从中提取数据进行测试。
f)FIELDS TERMINATED BY ","  --描述字段的终止符 
g)REJECT LIMIT UNLIMITED    --描述允许的错误数,此处为无限制 
 
  【源于本人笔记】 若有书写错误,表达错误,请指正...


此条目发表在  Oracle  分类目录。将固定连接加入收藏夹。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24113018/viewspace-1813436/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-02

  • 博文量
    43
  • 访问量
    76937