IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Convert mainframe data to .csv format using SORT


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
karan_reddy

New User


Joined: 02 Nov 2019
Posts: 2
Location: India

PostPosted: Sat Nov 02, 2019 2:09 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sat Nov 02, 2019 2:17 am
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Sat Nov 02, 2019 11:15 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sun Nov 03, 2019 9:53 am
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Mon Nov 04, 2019 8:00 pm
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Tue Nov 05, 2019 1:32 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Nov 05, 2019 2:51 am
Reply with quote

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
View user's profile Send private message
karan_reddy

New User


Joined: 02 Nov 2019
Posts: 2
Location: India

PostPosted: Tue Nov 05, 2019 2:51 am
Reply with quote

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
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Tue Nov 05, 2019 1:08 pm
Reply with quote

Please don't ban me for this :-)
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Tue Nov 05, 2019 9:20 pm
Reply with quote

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
View user's profile Send private message
dneufarth

Active User


Joined: 27 Apr 2005
Posts: 418
Location: Inside the SPEW (Southwest Ohio, USA)

PostPosted: Wed Nov 06, 2019 12:58 am
Reply with quote

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
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 Populate last day of the Month in MMD... SYNCSORT 2
No new posts Mainframe openings in Techmahnidra fo... Mainframe Jobs 0
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts JCL sort card - get first day and las... JCL & VSAM 9
Search our Forums:

Back to Top