Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2014
    Posts
    44

    Unanswered: Importing date from CSV format to a table

    Hi there,

    I have a question regarding formatting input.
    So far i have this,

    0,1/13/2014,ME-BD2-C 1,
    0,1/13/2014,ME-BD2-C 2
    0,1/13/2014,ME-BD2-C 3

    in my .CSV file.
    How do I format the CTL file so that oracle can accept the date format given by 1/13/2014.

    thanks,

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Read The Fine manual below
    Contents
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example:
    Code:
    SQL> create table test
      2  (c_id number,
      3   c_date date,
      4   c_string varchar2(20));
    
    Table created.
    Control file:
    Code:
    LOAD DATA 
    INFILE *
    INTO TABLE test
    FIELDS TERMINATED BY ',' 
    TRAILING NULLCOLS 
    ( c_id,
      c_date DATE "mm/dd/yyyy",
      c_string
    )
    
    begindata
    0,1/13/2014,ME-BD2-C 1,
    0,1/13/2014,ME-BD2-C 2
    0,1/13/2014,ME-BD2-C 3
    Loading:
    Code:
    M:\a1_maknuto>sqlldr scott/tiger@ora10 control=test13.ctl log=test13.log
    
    SQL*Loader: Release 11.2.0.1.0 - Production on ╚et OŚu 13 07:52:39 2014
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    Commit point reached - logical record count 2
    Commit point reached - logical record count 3
    Result:
    Code:
    SQL> alter session set nls_date_format='mm/dd/yyyy';
    
    Session altered.
    
    SQL> select * From test;
    
          C_ID C_DATE     C_STRING
    ---------- ---------- --------------------
             0 01/13/2014 ME-BD2-C 1
             0 01/13/2014 ME-BD2-C 2
             0 01/13/2014 ME-BD2-C 3
    
    SQL>

  4. #4
    Join Date
    Mar 2014
    Posts
    44
    Thank you so much for the concise answer. My last question is how to discard the CSV element '-' and send them as null or 0 in the ORACLE DB

    thanks

Posting Permissions

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