Creating CSV files excluding name fields from commas



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Creating CSV files excluding name fields from commas

Postby Ron Mascarenhas » Tue Dec 08, 2009 3:02 pm

I have a report file where all the fields are separated by multiple spaces, except for the name which has
a single space and the records have a carriage control at the start

I need to create a CSV file from this file, for example this could be a record:

0 6577676 Frank Jaegar 10/02/79 03

I want the records to be formatted as follows, excluding the carriage control:
6577676,Frank Jaegar,10/02/79,03

and have tried the following DFSORT parameters but the name field also gets a comma in-between the two names:

OPTION COPY
OUTFIL FNAMES=CSV,
BUILD=(2,33,SQZ=(SHIFT=LEFT,MID=C',')

How do I exclude the name field from a comma, is there any other way to exclude the carriage control besides starting from col 2.
Can I parse a variable record file this way without using PARSE.
Ron Mascarenhas
 
Posts: 15
Joined: Mon Nov 03, 2008 2:04 am
Has thanked: 0 time
Been thanked: 0 time

Re: Creating CSV files excluding name fields from commas

 

Re: Creating CSV files excluding name fields from commas

Postby Frank Yaeger » Wed Dec 09, 2009 6:05 am

What is the RECFM and LRECL of the input file?

You only showed one example input record and didn't describe its layout. You need to show more than one input record and describe the layout - is each field in fixed positions in the record, or are the fields in a certain order but delimited by the blanks, or what? Without knowing the structure of the records, I can't tell anything about the "name" field to identify it, so I can't tell you how to create a CSV file.

Please use ubb code tags around your examples so we can see what they really look like.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Creating CSV files excluding name fields from commas

Postby Ron Mascarenhas » Wed Dec 09, 2009 5:02 pm

The example file has FB LRECL 41. The records look as follows:

0  6577676    Frank Jaegar    10/02/79 03 
0  6577676    Ron Masci       11/01/85 04 
0  6577676    David McDurmen  11/01/85 04 


The fields are always separated by multiple spaces except for the name which has only 1 space separating the first and last name.
Also the fields all start in a fixed position.

Now I want the output to be a CSV file FB LRECL 41 with trailing spaces in the record

6577676,Frank Jaegar,10/02/79 03
6577676,Ron Masci,11/01/85 04
6577676,David McDurmen,11/01/85 04


I would like the SORT parms to be able to parse any FB file with similar input characteristics with only a change of the lrecl in the parms. fields separated by multiple spaces should have commas between them. Where there is a single space, no comma should be inserted(such as the name above).
Ron Mascarenhas
 
Posts: 15
Joined: Mon Nov 03, 2008 2:04 am
Has thanked: 0 time
Been thanked: 0 time

Re: Creating CSV files excluding name fields from commas

Postby Frank Yaeger » Wed Dec 09, 2009 11:14 pm

You aren't making this easy.

Also the fields all start in a fixed position.


What is the starting position, length and format of each field?

In the expected output, you show a blank before the last field (e.g. ' 03') instead of a comma. Is that what you really want or do you want a comma before the last field (e.g. ',03')?

I would like the SORT parms to be able to parse any FB file with similar input characteristics with only a change of the lrecl in the parms. fields separated by multiple spaces should have commas between them. Where there is a single space, no comma should be inserted(such as the name above).


If we know the positions of the fields with a single space, we can handle them. But DFSORT has no way of knowing automatically which fields those are. You have to tell it. I can give you a job to do what you want for a specific case, but I don't know if that will meet your criteria for just changing the LRECL. It depends on what the layout of the other FB files looks like (do they still have the fields in the same order and fixed positions, or not?). I'd need more detail about these other FB files to tell you if the same job will work for all of them.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post