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.
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
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.
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.
Joined: 10 May 2007 Posts: 2455 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.
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.
Joined: 10 May 2007 Posts: 2455 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.
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-
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.