Re: Skipping a Column With Loader

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/07/07
Message-ID: <33c0afe9.6255783_at_www.sigov.si>#1/1

On Sun, 06 Jul 1997 02:50:20 GMT, teetshd_at_ucbeh.san.uc.edu (greg teets) wrote:

>This may be a RTFM Question. If so, I apologize.
>
>I have a text file I want to load with SQLLoader. However, I want to
>skip one of the columns in the comma-delimited file. Can I do this,
>and if so, how?
>
>Thanks

AFAIK it can't be done directly. You must use intermediary table to load all of your fields from your text file and than reinsert the records into your final table without the unwanted column.

But you can avoid this two-step method if you create a view, based on your target table. This view must have one additional column, selected additionaly from the same table.

Example:
SQL> desc emp

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                                    NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9) 

SQL> CREATE VIEW view_emp AS
  2 (SELECT empno, ename, job, ename AS dummy FROM emp);

View created.

SQL> DESC view_emp

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                                    NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 DUMMY                                    VARCHAR2(10)

The following control file will insert records into the table emp through the view, leaving out the second (=unwanted) field from the text data.

  LOAD DATA INFILE *
  INSERT INTO TABLE view_emp
  FIELDS TERMINATED BY ','

        (empno, dummy, ename, job)
  BEGINDATA        
  1,skip me1,SMITH,CLERK

  2,skip me 2,SCOTT,MANAGER
  3,me too!,TEETS,PRESIDENT

The result after loading with loader:

SQL> SELECT * FROM EMP;
    EMPNO ENAME JOB
--------- ---------- ---------

        1 SMITH      CLERK
        2 SCOTT      MANAGER
        3 TEETS      PRESIDENT

3 rows selected.

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Jul 07 1997 - 00:00:00 CEST