If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Importing CSV file and Date & Timestamp fields

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,378
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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 14:04.
Reply With Quote
  #4 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,378
Try enclosing the entire IMPORT command in single quotes, not double quotes.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On