.TXT file conversion to .CSV/.XLSX

[dot1q3]

hi -

I have a batch job that produce an output file as HLQ1.DATA.TXT. it looks something like this:

1234567890;00;1234
2345678901;00;1234
3456789012;00;2345
4567890123;00;2345
5678901234;00;3456

And I want to send this file as excel to a customer. The current process is:



1. use reflection FTP to desktop and open the txt file in excel and adjust the columns accordingly (insert sheet rows with names, insert another row for the title).
2. then email them this file as excel.


So I’m trying to automate step #2/3 in a JCL as additional steps. Step #2 I can use a few utility to send. It’s Step #1 I’m trying to do in a job.

[Anuj Dhawan]

Excel files are not mainframe compatible, so you can not do it the way you are asking - instead you can create a comma or tab delimited text file or a HTML file, transfer that to your PC and import into Excel. And it sounds like that you are already doing.

I think your best bet can be:

• Create the data in XML. That format can be used directly by Excel.
• Create File with SLK extension on PC, that can also be used directly by Excel.

[Robert Sample]

Many people don't realize that Excel formats are exceedingly complex -- the last time I looked at the Excel file specifications was several years ago and the document was over 1000 pages at that time. And since Excel formats are binary, you run into the issue of mainframe EBCDIC versus PC ASCII.

Also, you need to realize that "comma-delimited" for Excel can use other delimiters besides commas (such as the semicolon). So the data you posted is, literally, already in delimited format (just not comma-delimited).

(insert sheet rows with names, insert another row for the title).

Why not just add the information you want to your mainframe data set before sending it off? It's not usually that hard to add a title row to a text file on a mainframe (even programmatically).

If you truly want to get Excel data on the mainframe, then convince your management to purchase one of the software products that support Excel on the mainframe. Depending upon the size of your mainframe, the product might even cost less than $100,000 -- but again that depends upon the size of your mainframe. These products generally include a way to get the data from the mainframe to the PC.

[dot1q3]

thank you anuj & robert for your response.

@robert, when i rename the .txt file to .csv, and ftp it to my windows desktop. upon opening in excel directly, all the data is on column 1 instead of 3 separate columns. can i change this to 3 separate columns programmatically via some jcl utilities?

[dot1q3]

sorry for the newbie question but how do i add/insert a string at the beginning of a dataset.

for example input:

abcdef
afdafa
fadfas

output would be:

this is a line
this is another line
abcdef
afdafa
fadfas

i understand i can use a sort but not sure. any examples would be helpful

[Robert Sample]

@robert, when i rename the .txt file to .csv, and ftp it to my windows desktop. upon opening in excel directly, all the data is on column 1 instead of 3 separate columns. can i change this to 3 separate columns programmatically via some jcl utilities?

Leave it as a .txt file. When you open the .txt file, Excel invokes the text conversion utility and one of the things you can specify is the delimiter (and semicolon is one of the options).

[dot1q3]

@robert, when i rename the .txt file to .csv, and ftp it to my windows desktop. upon opening in excel directly, all the data is on column 1 instead of 3 separate columns. can i change this to 3 separate columns programmatically via some jcl utilities?

Leave it as a .txt file. When you open the .txt file, Excel invokes the text conversion utility and one of the things you can specify is the delimiter (and semicolon is one of the options).

yes, i can leave it as is in .TXT and when opening excel will ask me to do the conversion....but the whole point is doing this on the mainframe side (to automate all the clicking on excel app) so the user won't have to do it.

OK, i'm able to have it in .CSV but some reason when i open up the CSV file in excel, the 2nd column that has '00' has been truncated to '0'. for example:

original input

1234567890;00;1234

output

1234567890 0 1234

any ideas how to change this?

also, on the z/os side, can i change the format of the cells to TEXT. it's default is GENERAL.

[Anuj Dhawan]

Microsoft suggests to open .csv in excel like this: https://support.microsoft.com/en-us/off ... 8bafe41857

also, on the z/os side, can i change the format of the cells to TEXT. it's default is GENERAL.

This does not sound like, it's doable as it's a attribute governed by excel.

[Rohit Jain]

You can send the file as "notepad" only or ".csv" file which looks like "notepad". We send them a lot and I have not see that the file is opened in excel by "double-click". We need to 'open it with excel".