RE: Using Excel spreadsheet data on the AS/400

["David Wright" <dwright2@xxxxxxxxxxxxxxx>]

Hi All,

I have a slightly different solution, and one additional problem...

Additional Problem:
If you have an Alpha field, and you enter an all numeric value, then excel
will change the definition of that field to numeric...

My workaround:
I the following formula to ensure that the value is always saved as numeric,
and always the correct length.
B1=LEFT(CONCATENATE(A1,"               "),15)

This is saying that the value in column B is equal to the first 15
characters of the value in column A plus 15 spaces.  So "1" becomes "1
", a 15 Alpha field.

This is the only way I know of to force excel to use the exact field lengths
you need for CA Data Transfer...

One other hint (Most probably know this one):
To force excel to treat a numeric entry as text, type a single colon before
the entry...  The colon disappears, and the number is treated as text.

Hope this helps,
David


-----Original Message-----
From: owner-rpg400-l@midrange.com [mailto:owner-rpg400-l@midrange.com]On
Behalf Of Phil Groschwitz
Sent: Thursday, March 01, 2001 6:11 PM
To: RPG400-L@midrange.com
Subject: RE: Using Excel spreadsheet data on the AS/400


And, each time you open and save the spreadsheet you
may change the lengths of the columns.

What I have done is require a csv format, and move
each field into character fields of a datastructure.
Then I test the data to ensure it's the correct
datatype then I do whatever with it.  I also test to
ensure I got the correct number of columns.  If I'm
expecting 8 and only got 7, the alignment of atleast
some of the columns is wrong and you won't know which
one.  Since it's comma delimited, you will have a
problem if there are commas in the data.

phil

--- Joel Fritz <JFritz@sharperimage.com> wrote:
> Fixed length text files can cause problems unless
> you're very careful about
> column formatting.  It's easy to forget that a
> column that has a bunch of
> two and three digit numbers in it has to have a
> length of say, 9 to come in
> to the file on the 400 correctly.
>
>
>
> > -----Original Message-----
> > From: Vladimir Ivanov [mailto:vivanov@medtac.com]
> > Sent: Thursday, March 01, 2001 7:40 AM
> > To: RPG400-L@midrange.com
> > Subject: Re: Using Excel spreadsheet data on the
> AS/400
> >
> >
> > Save the file as type : Formatted text(space
> delimited). This
> > will create a *.prn
> > file.
> > After just transfer file through FTP to AS400(FTP
> is better
> > than Client Access in
> > this
> > case, but you can use CA too)
> >
> > Frank Kany wrote:
> >
> > > Someone I'm working with is trying to use data
> from an
> > excel spreadsheet on
> > > the AS400.  If anyone knows of a way to do this
> please let me know.
> > >
> > > TIA
> > >
> > > Frank
> > >
> +---
> | This is the RPG/400 Mailing List!
> | To submit a new message, send your mail to
> RPG400-L@midrange.com.
> | To subscribe to this list send email to
> RPG400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to
> RPG400-L-UNSUB@midrange.com.
> | Questions should be directed to the list
> owner/operator: david@midrange.com
> +---


__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---