RE: Excel to iSeries transfer hell

Hi Dan

See Scott's advice. Get the Oracle analyst to dump the table out as some
kind of export into the file system. If you have to open the text files to
view the contents use a text editor; I used TextPad for this kind of work
but there are plenty of other options.

The Oracle guys I used to work with talked about an "Oracle export/import
data loader" if I remember rightly - seemed to be a fancy name for creating
.csv/.txt files. Maybe if you aren't going to use the data on the iSeries
you can leave the column causing issues out. Creating some kind of export
definition to a text file in the file system using SQL should be a piece of
cake for their DBA.

Avoid Excel completely. I once did a project where I was transforming a .csv
and used Excel to check the data. Saving as .csv modified the data, so if
you use excel don't use it to save the file you will be importing - there's
a chance it won't be the file you're expecting.

Regards
Evan Harris

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Tuesday, 9 February 2010 9:25 a.m.
To: Midrange Systems Technical Discussion
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