× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. July 2001

RE: AS400 data to .csv problem in Excel

  • Subject: RE: AS400 data to .csv problem in Excel
  • From: "jt" <jt@xxxxxx>
  • Date: Fri, 6 Jul 2001 22:13:46 -0400
  • Importance: Normal
Jim,
 
I think your data is fine, but the spreadsheet needs changed so it displays correctly.  For example, the 7.10104E+19 is displayed because the column is too small to show the full 20 digits.  So Excel automatically converts it to scientific notation, to abbreviate it.  Change the column on the spreadsheet to "best fit" and the numbers will show up fine.  The other problem you mentioned is, just like you said, because the column is formatted as "general".  I'm not sure how you can fix that, other than maybe specifying "right justified" alignment on the column.
 
HTH
 
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 4:25 PM
To: MIDRANGE-L@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

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.