Re: Comma-delimited help

Andy,

If none of these threads help you, you could give a freeware open
source utility a try - Martin Rowe has a utility called CRTCSVSTMF
that trims blanks from the resulting csv.

Since v5r3, I've found it to be more stable and predictable than
CPYTOIMPF - especially with regard to code pages and CCSID.

you can download at http://www.dbg400.net/

The best part is you have the source and can tweek it.  In our system
we have several alpha fields with leading zeros, and when the csv is
opened in excel, it trims them.   I modified my version to look for
long numeric strings (  000000044394930384 for instance ) and if
found, it places a single quote at the beginning of the string.

This keeps excel from trimming the leading zeros.

hope this helps,

Rick  


On Sat, 26 Feb 2005 08:48:05 -0600, Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote:
> Andy
> 
> Go to the archive for this list - link near the bottom of this message -
> and search on "cpytoimpf csv" or some such. I actually searched on "csv
> blank" and got some good threads. This has come up several times.
> 
> One of the posts indicated that IBM was thinking of changing this 5 years
> ago. There are supposedly some rules - fixed-length character fields are
> not trimmed, variable-length ones are. But numerics are not trimmed and are
> left-justified.
> 
> But I tried this on a V5R1 machine - the VARCHAR was still padded - no
> trimming.
> 
> One solution is to use an SQL SELECT statement - this technique has been
> discussed in this list. Do an appropriate search, such as "csv sql", maybe.
> The idea is to convert date, numerics, etc., to character, trim the
> results, & concatenate the lot with commas and quotes as needed. You could
> even do this with a UNION SELECT and put the column names in the first row
> as a bunch of constants. And with a little more work, you could put column
> headings in there, as well, using another UNION SELECT. You could even use
> a UDTF to get this automagically - user-defined table function. I have to
> try that one myself! That would be a call to a system program that'd return
> the whole row, filled with column headings in one swell foop.
> 
> HTH
> Vern
> 
> At 03:41 PM 2/25/2005, you wrote:
> >Anyone able to offer a little download help?
> >
> >I'm trying to download (via ftp) from the 400 to a comma-delimited file
> >(to a T&A system), using the following command:
> >
> >CPYTOIMPF FROMFILE(HS#LIBR/PPPTADN) TOFILE(HS#LIBR/PPPTADNCSV)
> >
> >The results are like so:
> >
> >"8732","A","JOHNSON             ","LINDA               ","NODIFF
> >","9066","    "
> >"6289","A","SMITH               ","MARIANNE            ","40/40
> >","9596","    "
> >
> >but I was expecting something like this:
> >
> >"8732","A","JOHNSON","LINDA","NODIFF","9066","    "
> >"6289","A","SMITH","MARIANNE","40/40","9596",""
> >
> >.... the extra blanks within each set of quotes is killing the import.
> >Any clues as to what I should be doing differently would be greatly
> >appreciated!
> >
> >
> >Andy