IBM Mainframe Forum

by **MitchellSTL** » Fri Aug 19, 2011 7:10 am

Have another fun CSV file to parse. It's a rather small CSV file that only has six columns in it. The problem is that the last column is not entered when "type" is 0. Any other type would column six ("comment") would be used. So an example of the records from Excel is:

Header:

Select all

"date","email","type","name","phone","comment"

Data:

Select all

"08/01/2011","abcdef@xyz.com","1","John Smith","555-555-1234","CEO"
"07/18/2011","abc@xyz.com","0","John Doe","123-345-6789"

So my question is there a way to conditionally process the row to handle 5 fields and then handle it another way when there is six rows?

by **dick scherrer** » Fri Aug 19, 2011 8:06 am

Hello,

One way might be to append an "empty" field to every record and then parse out the first 6 . . .

The intermediate "records" would be like:

Select all

"08/01/2011","abcdef@xyz.com","1","John Smith","555-555-1234","CEO"," "
"07/18/2011","abc@xyz.com","0","John Doe","123-345-6789"," "

When these are processed, the 7th field would simply be ignored, but there would always be 6 fields.

by **MitchellSTL** » Fri Aug 19, 2011 6:23 pm

That is an option but then it would have to be done in excel as a macro. The end user would be responsible for running the macro. As much as I trust them to do it, I can't guarantee it. I was just hoping that there was a way.

After thinking about it, I might try to do it in multiple steps (similar to when you have more than 100 fields in a CSV file - see http://www.ibmmainframeforum.com/dfsort-icetool-icegener/topic5631.html). First step would extract enough that I can view the "type" field in a consistent location for sort to split it up into two files. One file would have five fields and the second would have the six fields. Run the sort needed for each of those files then concatenate the files back into one file.

Doing this all in one step would be nice, but I can break it up into smaller pieces. I'd rather not have to rely on human memory to perform parsing of the data.

Thoughts? Think it will work? Or epic fail? =)

by **skolusu** » Fri Aug 19, 2011 9:30 pm

mitchellstl,

You haven't really shown us the output you want. You can parse the input looking for a space for the last field. I just parsed the input as is and then inserted a "no value" if you don't have anything in the last field

Select all

//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
"08/01/2011","ABCDEF@XYZ.COM","1","JOHN SMITH","555-555-1234","CEO"
"07/18/2011","ABC@XYZ.COM","0","JOHN DOE","123-345-6789"
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=12),
%02=(ENDBEFR=C',',FIXLEN=20),
%03=(ENDBEFR=C',',FIXLEN=03),
%04=(ENDBEFR=C',',FIXLEN=20),
%05=(ENDBEFR=C',',FIXLEN=12),
%06=(ENDBEFR=C' ',FIXLEN=15)),
BUILD=(%01,%02,%03,%04,%05,%06)

OUTREC IFTHEN=(WHEN=(68,15,CH,EQ,C' '),
OVERLAY=(68:C'"NO VALUE"'))
/*

The output from this is

Select all

"8/01/2011""ABCDEF@XYZ.COM" "1""JOHN SMITH" "555-555-123"CEO"
"7/18/2011""ABC@XYZ.COM" "0""JOHN DOE" "123-345-678"NO VALUE"

by **MitchellSTL** » Fri Aug 19, 2011 10:01 pm

skolusu,

Sorry for not being clear. Your output that you showed is close to what I wanted. Only difference will be the double quotes will be removed. I can use that and tweak it to get what I wanted - provided that I don't find any more "hiccups" in their data - haha! Thanks for your suggestion!!

by **skolusu** » Fri Aug 19, 2011 10:24 pm

MitchellSTL,

Use the following control cards.

Select all

//SYSIN DD *
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,
PARSE=(%01=(STARTAFT=C'"',ENDBEFR=C'"',FIXLEN=10),
%02=(STARTAFT=C'"',ENDBEFR=C'"',FIXLEN=20),
%03=(STARTAFT=C'"',ENDBEFR=C'"',FIXLEN=01),
%04=(STARTAFT=C'"',ENDBEFR=C'"',FIXLEN=20),
%05=(STARTAFT=C'"',ENDBEFR=C'"',FIXLEN=12),
%06=(STARTAFT=C'"',STARTAT=C' ',
ENDBEFR=C'"',ENDBEFR=C' ',FIXLEN=15)),
BUILD=(%01,%02,%03,%04,%05,%06)),
IFTHEN=(WHEN=(64,15,CH,EQ,C' '),OVERLAY=(64:C'NO VALUE'))

//*

The output from this is

Select all

08/01/2011ABCDEF@XYZ.COM 1JOHN SMITH 555-555-1234CEO
07/18/2011ABC@XYZ.COM 0JOHN DOE 123-345-6789NO VALUE

by **dick scherrer** » Fri Aug 19, 2011 10:46 pm

Hello,

That is an option but then it would have to be done in excel as a macro.

No, it wouldn't.

What i recommended is what skolusu has provided. The only difference is i used a blank instead of some "no value" literal. . .

IBM Mainframe Forum

Conditionally process different number of fields

Conditionally process different number of fields

Re: Conditionally process different number of fields

Re: Conditionally process different number of fields

Re: Conditionally process different number of fields

Re: Conditionally process different number of fields

Re: Conditionally process different number of fields

Re: Conditionally process different number of fields