|
View previous topic :: View next topic
|
| Author |
Message |
chillmo
New User
Joined: 31 Aug 2017
Posts: 30
Location: USA
|
|
|
|
I'm converting a flat file into a CSV; however, in position 251, the value could contain leading zeroes and the business wants to retain them.
| Code: |
00001809
0423
243161
00622
|
Can this be accomplished via SORT? FYI - The file on the mainframe has the leading zeroes but when I upload to LAN and open in Excel, the zeroes vanish.
I tried double quotes however, it kept the leading zeroes but added a quote at the end....no bueno!
Any assistance would be greatly appreciated! |
|
| Back to top |
|
 |
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1255
Location: Bamberg, Germany
|
|
|
|
| What's the format of the CSV? RECFM etc.? |
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 30
Location: USA
|
|
|
|
RECFM=FB, LRECL-134.
Can you please elaborate on type of CSV? Normally, CSV that I transfer to PC. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1255
Location: Bamberg, Germany
|
|
|
|
You could try to write the value as =CONCAT("0001") to the CSV for example. It seems it displays at least as text after importing to Excel.
Otherwise you have to follow some of the guidelines found be searching the web. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
The CSV "file" contains in fact only character data.
Just consider each line as the sequence of character strings, and you don't need to worry about "leading zeroes", or whatever else.
| Code: |
| 1234,56789,00000000000123,00000456,00098765 |
One way is, use SORT parameter EDIT=(TTTTTTTTTT.TT) to create elements with leading zeroes. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
| chillmo wrote: |
Can this be accomplished via SORT? FYI - The file on the mainframe has the leading zeroes but when I upload to LAN and open in Excel, the zeroes vanish. |
It has nothing to do with SORT.
Excel has its own formatting settings for each cell, or each column. By default there is a format with zero suppression. Change the column format in Excel, and your business should be happy with that.
If the values in Excel are used for display only (not for further calculation), then send such pseudo-numeric values as string starting with apostrophe:
| Code: |
| . . . . . .,"'0000123", . . . . |
|
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 30
Location: USA
|
|
|
|
Sergeyken,
Yes, I'm aware it's a function of Excel, but I wanted to see if coding something in the SORT would resolve this. And, yes, I changed the column to TEXT and the leading zero was gone as well. Thx for the response.
Joerg.Findeisen,
Thanks for suggestion as this worked......
| Code: |
| C'=CONCAT("',251,10,C'")' |
The code above allowed me to retain the leading zeroes which the business loved. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1255
Location: Bamberg, Germany
|
|
|
|
| Thanks for your feedback, welcome. |
|
| Back to top |
|
|
|
|
