Conditionally process different number of fields



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

Conditionally process different number of fields

Postby 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:
"date","email","type","name","phone","comment"

Data:
"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?
MitchellSTL
 
Posts: 27
Joined: Wed Feb 16, 2011 8:57 am
Has thanked: 1 time
Been thanked: 0 time

Re: Conditionally process different number of fields

 

Re: Conditionally process different number of fields

Postby 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:
"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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Conditionally process different number of fields

Postby 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? =)
MitchellSTL
 
Posts: 27
Joined: Wed Feb 16, 2011 8:57 am
Has thanked: 1 time
Been thanked: 0 time

Re: Conditionally process different number of fields

Postby 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

//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
"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"
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Conditionally process different number of fields

Postby 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!!
MitchellSTL
 
Posts: 27
Joined: Wed Feb 16, 2011 8:57 am
Has thanked: 1 time
Been thanked: 0 time

Re: Conditionally process different number of fields

Postby skolusu » Fri Aug 19, 2011 10:24 pm

MitchellSTL,

Use the following control cards.
//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
08/01/2011ABCDEF@XYZ.COM      1JOHN SMITH          555-555-1234CEO       
07/18/2011ABC@XYZ.COM         0JOHN DOE            123-345-6789NO VALUE 
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Conditionally process different number of fields

Postby 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. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post