IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

from CSV to fixed


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 28
Location: holland

PostPosted: Thu Feb 07, 2019 8:10 pm
Reply with quote

Hai

we have a CSV file where fields are delimited by ;
I have succeeded in making a fixed file of it by using

PARSE=(%01=(ENDBEFR=C';',FIXLEN=1),
%16=(ENDBEFR=C';',FIXLEN=21),
%17=(ENDBEFR=C';',FIXLEN=21),
%18=(ENDBEFR=C';',FIXLEN=20)),
BUILD(001:%01,
2:%16,UFF,M11,23:%17,UFF,M11,44:%18,UFF,M11))

The only problem is with the fields %16, %17, %18
;-0,37;3,50;9.999.999.999.999,99;

Those three fields are translated as follows, using UFF and M11

000000000000000000037
000000000000000000350
00000999999999999999

But I want them to be translated as

-000000000.370000
+000000003.500000
000009999999999999.990

Is there a possibility?

regards
Ron
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Fri Feb 08, 2019 12:34 am
Reply with quote

Ron,

Please use Code tags while positing code or sample data.

You might want to use the SFF-signed free format (instead of UFF) for the first 2 fields(since you need the sign for those in the output) along with EDIT parameters to achieve this. Something like this:
Code:
BUILD=(01:%01,
       02:%16,SFF,EDIT=(STTTTTTTTT.TTTTTT),SIGNS=(+,-),
       23:%17,SFF,EDIT=(STTTTTTTTT.TTTTTT),SIGNS=(+,-),
       44:%18,UFF,EDIT=(TTTTTTTTTTTTTTTTTT.TTT))       
Back to top
View user's profile Send private message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 28
Location: holland

PostPosted: Sat Feb 09, 2019 2:10 am
Reply with quote

Thnx Arun

The input file has amount fields with comma (,) as decimal point and dot (.) as thousands separator. When we use SFF and UFF, the numbers are considered integers by default i.e. for example 0,37 is considered as -0000000000000.000037 where as it is expected to be -0000000000000.370000.

Regards
Ron
Back to top
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 389
Location: Bamberg, Germany

PostPosted: Sun Feb 10, 2019 12:03 am
Reply with quote

Given you have this input:
Code:
-0,37;               
3,50;                 
9.999.999.999.999,99;

using something like this (looks a bit strange icon_eek.gif):
Code:
INREC IFOUTLEN=40,IFTHEN=(WHEN=INIT,                             
  PARSE=(%01=(ENDBEFR=C';',FIXLEN=20)),                           
    BUILD=(%01,SQZ=(SHIFT=RIGHT,LENGTH=20,PREBLANK=C'.'))),       
  IFTHEN=(WHEN=INIT,                                             
    PARSE=(%11=(ENDBEFR=C',',FIXLEN=17),                         
           %21=(FIXLEN=2)),                                       
      BUILD=(1:%11,SFF,EDIT=(STTTTTTTTTTTTTTTT),SIGNS=(+,-),C'.',
             %21,JFY=(SHIFT=LEFT,TRAIL=C'000000',LENGTH=6)))     
SORT FIELDS=(COPY)                                               
OUTREC BUILD=(1,40)                                               
END


will give you these results
Code:
-0000000000000000.370000
+0000000000000003.500000
+0009999999999999.990000


Edit: Forgot to add, that the input must have a separating comma. icon_redface.gif

Cheers,
Jörg
Back to top
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 389
Location: Bamberg, Germany

PostPosted: Sun Feb 10, 2019 12:31 am
Reply with quote

Minor fix in the SQZ to make it work even without a comma. icon_lol.gif

Code:
//SORTIN   DD *                                                     
8;                                                                   
-0,37;                                                               
3,50;                                                               
9.999.999.999.999,99;                                               
/*                                                                   
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  INREC IFOUTLEN=40,IFTHEN=(WHEN=INIT,                               
    PARSE=(%01=(ENDBEFR=C';',FIXLEN=20)),                           
      BUILD=(%01,SQZ=(SHIFT=LEFT,LENGTH=20,PREBLANK=C'.'))),         
    IFTHEN=(WHEN=INIT,                                               
      PARSE=(%11=(ENDBEFR=C',',FIXLEN=17),                           
             %21=(FIXLEN=2)),                                       
        BUILD=(1:%11,SFF,EDIT=(STTTTTTTTTTTTTTTT),SIGNS=(+,-),C'.', 
               %21,JFY=(SHIFT=LEFT,TRAIL=C'000000',LENGTH=6)))       
  SORT FIELDS=(COPY)                                                 
  OUTREC BUILD=(1,40)                                               
  END                                                               
/*


Result:
Code:

+0000000000000008.000000
-0000000000000000.370000
+0000000000000003.500000
+0009999999999999.990000
Back to top
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 389
Location: Bamberg, Germany

PostPosted: Mon Feb 11, 2019 6:10 am
Reply with quote

That should be it now

Code:
INREC IFOUTLEN=24,IFTHEN=(WHEN=INIT,                             
  PARSE=(%11=(ENDBEFR=C',',FIXLEN=17),                           
         %21=(ENDBEFR=C';',FIXLEN=6)),                           
    BUILD=(%11,SFF,EDIT=(STTTTTTTTTTTTTTTT),SIGNS=(+,-),C'.', 
           %21,JFY=(SHIFT=LEFT,TRAIL=C'000000',LENGTH=6)))       
SORT FIELDS=(COPY)                                               
OUTREC BUILD=(1,24)                                             
END
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Mon Feb 11, 2019 7:47 pm
Reply with quote

Ron Klop wrote:
Thnx Arun

The input file has amount fields with comma (,) as decimal point and dot (.) as thousands separator. When we use SFF and UFF, the numbers are considered integers by default i.e. for example 0,37 is considered as -0000000000000.000037 where as it is expected to be -0000000000000.370000.

Regards
Ron
Sorry, missed that. If your input always has 2 decimal places (2 digits after the comma) as shown in the sample, you might be able to change it slightly like this.
Code:
BUILD=(01:%01,                                                     
        02:%16,SFF,MUL,+10000,EDIT=(STTTTTTTTT.TTTTTT),SIGNS=(+,-),
        23:%17,SFF,MUL,+10000,EDIT=(STTTTTTTTT.TTTTTT),SIGNS=(+,-),
        44:%18,UFF,MUL,+10,EDIT=(TTTTTTTTTTTTTTTTTT.TTT))         
If it is not consistent then you might need to capture the portion after the decimal point separately as shown above by Joerg.Findeisen
Back to top
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 389
Location: Bamberg, Germany

PostPosted: Mon Feb 11, 2019 11:16 pm
Reply with quote

Arun,

I have assumed there is a reason for adding more digits to the converted fraction. icon_wink.gif

Cheers,
Jörg
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts comparasion between BIN FIXED(63) an... PL/I & Assembler 10
No new posts Variable length(Pipe delimter) to Fix... DFSORT/ICETOOL 8
No new posts How can I convert charactor to fixed ... DFSORT/ICETOOL 7
No new posts Converting Variable block file to Fix... DFSORT/ICETOOL 10
No new posts How to copy data with delimters to fi... DFSORT/ICETOOL 2
Search our Forums:

Back to Top