Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
from CSV to fixed

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: from CSV to fixed
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: 2482
Location: @my desk

PostPosted: Fri Feb 08, 2019 12:34 am    Post subject:
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    Post subject:
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

New User


Joined: 15 Aug 2015
Posts: 62
Location: Germany

PostPosted: Sun Feb 10, 2019 12:03 am    Post subject:
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

New User


Joined: 15 Aug 2015
Posts: 62
Location: Germany

PostPosted: Sun Feb 10, 2019 12:31 am    Post subject:
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

New User


Joined: 15 Aug 2015
Posts: 62
Location: Germany

PostPosted: Mon Feb 11, 2019 6:10 am    Post subject:
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: 2482
Location: @my desk

PostPosted: Mon Feb 11, 2019 7:47 pm    Post subject:
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

New User


Joined: 15 Aug 2015
Posts: 62
Location: Germany

PostPosted: Mon Feb 11, 2019 11:16 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How can I convert charactor to fixed ... liying DFSORT/ICETOOL 7 Tue May 22, 2018 3:35 pm
No new posts Converting Variable block file to Fix... bhavana yalavarthi DFSORT/ICETOOL 10 Tue May 16, 2017 2:20 pm
No new posts How to copy data with delimters to fi... Venkata Ramayya DFSORT/ICETOOL 2 Wed Feb 24, 2016 10:03 pm
No new posts FIXED BIN (31) and FIXED BIN (63) - H... sundarkudos PL/I & Assembler 4 Thu Nov 05, 2015 1:11 pm
No new posts Add delimiters in a fixed length record Venkata Ramayya DFSORT/ICETOOL 4 Fri Mar 27, 2015 3:49 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us