5 Replies Latest reply: Jan 26, 2013 2:38 PM by Solomon Yakobson RSS

    SQL loader for CSV question

    andy_schnelle
      Hi all,

      I have a quick question.

      We have a hypothetical loader here
      LOAD DATA
      INFILE 'B:\Data_Files\inbound\file.txt'
      REPLACE
      INTO TABLE EXAMPLE
      FIELDS TERMINATED BY ',' optionally enclosed by '"'
      trailing nullcols
      (
      Field1 char,
      Field2 char
      )

      --*******************************
      Say the file has three fields:

      Field1,Field2,Field3

      Will the loader load the first two fields and move on to the next record or will this cause issues?

      Edited by: andy_schnelle on Jan 25, 2013 11:48 AM
        • 1. Re: SQL loader for CSV question
          Srini Chavali-Oracle
          Use the FILLER specification - http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#sthref770

          HTH
          Srini
          • 2. Re: SQL loader for CSV question
            Solomon Yakobson
            No need to. OP wants to discard extra fields which happens by default. Control file:
            LOAD DATA 
            INFILE 'C:\Temp\file.txt' 
            REPLACE
            INTO TABLE tbl
            FIELDS TERMINATED BY ',' optionally enclosed by '"' 
            trailing nullcols
            (
            Field1 char,
            Field2 char
            )
            {code}
            
            C:\Temp\file.txt:
            
            {code}
            A,B,C,D
            E,F,G
            H,I
            {code}
            
            Now:
            
            {code}
            SQL> drop table tbl purge;
            
            Table dropped.
            
            SQL> create table tbl (field1 varchar2(10),field2 varchar2(10));
            
            Table created.
            
            SQL> host sqlldr scott@orcl/tiger control=c:\temp\file.ctl log=c:\temp\file.log
            
            SQL> select * from tbl
              2  /
            
            FIELD1     FIELD2
            ---------- ----------
            A          B
            E          F
            H          I
            
            SQL> {code}
            
            SY.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            • 3. Re: SQL loader for CSV question
              Srini Chavali-Oracle
              Thanks Solomon - that is interesting - I was always under the impression that all fields need to be defined. Has this 'feature' always been available in the older versions of the database too ?

              Srini
              • 4. Re: SQL loader for CSV question
                andy_schnelle
                Thanks Solomon, precisely the answer I was hoping for!
                • 5. Re: SQL loader for CSV question
                  Solomon Yakobson
                  Srini Chavali wrote:
                  Has this 'feature' always been available in the older versions of the database too ?
                  Well, I don't use SQL*Loader often, but yes it was that way in older versions (at least in 10g).

                  SY.