DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Can't SUBSTR date fields. Mixed date field lengths.

  1. #1
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    3

    Can't SUBSTR date fields. Mixed date field lengths.

    I am trying to import a text file filled with numeric, text and date fields. Unforunetly the date fields come in 2 different formats. What is worse is that the format changes from record to record within a field. Here are the formats.

    YYYY-MM-DD HH:MM:SS
    YYYY-MM-DD HH:MM:SS.SSSSSSSSS

    The records are variable length with "|" pipe delimiters. I am using sqlldr to load the data on Oracle 7.3.3.

    My first option is to go back to where the data came from and ask for standard date formats. Too simple for me.

    The other option is to chop the ".SSSSSSSSS" off the long date format using something similar to SUBSTR. I have tried various combinations of using TO_DATE of TO_CHAR to try to import the records without success. Should there be a way to chop the long date to short within the control file.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Since no one has come up with anything in over a day, I would:
    - create temp_table with a varchar2 column for the date
    - import into temp_table
    - insert into final_table select a,b,c,to_date(substr(d,1,19,'DD-etc
    - drop temp_table

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Here is a sample controlfile named TEST.CTL:
    Code:
    load data
    infile *
    into table test
    fields terminated by '|'
    (col1 CHAR,
     col2 DATE "YYYY-MM-DD HH24:MI:SS          ",
     col3 CHAR
    )
    begindata
    pre1|2003-06-26 23:03:59|post1
    pre2|2003-06-26 23:04:01.123456789|post2
    Note that in date format specification for COL2 there is 10 trailing blanks for your eventual subseconds part (".SSSSSSSSS")

    And here is a test:
    Code:
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production
    With the Partitioning option
    JServer Release 8.1.7.1.1 - Production
    
    SQL> create table test (col1 varchar2(10), col2 date, col3 varchar2(10));
    
    Table created.
    
    SQL> host "D:\ORACLE\ORA81\BIN\SQLLDR SCOTT/TIGER@O815 C:\TEMP\TEST.CTL"
    
    SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
    
    Session altered.
    
    SQL> SELECT * FROM TEST;
    
    COL1       COL2                 COL3
    ---------- -------------------- ----------
    pre1       26-JUN-2003 23:03:59 post1
    pre2       26-JUN-2003 23:04:01 post2
    
    SQL>
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Jurij scores again

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    3
    Jurij,
    Thank you for your suggestion. You seemed to know exactly what I am doing. I tried your solution but got this error message in the log file when running it.

    Record 1: Rejected - Error on table TEST, column COL2.
    ORA-01840: input value not long enough for date format

    I really have no way of checking this but could this be because I am using Oracle 7.3.3 and SQLPLUS 3.3.4.0.0.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by roliver
    Record 1: Rejected - Error on table TEST, column COL2.
    ORA-01840: input value not long enough for date format

    I really have no way of checking this but could this be because I am using Oracle 7.3.3 and SQLPLUS 3.3.4.0.0.
    Yes, I'm affraid the reason for this error message might be your database release. Unfortunately I don't have any pre-8i database available to test this - I've tried my example on 8i and it worked.

    I guess after all you'll realy have to go to the origin where the data file is created and asked them to trim that ".SSSSSSSSSS" part off. Or you can use what DaPi has suggested.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jun 2003
    Posts
    1
    I tested in 9i, it works fine.
    If it's a one-time load, then you can replace ".SSSSSSSSS" with
    null by using vi editor or any editor.Then you can use that file which will have this format "YYYY-MM-DD HH:MM:SS" only.

  8. #8
    Join Date
    Jan 2002
    Posts
    148
    Jurij - Thats just wonderful idea...never thought it was possible.
    Bendon - if its 9i then why dont u use TIMESTAMP datatype ???? - Simple.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    3
    A colegue pointed this out to me today and I thought you would like the solution. You can SUBSTR a date string if you add the terminated by clause. This would change Jurij's example to

    load data
    infile *
    into table test
    fields terminated by '|'
    (col1 CHAR,
    col2 DATE 'RRRR-MM-DD HH24:MI:SS' TERMINATED BY '|' "SUBSTR(:col2,1,19)",
    col3 CHAR
    )
    begindata
    pre1|2003-06-26 23:04:01.123456789|post1
    pre2|2003-06-26 22:54:05|post2

    I was under the wrong impression that "fields terminated by '|' was enough. I really don't know why "TERMINATED BY '|'" needs to be in the col2 line but it worked. Thanks for your comments.
    Last edited by roliver; 06-30-2003 at 04:47 PM.

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Nice litle trick indeed!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width