RE: AS400 data to .csv problem in Excel

Jim,

Should have tried it first.  I see what you mean.  I guess Excel has a
maximum size of a numeric field of 16 digits.

However, if you change the value to an character value it will display
correctly (that's probaly how the edit code makes it work).  You can
manually change a cell to alpha by keying a single-quote in front of it.  If
your 400 file can be set up so the field is character, I imagine the
CPYTOIMPF will make that conversion for you.

jt



-----Original Message-----
From: owner-midrange-l@midrange.com
[mailto:owner-midrange-l@midrange.com]On Behalf Of Jim Franz
Sent: Friday, July 06, 2001 5:51 PM
To: MIDRANGE-L@midrange.com
Subject: Re: AS400 data to .csv problem in Excel




Thanks Art, JT -  except that there really is something wrong with the
number. When
the column is widened on the screen, the "fomula" number stays the same.
Each formula number is exactly the same, and so when each cell is
highlighted,
the same wrong value appears in the window at top.And I verified data in
file is
correct.
I just did experiment & made sure column heading was 20 characters long to
match
20 character field. No difference.
Just solved my problem by adding an edit word, turning 20 digits into
xxxx-xxxx-xxxx-xxxx-xxxx. But I had tried what you & JT suggested, and it
really was messed up. I think MS's formula problem because 1st 11 bytes
always were same. The end resulting number in Excel always had 4 zeros at
end, which was definitely wrong.
jim
>
> ----- Original Message -----
> From: "Art Tostaine, Jr." <art@link400.com>
> To: <franz400@triad.rr.com>
> Sent: Friday, July 06, 2001 10:08 PM
> Subject: RE: AS400 data to .csv problem in Excel
>
>
> > The .CSV format doesn't tell Excel what kind of data is coming, or, how
> long
> > it is.  If your end users are excel people, they'll know that all they
> have
> > to do to fix the 7e+ deal is to click on the column separator at the top
> of
> > the spreadsheet and drag it to make it wider.  That will show the true
> > number.
> >
> > There is nothing wrong with your file.  I think Excel should make the
> > columns big enough.
> >
> > There is also a menu option somewhere in excel that will make the column
> big
> > enough.
> >
> > Art Tostaine, Jr.
> > CCA, Inc.
> > Jackson, NJ 08527
> >
> > -----Original Message-----
> > From: owner-web400@midrange.com [mailto:owner-web400@midrange.com]On
> > Behalf Of Jim Franz
> > Sent: Friday, July 06, 2001 4:26 PM
> > To: WEB400@midrange.com
> > Subject: AS400 data to .csv problem in Excel
> >
> >
> > I know many of you have done as400 to Excel using .csv. I'm having a
very
> > strange
> > problem and could use some help.
> >
> > Building .csv files in IFS for web users. Users make request thru cgi,
clp
> > builds stmf
> > to set code page, then cpytoimpf to create delimited file
> >  CPYTOIMPF  FROMFILE(&CUST6/WWRTN *FIRST) TOSTMF(&URLW1) +
> >   MBROPT(*REPLACE) RCDDLM(*CRLF) DTAFMT(*DLM) STRDLM('"') FLDDLM(',')
> >  CPYTOIMPF  FROMFILE(&CUST6/WWRCPY *FIRST) TOSTMF(&URLC1) +
> >   MBROPT(*REPLACE) RCDDLM(*CRLF) DTAFMT(*DLM) STRDLM('"') FLDDLM(',')
> >
> > File looks ok in wordpad, except I expected delimiter at end of data,
but
> > these files
> > only have delimiter at end of each field.
> > When Excel 2000 displays data, one field, 20 byte number has changed
from
> > ,"71010412344005315256", to 7.10104E+19. In fact every number in that
> column
> > resulted in the same strange number. (the 20 digit number is certified
> mail
> > numbers
> > and the first 11 bytes are always the same, and MUST be included).
> > Some other colums, with values like CC, OW, 01, only the 01 is right
> > justified, zero
> > suppressed. In both cases the cell format is "general" which I think is
> > messing it up,
> > but see no way to set to "text" when building this.
> >
> > Before we go too far... this process allows any web user to dynamically
> > select data
> > to download to their pc. The .csv is the standard for this industry
(they
> > can't spell
> > XML or anything other than "Excel" or "spreadsheet". Some are reading
the
> > files
> > in FoxPro. File name is unique every time. Client Access is not an
option.
> > FTP is not. Needs to run, look, feel like any other website with no
> special
> > plugins.
> > Don't know if this is a "feature" of Excel 2000, but that's all we have
on
> > inhouse
> > desktops to test with. Am at V4R4, fairly recent ptfs. On V4R5 in a few
> > weeks.
> > Any help appreciated.
> > jim
> >
> > +---
> > | This is the WEB400 Mailing List!
> > | To submit a new message, send your mail to WEB400@midrange.com.
> > | To subscribe to this list send email to WEB400-SUB@midrange.com.
> > | To unsubscribe from this list send email to WEB400-UNSUB@midrange.com.
> > | Questions should be directed to the list owner/operator:
> > david@midrange.com
> > +---
> >
>

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

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