468,277 Members | 1,553 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,277 developers. It's quick & easy.

importing data with date format

I have several files with data I would like to import into DB2, but I
have timestamps set in a format, which DB2 can't catch - DD-MM-YYYY
HH:MM:SS

DB2 wants to get the year first. Is there a way I can say that my data
is going to come in this format or do I need to convert it outside the
database?

--
Ewa
Jan 18 '08 #1
5 6846
E.************@gmail.com wrote:
I have several files with data I would like to import into DB2, but I
have timestamps set in a format, which DB2 can't catch - DD-MM-YYYY
HH:MM:SS

DB2 wants to get the year first. Is there a way I can say that my data
is going to come in this format or do I need to convert it outside the
database?
Which version of DB2?
You can load the data into a table with a generated column and let the
generated column convert the string into a timestamp.
Then you alter the generated column to a normal column and drop the
staging column.
Reorg.. done.

In DB2 9.5 for LUW the TIMESTAMP_FORMAT (aka TO_DATE) function can do
this conversion easily. Prior to DB2 9.5 you have to reshuffle the
timestamp string with substr and concat and then pass it into the
TIMESTAMP function

Of course you can always to a regular LOAD into a string column to a
staging table and then LOAD FROM CURSOR to the final table. Doing the
transformation in the cursor.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jan 18 '08 #2
I found what I was looking for in the DB2 Control Center where in the
import function I can define the date format which I have in my file,
but I have another problem now. During the import I get an error, that
says that the data in my first column and first row can't be converted
into an INTEGER. My data looks like this:
1,01-01-2007 00:00:00,S,0,30-10-2007 13:02:51:357,30-10-2007
13:02:51:357,inf39dl,inf39dl
2,02-01-2007 00:00:00,R,0,30-10-2007 13:02:51:387,30-10-2007
13:02:51:387,inf39dl,inf39dl
3,03-01-2007 00:00:00,R,0,30-10-2007 13:02:51:387,30-10-2007
13:02:51:387,inf39dl,inf39dl
Any ideas? Should the integer values be formatted in some special way?

Hmmm... I wonder... Just for kicks add a space after that first comma.
Maybe in your local a 1,01 is a decimal 1.01 ???
I'm guessing....
That was a long shot ;) Unfortunately that's not the case... I'm
getting another error that says something about not importing all of
the data after the 8 column (inf39dl) in the first row (and then the
same 2 errors for the rest od the rows in the file), so he gets all
the columns right and it's definitely a problem with the "1"... I just
can't get it...

--
Ewa
Jan 22 '08 #3
E.************@gmail.com wrote:
That was a long shot ;) Unfortunately that's not the case... I'm
getting another error that says something about not importing all of
the data after the 8 column (inf39dl) in the first row (and then the
same 2 errors for the rest od the rows in the file), so he gets all
the columns right and it's definitely a problem with the "1"... I just
can't get it...
Hmmm... while we're doing long shots:
1,01-01-2007 00:00:00,S,0,30-10-2007 13:02:51:357,30-10-2007
13:02:51:357,inf39dl,inf39dl
~~~~~~~~~~~~
This cannot be converted to integer. Wrapping of the message or a
problem in your file?

I think to help further we'd need to see the IMPORT statement ("show
SQL" in control center?) and the table definition.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jan 22 '08 #4
That was a long shot ;) Unfortunately that's not the case... I'm
getting another error that says something about not importing all of
the data after the 8 column (inf39dl) in the first row (and then the
same 2 errors for the rest od the rows in the file), so he gets all
the columns right and it's definitely a problem with the "1"... I just
can't get it...

Hmmm... while we're doing long shots:
1,01-01-2007 00:00:00,S,0,30-10-2007 13:02:51:357,30-10-2007
13:02:51:357,inf39dl,inf39dl
~~~~~~~~~~~~
This cannot be converted to integer. Wrapping of the message or a
problem in your file?
Sorry, wrapping...
I think to help further we'd need to see the IMPORT statement ("show
SQL" in control center?) and the table definition.

CONNECT TO DEVDB USER "devdb";
IMPORT FROM "C:\DB\dane.txt" OF DEL MODIFIED BY TIMESTAMPFORMAT="DD-MM-
YYYY HH:MM:SSTT" COLDEL, METHOD P (1, 2, 3, 4, 5, 6, 7, 8) MESSAGES "C:
\DB\komunikaty.txt" INSERT INTO DEVDB.KALENDARZ ("id_kalendarza",
"dzien_w_kalendarzu", "status_dnia", "id_typu_kalendarza",
"kiedy_utworzono", "kiedy_modyfikowano", "kto_modyfikował",
"kto_utworzył");
CONNECT RESET;

"id_kalendarza" INTEGER 4 Nie
"dzien_w_kalendarzu" TIMESTAMP 10 Nie
"status_dnia" CHARACTER 1 Nie
"id_typu_kalendarza" INTEGER 4 Nie
"kiedy_utworzono" TIMESTAMP 10 Nie
"kiedy_modyfikowano" TIMESTAMP 10 Nie
"kto_modyfikował" VARCHAR 16 Nie
"kto_utworzył" VARCHAR 16 Nie

I hope this will give a better picture (sorry for the polish names, I
didn`t have the inspiration to do the translating ;))

Thanks a lot for your help!

--
Ewa
Jan 23 '08 #5
I hope this will give a better picture (sorry for the polish names, I
didn`t have the inspiration to do the translating ;))
Found it!

It was an encoding problem... just figures...

Thanks a lot for all your help :)

--
Ewa
Jan 23 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Nathan Bloom | last post: by
2 posts views Thread by TheElectron707 | last post: by
7 posts views Thread by Darren | last post: by
8 posts views Thread by harry | last post: by
9 posts views Thread by jillandgordon | last post: by
2 posts views Thread by runway27 | last post: by
1 post views Thread by Sudhakar | last post: by
reply views Thread by zattat | last post: by
1 post views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.