SQL LOADER 사용예

익명님이 말씀하신대로 그대로 읽을 수는 없습니다.
그러나 엑셀 파일을 csv파일로 저장하셔서 서버에 올리시면 읽을 수 있습니다.
오라클 샘플 데이터인 emp테이블을 예로 들어 설명 하겠습니다.

아래에서 보시는 것처럼 엑셀데이터를 cvs로 저장하여 올리시면...
emp.csv 처럼 보입니다.
이를 이용하기 위해 테이블 구조에 맞추어 EMP.ctl을 작성 하시고....
실행 시켜 주심 됩니다.
이행 해야 할 데이터가 많다면... 스크립트를 미리 한방에 만들어 하심이 편하실겁니다.

====================================================================
SQL> desc emp
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                                      NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 19801217        800                    20     
      7499 ALLEN      SALESMAN        7698 19810220       1600        300         30     
      7521 WARD       SALESMAN        7698 19810222       1250        500         30     
      7566 JONES      MANAGER         7839 19810402       2975                    20     
      7654 MARTIN     SALESMAN        7698 19810928       1250       1400         30     
      7698 BLAKE      MANAGER         7839 19810501       2850                    30     
      7782 CLARK      MANAGER         7839 19810609       2450                    10     
      7788 SCOTT      ANALYST         7566 19821209       3000                    20     
      7839 KING       PRESIDENT            19811117       5000                    10     
      7844 TURNER     SALESMAN        7698 19810908       1500          0         30     
      7876 ADAMS      CLERK           7788 19830112       1100                    20     
      7900 JAMES      CLERK           7698 19811203        950                    30     
      7902 FORD       ANALYST         7566 19811203       3000                    20     
      7934 MILLER     CLERK           7782 19820123       1300                    10     
     
14 rows selected.

==================================================== EMP.ctl
LOAD DATA
INFILE 'emp.csv'
INTO TABLE EMP
FIELDS TERMINATED BY "," (
EMPNO
, ENAME
, JOB
, MGR
, HIREDATE
, SAL
, COMM
, DEPTNO
)
==================================================== emp.csv
7369,SMITH ,CLERK    ,7902,19801217,800,    ,20
7499,ALLEN ,SALESMAN ,7698,19810220,1600,300,30
7521,WARD  ,SALESMAN ,7698,19810222,1250,500,30
7566,JONES ,MANAGER  ,7839,19810402,2975,    ,20
7654,MARTIN,SALESMAN ,7698,19810928,1250,1400,30
7698,BLAKE ,MANAGER  ,7839,19810501,2850,    ,30
7782,CLARK ,MANAGER  ,7839,19810609,2450,    ,10
7788,SCOTT ,ANALYST  ,7566,19821209,3000,    ,20
7839,KING  ,PRESIDENT,    ,19811117,5000,    ,10
7844,TURNER,SALESMAN ,7698,19810908,1500,0,30
7876,ADAMS ,CLERK    ,7788,19830112,1100,    ,20
7900,JAMES ,CLERK    ,7698,19811203,950,    ,30
7902,FORD  ,ANALYST  ,7566,19811203,3000,    ,20
7934,MILLER,CLERK    ,7782,19820123,1300,    ,10
====================================================  sqlldr 실행
sqlldr username/password control=EMP.ctl direct=true

 

출처 : 데이터베이스 사랑넹

ohyouknow님이 2007-01-12 12:38:29에 작성한 댓글입니다

http://database.sarang.net/?inc=read&aid=29358&criteria=oracle&subcrit=&id=&limit=20&keyword=sqlload&page=1