RE: Using Excel spreadsheet data on the AS/400

I agree with what you and Bill say, but the part that many people get hung
up on is that although CA correctly interprets the data types when writing
the fdf file, it creates the fields as 'General' data types in the actual
excel file....

In the past, this meant that you might not be able to reupload an unmodified
file because the numeric-alpha fields get misinterpreted when being sent...

Having said all of this, it now seems to work just fine...  Maybe one of the
recent releases of Office or CA fixed it, maybe my memory is just failing
me...

If someone is running an older Excel and CA combo, I would be interested to
see if I am remembering this problem correctly...

David

-----Original Message-----
From: owner-rpg400-l@midrange.com [mailto:owner-rpg400-l@midrange.com]On
Behalf Of Alex A Moore
Sent: Friday, March 02, 2001 2:08 PM
To: RPG400-L@midrange.com
Subject: RE: Using Excel spreadsheet data on the AS/400


To hopefully clarify:

        If you enter data that is interpreted as numeric in an empty Excel 
cell, it
will be interpreted as numeric by the Client Access add-in.  Heres the
Micro$oft help:
        Entering numbers as text   Excel stores a number as numeric data even if
you use the Cells command to apply the Text format to the cells that contain
the numbers. To have Excel interpret numbers such as part numbers as text,
first apply the Text format to empty cells, and then type the numbers. If
you've already entered the numbers, apply the Text format to the cells,
click each cell, press F2, and then press ENTER to reenter the data.

        If I need to _CONTROL_ the format of the data that is being uploaded to 
the
iSeries, I create the database file on the iSeries in the desired format,
enter at least one record (DBU, DFU - whatever), download it to a
spreadsheet, and then, either link the data to the downloaded sheet, or cut
and paste the data in.  Then when you upload, all your data is formatted
correctly.  You can accomplish the same results without these steps, but
when it gets to field lengths, it becomes cumbersome.

        HTH

        Alex


-----Original Message-----
From: owner-rpg400-l@midrange.com [mailto:owner-rpg400-l@midrange.com]On
Behalf Of David Wright
Sent: Friday, March 02, 2001 3:39 PM
To: RPG400-L@midrange.com
Subject: RE: Using Excel spreadsheet data on the AS/400


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
+---

+---
| 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
+---