Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Importing CSV file and Date & Timestamp fields

    I'm using DB2 v9.1. I'm importing a Excel CSV file to a DB2 table. It does not like the Date field and the Timestamp fields. I'm new to working with this in DB2 so I'm looking for some help.

    Here is how the Date and Timestamp fields look like in the Excel CSV file:
    06/23/2010 6/23/2010 7:18 6/23/2010 7:22


    Here is what is setup in DB2 right now in the Table:
    Name Datetype Length Nullable
    OrderStartDate Date 4 No
    StartDateTime Timestamp 10 No
    EndDateTime Timestamp 10 No


    Here is part of the Create Statement for creating the Table:

    CREATE TABLE RF_DAILY_DUMP_TABLE
    (ORDERSTARTDATE DATE NOT NULL,
    STARTDATETIME TIMESTAMP NOT NULL,
    ENDDATETIME TIMESTAMP NOT NULL,


    Should we reformat the Excel CSV file to match the DB2 fields or what should I change the DB2 fields to import?

    Could someone that is experience give me some advice here?
    Thank you.

    CC

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,458
    Quote Originally Posted by rockycj View Post
    I'm using DB2 v9.1. I'm importing a Excel CSV file to a DB2 table. It does not like the Date field and the Timestamp fields. I'm new to working with this in DB2 so I'm looking for some help.

    Here is how the Date and Timestamp fields look like in the Excel CSV file:
    06/23/2010 6/23/2010 7:18 6/23/2010 7:22

    Use the dateformat and timestampformat modifiers on IMPORT command.

  3. #3
    Join Date
    Feb 2007
    Posts
    84

    Did that it's still not Importing

    Okay I updated to the TIMESTAMPFORMAT for all 3 of these fields.
    This is what is actually in the TimeStamp fields:
    6/23/2010 7:18:45 AM but it's showing up in this format on the CSV file:

    6/23/2010 7:18

    Here is what I'm doing now:

    dbtest$ db2 "IMPORT FROM "/source/Carla/test_for_dates_datadumpnongroup.csv" OF DEL MODIFIED BY TIMESTAMPFORMAT="MM/DD/YYYY HH:MMS TT" COLDEL, METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19) INSERT INTO DB2INST1.RF_DAILY_DUMP_TABLE (EMPPINNBR, EMPNAME, ORDERSTARTDATE, STARTDATETIME, ENDDATETIME, DOCONBR, DOCOTYPE, DOCOGROUP, JOBDESC, WHSE, CUST, CUSTORDRNBR, UM, LOTN, FROMLOC, TOLOC, PRODUCT, ITEMNBR, MUQTY);"
    SQL3192N In the filetmod a user specified format "TIMESTAMPFORMAT" beginning
    with the string "MM/DD/YYYY" is not valid.


    Here is part of my CSV file to import:
    10600,John Doe,6/23/2010 7:18:45 AM,6/23/2010 7:18:45 AM,6/23/2010 7:22:22 AM,


    Sorry to be a pain, but I'm learning.

    Thank you.

    CC
    Last edited by rockycj; 07-14-10 at 15:04.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,458
    Try enclosing the entire IMPORT command in single quotes, not double quotes.

Posting Permissions

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