1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

I need to import iso 8061 date format into my db from csv file

Discussion in 'Oracle Apps Technical' started by heepth, Dec 11, 2014.

  1. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    its giving me different error whenever im trying with different format to solve this issue.
    anyone suggest me with good solution for this issue

    my sample date format is
    2014-12-30T08:44:32-08:00

    CONTROL FILE:
    load data
    infile 'C:\Users\Bharath\Desktop\load_eval.csv'
    badfile 'C:\Users\Bharath\Desktop\load_bad.bad'
    discardfile 'C:\Users\Bharath\Desktop\load_dsc.csv'
    replace into table xx_testload
    fields terminated by ',' optionally enclosed by '"'
    TRAILING NULLCOLS
    ( COMPANY_NAME NULLIF COMPANY_NAME=BLANKS,
    add_LOCATION NULLIF COMPANY_NAME=BLANKS,
    PHONE_NUMBER NULLIF COMPANY_NAME=BLANKS,
    EMAIL NULLIF COMPANY_NAME=BLANKS,
    ESTABLISHED DATE "YYYY-MM-DD\"T\"HH24:MI:SS\"TZH:TZM",
    TAN NULLIF COMPANY_NAME=BLANKS,
    BANK_ACCOUNT_NUMBER NULLIF COMPANY_NAME=BLANKS
    )
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi heepth,

    Try replacing following line:

    with:

    Code (SQL):
    ESTABLISHED "TO_TIMESTAMP_TZ(:established, 'YYYY-MM-DD\"T\"HH24:MI:SSTZH:TZM')",
     
    Note: I suppose the column "ESTABLISHED" is of "DATE" datatype - just beware, this field will only keep the date+time information and you'll lose the Timezone information. If you want to keep the timezone, your "ESTABLISHED" column should be of "TIMESTAMP WITH TIME ZONE" datatype.