SQLLoader2(导入EXCEL或csv格式的文件)

浏览数：32 / 时间：2015年06月12日

一、EXCEL文件导入：
1、首先将EXCEL文件另存为CSV格式文件--->用UltraEdit工具打开时可看到字段之间以逗号分隔。

  ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
1,7369,SMITH,CLERK,7902,1980/12/17,800,,20
2,7499,ALLEN,SALESMAN,7698,1981/2/20,1600,300,30
3,7521,WARD,SALESMAN,7698,1981/2/22,1250,500,30
4,7566,JONES,MANAGER,7839,1981/4/2,2975,,20
5,7654,MARTIN,SALESMAN,7698,1981/9/28,1250,1400,30
6,7698,BLAKE,MANAGER,7839,1981/5/1,2850,,30
7,7782,CLARK,MANAGER,7839,1981/6/9,2450,,10
8,7788,SCOTT,ANALYST,7566,1987/4/19,3000,,20
9,7839,KING,PRESIDENT,,1981/11/17,5000,,10
10,7844,TURNER,SALESMAN,7698,1981/9/8,1500,0,30
11,7876,ADAMS,CLERK,7788,1987/5/23,1100,,20
12,7900,JAMES,CLERK,7698,1981/12/3,950,,30
13,7902,FORD,ANALYST,7566,1981/12/3,3000,,20
14,7934,MILLER,CLERK,7782,1982/1/23,1300,,10

2、执行导入
--编写控制文件：testSqlLdr2.ctl

OPTIONS(SKIP=1) --跳过第一行
LOAD DATA
INFILE ‘D:\oracletest\testSqlLdr2.csv‘
APPEND
INTO TABLE EMP2
FIELDS TERMINATED BY ‘,‘
(
virtual_column FILLER, --这是一个虚拟字段，用来跳过由PL/SQL Developer生成的第一列序号 
EMPNO,ENAME,JOB,MGR,HIREDATE date ‘YYYY/MM/DD‘,SAL,COMM,DEPTNO)

--执行命令

D:\oracletest>sqlldr scott/tiger@orcl control=testSqlLdr2.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 星期日 11月 30 15:35:40 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

达到提交点 - 逻辑记录计数 14

--验证结果
--已导入成功

SQL> select * from emp2;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择14行。

二、CSV文件的导入
1、这种文件一般是从PLSQL中导出的查询结果，内容格式如下：

" ","EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
"1","7369","SMITH","CLERK","7902","1980/12/17","800.00","","20"
"2","7499","ALLEN","SALESMAN","7698","1981/2/20","1600.00","300.00","30"
"3","7521","WARD","SALESMAN","7698","1981/2/22","1250.00","500.00","30"
"4","7566","JONES","MANAGER","7839","1981/4/2","2975.00","","20"
"5","7654","MARTIN","SALESMAN","7698","1981/9/28","1250.00","1400.00","30"
"6","7698","BLAKE","MANAGER","7839","1981/5/1","2850.00","","30"
"7","7782","CLARK","MANAGER","7839","1981/6/9","2450.00","","10"
"8","7788","SCOTT","ANALYST","7566","1987/4/19","3000.00","","20"
"9","7839","KING","PRESIDENT","","1981/11/17","5000.00","","10"
"10","7844","TURNER","SALESMAN","7698","1981/9/8","1500.00","0.00","30"
"11","7876","ADAMS","CLERK","7788","1987/5/23","1100.00","","20"
"12","7900","JAMES","CLERK","7698","1981/12/3","950.00","","30"
"13","7902","FORD","ANALYST","7566","1981/12/3","3000.00","","20"
"14","7934","MILLER","CLERK","7782","1982/1/23","1300.00","","10"

2、执行导入
--编写控制文件：testSqlLdr3.ctl

OPTIONS(SKIP=1) --跳过第一行
LOAD DATA
INFILE ‘D:\oracletest\222.csv‘
REPLACE
INTO TABLE EMP2
FIELDS TERMINATED BY ‘,‘ 
OPTIONALLY ENCLOSED BY ‘"‘ -- 数据中每个字段用 ‘"‘ 框起
(
virtual_column FILLER, --这是一个虚拟字段，用来跳过由PL/SQL Developer生成的第一列序号 
EMPNO,ENAME,JOB,MGR,HIREDATE date ‘YYYY/MM/DD‘,SAL,COMM,DEPTNO)

--执行命令,导入成功

D:\oracletest>sqlldr scott/tiger@orcl control=testSqlLdr3.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 星期日 11月 30 15:43:26 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

达到提交点 - 逻辑记录计数 14