Re: AS400 data to .csv problem in Excel

[Rich Duzenbury <rduz@xxxxxxxxxxxxxxxxxxx>]

The general format is likely the problem.

98765432109876543210  ,"98765432109876543210"

I did a test to import a 20 digit number, and a 20 digit alpha string 
enclosed in quotes.  With format 'general', both are converted by excel to 
numbers.  Since a 20 digit number is longer than excel can deal with, it 
reformats to scientific notation, or 9.87654E+19.  On Excel 97-SR1, which 
is what I have here, changing the column width does not change the number 
to an integer display format.

I can, on this version of excel, specify column formats in the import 
wizard.  I have not seen the 2000 product, so perhaps it is somehow 
different.  Even so, you may select a column or columns, and then in the 
Format Cells dialog, choose 'Text' for the formatting, and all of the 
numbers are converted to a textual representation, which looks great.

Regards,
Rich

At 04:25 PM 7/6/01 -0400, you wrote:
>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
>+---

Regards,
Rich

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