Re:Excel to iSeries transfer hell

Excel stores dates as a julian number based on it's own criteria (maybe Jan 1, 1900? I don't know). That's the 5 digit number you are getting. Probably the 15.5 number as well.

I have not dug deeply into file transfer, but there may be some parameter that controls date conversion. Or write your own. Google should be able to find some discussions on excel dates.

Better yet, have the oracle analyst format the date field as a mmddyy number(or whatever number - mmddyyyy hhmmssss) , not an excel julian date. I don't think excel can handle oracle or DB2 data type fields.
Or get the oracle analyst to FTP the file to you, then the field types should match up.

---Dale

midrange-l-request@xxxxxxxxxxxx wrote:

Send MIDRmessage: 5
date: Mon, 8 Feb 2010 15:25:07 -0500
from: Dan <dan27649@xxxxxxxxx>
subject: Excel to iSeries transfer hell

Within the company, there is an Oracle database with a table I have been
trying desperately for the past two days trying to transfer to the iSeries.

Since I do not have access to the Oracle database, I am left with having an
Oracle analyst create an Excel spreadsheet with the data. I can open the
spreadsheet, and everything "looks fine". I created a table on the iSeries
from SDL DDL based on how the columns are defined in the Excel spreadsheet,
but this is NOT working. The one problem I am consistently encountering
with this is that one of the columns is a date field, and there are many
cells in this column that are blank. I've defined the iSeries table with
the date type.

I gave up on that and am finally at the point where I've let the data
transfer function define the iSeries file, but now the generated table uses
a signed 5.0 field for one Excel date field, and a signed 15.5 for the other
Excel date field! Excel date 2008-11-03 = iSeries numeric 39755.

Also, while the data shows up fine via DBU, but the non-numeric data looks
like garbage in interactive SQL and DSPPFM. DBU shows:
Field
Name Type Length Dec
MCCOMP S 2 0
MCSTAT G 2
MCPCOD G 3
MCTGRP G 8
MCPNCD G 3
MCVRCD G 2
MCEFDT S 5 0

What is type 'G'? A graphics type? If I attempt to do an SQL insert of
this data into a table that has these "G" types defined as alphameric, will
the data "convert"?

FWIW, currently, this is intended to be a one-off transfer, however it is
possible we may see a need to do this transfer once monthly.

Any advice would be greatly appreciated!
- Dan