Re: Transfer date fields from Excel to DB2

[Jerry Adams <jerry@xxxxxxxxxxxxxxx>]

Curt,

Haven't tried moving date fields so I cannot answer from experience.

A guess, though, would be to try defining the Excel column as numeric 
with the format.  The column is probably formatted in Excel as a date 
field, which would explain the '38470' instead of the '4/28/2005' you 
were expecting. 

I'm usually going the other way (iSeries -> Excel), but even in that 
case I don't recall trying to port a date data type as most of our 
primary files have formatted numeric dates (such as 20060201) in the clear.

Other than trying various definitions for the Excel sheet and, possibly, 
for the DB2 field, nothing else comes to mind at this time.


        * Jerry C. Adams
*iSeries Programmer/Analyst
B&W Wholesale Distributors, Inc.* *
voice
        615.995.7024
fax
        615.995.1201
email
        jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>



Curt Vancalster wrote:

> Jerry,
>
> If I import the date field from Excel to the DB2 file described as
> numeric I get an error because it is trying to move a 5 digit number
> into a date type field in the DB2 file.  For example, if there is a date
> of 4/28/2005 in excel, when I transfer the data it tries to move 38470
> into the DB2 file which is invalid. A field described as a date stamp in
> db2 is requiring a format of yyyy-mm-dd-hh.mm.ssssss.
>
> Thanks,
>
> Curt 
>
> /snip/
>
> Curt,
>
> What kind of problems are you having?
>
>
> Just yesterday I helped our accountant use the Excel plug-in to transfer
>
> a report to an Excel spreadsheet. 
>
>         * Jerry C. Adams
> *iSeries Programmer/Analyst
> B&W Wholesale Distributors, Inc.* *
> voice
>         615.995.7024
> fax
>         615.995.1201
> email
>         jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>
>
>
>
> Curt Vancalster wrote:
>
>  
>
> > I have an excel spreadsheet that I want to import into a DB2 file.
> > There are a couple of date fields in this spread sheet.  The DB2 file
> > has the same date fields described as follows:
> >
> > Field                             Field                       
> > Text                              Name                Type     Length
> > Dec 
> > Last_sale                      LAST_SALE      Z          26      
> > Balance_forward            BALAN00001     A          1      
> >
> > I am struggling with a way to do this and I am at the point now where I
> > want to just import from excel to a separate DB2 file but create the
> > date fields as text in this new file.  Then create a conversion program
> > to populate the DB2 file above from the file with the date fields
> > described as text fields.  Confused yet?  :-)
> >
> > Is there another way of doing this...possibly an easier way?  I am
> > having a hard time believing that there isn't an easier way to do this.
> > I am currently using the Data Transfer to iSeries that is in iSeries
> > navigator V5R2's to attempt to do this but without any luck.
> >
> > Thanks,
> >
> > Curt VanCalster
> > Lead Programmer
> > Smithfield Beef Group., - Green Bay, WI
> > (920) 406-1174
> > curt.vancalster@xxxxxxxxxx
> >    
>
> /Snip/
>
>