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
 
Convert mainframe data to .csv format using SORT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Convert mainframe data to .csv format using SORT
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

Senior Member


Joined: 21 Sep 2010
Posts: 2296
Location: NY,USA

PostPosted: Sat Nov 02, 2019 2:17 am    Post subject:
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: 2246
Location: Hampshire, UK

PostPosted: Sat Nov 02, 2019 11:15 am    Post subject: Reply to: Convert mainframe data to .csv format using SORT
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

Senior Member


Joined: 21 Sep 2010
Posts: 2296
Location: NY,USA

PostPosted: Sun Nov 03, 2019 9:53 am    Post subject:
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

Active Member


Joined: 29 Apr 2008
Posts: 603
Location: Maryland

PostPosted: Mon Nov 04, 2019 8:00 pm    Post subject: Re: Convert mainframe data to .csv format using SORT
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: 2246
Location: Hampshire, UK

PostPosted: Tue Nov 05, 2019 1:32 am    Post subject: Reply to: Convert mainframe data to .csv format using SORT
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

Senior Member


Joined: 21 Sep 2010
Posts: 2296
Location: NY,USA

PostPosted: Tue Nov 05, 2019 2:51 am    Post subject:
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    Post subject: Reply to: Convert mainframe data to .csv format using SORT
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: 1656
Location: Tirupur

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

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

Active Member


Joined: 29 Apr 2008
Posts: 603
Location: Maryland

PostPosted: Tue Nov 05, 2019 9:20 pm    Post subject: Re: Reply to: Convert mainframe data to .csv format using SORT
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: 299
Location: Cincinnati OH USA

PostPosted: Wed Nov 06, 2019 12:58 am    Post subject:
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    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 Enabling Crosshair cursor in mainframe priya_t23 TSO/ISPF 2 Wed Nov 06, 2019 11:29 am
No new posts Capture DSN names through SORT / IDCAMS reach2abhinavtyagi DFSORT/ICETOOL 8 Tue Nov 05, 2019 7:33 pm
No new posts Parsing single liner XML into readabl... rohanthengal COBOL Programming 1 Fri Oct 18, 2019 8:41 pm
No new posts How to place name of the file where t... danielgp89 SYNCSORT 18 Thu Oct 17, 2019 12:55 am
No new posts One-One matching using SORT dearlux SYNCSORT 14 Fri Oct 11, 2019 12:11 am

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