|
View previous topic :: View next topic
|
| Author |
Message |
Ron Klop
New User
Joined: 28 Sep 2012
Posts: 28
Location: holland
|
|
|
|
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 |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
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 |
|
|
Ron Klop
New User
Joined: 28 Sep 2012
Posts: 28
Location: holland
|
|
|
|
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 |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1441
Location: Bamberg, Germany
|
|
|
|
Given you have this input:
| Code: |
-0,37;
3,50;
9.999.999.999.999,99;
|
using something like this (looks a bit strange  ):
| 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. 
Cheers,
Jörg |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1441
Location: Bamberg, Germany
|
|
|
|
Minor fix in the SQZ to make it work even without a comma. 
| 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 |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1441
Location: Bamberg, Germany
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
| 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 |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1441
Location: Bamberg, Germany
|
|
|
|
Arun,
I have assumed there is a reason for adding more digits to the converted fraction. 
Cheers,
Jörg |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
