Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: concatenation when using sql loader

    Hi All,

    I'm using SQL loader to load a file into a table and would like to concatenate two fields from the incoming file.

    The file format is as shown below -
    "abc","dee","REMOTE-ACCESS","Ho","01/01/1986","12:00:00"
    "ps","Pt","REMOTE-ACCESS","Saa","09/07/2011","13:54:26"
    "ky","Kt","PRIORITY-REMOTE-ACCESS","Dsey","09/05/2011","10:42:46"
    "ksey","Krt","REMOTE-ACCESS","Dsey","09/05/2011","10:42:46"
    "spil","Srj","REMOTE-ACCESS","Pil","09/06/2011","00:01:31"

    I'd like to concatenate the last 2 fields in the file.
    "09/06/2011","00:01:31" as 09/06/2011 00:01:31

    Find the control file below

    options (skip=1)
    LOAD DATA
    INFILE 'final_file.txt'
    INTO TABLE access_temp
    REPLACE
    fields terminated by "," optionally enclosed by '"'
    TRAILING NULLCOLS
    (
    User_ID char,
    First_Name char,
    User_Group char,
    Last_Name char,
    Last_login_timestamp date "MM/DD/YYYY HH24:MIS"
    )

    CREATE TABLE access_temp
    (
    USER_ID VARCHAR2(30 BYTE),
    FIRST_NAME VARCHAR2(50 BYTE),
    LAST_NAME VARCHAR2(50 BYTE),
    USER_GROUP VARCHAR2(50 BYTE),
    LAST_LOGIN_TIMESTAMP DATE
    )

    I tried || and + but it looks like that doesnt work. Is this doable?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    BOUNDFILLER is the keyword here.

    Here's how the control file should look like; note the "imaginary" column I used (its name is "nothing") and the way it is used in the last column value creation.

    Code:
    LOAD DATA
    INFILE *
    INTO TABLE access_temp
    REPLACE
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
     (
      User_ID        char,
      First_Name     char,
      User_Group     char,
      Last_Name      char,
      nothing        boundfiller,
      Last_login_timestamp "to_date(:nothing ||' '|| :last_login_timestamp, 'mm/dd/yyyy hh24:mi:ss')"
     )
    
    BEGINDATA
    "abc","dee","REMOTE-ACCESS","Ho","01/01/1986","12:00:00"
    "ps","Pt","REMOTE-ACCESS","Saa","09/07/2011","13:54:26"
    "ky","Kt","PRIORITY-REMOTE-ACCESS","Dsey","09/05/2011","10:42:46"
    "ksey","Krt","REMOTE-ACCESS","Dsey","09/05/2011","10:42:46"
    "spil","Srj","REMOTE-ACCESS","Pil","09/06/2011","00:01:31"
    Loading session:
    Code:
    SQL> $sqlldr scott/tiger control=test.ctl log=test.log
    
    SQL*Loader: Release 10.2.0.1.0 - Production on Cet Ruj 8 22:44:41 2011
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 4
    Commit point reached - logical record count 5
    What we've done (I formatted columns so that they fit the screen width):
    Code:
    SQL> select * from access_temp;
    
    USER_ID FIRST_NAME LAST_NAME USER_GROUP                LAST_LOGIN_TIMESTAM
    ------- ---------- --------- ------------------------- -------------------
    abc     dee        Ho        REMOTE-ACCESS             01/01/1986 12:00:00
    ps      Pt         Saa       REMOTE-ACCESS             09/07/2011 13:54:26
    ky      Kt         Dsey      PRIORITY-REMOTE-ACCESS    09/05/2011 10:42:46
    ksey    Krt        Dsey      REMOTE-ACCESS             09/05/2011 10:42:46
    spil    Srj        Pil       REMOTE-ACCESS             09/06/2011 00:01:31
    
    SQL>

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    Awesome!! Thank you soooooooo much Littlefoot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •