Re: AS400 data to .csv problem in Excel -- WEB400

My final solution was to add edit word and output 24 characters
as xxxx-xxxx-xxxx-xxxx-xxxx. What I was trying to understand,
not knowing much about Excel & it's limitations, is if there was a way
to setup Excel to "always" put larger numbers into text. Apparently not.
(File is dynamically built & unique file name each time. Users would
not want anything complicated).
I do have another "what can Excel do" question. What would be the
max columns in a single record, and maximum record length. Currently
writing Header and separate file for Detail. Users have asked for
single record per order, but that might mean 100 columns and up to
5000 bytes per record. Anyone know?
BTW- on the 400 we certainly have lived with years of sizing limitations.
It has gotten better.
jim

----- Original Message -----
From: "Peter Dow" <pcdow@yahoo.com>
To: <WEB400@midrange.com>
Sent: Monday, July 09, 2001 5:49 PM
Subject: Re: AS400 data to .csv problem in Excel


> Hi Peter,
>
> In fairness to M$, Excel _is_  working perfectly, if by working perfectly
> you mean that they do what they say they do. The help topic "Tips on
> entering numbers" says:
>
>     "� Regardless of the number of digits displayed, Microsoft Excel
stores
> numbers with up to 15 digits of precision. If a number contains more than
15
> significant digits, Microsoft Excel converts the extra digits to zeros
(0)."
>
> Regards,
> Peter Dow
> Dow Software Services, Inc.
> 909 425-0194 voice
> 909 425-0196 fax
>
>
> ----- Original Message -----
> From: "Peter_Lunde@hotmail.com" <peter_lunde@hotmail.com>
> To: <WEB400@midrange.com>
> Sent: Monday, July 09, 2001 1:36 PM
> Subject: Re: AS400 data to .csv problem in Excel
>
>
> > Looks like Excel has problems with numbers greater than 15 digits.
> >
> > When I put a 20 digit number (12345678901234567890) into Excel 2000,
only
> 15
> > digits of accuracy are retained (12345678901234500000).
> >
> > I suspect that the AS400 is working perfectly and Excel is not.
> >
> > You may be able to circumvent this problem by casting numeric fields
that
> > are greater than 15 digits into alpha fields.
> >
> > When I put '12345678901234567890 into an Excel field, it did not show
the
> > opening apostrophe and retained all the characters of  this alpha value.
> >
> > Sincerely,
> >
> > Peter Lunde.
> >
> > ----- Original Message -----
> > From: "Jim Franz" <franz400@triad.rr.com>
> > To: <WEB400@midrange.com>
> > Sent: Friday, July 06, 2001 4:25 PM
> > 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 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
> > +---
>
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
> +---
> | 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 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
+---