Page 1 of 1

Creating CSV files excluding name fields from commas

PostPosted: Tue Dec 08, 2009 3:02 pm
by Ron Mascarenhas
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.

Re: Creating CSV files excluding name fields from commas

PostPosted: Wed Dec 09, 2009 6:05 am
by Frank Yaeger
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.

Re: Creating CSV files excluding name fields from commas

PostPosted: Wed Dec 09, 2009 5:02 pm
by Ron Mascarenhas
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).

Re: Creating CSV files excluding name fields from commas

PostPosted: Wed Dec 09, 2009 11:14 pm
by Frank Yaeger
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.