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

csv 2 fixed with non consistant amount fileds

Postby AKDEARAGON » Fri Oct 07, 2011 12:08 am

My problem is Im taking amount fields that some times have 2 decimal places and some times not but need consistancy. I have 2 columns back to back in middle of a file. I got first column to work not not always on second column. Note that the '.' between fields all look like periods but different when looked at hex. I don't have problem with that

input example
99.X.155.04.172.Y.Y
99.X.143.171.N.N
99.X.2.2.N.N
99.X.154.84.172.N.N
99.X.166.166.N.N

need output to look like the following
99 X 155.04 172.00 Y Y
99 X 143.00 171.00 N N
99 X 2.00 2.00 N N
99 X 154.84 172.00 N N
99 X 166.00 166.00 N N


I'M USING 2 STEPST:
INREC PARSE=(%00=(ENDBEFR=X'05',FIXLEN=06), SKIP LOT OF LINES
%35=(ENDBEFR=X'05',FIXLEN=02),
%36=(ENDBEFR=X'05',FIXLEN=01),
%37=(ENDBEFR=X'05',FIXLEN=06), THIS IS 1ST AMOUNT
%38=(ENDBEFR=X'05',FIXLEN=06), THIS IS 2ND AMOUNT
%39=(ENDBEFR=X'05',FIXLEN=01),
%40=(ENDBEFR=X'05',FIXLEN=01),
BUILD=(%00,JFY=(SHIFT=RIGHT,LEAD=C'0'), SKIP DOWN TO BUILD
%35,JFY=(SHIFT=RIGHT,LEAD=C'0'),
%36,
%37,
%38,
%39%40

NEXT STEP TO HANDLE THE AMOUNT COLUMNS:
SORT FIELDS=COPY
OUTREC OVERLAY=(1:1,396,TRAN=LTOU)
INREC IFTHEN=(WHEN=(308,6,SS,NE,C'.'),
OVERLAY=(308:308,3,UFF,EDIT=(TTT),311:C'.',312:C'00'))
OUTREC FIELDS=(308,6,UFF,EDIT=(TTT.TT)))
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

 

Re: csv 2 fixed with non consistant amount fileds

Postby dick scherrer » Fri Oct 07, 2011 12:43 am

Hello and welcome to the forum,

Note that the '.' between fields all look like periods but different when looked at hex. I don't have problem with that
These are "tab" characters (x'05').

What happens when this is executed? What output is generated?

What are the recfm and lrecl of the files?

The more & better info you provide, the more someone can help.

I'm not sure what is the question here :?
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: csv 2 fixed with non consistant amount fileds

Postby Frank Yaeger » Fri Oct 07, 2011 1:07 am

AKDEARAGON,

Your example is a bit difficult to follow. Let me try to state your requirement more clearly.

Your input file looks something like this where t is the X'05' (tab) character, and a and b are some leading fields you want to ignore (you can add more and ignore them with %=).

atbt99tXt155.04t172tYtY       
atbt99tXt143t171tNtN           
atbt99tXt2t2tNtN               
atbt99tXt154.84t172tNtN       
atbt99tXt166t166tNtN           
atbt99tXt1.6t5.6tNtN           
atbt99tXt1.06t5tNtN           
atbt99tXt12.3t192.1tNtN       


Your output should look like this:

99  X  155.04  172.00  Y  Y                       
99  X  143.00  171.00  N  N                       
99  X    2.00    2.00  N  N                       
99  X  154.84  172.00  N  N                       
99  X  166.00  166.00  N  N                       
99  X    1.60    5.60  N  N                       
99  X    1.06    5.00  N  N                       
99  X   12.30  192.10  N  N                       


If that describes what you want, you can use a one-pass DFSORT job like the following:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=...  input (FB)
//SORTOUT DD DSN=...  output (FB)
//SYSIN DD *
  OPTION COPY
  ALTSEQ CODE=(40F0)
  INREC IFTHEN=(WHEN=INIT,
    PARSE=(%=(ENDBEFR=X'05'),
           %=(ENDBEFR=X'05'),
           %01=(ENDBEFR=X'05',FIXLEN=2),
           %02=(ENDBEFR=X'05',FIXLEN=1),
           %03=(ENDBEFR=X'05',FIXLEN=6),
           %04=(ENDBEFR=X'05',FIXLEN=6),
           %05=(ENDBEFR=X'05',FIXLEN=1),
           %06=(ENDBEFR=X'05',FIXLEN=1)),
    BUILD=(1:%01,5:%02,8:%03,16:%04,24:%05,27:%06)),
   IFTHEN=(WHEN=INIT,
     PARSE=(%07=(ABSPOS=8,ENDBEFR=C'.',ENDBEFR=C' ',FIXLEN=3),
            %08=(FIXLEN=2),
            %09=(ABSPOS=16,ENDBEFR=C'.',ENDBEFR=C' ',FIXLEN=3),
            %10=(FIXLEN=2)),
     OVERLAY=(8:%07,JFY=(SHIFT=RIGHT),C'.',%08,TRAN=ALTSEQ,
             16:%09,JFY=(SHIFT=RIGHT),C'.',%10,TRAN=ALTSEQ))
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: csv 2 fixed with non consistant amount fileds

Postby AKDEARAGON » Wed Oct 12, 2011 5:14 am

Ok I changed to look more like what you showed Frank, but I get this error:
ICE002I 0 DUPLICATE OR CONFLICTING OPTION STATEMENT
ICE126A 2 INCONSISTENT REFORMATTING FOR *INREC : REASON CODE 04, IFTHEN 1

my entire code looks like :

//SORT0 EXEC PGM=SORT
//SORTIN DD DSN=xxxx,DISP=SHR
//SORTOUT DD DSN=ddddd,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE),
// DCB=(LRECL=410,BLKSIZE=0,RECFM=FB)
//SYSSORT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTMSG DD SYSOUT=*
//SYSIN DD *
OPTION COPY
OPTION SKIPREC=1
ALTSEQ CODE=(40F0)
INREC IFTHEN=(WHEN=INIT,
PARSE=(%00=(ENDBEFR=X'05',FIXLEN=06),
%01=(ENDBEFR=X'05',FIXLEN=20),
%02=(ENDBEFR=X'05',FIXLEN=02),
%03=(ENDBEFR=X'05',FIXLEN=50),
%04=(ENDBEFR=X'05',FIXLEN=06),
%05=(ENDBEFR=X'05',FIXLEN=10),
%06=(ENDBEFR=X'05',FIXLEN=15),
%07=(ENDBEFR=X'05',FIXLEN=35),
%08=(ENDBEFR=X'05',FIXLEN=35),
%09=(ENDBEFR=X'05',FIXLEN=35),
%10=(ENDBEFR=X'05',FIXLEN=08),
%11=(ENDBEFR=X'05',FIXLEN=30),
%12=(ENDBEFR=X'05',FIXLEN=05),
%13=(ENDBEFR=X'05',FIXLEN=10),
%14=(ENDBEFR=X'05',FIXLEN=01),
%15=(ENDBEFR=X'05',FIXLEN=01),
%16=(ENDBEFR=X'05',FIXLEN=02),
%17=(ENDBEFR=X'05',FIXLEN=02),
%18=(ENDBEFR=X'05',FIXLEN=01),
%19=(ENDBEFR=X'05',FIXLEN=01),
%20=(ENDBEFR=X'05',FIXLEN=08),
%21=(ENDBEFR=X'05',FIXLEN=10),
%22=(ENDBEFR=X'05',FIXLEN=02),
%23=(ENDBEFR=X'05',FIXLEN=10),
%24=(ENDBEFR=X'05',FIXLEN=02),
%25=(ENDBEFR=X'05',FIXLEN=01),
%26=(ENDBEFR=X'05',FIXLEN=02),
%27=(ENDBEFR=X'05',FIXLEN=02),
%28=(ENDBEFR=X'05',FIXLEN=01),
%29=(ENDBEFR=X'05',FIXLEN=06),
%30=(ENDBEFR=X'05',FIXLEN=06),
%31=(ENDBEFR=X'05',FIXLEN=01),
%32=(ENDBEFR=X'05',FIXLEN=01),
%33=(ENDBEFR=X'05',FIXLEN=01),
%34=(ENDBEFR=X'05',FIXLEN=01),
%35=(ENDBEFR=X'05',FIXLEN=01),
%36=(ENDBEFR=X'05',FIXLEN=01),
%37=(ENDBEFR=X'05',FIXLEN=01),
%38=(ENDBEFR=X'05',FIXLEN=01),
%39=(ENDBEFR=X'05',FIXLEN=01),
%40=(ENDBEFR=X'05',FIXLEN=01),
%41=(ENDBEFR=X'05',FIXLEN=01),
%42=(ENDBEFR=X'05',FIXLEN=01),
%43=(ENDBEFR=X'05',FIXLEN=01),
%44=(ENDBEFR=X'05',FIXLEN=10),
%45=(ENDBEFR=X'05',FIXLEN=01),
%46=(ENDBEFR=X'05',FIXLEN=01),
%47=(ENDBEFR=X'05',FIXLEN=04),
%48=(ENDBEFR=X'05',FIXLEN=01),
%49=(ENDBEFR=X'05',FIXLEN=01),
%50=(ENDBEFR=X'05',FIXLEN=04),
%51=(ENDBEFR=X'05',FIXLEN=04),
%52=(ENDBEFR=X'05',FIXLEN=04),
%53=(ENDBEFR=X'05',FIXLEN=01),
%54=(ENDBEFR=X'05',FIXLEN=01),
%55=(ENDBEFR=X'05',FIXLEN=01),
%56=(ENDBEFR=X'05',FIXLEN=01),
%57=(ENDBEFR=X'05',FIXLEN=35),
%58=(ENDBEFR=X'05',FIXLEN=01),
%59=(ENDBEFR=X'05',FIXLEN=01)),
BUILD=(1:%00,7:%01,27:%02,29:%03,79:%04,85:%05,95:%06,
110:%07,145:%08,180:%09,215:%10,223:%11,253:%12,258:%13,
265:%14,266:%15,267:%16,269:%17,271:%18,272:%19,273:%20,
283:%21,293:%22,295:%23,305:%24,307:%25,308:%26,310:%27,
312:%28,313:%29,319:%30,325:%31,326:%32,327:%33,328:%34,
329:%35,330:%36,331:%37,332:%38,333:%39,334:%40,335:%41,
336:%42,337:%43,338:%44,348:%45,349:%46,350:%47,354:%48,
355:%49,356:%50,360:%51,364:%52,368:%53,369:%54,370:%55,
371:%56,372:%57,407:%58,408:%59)),
IFTHEN=(WHEN=INIT,
PARSE=(%60=(ABSPOS=313,ENDBEFR=C'.',ENDBEFR=C' ',FIXLEN=3),
%61=(FIXLEN=2),
%62=(ABSPOS=319,ENDBEFR=C'.',ENDBEFR=C' ',FIXLEN=3),
%63=(FIXLEN=2)),
OVERLAY=(313:%60,JFY=(SHIFT=RIGHT),C'.',%61,TRAN=ALTSEQ,
319:%62,JFY=(SHIFT=RIGHT),C'.',%63,TRAN=ALTSEQ))

I know I can get rid of 1st error by usion option copy or option skiprec=1 but the 2nd one I don't know were to start.
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 Frank Yaeger » Wed Oct 12, 2011 5:47 am

You can only have one OPTION statement, so it should be:

   OPTION COPY,SKIPREC=1


The ICE126A error indicates you have overlapping fields.

   BUILD=(1:%00,7:%01,27:%02,29:%03,79:%04,85:%05,95:%06,
   ...


Since you chose to specify the column (c:) before each field, you have to ensure that the c: values are consistent with the FIXLEN values. Somewhere in there you have one or more wrong. c should be >= the previous c+fixlen.

For example, 7=1+6 (for %00 FIXLEN) so that's ok.
27=7+20 (for %01 FIXLEN) so that's ok.
But somewhere down the line, it's wrong (it seems to be going wrong around %14) so you have to make it all right before it will work.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: csv 2 fixed with non consistant amount fileds

Postby AKDEARAGON » Wed Oct 12, 2011 10:02 pm

MY RESULTS TURNED OUT LIKE:

99X.04 .04 YY
99X.00 .00 NN
99X.00 .00 NN
99X.84 .84 NN
99X.00 .00 NN
.00 .00
.00 .00

I did not get the value infront of the decimal place
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 dick scherrer » Wed Oct 12, 2011 10:21 pm

Hello,

Please post all of the informational messages generated by the run including the message ids.
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: csv 2 fixed with non consistant amount fileds

Postby AKDEARAGON » Wed Oct 12, 2011 10:27 pm

My job ran clean after I corrected the starting postions of each field int the "build" area. I did have some overlap. but my problem is that I didn't get the expected results on the amounts. I got the following:

99X.04 .04 YY
99X.00 .00 NN
99X.00 .00 NN
99X.84 .84 NN
99X.00 .00 NN
.00 .00
.00 .00

I need it to look like:
99 X 155.04 172.00 Y Y
99 X 143.00 171.00 N N
99 X 2.00 2.00 N N
99 X 154.84 172.00 N N
99 X 166.00 166.00 N N
99 X 1.60 5.60 N N
99 X 1.06 5.00 N N
99 X 12.30 192.10 N N
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 Frank Yaeger » Wed Oct 12, 2011 10:47 pm

You have many fields and I don't have your input data, so I can't really figure it out for you.

The best way to figure out what's wrong is to experiment in stages. Run pieces of the job and check the intermediate output.

For example:

1) Run with just the first IFTHEN clause and capture the intermediate output so you can look at it.

2) Check if your second IFTHEN clause is using the correct fields from the intermediate output.

3) Do the entire job but use a simple BUILD starting at position 1 instead of OVERLAY for the second IFTHEN clause so you can see what you get for %60-%62.

Hopefully, that will help you figure out where you are going wrong. If not, break it down even further. Don't be afraid to experiment with pieces of the job as appropriate.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: csv 2 fixed with non consistant amount fileds

Postby dick scherrer » Wed Oct 12, 2011 11:13 pm

Hello,

To repeat:
Please post all of the informational messages generated by the run including the message ids.
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

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post