Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Problem loading datetime from CSV file - 10g

    Hi

    I'm trying to load a CSV file which contains a datetime column containing a date format mm/dd/yyyy hh:mi:ss AM/PM.

    I'm using the external table loading method and have specified the formula to be used when loading this CSV file as:

    Code:
    TO_DATE (DATETIME, 'DD/MM/YYYY HH24:MI:SS')
    I understand using this formula takes precedence over the NLS settings for the Database, Instance and Session?

    Which are:

    Code:
    SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_NCHAR_CHARACTERSET         UTF8
    NLS_LANGUAGE                   ENGLISH
    NLS_TERRITORY                  UNITED KINGDOM
    NLS_CURRENCY                   #
    NLS_ISO_CURRENCY               UNITED KINGDOM
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CHARACTERSET               WE8ISO8859P15
    NLS_CALENDAR                   GREGORIAN
    NLS_DATE_FORMAT                DD-MON-RR
    NLS_DATE_LANGUAGE              ENGLISH
    NLS_SORT                       BINARY
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_TIME_FORMAT                HH24.MI.SSXFF
    NLS_TIMESTAMP_FORMAT           DD-MON-RR HH24.MI.SSXFF
    NLS_TIME_TZ_FORMAT             HH24.MI.SSXFF TZR
    NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH24.MI.SSXFF TZR
    NLS_DUAL_CURRENCY              ?
    NLS_COMP                       BINARY
    NLS_LENGTH_SEMANTICS           BYTE
    NLS_NCHAR_CONV_EXCP            FALSE
    NLS_RDBMS_VERSION              10.2.0.5.0
    
    
    
    SQL> SELECT * FROM NLS_INSTANCE_PARAMETERS;
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE                   ENGLISH
    NLS_TERRITORY                  UNITED KINGDOM
    NLS_SORT
    NLS_DATE_LANGUAGE
    NLS_DATE_FORMAT
    NLS_CURRENCY
    NLS_NUMERIC_CHARACTERS
    NLS_ISO_CURRENCY
    NLS_CALENDAR
    NLS_TIME_FORMAT
    NLS_TIMESTAMP_FORMAT
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_TIME_TZ_FORMAT
    NLS_TIMESTAMP_TZ_FORMAT
    NLS_DUAL_CURRENCY
    NLS_COMP
    NLS_LENGTH_SEMANTICS           BYTE
    NLS_NCHAR_CONV_EXCP            FALSE
    
    
    
    SQL> SELECT * FROM NLS_SESSION_PARAMETERS;
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE                   AMERICAN
    NLS_TERRITORY                  AMERICA
    NLS_CURRENCY                   $
    NLS_ISO_CURRENCY               AMERICA
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CALENDAR                   GREGORIAN
    NLS_DATE_FORMAT                DD-MON-RR
    NLS_DATE_LANGUAGE              AMERICAN
    NLS_SORT                       BINARY
    NLS_TIME_FORMAT                HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY              $
    NLS_COMP                       BINARY
    NLS_LENGTH_SEMANTICS           BYTE
    NLS_NCHAR_CONV_EXCP            FALSE
    The problem is I keep getting:

    Code:
    Literal does not match format string.
    If I use the formula:

    Code:
    TO_DATE (DATETIME, 'MM/DD/YYYY HH24:MI:SS')
    I get:

    Code:
    Not a valid month
    Any ideas?

    Regards
    Shajju

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Post ddl for your external table and some example data.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'm trying to load a CSV file which contains a datetime column containing a date format mm/dd/yyyy hh:mi:ss AM/PM.
    Below does not map the "AM/PM"
    >TO_DATE (DATETIME, 'DD/MM/YYYY HH24:MIS')

    You need something like below
    TO_DATE (DATETIME, 'DD/MM/YYYY HH:MIS AM')
    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.

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    I checked the external table. It contains the contents of the CSV file.

    The DATETIME column (aka RESULT) has a data type of VARCHAR2

    Code:
    The format of the date in the External Table is: yyyy-mm-dd hh24:mi
    Code:
    CREATE TABLE EXT_1234567890
    (
      RESULT                          VARCHAR2(500 BYTE),
      AAAABBBBB                   VARCHAR2(500 BYTE),
      AABBCCDDEE                 VARCHAR2(500 BYTE),
     )
    ORGANIZATION EXTERNAL
      (  TYPE ORACLE_LOADER
         DEFAULT DIRECTORY LOAD_00002
         ACCESS PARAMETERS 
           ( RECORDS DELIMITED BY '\r\n'  NOLOGFILE  NOBADFILE  DATE_CACHE 0  skip 2 FIELDS TERMINATED BY  ','  OPTIONALLY ENCLOSED BY '"'  MISSING FIELD VALUES ARE NULL  (Result CHAR(500),  AAAABBBBB  CHAR(500),  AABBCCDDEE CHAR(500))  )
         LOCATION (LOAD_00002:'Loaddata.ext')
      )
    REJECT LIMIT UNLIMITED
    NOPARALLEL
    NOMONITORING;
    Last edited by shajju; 02-23-13 at 17:09.

  5. #5
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    When you do a "select *" from the table, Do you get an error?

    Does the date field in the flat file have leading/trailing blanks/spaces?

    If you can view the data, Post an example of what a record actually looks like on each record in the flat file(Loaddata.ext).

  6. #6
    Join Date
    Aug 2008
    Posts
    464
    I get no error and can select data from the ext table.

    No spaces/blanks as far as I can see.

    RESULT COL1 COL2

    2012-11-24 23:30 1 2

  7. #7
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    -- Your data(a comma delimited flat file)
    2012-11-24 23:30,1,2

    Try it like this:
    Code:
    create table ext_1234567890 (
      result            date,
      col1              varchar2( 500 ),
      col2              varchar2( 500 )
     )
    organization external (
      type              oracle_loader
      default directory load_00002
      access parameters (
        records delimited by '\r\n'
        nologfile
        nobadfile
        skip 2
        fields terminated by  ','  optionally enclosed by '"'
        missing field values are null
        ( result     date 'yyyy-mm-dd hh24:mi',
          aaaabbbbb  char( 500 ),
          aabbccddee char( 500 ) )  )
         location ( 'Loaddata.ext' ) )

  8. #8
    Join Date
    Aug 2008
    Posts
    464
    Thanks for the suggestion. I found the problem.

    Basically, the
    TO_DATE (DATETIME, 'DD/MM/YYYY HH24:MIS') expression I was using to load the date data type values

    needed to be

    TO_DATE (DATETIME, 'YYYY/MM/DD' HH24:MIS')

    which is the format in the External Table.

    Regards
    Shajju

Posting Permissions

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