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 Quick References
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

Senior Member


Joined: 15 Aug 2015
Posts: 1245
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

Senior Member


Joined: 15 Aug 2015
Posts: 1245
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

Senior Member


Joined: 15 Aug 2015
Posts: 1245
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

Senior Member


Joined: 15 Aug 2015
Posts: 1245
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 Store the data for fixed length COBOL Programming 1
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Converting fixed length file to excel... IBM Tools 7
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
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us