Import of csv-File

[Ina Schmitz]

Hello all,

I would like to import a comma separated values file to DB2 UDB 8.2.
This file also contains values of type DATE and TIMESTAMP having for example
the following format 900925 033701.0000 (yyMMdd HHmmss.ffff).

For the import, I right-click on the table -> Import and then choose the
file with file type "DEL" and confirm the mapping given in "columns".
But as result, I get that all rows are refused. Could I somewhere find more
detailed information WHICH column causes the problem?
With trying out and leaving away different columns, I found out that the
error comes from the DATE and TIMESTAMP column, but is there a possibility
to find it out directly after getting the result that rows are refused?

I think that "my" DATE and TIMESTAMP columns cound not be inserted because
of the format. How could I tell DB2 which format my date have? Is there
anything like DTS for MS SQL Server 2000 also for IBM DB2?

Would be glad if anybody could help me in this topic.

Cheers,
Ina

[Pierre Saint-Jacques]

Look in your docs. for the import command and within for the MODIFIED BY
option.
It will show (there are examples I think) what to specify to tell it of your
timstamp format.
I don't have access to my docs. for the moment.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Ina Schmitz" <kl***@fmi.uni-passau.de> a écrit dans le message de
news:d0*************@news.t-online.com...

Hello all,

I would like to import a comma separated values file to DB2 UDB 8.2.
This file also contains values of type DATE and TIMESTAMP having for example the following format 900925 033701.0000 (yyMMdd HHmmss.ffff).

For the import, I right-click on the table -> Import and then choose the
file with file type "DEL" and confirm the mapping given in "columns".
But as result, I get that all rows are refused. Could I somewhere find more detailed information WHICH column causes the problem?
With trying out and leaving away different columns, I found out that the
error comes from the DATE and TIMESTAMP column, but is there a possibility
to find it out directly after getting the result that rows are refused?

I think that "my" DATE and TIMESTAMP columns cound not be inserted because
of the format. How could I tell DB2 which format my date have? Is there
anything like DTS for MS SQL Server 2000 also for IBM DB2?

Would be glad if anybody could help me in this topic.

Cheers,
Ina

[Ina Schmitz]

Hello Pierre,

thanks for your answer.

Look in your docs. for the import command and within for the MODIFIED BY
option.
It will show (there are examples I think) what to specify to tell it of
your
timstamp format.

I studied the modified by option in more detail, but didn't find a solution
in the docs for my problem:
I have to modify TWO formats, for example:

IMPORT FROM "test.csv" OF
DEL
modified by dateformat="YYYYMMDD"
modified by timestampformat="YYYYMMDD HHMMSS.UUUUUU"
METHOD P (1, 2)
INSERT INTO TESTTABLE
(DATUM, ZEITSTEMPEL)

Here, I have the problem, that I couldn't find out how to modify TWO
formats. Connected with AND or "," it doesn't work. Does anybody know an
working example for this?
The other problem I have is that I have only YY and not YYYY. I don't want
to add "19" in front of my year in every row in the csv-file, because it has
thousands of rows. Does anybody know a workaround for this problem, how I
could use 2-digit-years?

Thanks a lot in advance for any good ideas!
Cheers,
Ina

[Stefan M. Mihokovic]

Hallo Ina,

The correct syntax is

IMPORT FROM "test.csv"
OF DEL
modified by dateformat="YYYYMMDD" timestampformat="YYYYMMDD HHMMSS.UUUU"
METHOD P (1, 2)
INSERT INTO TESTTABLE
(DATUM, ZEITSTEMPEL)

, but the year must have four digits. Sorry!

Regards / Gruß Stefan

----------------------------------
Stefan M. Mihokovic
email: it at stemi dot de


"Ina Schmitz" <kl***@fmi.uni-passau.de> schrieb im Newsbeitrag
news:d0*************@news.t-online.com...

Hello Pierre,

thanks for your answer.

Look in your docs. for the import command and within for the MODIFIED BY
option.
It will show (there are examples I think) what to specify to tell it of
your
timstamp format.

I studied the modified by option in more detail, but didn't find a
solution in the docs for my problem:
I have to modify TWO formats, for example:

IMPORT FROM "test.csv" OF
DEL
modified by dateformat="YYYYMMDD"
modified by timestampformat="YYYYMMDD HHMMSS.UUUUUU"
METHOD P (1, 2)
INSERT INTO TESTTABLE
(DATUM, ZEITSTEMPEL)

Here, I have the problem, that I couldn't find out how to modify TWO
formats. Connected with AND or "," it doesn't work. Does anybody know an
working example for this?
The other problem I have is that I have only YY and not YYYY. I don't want
to add "19" in front of my year in every row in the csv-file, because it
has thousands of rows. Does anybody know a workaround for this problem,
how I could use 2-digit-years?

Thanks a lot in advance for any good ideas!
Cheers,
Ina