|
View previous topic :: View next topic
|
| Author |
Message |
karan_reddy
New User
Joined: 02 Nov 2019
Posts: 2
Location: India
|
|
|
|
Hello,
I'm working on converting data in mainframe data set to write into output file with comma delimiter with some formatting so that I can send the output file in .csv format through email in JCL. However I could get some output but not the one as expected so thought of posting it here and get some help.
Input:
| Code: |
XYZ SUMMARY REPORT
DURATION - 3 MONTH(S)
RUN-DATE - 10/29/19
DESCRIPTION - ABC PAYMENTS
P# CODE AMT
01 0001 1,100.05
01 0002 200.03-
TOTL 900.02
P# CODE AMT
03 0001 400.10
03 0003 2.100.10
03 0004 50.00-
TOTL 2,450.20
|
Requirement:
1.Display Amount field without comma separator
2.If P# value is '01' then write output as "JEN1" in P# field
3.When converted to excel leading zero's should be retained
4.Header "P# CODE AMT" should display only once.
5.Negative should display before the value as leading sign
Expected Output:
| Code: |
XYZ SUMMARY REPORT ,
DURATION - 3 MONTH(S) ,
RUN-DATE - 10/29/19 ,
DESCRIPTION - ABC PAYMENTS ,
P# , CODE, AMT
JEN1, 0001, 1100.05
JEN1, 0002, -200.03
, TOTL, 900.02
JEN3, 0001, 400.10
JEN3, 0003, 2100.10
JEN3, 0004 -50.00
, TOTL, 2450.20
|
SORT statement I have coded:
| Code: |
OPTION COPY
OMIT COND=(1,133,CH,EQ,C' ',OR,1,2,CH,EQ,C'P#')
INREC IFTHEN=(WHEN=(1,18,CH,EQ,C'XYZ SUMMARY REPORT'),
OVERLAY=(50:C',')),
IFTHEN=(WHEN=(1,8,CH,EQ,C'DURATION'),
OVERLAY=(50:C',')),
IFTHEN=(WHEN=(1,8,CH,EQ,C'RUN-DATE'),
OVERLAY=(50:C',')),
IFTHEN=(WHEN=(1,17,CH,EQ,C'DESCRIPTION - ABC'),
OVERLAY=(50:C',')),
IFTHEN=(WHEN=(1,2,CH,EQ,C'01'),
OVERLAY=(3:C',',15:C',')),
IFTHEN=(WHEN=(1,2,CH,EQ,C'03'),
OVERLAY=(3:C',',15:C',')),
IFTHEN=(WHEN=(11,4,CH,EQ,C'TOTL'),
OVERLAY=(3:C',',15:C','))
OUTFIL OUTREC=(1,80,53X)
|
From the above SORT the output I got:
| Code: |
XYZ SUMMARY REPORT ,
DURATION - 3 MONTH(S) ,
RUN-DATE - 10/29/19 ,
DESCRIPTION - ABC PAYMENTS ,
01, 0001, 1,100.05
01, 0002, 200.03-
, TOTL, 900.02
03, 0001, 400.10
03, 0003, 2,100.10
03, 0004, 50.00-
, TOTL, 2,450.20
|
When I open this .csv file in excel I could see values "0001" is displayed as "1" with leading zeros truncated and 1,100.05 displayed in 2 seperate columns due to comma delimiter; and negative sign is at the end but I want that to display at the beginning.
Please advise. |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
Welcome to the forum!!
| Quote: |
When I open this .csv file in excel I could see values "0001" is displayed as "1" with leading zeros truncated and 1,100.05 displayed in 2 seperate columns due to comma delimiter; and negative sign is at the end but I want that to display at the beginning.
|
1. Leading 0s truncation is not something DFSORT can help you with, Excel will truncate it even if you wrap them under "".
2. You need to wrap all the Numeric values by "" and it will solve the problem.Use in BUILD JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"',LENGTH=?)
3. When you wrap them with "" , '-' problem too should go away.
Let us know. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| To show the value 0001 as 0001 in Excel you need to format the Excel column to have 4 leading zeros or make it a text column in which case you may have to right justify it. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
| Nic, If you are suggesting a DFSORT solution then I don’t see a way it works , it’s the Excel that truncates it in whichever way you format in DFSORT. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2283
Location: USA
|
|
|
|
| karan_reddy wrote: |
When I open this .csv file in excel I could see values "0001" is displayed as "1" with leading zeros truncated and 1,100.05 displayed in 2 seperate columns due to comma delimiter; and negative sign is at the end but I want that to display at the beginning.
Please advise. |
1. EXCEL performs its own internal formatting of data before they are displayed in the spreadsheet. By default it uses "leading zero suppression" formatting for numeric (but this can be changed for specific columns/cells)
2. If you really(??) need to suppress leading zeroes in SORT operations, then use parameter BUILD=(. . .,pos,length,ZD,EDIT=(IIIT),. . .)
3. The problem with comma separator is so obvious that it's a shame to discuss it again in the Expert Forum. If comma is used anywhere in CSV data values, then the separator COMMA of CSV itself must be changed to something else; usually either ';', or '|' characters are used for that purpose.
4. CSV format is used mainly to reduce the size of the transferred file. If so, you also need to eliminate meaningless spaces from each whole record by using final re-formatting like this one:
OUTFIL FTOV,BUILD=(1,maxlen,SQZ=(VL))
or
OUTFIL FTOV,BUILD=(1,maxlen,SQZ=(VL,MID=C' ')) - to leave single spaces as separators of words, if needed. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| Rohit - I thought it was obvious that I was talking Excel as I used the term column rather than field. However, I have updated the post so it should be obvious that I am talking spreadsheet and not dfsort. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
No problems Nic. Now it makes it clear.
| Quote: |
| 2. If you really(??) need to suppress leading zeroes in SORT operations, then use parameter BUILD=(. . .,pos,length,ZD,EDIT=(IIIT),. . .) |
OP just expects the opposite. |
|
| Back to top |
|
|
karan_reddy
New User
Joined: 02 Nov 2019
Posts: 2
Location: India
|
|
|
|
Thank you all for the suggestions. Excel problem solved by wrapping up the numeric value with double quotes.
Input:
| Code: |
P# CODE AMT
01 0001 1,100.05
01 0002 200.03-
TOTL 900.02
P# CODE AMT
03 0001 400.10
03 0003 2.100.10
03 0004 50.00-
TOTL 2,450.20
|
Expected Output:
| Code: |
XYZ SUMMARY REPORT ,
DURATION - 3 MONTH(S) ,
RUN-DATE - 10/29/19 ,
DESCRIPTION - ABC PAYMENTS ,
P# , CODE, AMT
JEN1, 0001, 1100.05
JEN1, 0002, -200.03
, TOTL, 900.02
JEN3, 0001, 400.10
JEN3, 0003, 2100.10
JEN3, 0004 -50.00
, TOTL, 2450.20
|
Can you please provide your inputs on below items?
1.In the output write Amount field without comma separator. I tried using EDIT=(STTTTTTTT.TT),SIGNS=(,-) but the output is still written as 1,100.05
2.If P# value is '01' then write output as "JEN1" in P# field
4.Header "P# CODE AMT" should write only once after line 4.
5.Negative sign should display before the value as leading sign. I tried using same EDIT=(STTTTTTTT.TT),SIGNS=(,-) but the sign is written after the value 50.00-
[/code] |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1751
Location: Tirupur, India
|
|
|
|
| Please don't ban me for this :-) |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2283
Location: USA
|
|
|
|
| karan_reddy wrote: |
1.In the output write Amount field without comma separator. I tried using EDIT=(STTTTTTTT.TT),SIGNS=(,-) but the output is still written as 1,100.05
5.Negative sign should display before the value as leading sign. I tried using same EDIT=(STTTTTTTT.TT),SIGNS=(,-) but the sign is written after the value 50.00-[/code] |
All these are 100% bullshit; it cannot be like this.
It has been tested by hundreds of thousands users on billions of samples.
Check your tests carefully, and/or copy your samples here instead of blah-blah-blah-ing such nonsense. |
|
| Back to top |
|
|
dneufarth
Active User
Joined: 27 Apr 2005
Posts: 420
Location: Inside the SPEW (Southwest Ohio, USA)
|
|
|
|
| Try viewing file using Notepad to see the exact values that are comma delimited. You can easily tell what Excel formatting is doing. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
