|
View previous topic :: View next topic
|
| Author |
Message |
ojdiaz
New User
Joined: 19 Nov 2008
Posts: 99
Location: Spain
|
|
|
|
Hi.
I have an *.csv input file, from which I need to extract several decimal numbers, which can have from 0 to 4 fractional part. An example input file would be:
| Code: |
1541,58;789654125,3;748
478,5;444,88;985,777
544;66,8789;7451,44 |
The field separator is the ";" character
I need to extract all 3 numeric fields, justify them to the right, fill by the left with zeroes, and truncate to two las decimals, or if the number doesn't has any fractional part, fill it with ",00".
On our shop, the decimal/fractional part separator is the comma "," character
I've been testing with parse and extraction of the fields, wich works perfectly, but I'm having problems with the variable amount of fractional parts, as well as to convert the data to a numeric number
Could you provide an example how to achieve this with dfsort?
The desired output would be like:
| Code: |
0000000154158 0078965412530 00000000074800
0000000004785 0000000044488 00000000098577
0000000054466 0000000878900 00000000745144 |
|
|
| Back to top |
|
 |
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1387
Location: Bamberg, Germany
|
|
|
|
| What have you tried to achieve the desired result? |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
At this forum it is not appreciated: doing someone's job from start to finish.
This is a HELP forum, not DO-MY-JOB-FOR-ME-FOR-FREE forum.
Try to do it step-by-step, not "all together"
1. Learn how to split free-format fields (separated by ';' in your case), and to place them to aligned positions?
You must get:
| Code: |
1541,58 789654125,3 748
478,5 444,88 985,777
544 66,8789 7451,44 |
2. When finished with #1, try to detect integer part, and fractional part of each aligned field (separated by ',' in your case). Keep in mind to use '00' when no comma found!
| Code: |
1541 58 789654125 30 748 00
478 50 444 88 985 77
544 00 66 87 7451 44 |
3. Extend each fractional part with '0's to the desired 2 digits, align them to the right, append the integer part from left, and extend with '0's at the left.
| Code: |
000001541 58 789654125 30 0000000748 00
000000478 50 000000444 88 0000000985 77
000000544 00 000000066 87 0000007451 44 |
| Code: |
00000154158 78965412530 000000074800
00000047850 00000044488 000000098577
00000054400 00000006687 000000745144 |
Do AT LEAST SOMETHING!!! by yourself before asking others to do it for you.  |
|
| Back to top |
|
|
ojdiaz
New User
Joined: 19 Nov 2008
Posts: 99
Location: Spain
|
|
|
|
I'm sorry I attach part of the code I have. I forgot to include it in my previous message
| Code: |
//P020 EXEC PGM=SORT,COND=(4,LT)
//SORTIN DD DISP=SHR,DSN=BKFTP.PRO.LISTADO.AUTO
//SORTOUT DD DSN=PRO.WORK.XPGD052.DATOS.PARSED,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(50,25),RLSE),
// DCB=(RECFM=FB,LRECL=100,BLKSIZE=0),UNIT=WORK
//SYSOUT DD SYSOUT=*
//SYSIN DD *
OPTION SKIPREC=1
INREC IFTHEN=(WHEN=(56,15,SS,EQ,C','),
PARSE=(%01=(ABSPOS=1,ENDBEFR=C';',FIXLEN=11), BIC
%02=(ENDBEFR=C';',FIXLEN=17), CTA 18
%03=(ENDBEFR=C';',FIXLEN=16), REFERENCIA
%04=(ENDBEFR=C';',FIXLEN=8), FECHA
%05=(ENDBEFR=C',',FIXLEN=13), IMPORTE-E
%08=(ENDBEFR=C';',FIXLEN=2), IMPORTE-D
%06=(ENDBEFR=C';',FIXLEN=3), DIV
%07=(ENDBEFR=C';',FIXLEN=4)), TIPO-GASTO
BUILD=(C'1',%01,%02,%03,%04,
%05,UFF,TO=ZD,LENGTH=13,
%08,JFY=(SHIFT=LEFT,TRAIL=C'0'),
%06,%07,25X)),
IFTHEN=(WHEN=NONE,
PARSE=(%11=(ABSPOS=1,ENDBEFR=C';',FIXLEN=11), BIC
%12=(ENDBEFR=C';',FIXLEN=17), CTA 18
%13=(ENDBEFR=C';',FIXLEN=16), REFERENCIA
%14=(ENDBEFR=C';',FIXLEN=8), FECHA
%15=(ENDBEFR=C';',FIXLEN=15), IMPORTE
%16=(ENDBEFR=C';',FIXLEN=3), DIV
%17=(ENDBEFR=C';',FIXLEN=4)), TIPO-GASTO
%17=(ENDBEFR=C';',FIXLEN=4)), TIPO-GASTO
BUILD=(C'1',%11,%12,%13,%14,
%15,UFF,TO=ZD,LENGTH=13,
C'00',%16,%17,25X))
SORT FIELDS=(2,11,CH,A,13,17,CH,A,69,3,CH,A) ORD BIC,CTA,DIV
|
My first example is a simplifaction of my actual code. Since I'm not happy with the result, i was asking here.
Sorry I didn't meant for this to look as a "do-it-for-me". |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
What is the need to re-sort your lines? This is not required by your task...
From the first sight I do not see anything related to your initial post - ???
And your result is LIKE WHAT?
Are you satisfied with the result?
If not: what exactly is wrong?
If you really seek for help, you must provide all info from your attempts.
Once again: DO IT STEP-BY-STEP, and analyze carefully ALL INTERMEDIATE RESULTS before moving forward. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1387
Location: Bamberg, Germany
|
|
|
|
| @ojdiaz: Any reason why the last column is 14 digits and not 13 like the others? |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
| @ojdiaz: Any reason why the only one presented sample of code performs operations not related to the initial task? |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
Looks like the topic needs to be deleted?
TS is not interested in learning something, except making his job for free...  |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1387
Location: Bamberg, Germany
|
|
|
|
| While we still wait, I was wondering if the values to be parsed are positives only (as seen in the sample data). |
|
| Back to top |
|
|
ojdiaz
New User
Joined: 19 Nov 2008
Posts: 99
Location: Spain
|
|
|
|
First off, I think the attitude and the aggressiveness regarding the "you doing my job" is not necesary. Thanks though, for the feedback but honestly, this made my return to this forum after several years not a pleasant experience.
My original question was a simplification of my overall requirement, which does a few more transformations, but the issue at hand was the handling of the decimal and non decimal parts of the CSV final. The final code I posted, which one of my programmers made was heavily dependant on one decimal field present on the position 56 of the input file, which was almost never the case. Thanks to the feedback we managed to make this, which works as expected (for the example). The full sort card has many more instructions, but that wasn't our issue so I'll not post this here
The final code is this:
| Code: |
//P040 EXEC PGM=SORT,COND=(4,LT)
//SORTIN DD *
IMPT1;IMPT2;IMPT3
1545;1,254;4,55
3547,66;3,3;5,8
7896,7854;4789;9987
1,45879;5,45;6,35
345,8 ;3,9 ;6
//SALIDA1 DD DSN=DES.WORK.XOC.CSV.PARSE,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(5,1),RLSE),UNIT=SYSDES,
// DCB=(RECFM=FB,BLKSIZE=0)
//SYSOUT DD SYSOUT=*
//DFSPARM DD *
SOLRF
//SYSIN DD *
OPTION SKIPREC=1
INREC IFTHEN=(WHEN=INIT,
PARSE=(%01=(ABSPOS=1,ENDBEFR=C';',FIXLEN=13), 1ST NUMBER
%02=(ENDBEFR=C';',FIXLEN=13), 2ND NUMBER
%03=(ENDBEFR=C';',FIXLEN=13)), 3RD NUMBER
BUILD=(%01,SQZ=(SHIFT=LEFT,LENGTH=13),
1X,
%02,SQZ=(SHIFT=LEFT,LENGTH=13),
1X,
%03,SQZ=(SHIFT=LEFT,LENGTH=13))),
*
IFTHEN=(WHEN=(1,13,SS,EQ,C','),
PARSE=(%04=(ABSPOS=1,ENDBEFR=C',',FIXLEN=10), INTEGER 1
%05=(ENDBEFR=BLANKS,FIXLEN=2)), DECIMAL 2
OVERLAY=(%04,SQZ=(SHIFT=RIGHT, PARTE ENTERA
LEAD=C'0000000000',LENGTH=10),
1C',',
%05,SQZ=(SHIFT=LEFT,TRAIL=C'00',LENGTH=2)),HIT=NEXT),
*
IFTHEN=(WHEN=(1,13,SS,NE,C','),
PARSE=(%06=(ABSPOS=1,ENDBEFR=BLANKS,FIXLEN=10)), INTEGER 1
OVERLAY=(%06,SQZ=(SHIFT=RIGHT,
LEAD=C'0000000000',LENGTH=10),
1C',00'))
*
SORT FIELDS=COPY
OUTFIL FNAMES=SALIDA1 SOLRF
|
The input sample data:
| Code: |
IMPT1;IMPT2;IMPT3
IMPT1;IMPT2;IMPT3
1545;1,254;4,55
3547,66;3,3;5,8
7896,7854;4789;9987
1,45879;5,45;6,35
345,8 ;3,9 ;6
|
The desired obtained result
| Code: |
0000001545,00 1,254 4,55
0000003547,66 3,3 5,8
0000007896,78 4789 9987
0000000001,45 5,45 6,35
0000000345,80 3,9 6
|
@joerg, all the columns will he made to be 13 byte long. The input numbers will always be positive.
Laters. Have a nice one |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
| @ojdiaz: I insist you to start doing the whole task step-by-step (as I suggested above), and to CAREFULLY VERIFY ALL INTERMEDIATE RESULTS, before adding the next step to your processing code. |
|
| Back to top |
|
|
ojdiaz
New User
Joined: 19 Nov 2008
Posts: 99
Location: Spain
|
|
|
|
Sergey:
As far as we’ve tested it, step by step it worked as expected. First, we used WHEN=INIT to split the numbers into percentage variables. Then, we employed BUILD to create three fields of 13-character length and aligned them to the left.
Next, we focused on the first field and checked whether it contained a decimal separator (“,”), which indicates a fractional part. If it did, we reformatted it by aligning the numbers to the right, filling the left with zeroes, and completing the decimal part with additional zeroes until we had a 13-character field. We achieved this using the first IFTHEN=COND statement.
Finally, we performed another check in case the field didn’t have any decimal character. In that scenario, we aligned the character to the right, added the comma, and appended two zeroes to complete a full 13-character field.
It seems to be working as I tested it step by step: first the INIT, then the first WHEN=COND, and finally the next WHEN=COND. Checking intermediate results. Which is what I usually do, and I was just looking for some insights or methods to perform this other than what we had at the begining
If you notice any risks, incorrect code, or potential undesired results based on the requirements I posted, please let me know wherE because I can't seem to find it |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1387
Location: Bamberg, Germany
|
|
|
|
Input:
| Code: |
1545;1,254;4,55
3547,66;3,3;5,8
7896,7854;4789;9987
1,45879;5,45;6,35
345,8 ;3,9 ;6 |
Code:
| Code: |
OPTION COPY
ALTSEQ CODE=(406B)
INREC IFTHEN=(WHEN=INIT,
PARSE=(%01=(ENDBEFR=C';',FIXLEN=13,REPEAT=3)),
BUILD=(%01,TRAN=ALTSEQ,C';',
%02,TRAN=ALTSEQ,C';',
%03,TRAN=ALTSEQ,C';')),
IFTHEN=(WHEN=INIT,
PARSE=(%11=(ENDBEFR=C',',FIXLEN=10),
%12=(ENDBEFR=C';',FIXLEN=2),
%21=(ENDBEFR=C',',FIXLEN=10),
%22=(ENDBEFR=C';',FIXLEN=2),
%31=(ENDBEFR=C',',FIXLEN=10),
%32=(ENDBEFR=C';',FIXLEN=2)),
BUILD=(%11,SFF,M1,SIGNS=(,,',',','),
%12,JFY=(SHIFT=LEFT,PREBLANK=C',',TRAIL=C'00'),X,
%21,SFF,M1,SIGNS=(,,',',','),
%22,JFY=(SHIFT=LEFT,PREBLANK=C',',TRAIL=C'00'),X,
%31,SFF,M1,SIGNS=(,,',',','),
%32,JFY=(SHIFT=LEFT,PREBLANK=C',',TRAIL=C'00')))
END |
Output:
| Code: |
0000001545,00 0000000001,25 0000000004,55
0000003547,66 0000000003,30 0000000005,80
0000007896,78 0000004789,00 0000009987,00
0000000001,45 0000000005,45 0000000006,35
0000000345,80 0000000003,90 0000000006,00 |
|
|
| Back to top |
|
|
ojdiaz
New User
Joined: 19 Nov 2008
Posts: 99
Location: Spain
|
|
|
|
Hello Joerg!
First thank for your input. A few comments on your code:
I wasn't aware that you could use this operand REPEAT=3 in the parse instruction. That's brilliant, I'll defintely remember this in the future.
I'll check the manual for the ALTSEQ operand, since I think I used it a long time ago but I don't remember at the moment the exact way it functions.
Also, I didn't knew you could repeat several WHEN=INIT instructions, so I'll definetely will remember it.
Thanks for your support and help. I'll get back to you after our testings 
Cheers! |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1387
Location: Bamberg, Germany
|
|
|
|
| ALTSEQ/TRAN will replace X'40' by X'6B' in the parsed fields. It enables the second PARSE to detect fractional parts (if any). The JFY fixes the latter, or fills up with Zeros when needed. |
|
| Back to top |
|
|
ojdiaz
New User
Joined: 19 Nov 2008
Posts: 99
Location: Spain
|
|
|
|
Hi Joerg
Afeter several test we now understand the ALTSEQ usage and the loginc behind your code. Honestly simpler than what we managed.
Definetely I appreciate your help on the subject. Cheers |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
| Joerg.Findeisen wrote: |
| ALTSEQ/TRAN will replace X'40' by X'6B' in the parsed fields. It enables the second PARSE to detect fractional parts (if any). The JFY fixes the latter, or fills up with Zeros when needed. |
Excessive "optimization", such as using ALTSEQ to replace comma characters, or using SIGNS parameter to substitute non-sign characters like a comma, makes the code hard to be understood by a human...
The last issue may be more important than executability of the code by a computer. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1387
Location: Bamberg, Germany
|
|
|
|
| Of course the code should be understandable/maintainable by humans. That is, keeping the balance of avoiding too many IFTHEN, absolute positions, and on the other hand maybe adding some comments if the provided code is helpful and understood by the requestor. |
|
| Back to top |
|
|
|
|
|
