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

Keep leading zero(s) after convert flat file to CSV.


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
chillmo

New User


Joined: 31 Aug 2017
Posts: 30
Location: USA

PostPosted: Wed Aug 16, 2023 10:44 am
Reply with quote

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

Senior Member


Joined: 15 Aug 2015
Posts: 1280
Location: Bamberg, Germany

PostPosted: Wed Aug 16, 2023 10:56 am
Reply with quote

What's the format of the CSV? RECFM etc.?
Back to top
View user's profile Send private message
chillmo

New User


Joined: 31 Aug 2017
Posts: 30
Location: USA

PostPosted: Wed Aug 16, 2023 11:22 am
Reply with quote

RECFM=FB, LRECL-134.

Can you please elaborate on type of CSV? Normally, CSV that I transfer to PC.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1280
Location: Bamberg, Germany

PostPosted: Wed Aug 16, 2023 12:51 pm
Reply with quote

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

Senior Member


Joined: 29 Apr 2008
Posts: 2050
Location: USA

PostPosted: Wed Aug 16, 2023 5:06 pm
Reply with quote

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

Senior Member


Joined: 29 Apr 2008
Posts: 2050
Location: USA

PostPosted: Wed Aug 16, 2023 5:14 pm
Reply with quote

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

New User


Joined: 31 Aug 2017
Posts: 30
Location: USA

PostPosted: Wed Aug 16, 2023 6:54 pm
Reply with quote

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

Senior Member


Joined: 15 Aug 2015
Posts: 1280
Location: Bamberg, Germany

PostPosted: Wed Aug 16, 2023 7:06 pm
Reply with quote

Thanks for your feedback, welcome.
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 -> SYNCSORT

 


Similar Topics
Topic Forum Replies
No new posts Format Binary file to EBCDIC JCL & VSAM 4
No new posts Binary File format getting change whi... All Other Mainframe Topics 7
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts FTP VB File from Mainframe retaining ... JCL & VSAM 8
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
Search our Forums:

Back to Top