Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Parsing CSV containing a floating point field.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
sl2pradeep

New User


Joined: 26 Jan 2006
Posts: 6
Location: Kuala Lumpur

PostPosted: Wed May 04, 2011 8:47 am    Post subject: Parsing CSV containing a floating point field.
Reply with quote

Gentlemen,

I'm trying to parse a CSV input using syncsort, but I'm stuck in once place where there's a floating point field.

For example, my input will look like this
Code:
aaa,bbbb,cccc,51.500,xxx,ddd
aab,bbc,ccd,51.50,xxx,ddd
aab,bbc,ccd,51.5,xxx,ddd
aab,bbc,ccd,51,xxx,ddd


Where 51.500 is the ideal value as I'm expecting to extract the value as PIC9(2)V9(3).
But at the sametime if the value is 51.50 or 51.5 or 51 , then I should convert this to PIC9(2)V9(3) [ or TT.TTT] before converting it to PD!

I tried using MUL by 1000 and then dividing by 1000 to see if it'll work, but failed.

These are the sort cards used
Code:
SORT FIELDS=COPY                             
 OUTREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=04),   
               %02=(ENDBEFR=C',',FIXLEN=02),   
               %03=(ENDBEFR=C',',FIXLEN=03),   
               %04=(ENDBEFR=C',',FIXLEN=10),   
               %05=(ENDBEFR=C',',FIXLEN=18),   
               %06=(ENDBEFR=C',',FIXLEN=12),   
               %07=(ENDBEFR=C',',FIXLEN=10),   
               %08=(ENDBEFR=C',',FIXLEN=19),   
               %09=(ENDBEFR=C',',FIXLEN=19),   
               %10=(ENDBEFR=C',',FIXLEN=36),   
               %11=(ENDBEFR=C',',FIXLEN=19),   
               %12=(ENDBEFR=C',',FIXLEN=01),   
               %13=(ENDBEFR=C',',FIXLEN=06),   
               %14=(ENDBEFR=C',',FIXLEN=32),   
               %15=(ENDBEFR=C',',FIXLEN=32),   
               %16=(ENDBEFR=X'0D',ENDBEFR=C',',
                    FIXLEN=32)),               
 BUILD=(01:%01,                               
        05:%02,                               
        07:%03,                               
        10:%04,                               
        20:%05,                               
        38:%06,                               
        50:%07,                               
        60:%08,SFF,TO=PD,LENGTH=10,           
        70:%09,SFF,TO=PD,LENGTH=10,           
        80:%10,                               
        116:%11,SFF,TO=PD,LENGTH=10,           
        126:%12,                               
        127:%13,SFF,TO=PD,LENGTH=3,                 
        133:%14,                               
        165:%15,                               
        197:%16)             


%13 is the field I'm having trouble with.
Tried the following but didn't get the desired output
Code:
SORT FIELDS=COPY                             
 OUTREC BUILD=(1:1,126,                       
              127:+1000,MUL,                 
              127,6,SFF,DIV,                 
              +1000,EDIT=(TT.TTT))   


Appreciate any help on this.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed May 04, 2011 11:37 am    Post subject: Reply to: Parsing CSV containing a floating point field.
Reply with quote

You don't have a "floating point" field. You have a field where trailing zeros are being suppressed up to the decimal point. Different thing.

Did you try to convert it anyway, or just assume it didn't work?
Back to top
View user's profile Send private message
sl2pradeep

New User


Joined: 26 Jan 2006
Posts: 6
Location: Kuala Lumpur

PostPosted: Wed May 04, 2011 2:01 pm    Post subject:
Reply with quote

Bill,

The 13th Field, is a ''floating point'' field, but I just gave an example where I had the trouble, and yes where the trailing zeros are suppressed up to the decimal point.

And I did tried all of that I know, and not assume!!

I can't give the actual output over here as its a production data. But the output was like following,
I/p - O/p
51.500 - 51.500 --- > Correct, as expected
51.50 - 5.150 -----> Incorrect
51.5 - 0.515 -----> Incorrect

Thanks
Pradeep
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 982
Location: Oostende, Belgium

PostPosted: Wed May 04, 2011 2:08 pm    Post subject:
Reply with quote

sl2pradeep wrote:
I can't give the actual output over here as its a production data.

Bullthis!

In View:
Code:
c p'#' '8' all
c p'@' 'X' all

Now was that so difficult?
Back to top
View user's profile Send private message
sl2pradeep

New User


Joined: 26 Jan 2006
Posts: 6
Location: Kuala Lumpur

PostPosted: Wed May 04, 2011 2:31 pm    Post subject:
Reply with quote

prino wrote:
sl2pradeep wrote:
I can't give the actual output over here as its a production data.

Bullthis!

In View:
Code:
c p'#' '8' all
c p'@' 'X' all

Now was that so difficult?


Sorry didn't get what you mean by that, anyway..

Data given as
Code:
aaa,bbbb,ccc,51.500,xxx,ddd
aab,bbb,ccc,51.50,xxx,ddd
aab,bbb,ccc,51.5,xxx,ddd


and the sort card
Code:
SORT FIELDS=COPY                             
 OUTREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=03),   
               %02=(ENDBEFR=C',',FIXLEN=03),   
               %03=(ENDBEFR=C',',FIXLEN=03),   
               %04=(ENDBEFR=C',',FIXLEN=06),   
               %05=(ENDBEFR=C',',FIXLEN=03),   
               %06=(ENDBEFR=C',',FIXLEN=03)),               
 BUILD=(01:%01,                               
        04:%02,                               
        07:%03,                               
        10:%04,SFF,TO=PD,LENGTH=3,                                   
        13:%05,                               
        16:%06)                               
     


Got the output as
Code:
aaabbbccc.&.xxxddd
aaabbbccc...xxxddd
aaabbbccc..¬xxxddd


When formatted it displayed as
Code:
aaa         bbb         ccc              51.500 xxx         ddd
aaa         bbb         ccc               5.150 xxx         ddd
aaa         bbb         ccc               0.515 xxx         ddd
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed May 04, 2011 3:34 pm    Post subject:
Reply with quote

sl2pradeep wrote:
The 13th Field, is a ''floating point'' field


for your information, 'floating point' has a definite mathematical definition,
you problem does not qualify.

your problem is that your input in not formated consistantly.
and it is 'pre-edited', which means that it is a char field and not a numeric field.
you are causing yourself problems by defining the field as UFF/SFF

from articles and manuals concerning UFF and SFF:
Quote:
Any combination of characters is valid,
but characters other than 0-9, the minus sign, and the rightparenthesis
are ignored.
(emphasis is mine)

the above tells me that the decimal point,
which is not consistantly formated with trailing numerics,
is ignored.

so, the problem is your parsing technique.
my limited DFSORT capabilities would lead me down this path:

1. parse two fields
  • the first: prior to the 'decimal point' (the period)
  • the second: after the 'decimal point' (the period)

2. expand the second to a consistant size
3. put them together and do the 'TO PD,LENGTH=3

or wait for Frank or Kolusu to provide a solution.
Back to top
View user's profile Send private message
sl2pradeep

New User


Joined: 26 Jan 2006
Posts: 6
Location: Kuala Lumpur

PostPosted: Wed May 04, 2011 3:40 pm    Post subject:
Reply with quote

Thanks Dick..

Just got a way-around using NUMVAL in COBOL.

The NUMVAL function returns the numeric value represented by the alphanumeric character string specified in an argument. The function strips away any leading or trailing blanks in the string, producing a numeric value that can be used in an arithmetic expression.

but will wait to see if Frank or Kolusu has any solution to this one in DF or SYncSort.

Cheers
Pradeep
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed May 04, 2011 4:03 pm    Post subject:
Reply with quote

sl2pradeep wrote:

[...]
The 13th Field, is a ''floating point'' field [...]


Hi, Dick has already provided you with an excellent explanation of what you are doing to achieve your incorrect results.

It may aid your future knowledge if you do a google search for "what is a floating point number".

In a "floating point number", a completely different thing from what you are talking about, the visible decimal place will always be in the same position with an "exponent" (a power in whatever number base) to tell you where the decimal point is for calculations.

"Floating point" is good for very big numbers and very small numbers. It is not much use for numbers of your size due to processing overheads anyway. If you are taking a CSV from Excel in a number of financial applications, you may well see a true "floating point" number. Looks nothing like yours. Yours is a piece of text with a decimal point in a varying position.

I tried to point this out shortly to you originally to aid your search for a solution. I thought to myself, "if this guy is looking up floating point, he's not going to find the answer except by accident".

When you say something is such-and-such, and someone else says "no it is not", next time check rather than just saying "yes it is", which is no help to you at all. Sometimes you'll be right. This time, not.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed May 04, 2011 6:16 pm    Post subject:
Reply with quote

sl2pradeep wrote:
[...]
Just got a way-around using NUMVAL in COBOL.
[...]


And now you get a floating-point number! :-)
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed May 04, 2011 7:06 pm    Post subject:
Reply with quote

Quote:
but will wait to see if Frank or Kolusu has any solution to this one in DF or SYncSort.


Well.. WHY DONT YOU TELL YOU HAVE SYNCSORT or DFSORT AT YOUR SHOP??????

Solutions might be different based on product YOU are using....
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Wed May 04, 2011 7:11 pm    Post subject: Reply to: Parsing CSV containing a floating point field.
Reply with quote

You posted in the jcl part of the forum, and explicitly expected a SYNCSORT solution
Quote:
I'm trying to parse a CSV input using syncsort,

Quote:
but will wait to see if Frank or Kolusu has any solution to this one in DF or SYncSort.

since Frank and Kolusu are DFSORT developers no reason to expect them to
provide solutions for SYNCSORT
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed May 04, 2011 7:35 pm    Post subject:
Reply with quote

Quote:
You posted in the jcl part of the forum


my bad.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Wed May 04, 2011 7:40 pm    Post subject: Reply to: Parsing CSV containing a floating point field.
Reply with quote

icon_redface.gif icon_biggrin.gif
anyway it is worth even for a syncsort user to look at the dfsort solutions
an the smart sort tricks
Back to top
View user's profile Send private message
sl2pradeep

New User


Joined: 26 Jan 2006
Posts: 6
Location: Kuala Lumpur

PostPosted: Thu May 05, 2011 8:16 am    Post subject:
Reply with quote

Erm.. icon_surprised.gif Its SYNCSORT which we are using!

Escapa wrote:
Quote:
but will wait to see if Frank or Kolusu has any solution to this one in DF or SYncSort.


Well.. WHY DONT YOU TELL YOU HAVE SYNCSORT or DFSORT AT YOUR SHOP??????

Solutions might be different based on product YOU are using....
Back to top
View user's profile Send private message
sl2pradeep

New User


Joined: 26 Jan 2006
Posts: 6
Location: Kuala Lumpur

PostPosted: Thu May 05, 2011 8:21 am    Post subject: Re: Reply to: Parsing CSV containing a floating point field.
Reply with quote

enrico-sorichetti wrote:
You posted in the jcl part of the forum, and explicitly expected a SYNCSORT solution
Quote:
I'm trying to parse a CSV input using syncsort,

Quote:
but will wait to see if Frank or Kolusu has any solution to this one in DF or SYncSort.

since Frank and Kolusu are DFSORT developers no reason to expect them to
provide solutions for SYNCSORT


Enrico, its SYNCSORT which I'm using and hence posted in the jcl part; and I wasn't aware that Frank & Kolusu are DFSORT experts! Oops, Sorry!
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Fri May 06, 2011 3:45 pm    Post subject:
Reply with quote

Quote:
Erm.. Its SYNCSORT which we are using!
Not to worry about that. icon_lol.gif Here's an example syncsort job which does what you're trying. You could tweak this to make it work for your actual requirement
Code:
//STEP01  EXEC PGM=SORT           
//SYSOUT   DD  SYSOUT=*           
//*                               
//SORTIN   DD  DSN= Input file   
//SORTOUT  DD  SYSOUT=*           
//SYSIN    DD  *                 
  SORT FIELDS=COPY                                       
  INREC IFTHEN=(WHEN=INIT,                               
        PARSE=(%01=(ENDBEFR=C',',FIXLEN=04),             
               %02=(ENDBEFR=C',',FIXLEN=04),             
               %03=(ENDBEFR=C',',FIXLEN=04),             
               %04=(ENDBEFR=C',',FIXLEN=06),             
               %05=(ENDBEFR=C',',FIXLEN=04),             
               %06=(FIXLEN=04)),                         
        BUILD=(%01,C'|',%02,C'|',%03,C'|',%04,C'|',       
               %05,C'|',%06)),                           
        IFTHEN=(WHEN=INIT,                               
        PARSE=(%07=(ABSPOS=16,ENDBEFR=C'.',FIXLEN=02),   
               %08=(ENDBEFR=C'|',FIXLEN=03)),             
        BUILD=(1,15,%07,UFF,EDIT=(TT),%08,22,9)),         
        IFTHEN=(WHEN=INIT,                               
        FINDREP=(INOUT=(C' ',C'0'),STARTPOS=18,ENDPOS=20))
SORTIN
Code:
aaa,bbbb,cccc,51.500,xxx,ddd
aab,bbc,ccd,51.50,xxx,ddd   
aab,bbc,ccd,51.5,xxx,ddd     
aab,bbc,ccd,51,xxx,ddd       
aab,bbc,ccd,5,xxx,ddd       
aab,bbc,ccd,5.5,xxx,ddd     
aab,bbc,ccd,.5,xxx,ddd       
aab,bbc,ccd,05,xxx,ddd       
aab,bbc,ccd,0.5,xxx,ddd     
aab,bbc,ccd,0.050,xxx,ddd   
aab,bbc,ccd,5.0,xxx,ddd
SORTOUT
Code:
aaa |bbbb|cccc|51500|xxx |ddd
aab |bbc |ccd |51500|xxx |ddd
aab |bbc |ccd |51500|xxx |ddd
aab |bbc |ccd |51000|xxx |ddd
aab |bbc |ccd |05000|xxx |ddd
aab |bbc |ccd |05500|xxx |ddd
aab |bbc |ccd |00500|xxx |ddd
aab |bbc |ccd |05000|xxx |ddd
aab |bbc |ccd |00500|xxx |ddd
aab |bbc |ccd |00050|xxx |ddd
aab |bbc |ccd |05000|xxx |ddd
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us