REPLACE nth Column of CSV file based on condition



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

REPLACE nth Column of CSV file based on condition

Postby Balamurugan3 » Tue Feb 25, 2014 5:20 pm

Hi ,

I have a requirement to replace nth column of a CSV file based on condition . I did tried using parse option but i am not having idea to include the condition and replace the value

My Input looks like below

F19A,00001243,0165,20120320,1,245.00,,,,,,,0,0,,0,26,0,0,0,0,0,,1646,N,N
F19A,00001243,0165,20120321,1,245.00,,,,,,,0,0,,0,26,0,0,0,0,0,,,N,N
F19A,00001243,0200,20120320,1,245.00,,,,,,,0,0,,0,26,0,0,0,0,0,,1646,N,N

The value 1646 present in the 24th column need to be changed based on condition 0165 [value in 3rd column]

For e.g if the input file contains 0165 then value in 24th column need to be changed as 1900 remaining columns unchanged . If the input file contains 0200 then value in 24th column need to be changed as 1000 remaining columns unchanged , similarly for some more codes.If an match is not found nothing need to be done for that record.

can any help in letting me know how to achieve this ?
Many Thanks,
Bala
Balamurugan3
 
Posts: 17
Joined: Tue Sep 29, 2009 11:23 am
Has thanked: 0 time
Been thanked: 0 time

Re: REPLACE nth Column of CSV file based on condition

 

Re: REPLACE nth Column of CSV file based on condition

Postby BillyBoyo » Tue Feb 25, 2014 5:39 pm

Can you post the sysout from a SORT step so we can see which level/version of SORT you have?

Your sample data shows everything before the 24th column is in a fixed position. Presumably we can't rely on that?

What is the REFCM and LRECL of your file?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: REPLACE nth Column of CSV file based on condition

Postby Balamurugan3 » Tue Feb 25, 2014 5:48 pm

Apologies , its a VB file with RECL 184

Also columns before 24 can vary in length

SYSOUT of normal sort program run in our environment

******************************************************************************************************

1ICE201I H RECORD TYPE IS V - DATA STARTS IN POSITION 5
ICE751I 0 C5-K76982 C6-K90026 C7-K94453 C8-K94453 E9-K60824 C9-BASE E5-K80744 E7-K95469
ICE143I 0 BLOCKSET COPY TECHNIQUE SELECTED
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 12:14 ON TUE FEB 25, 2014 -
0 SORT FIELDS=COPY
OUTFIL REMOVECC,NODETAIL,
TRAILER1=('NO OF RECORDS: ',COUNT=(M11,LENGTH=8))
ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
ICE252I 1 PARMLIB OPTIONS WERE MERGED WITH INSTALLATION MODULE DEFAULTS
ICE088I 0 RNFCOUNT.OMITCND . , INPUT LRECL = 220, BLKSIZE = 27998, TYPE = VB
ICE093I 0 MAIN STORAGE = (MAX,6291456,6291456)
ICE156I 0 MAIN STORAGE ABOVE 16MB = (6234096,6234096)
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256
ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=RC16 ,MSGDDN=SYSOUT
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=NO ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N ,ABCODE=MSG
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,COBEXIT=COB2
ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=2097152,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
ICE235I 0 OPTIONS: NULLOUT=RC0
ICE236I 0 OPTIONS: DYNAPCT=10 ,MOWRK=Y
ICE084I 0 EXCP ACCESS METHOD USED FOR SORTIN
ICE231I 0 STORAGE USED FOR OUTFIL : BELOW 16M = 25600, ABOVE 16M = 2126848
ICE210I 0 SORTOUT : BSAM USED, LRECL = 220, BLKSIZE = 27998, TYPE = VB
ICE751I 1 EF-BASE CB-K64632 F0-K91597 E8-K94453
ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 651807, OUT: 651807
ICE227I 0 SORTOUT : DELETED = 651807, REPORT = 1, DATA = 0
ICE228I 0 SORTOUT : TOTAL IN = 651807, TOTAL OUT = 1
ICE174I 0 NO DATA RECORDS FOR AN OUTFIL DATA SET - RC=0
ICE052I 0 END OF DFSORT

********************************************************************************************
Many Thanks,
Bala
Balamurugan3
 
Posts: 17
Joined: Tue Sep 29, 2009 11:23 am
Has thanked: 0 time
Been thanked: 0 time

Re: REPLACE nth Column of CSV file based on condition

Postby BillyBoyo » Tue Feb 25, 2014 7:04 pm

Thanks. I was hoping you'd have V2.1 :-)

Are the first three columns fixed in length? You have shown all the columns you have, so there are 26 maximum? Are the last two columns fixed in length?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: REPLACE nth Column of CSV file based on condition

Postby Balamurugan3 » Tue Feb 25, 2014 7:14 pm

yes first 3 fields are fixed in length but last 2 columns can be NULL or N or Y hence does not have fixed length
Many Thanks,
Bala
Balamurugan3
 
Posts: 17
Joined: Tue Sep 29, 2009 11:23 am
Has thanked: 0 time
Been thanked: 0 time

Re: REPLACE nth Column of CSV file based on condition

Postby BillyBoyo » Tue Feb 25, 2014 7:44 pm

OK, I was wondering about using JFY and SHIFT=RIGHT to get the 24th column into a fixed position. Not too difficult with only four possibilities for the final two columns, but here's the next thing I'd consider, from the sister-site, http://ibmmainframes.com/about58249.html

Since you have a CSV, all your data will be "displayable". Rather than changing to a space in the FINDREP, I'd pick an "unlikely" hex value like X'FE'.

You'll need three PARSEd fields - the chunk up to and including the comma before the 24th (change 23 commas to X'FE' and then that is easy) the value you potentially want to change, and the two following columns.

The field you want to test against is in a fixed location, so no problem with that. On the PARSEs you will have to specify maximum lengths. You'll need to use OUTFIL with VLTRIM to get back to your final (original) record-length. You'll need to locate the field you are changing somewhere, to change if necessary, so do that at the front of the record (as yours are variable). You'll need to drop off that field in OUTFIL.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: REPLACE nth Column of CSV file based on condition

Postby BillyBoyo » Tue Feb 25, 2014 9:22 pm

And you'll need SQZ. You can of course also do it with 25/26 PARSEd fields. Same issues (SQZ, VLTRIM, extension).
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post