Re: AS400 data to .csv problem in Excel

did make the 400 field alpha (first record is column headings).
Cpytoimpf seems ok, just Excel a problem. Don't know if you saw my
last post-fixed problem by adding edit word so that value  is not just
numbers. 16 byte numbers - Now, who is the "old" system?
jim

----- Original Message -----
From: "jt" <jt@ee.net>
To: <MIDRANGE-L@midrange.com>
Sent: Friday, July 06, 2001 11:19 PM
Subject: 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
> +---

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