首页 > 代码库 > SQLLoader2(导入EXCEL或csv格式的文件)

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

2024-08-05 07:37:02   217人阅读

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

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

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

OPTIONS(SKIP=1) --跳过第一行LOAD DATAINFILE ‘D:\oracletest\testSqlLdr2.csv‘APPENDINTO TABLE EMP2FIELDS 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.ctlSQL*Loader: Release 11.2.0.1.0 - Production on 星期日 11月 30 15:35:40 2014Copyright (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 207499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 307521 WARD SALESMAN 7698 22-2月 -81 1250 500 307566 JONES MANAGER 7839 02-4月 -81 2975 207654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 307698 BLAKE MANAGER 7839 01-5月 -81 2850 307782 CLARK MANAGER 7839 09-6月 -81 2450 107788 SCOTT ANALYST 7566 19-4月 -87 3000 207839 KING PRESIDENT 17-11月-81 5000 107844 TURNER SALESMAN 7698 08-9月 -81 1500 0 307876 ADAMS CLERK 7788 23-5月 -87 1100 207900 JAMES CLERK 7698 03-12月-81 950 307902 FORD ANALYST 7566 03-12月-81 3000 207934 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 DATAINFILE ‘D:\oracletest\222.csv‘REPLACEINTO TABLE EMP2FIELDS 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.ctlSQL*Loader: Release 11.2.0.1.0 - Production on 星期日 11月 30 15:43:26 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.达到提交点 - 逻辑记录计数 14

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


联系
我们