Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007
    Posts
    54

    Unanswered: importing ms access data

    i'm trying to load data from access into oracle. i dumped all data into a xls file from access.
    the field i'm having trouble with is a timestamp column.
    here's the code that was generated for the table (which was created successfully):
    Code:
    SET DEFINE OFF
    CREATE TABLE "Audit" ( "MyKey" VARCHAR2(255),
    "MyKeyName" VARCHAR2(255),
    "userid" VARCHAR2(50) NOT NULL,
    "NewVal" VARCHAR2(4000),
    "OldVal" VARCHAR2(4000),
    "dtchg" TIMESTAMP NOT NULL,
    "fldname" VARCHAR2(50) NOT NULL,
    "FrmName" VARCHAR2(255),
    "ChangeID" INTEGER NOT NULL);
    and one of the insert statements created by the data loader:


    Code:
    INSERT INTO "Audit" ("ChangeID", "FrmName", "fldname", "dtchg", "OldVal", "NewVal", "userid", "MyKeyName", "MyKey") 
    VALUES (2, 'Splash', NULL, to_date('39,731.519', 'null'), NULL, NULL, 'jj', 'login', 'logout');
    as you can see, the date is really weird. in the xls file, i have tried both:
    10/10/2008 12:26 and 2008-10-10 12:26:47
    but both get translated oddly.

    i've also tried to edit the sql statement so the date was "normal":
    Code:
    INSERT INTO "Audit" ("ChangeID", "FrmName", "fldname", "dtchg", "OldVal", "NewVal", "userid", "MyKeyName", "MyKey") 
    VALUES (1, 'Splash', NULL, to_date('2008-10-10 12:26:47', 'null'), NULL, NULL, 'jj', 'login', 'Login');
    I have two questions:
    1. i'm getting an error message saying that the date format is not recognized, regardless of which sql code i use.
    2. why is the date field in my xls file being replaced with such an odd number? maybe it makes sense but i'm just missing something....

    thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    TO_DATE

    You need to provide a valid format mask for the TO_DATE() function.
    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
    Jul 2007
    Posts
    54

    valid format mask

    can you give me an example of what you mean?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Are you incapable or unwilling to Read The Fine Manual at the URL I provided?
    It is documented in the manual.
    Why should I waste my time posting it again here for you?
    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.

  5. #5
    Join Date
    Jul 2007
    Posts
    54
    somehow i missed the link at the top of you post. i initially only noticed your comments.
    sorry. i really appreciate your help - i also don't think you need to be rude. people make mistakes.
    ??
    thank you.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Access stores all dates as a formatted number. Why not simply create an odbc link in access to your oracle table and then do a simple append query?

    Also do NOT use lowercase letters to build oracle columns and tables. It will allow you, but it is a nightmare to maintain or query.
    Last edited by beilstwh; 05-04-09 at 16:45.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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