473,856 Members | 1,650 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 8187
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_FORMA T (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,in f39dl,inf39dl
2,02-01-2007 00:00:00,R,0,30-10-2007 13:02:51:387,30-10-2007
13:02:51:387,in f39dl,inf39dl
3,03-01-2007 00:00:00,R,0,30-10-2007 13:02:51:387,30-10-2007
13:02:51:387,in f39dl,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,in f39dl,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,in f39dl,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_kalend arzu", "status_dni a", "id_typu_kalend arza",
"kiedy_utworzon o", "kiedy_modyfiko wano", "kto_modyfikowa ³",
"kto_utworzy³") ;
CONNECT RESET;

"id_kalenda rza" INTEGER 4 Nie
"dzien_w_kalend arzu" TIMESTAMP 10 Nie
"status_dni a" CHARACTER 1 Nie
"id_typu_kalend arza" INTEGER 4 Nie
"kiedy_utworzon o" TIMESTAMP 10 Nie
"kiedy_modyfiko wano" TIMESTAMP 10 Nie
"kto_modyfikowa ³" VARCHAR 16 Nie
"kto_utworz y³" 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
7290
by: Nathan Bloom | last post by:
Hi, I have a procedure that transfer data from an Excel spreadsheet to an Access 2000 table. There is a start date and an end date in the range specified. One of the date fields transfers correctly and the other one transfers as a text field containing a number like 37917. In the spreadsheet both date cells are formatted as a date. The data in both fields are entered as dates (i.e. 10/27/03). The TransferSpreadsheet Command creates...
2
2009
by: TheElectron707 | last post by:
Hi! I have a scenario in which i am importing a VERY LARGE database over the network. (Linking is not my solution). Now it takes many hours to import the complete database. I want that once the whole database has been imported, next time when i import only the NEWER records come in (almost database refresh every 24hours) so that the time taken is much much smaller and the network donot get chocked for many hours daily. Any solution...
9
4038
by: Edward S | last post by:
I budget for a Project in an Excel sheet as illustrated below. The months below are usually a 2 year period i.e. 24 months, though it could be over 24 months depending upon a Project. I then need to input this in an Access database, where I do a comparison with the Actual cost. The table “TblBudget” in Access is made of 4 fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$. At the moment this method is very cumbersome....
7
3068
by: Darren | last post by:
I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the correct cells within that worksheet. The Excel Book is made up of 12 worksheets named Jan-Dec. Each worksheet has columns labeled as each day of that month. Column 'A' is reserved for 19 rows named "room1 - room19". The data I am importing from the...
8
2559
by: harry | last post by:
Hi Folks: I'm trying to do something that looks simple, but I can't make it work right. It's Access 2000 on a Win2000 computer. I create a database with 5 columbs. The data I need to import is text. The way it's formatted is one line each field, for the 5 fields, then to the next record. The text file looks like this: WPTJ410
2
3619
by: nutthatch | last post by:
I want to be able to import an Excel spreadsheet into Access 2K using the macro command Transferspreadsheet. However, the file I am importing (over which I have no control) contains some records that are "dirty" i.e. the field contents do not comply with the expected format (date/time) and they end up in a seperate table of import errors. (The records in "error" are actually empty fields.) This is a regular event and I do not want to...
9
3924
by: jillandgordon | last post by:
I am trying to import an excel file into Access 97. It looks perfectly all right but, every time I try to import it, I get to the lst step and am told that it was not imported due to an error. There is no further explanation. What are the kinds of things that make this happen? Thanks from an obvious rookie. Gordon
2
2156
by: runway27 | last post by:
presently i have a table in which there are records and the fields are slno (this is a primary key auto increment), firstname, lastname, email, date(which inserts as a date format) these records are presenty being inserting from a html form. this works fine. i have additional data that is on paper and this paper data needs to be added to this table and the are numerous records that have to be inserted so executing an sql query from myphpadmin...
1
1541
by: Sudhakar | last post by:
presently i have a table in which there are records and the fields are slno (this is a primary key auto increment), firstname, lastname, email, date(which inserts as a date format) these records are presenty being inserting from a html form. this works fine. i have additional data that is on paper and this paper data needs to be added to this table and the are numerous records that have to be inserted so executing an sql query from...
0
9763
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11060
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10697
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7932
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7097
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5762
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5959
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4175
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3202
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.