将CSV导入Oracle数据库中的办法

microjuz

在Plsql Developer中 export to csv 很方便，但是没有相应的逆向的import功能， 譬如某次对查询出来的结果export to csv做了保存，之后如果需要将该csv文件再导入数据库，对这部分查询数据再做处理，会不太方便。

大约有40w数据。数据格式为：

"R1_1","R1_2","R4_1","R4_2","R12","H1","RDM","H1","RDM","R7_1","R7_2"
"艾","炳云","1944","8","2","331102101207002081","1","331102001006016074","1","3",""
"艾","程","1978","1","6","330204001008009032","2","330204004007015052","1","3",""
"艾","春丽","1978","10","3","330522100201010044","2","330522100037002170","1","2",""

方法1、在编辑器中替换 \n 为 \n insert into table_name values(
方法2、先导入Access数据库，然后导入Oracle

方法1面对数据量大时不能用。

其实可以用oracle的external table来处理，创建外部表，读取文本文件，像数据表那样处理。

第一步：创建Directory
create or replace directory dest_dir_name as 'G:\'

第二步：创建external table，读取数据文件
create table TEMP_12
(
  R1_1  VARCHAR2(255),
  R1_2  VARCHAR2(255),
  R4_1  VARCHAR2(255),
  R4_2  VARCHAR2(255),
  R12   VARCHAR2(255),
  H1    VARCHAR2(255),
  RDM   VARCHAR2(255),
  H1_1  VARCHAR2(255),
  RDM_1 VARCHAR2(255),
  R7_1  VARCHAR2(255),
  R7_2  VARCHAR2(255)
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY dest_dir_name
    ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (R1_1, R1_2, R4_1, R4_2, R12, H1, RDM, H1_1, RDM_1, R7_1, R7_2))
    LOCATION ('test20110217.csv')
  )
PARALLEL
REJECT LIMIT 0
NOMONITORING;