I always convert any excel document to CSV before transferring to iseries. Then I use the tool to create an iseries file on the file transfer program. Then I either use the file that the tool created or a write a program to put that file into a file I created. Not the prettiest way to do it but it works.
Thanks,
Jay Jernigan
Alamance Regional Medical Center
Financial Analyst
jjernigan@xxxxxxxx
336-538-8299
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Monday, February 08, 2010 3:25 PM
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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
midrange.com
