Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    11

    Unanswered: SQL Loader and Dates

    Hi All,

    I'm new to this forum and quite new to Oracle/SQL.

    I have Oracle Home edition setup at home and I'm trying to load the Orders table from the MS Access Northwind table into a table I created in Oracle.

    I have successfully loaded the Customers table. I setup all the fields as they were in MS Access for the Orders table but I'm having problems loading it into Oracle. SQL Loader runs and it says something about committ but when I check the Orders table in Oracle the information is not there. I think it's something to do with dates because I can load other fields in the Orders table successfully.

    The control file is a follows:

    Load Data
    INFILE 'C:\Orders.csv'
    REPLACE
    INTO TABLE slonge.Orders
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (orderid INTEGER EXTERNAL,
    customerid,
    EmployeeID INTEGER EXTERNAL,
    Orderdate "to_date(:Orderdate,'DDMMYYYY')",
    Requireddate "to_date(:Requireddate,'DDMMYYY')",
    Shippeddate "to_date(hippeddate,'DDMMYYYY')",
    Shipvia,
    Freight DECIMAL EXTERNAL,
    Shipname,
    Shipaddress,
    Shipcity,
    Shipregion,
    Shippostalcode,
    Shipcountry)

    The dates in the CVS file are in the format DD/MM/YYYY and the dates field in Oracle as defined as DATE. I've searched all over the Internet but still can't figure this out. Solutions to this problem will be well appreciated.

    Rgds,

    S

  2. #2
    Join Date
    May 2006
    Posts
    132
    If the data in your CVS file is in the format of DD/MM/YYYY, then set your date types in the control file as:

    Code:
    Orderdate DATE 'DD/MM/YYYY',
    Requireddate DATE 'DD/MM/YYYY',
    Shippeddate DATE 'DD/MM/YYYY',
    Without any error messages, it's hard to suggest anything else.

  3. #3
    Join Date
    Nov 2006
    Posts
    11

    Solution worked...but more prolems

    Hi Brian,

    Thanks for the solution- it worked perfect. However, I'm now stuck with trying to load the 'Freight' column.

    In Oracle I have defined the datatype as float for 'Freight' because it is in decimal places in the csv file, for example 23.56. However, same as before with the dates it is not loading. The control file entry for 'Freight' is:

    Freight DECIMAL EXTERNAL,

    I know I've probably made a mistake somewhere. I'm new to this SQL Loader. Any help appreciated.

    S

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    When numeric columns you'll have to supply the format mask (similar to the date columns, as ebrian has already shown).

    Check out the Oracle manual for number format models:
    http://download-west.oracle.com/docs...ts4a.htm#34597

  5. #5
    Join Date
    Nov 2006
    Posts
    11

    All done!

    Thanks Brian and Shammat,

    I altered the 'Freight' column in Oracle to number(19,2)- enough to hold the currency details. Then changed the control file for that column to:

    Freight decimal external,

    And everything as now uploaded fine.

    References:

    http://www.wisc.edu/drmt/sqlldr-pc.html
    http://www.oreilly.com/catalog/orsql...pter/ch01.html
    https://www1.columbia.edu/sec/acis/d...e/faq-sql.html

  6. #6
    Join Date
    May 2006
    Posts
    132
    Seaweed, glad to see you got it worked out. Thanks for the update.

Posting Permissions

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