Trying to add extra field on a .csv parsing job



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

Trying to add extra field on a .csv parsing job

Postby golemis » Tue Sep 10, 2019 3:49 pm

Hi team,
I am decomposing a .cvs file into a fixed column length one using the following CNTL:
 OPTION VLSHRT
  RECORD TYPE=V,LENGTH=300
  OUTFIL PARSE=(%01=(ABSPOS=6,ENDBEFR=C'","',FIXLEN=7),
                %02=(ENDBEFR=C'","',FIXLEN=42),
                %03=(ENDBEFR=C'","',FIXLEN=8),
                %04=(ENDBEFR=C'","',FIXLEN=3),
                %05=(ENDBEFR=C'","',FIXLEN=37),
                %06=(ENDBEFR=C'","',FIXLEN=32),
                %07=(ENDBEFR=C'","',FIXLEN=26),
                %08=(ENDBEFR=C'","',FIXLEN=18),
                %09=(ENDBEFR=C'","',FIXLEN=19),
                %10=(ENDBEFR=C'","',FIXLEN=1),
                %11=(ENDBEFR=C'","',FIXLEN=1),
                %12=(ENDBEFR=C'","',FIXLEN=8),
                %13=(ENDBEFR=C'","',FIXLEN=20),
                %14=(ENDBEFR=C'"',FIXLEN=16)),
  VTOF,
  BUILD=(%02,
         %01,
         %04,
         %05,
         %03,
         %09,JFY=(SHIFT=RIGHT),
         %06,
         %07,
         %08,
         %10,
         %11,
         %12,
         %13)

and works fine.

however, i need to add an extra filed at the end, which would be a single character, "R" if the first char of field %04% is "R" and "A" if first char of field %04 is "V"

Tried several options including IFTHEN or CHANGE, but successful. Last attempt was :

OPTION VLSHRT
  RECORD TYPE=V,LENGTH=300
  OUTFIL PARSE=(%01=(ABSPOS=6,ENDBEFR=C'","',FIXLEN=7),
                %02=(ENDBEFR=C'","',FIXLEN=42),
                %03=(ENDBEFR=C'","',FIXLEN=8),
                %04=(ENDBEFR=C'","',FIXLEN=3),
                %05=(ENDBEFR=C'","',FIXLEN=37),
                %06=(ENDBEFR=C'","',FIXLEN=32),
                %07=(ENDBEFR=C'","',FIXLEN=26),
                %08=(ENDBEFR=C'","',FIXLEN=18),
                %09=(ENDBEFR=C'","',FIXLEN=19),
                %10=(ENDBEFR=C'","',FIXLEN=1),
                %11=(ENDBEFR=C'","',FIXLEN=1),
                %12=(ENDBEFR=C'","',FIXLEN=8),
                %13=(ENDBEFR=C'","',FIXLEN=20),
                %14=(ENDBEFR=C'"',FIXLEN=16)),
  VTOF,
  BUILD=(%02,
         %01,
         %04,
         %05,
         %03,
         %09,JFY=(SHIFT=RIGHT),
         %06,
         %07,
         %08,
         %10,
         %11,
         %12,
         %13,
         %04,CHANGE=(1,
             C'R',C'R',
             C'V',C'A'))

but did not work. Any ideas would be appreciated. Thanks, GG
golemis
 
Posts: 21
Joined: Wed Apr 04, 2018 8:13 pm
Location: London, UK
Has thanked: 4 times
Been thanked: 0 time

Re: Trying to add extra field on a .csv parsing job

 

Re: Trying to add extra field on a .csv parsing job

Postby enrico-sorichetti » Tue Sep 10, 2019 5:01 pm

but did not work.


that' s very sad, we all hope that You will be able to find a solution :mrgreen:
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2884
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 153 times

Re: Trying to add extra field on a .csv parsing job

Postby sergeyken » Tue Sep 10, 2019 7:21 pm

 OUTFIL   VTOF,
    IFTHEN=(WHEN=INIT,
            PARSE=(%01=(ABSPOS=6,ENDBEFR=C'","',FIXLEN=7),
 . . . . . . . . . . . . .
                   %14=(ENDBEFR=C'"',FIXLEN=16)),
            BUILD=(%02,
 . . . . . . . . . . . . .
                   %14)),                 initially use %14 as is
    IFTHEN=(WHEN=(pos,1,CH,EQ,C'V'),      use pos value as real position of %14
            OVERLAY=(pos:C'A'))           replace char 1 of %14 when needed
 

These users thanked the author sergeyken for the post:
golemis (Tue Sep 10, 2019 7:32 pm)
sergeyken
 
Posts: 28
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 2 times
Been thanked: 5 times

Re: Trying to add extra field on a .csv parsing job

Postby golemis » Tue Sep 10, 2019 7:34 pm

Thanks sergeyken, will try it.
golemis
 
Posts: 21
Joined: Wed Apr 04, 2018 8:13 pm
Location: London, UK
Has thanked: 4 times
Been thanked: 0 time

Re: Trying to add extra field on a .csv parsing job

Postby golemis » Wed Sep 11, 2019 1:20 pm

sergeyken wrote:
 OUTFIL   VTOF,
    IFTHEN=(WHEN=INIT,
            PARSE=(%01=(ABSPOS=6,ENDBEFR=C'","',FIXLEN=7),
 . . . . . . . . . . . . .
                   %14=(ENDBEFR=C'"',FIXLEN=16)),
            BUILD=(%02,
 . . . . . . . . . . . . .
                   %14)),                 initially use %14 as is
    IFTHEN=(WHEN=(pos,1,CH,EQ,C'V'),      use pos value as real position of %14
            OVERLAY=(pos:C'A'))           replace char 1 of %14 when needed
 


Unfortunately it gave Error:
ICE126A 9 INCONSISTENT REFORMATTING FOR T1 : REASON CODE 05, IFTHEN 1

Reading the manual found out that:
Note: VTOF cannot be used with an IFTHEN or
OVERLAY operand.
golemis
 
Posts: 21
Joined: Wed Apr 04, 2018 8:13 pm
Location: London, UK
Has thanked: 4 times
Been thanked: 0 time

Re: Trying to add extra field on a .csv parsing job

Postby sergeyken » Wed Sep 11, 2019 7:17 pm

golemis wrote:
sergeyken wrote:
 OUTFIL   VTOF,
    IFTHEN=(WHEN=INIT,
            PARSE=(%01=(ABSPOS=6,ENDBEFR=C'","',FIXLEN=7),
 . . . . . . . . . . . . .
                   %14=(ENDBEFR=C'"',FIXLEN=16)),
            BUILD=(%02,
 . . . . . . . . . . . . .
                   %14)),                 initially use %14 as is
    IFTHEN=(WHEN=(pos,1,CH,EQ,C'V'),      use pos value as real position of %14
            OVERLAY=(pos:C'A'))           replace char 1 of %14 when needed
 


Unfortunately it gave Error:
ICE126A 9 INCONSISTENT REFORMATTING FOR T1 : REASON CODE 05, IFTHEN 1

Reading the manual found out that:
Note: VTOF cannot be used with an IFTHEN or
OVERLAY operand.

Move all IFTHENs to statement OUTREC, and leave other parameter(s) with OUTFIL.
Also don't forget to mention RDW field in your BUILD parameters
 OUTREC IFTHEN=(WHEN=INIT,
            PARSE=(%01=(ABSPOS=6,ENDBEFR=C'","',FIXLEN=7),
 . . . . . . . . . . . . .
                   %14=(ENDBEFR=C'"',FIXLEN=16)),
            BUILD=(1,4,                            RDW from RECFM=VB      
                   %02,
 . . . . . . . . . . . . .
                   %14)),                 initially use %14 as is
    IFTHEN=(WHEN=(pos,1,CH,EQ,C'V'),      use pos value as real position of %14 (plus 4 for RDW)
            OVERLAY=(pos:C'A'))           replace char 1 of %14 when needed
  OUTFIL   VTOF
sergeyken
 
Posts: 28
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 2 times
Been thanked: 5 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post