csv 2 fixed with non consistant amount fileds



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

Re: csv 2 fixed with non consistant amount fileds

Postby AKDEARAGON » Thu Oct 13, 2011 11:26 pm

my input is like this:
.10/11/2011.12/15/2011
..5/15/2011
.12/5/2011.3/21/2011

On second occurance I don't have the first date. how do I check for that and leave a value on the output? I want out put to be:
2011101120111215
20110515
2011120520110321

The missing date can be blank or zeros.
AKDEARAGON
 
Posts: 13
Joined: Wed Oct 05, 2011 5:31 am
Has thanked: 0 time
Been thanked: 0 time

Re: Re: csv 2 fixed with non consistant amount fileds

 

Re: csv 2 fixed with non consistant amount fileds

Postby skolusu » Thu Oct 13, 2011 11:48 pm

AKDEARAGON wrote:my input is like this:
.10/11/2011.12/15/2011
..5/15/2011
.12/5/2011.3/21/2011

On second occurance I don't have the first date. how do I check for that and leave a value on the output? I want out put to be:
2011101120111215
20110515
2011120520110321

The missing date can be blank or zeros.


Did you see my last post? Fyi
//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                       
10/11/2011 12/15/2011                                                 
.5/15/2011                                                           
.12/5/2011 3/21/2011                                                 
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                       
  OPTION COPY                                                         
  INREC PARSE=(%01=(ENDBEFR=C'/',FIXLEN=02),                         
               %02=(ENDBEFR=C'/',FIXLEN=02),                         
               %03=(ENDBEFR=X'05',FIXLEN=04),                         
               %04=(ENDBEFR=C'/',FIXLEN=02),                         
               %05=(ENDBEFR=C'/',FIXLEN=02),                         
               %06=(ENDBEFR=X'05',FIXLEN=04)),                       
  BUILD=(%03,UFF,EDIT=(TTTT),%01,UFF,EDIT=(TT),%02,UFF,EDIT=(TT),C'|',
         %06,UFF,EDIT=(TTTT),%04,UFF,EDIT=(TT),%05,UFF,EDIT=(TT))     
//*                                                                   


will produce
20111011|20111215
20110515|00000000
20110105|20110321
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: csv 2 fixed with non consistant amount fileds

Postby AKDEARAGON » Thu Oct 13, 2011 11:54 pm

I didn't notice that my data slide over for expected results, It should of been :

2011101120111215
20110515
2011120520110321


I do not have space between the two columns of dates to put any character.
AKDEARAGON
 
Posts: 13
Joined: Wed Oct 05, 2011 5:31 am
Has thanked: 0 time
Been thanked: 0 time

Re: csv 2 fixed with non consistant amount fileds

Postby AKDEARAGON » Thu Oct 13, 2011 11:56 pm

on the input each field is seperated by x'05' or so when it gets to the date and it starts to look for the / but find just then next x'05' how does it know to skip that date but leave a position for it on the output and go to the second date?
AKDEARAGON
 
Posts: 13
Joined: Wed Oct 05, 2011 5:31 am
Has thanked: 0 time
Been thanked: 0 time

Re: csv 2 fixed with non consistant amount fileds

Postby BillyBoyo » Fri Oct 14, 2011 12:05 am

AKDEARAGON wrote:I didn't notice that my data slide over for expected results, It should of been :

2011101120111215
        20110515
2011120520110321


I do not have space between the two columns of dates to put any character.


You have to use the Code tags to preserve spacing. It is the Code button over the input area. Highlight the text you have formatted, then use the Preview button to check it. Then Submit when you are happy with how it looks.

The spaces in the ordinary text are "proportional" and leading space is swallowed without being displayed, so you got your slippage.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: csv 2 fixed with non consistant amount fileds

Postby skolusu » Fri Oct 14, 2011 1:20 am

AKDEARAGON wrote:I do not have space between the two columns of dates to put any character.


Sigh*. I did not have a space. The space you see is actually x'05'. In edit mode it looks like it is a space and in browse mode it is shown as .

if your dates are indeed separated by tab x'05' then the JCL I posted will work.
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: csv 2 fixed with non consistant amount fileds

Postby AKDEARAGON » Fri Oct 14, 2011 2:33 am

My data comes back as if the missing first date isn't there it doesn't leave space for it on the output. I'll list out better example:

Input from .csv file
X.X..8/23/2010.10.5/20/2011.7
X.X..8/24/2010.10.5/26/2011.7
X.X..4/28/2011.1.4/29/2011.7


expected output:
XX               20100823102011052007
XX               20100824102011052607
XX               20110428012011042907


what i'm getting is:

XX201008232011102007
XX201008242011102607
XX201104282011012907


i'm using your type of code but as you can see it isn't handling the missing first date. It will not always be missing.
AKDEARAGON
 
Posts: 13
Joined: Wed Oct 05, 2011 5:31 am
Has thanked: 0 time
Been thanked: 0 time

Re: csv 2 fixed with non consistant amount fileds

Postby skolusu » Fri Oct 14, 2011 5:03 am

AKDEARAGON,

As Bill explained earlier, you need to use code tags to be able to see how the spacing is retained. Frank added the code tags for you. In future make sure to enclose the data/jcl in code tags.

Anyway since your input has both missing dates as well as left justified dates you need to do double parsing.

1. parse and get the values and put the date fields at the end of the file in fixed positions. I assumed input is FB 80 byes , so I added the date fields at the end at pos 81,91,101

2. Now parse again the date fields specifying the positions looking for separators and build the entire record as needed.



//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD *                                               
X.X..8/23/2010.10.5/20/2011.7                                 
X.X..8/24/2010.10.5/26/2011.7                                 
X.X..4/28/2011.1.4/29/2011.7                                   
X.X.12/1/2012.10/5/2011.2.11/9/2011.8                         
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                               
  SORT FIELDS=COPY                                             
  INREC IFTHEN=(WHEN=INIT,                                       
        PARSE=(%01=(ENDBEFR=X'05',FIXLEN=1),    $ VALUE-1         
               %02=(ENDBEFR=X'05',FIXLEN=1),    $ VALUE-2         
               %03=(ENDBEFR=X'05',FIXLEN=10),   $ DATE-1         
               %04=(ENDBEFR=X'05',FIXLEN=10),   $ DATE-2         
               %05=(ENDBEFR=X'05',FIXLEN=02),   $ VALUE-3         
               %06=(ENDBEFR=X'05',FIXLEN=10),   $ DATE-3         
               %07=(ENDBEFR=X'05',FIXLEN=01)),  $ VALUE-4         
  OVERLAY=(81:%03,%04,%06)),                                     
                                                                 
  IFTHEN=(WHEN=INIT,                                             
  PARSE=(%08=(ABSPOS=81,ENDBEFR=C'/',ENDBEFR=C' ',FIXLEN=2),     
         %09=(ENDBEFR=C'/',ENDBEFR=C' ',FIXLEN=2),               
         %10=(FIXLEN=4),                                         
         %11=(ABSPOS=91,ENDBEFR=C'/',ENDBEFR=C' ',FIXLEN=2),     
         %12=(ENDBEFR=C'/',ENDBEFR=C' ',FIXLEN=2),               
         %13=(FIXLEN=4),                                         
         %14=(ABSPOS=101,ENDBEFR=C'/',ENDBEFR=C' ',FIXLEN=2),     
         %15=(ENDBEFR=C'/',FIXLEN=2),                             
         %16=(FIXLEN=4)),                                         
  BUILD=(%01,                            $ VALUE-1         
         %02,                            $ VALUE-2         
         %10,UFF,EDIT=(TTTT),            $ DATE-1-CCYY     
         %08,UFF,EDIT=(TT),              $ DATE-1-MM       
         %09,UFF,EDIT=(TT),              $ DATE-1-DD       
                                                           
         %13,UFF,EDIT=(TTTT),            $ DATE-2-CCYY     
         %11,UFF,EDIT=(TT),              $ DATE-2-MM       
         %12,UFF,EDIT=(TT),              $ DATE-2-DD       
                                                           
         %05,UFF,EDIT=(TT),              $ VALUE-3         
                                                           
         %16,UFF,EDIT=(TTTT),            $ DATE-3-CCYY     
         %14,UFF,EDIT=(TT),              $ DATE-3-MM       
         %15,UFF,EDIT=(TT),              $ DATE-3-DD       
                                                           
         %07,UFF,EDIT=(TT)))             $ VALUE-4         
                                                           
//*
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

Previous

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post