Keep comma inside CSV file when send an CSV email attachment

Jeya Raj · New User Joined: 14 Jan 2007 Posts: 33 Location: USA

Hello,

I need to send a CSV file as an email attachment from JCL. The CSV file which I sent, has comma in the amount value (e.g $12,000). When I sent the file the amount is moved into 2 cells in the excel sheet. Is there any way that I can send the file with comma in it for amount values and should open in excel sheet? I need to send this file to clients (I don’t want to send it as a txt file and ask the clients to import it into an excel sheet). I tried all I know and it didn’t workout.

Please advice.

I have attached my screen shots and the methods that I tried ...

Thanks for looking at this issue.

superk · Posted: Sat Jun 30, 2012 1:25 am

This is a mainframe forum, so I fail to see the relevance of this topic to this forum. I'd suggest that you post this in a forum relevant to Microsoft products and/or Excel.

dick scherrer · Posted: Sat Jun 30, 2012 8:26 am

Hello,

Most of the clients i've supported for more than 15 years need to create MS-friendly data on the mainframe. And that population is growing.

Using some other delimeter (i use the tilde (~) or the backslash (\) ) if i (or some doubting Thomases) want to be able to "see" the values as these rarely are in "user data".

For most things, having displayable delimiters is not an issue so i do most things using the tab-character x'05' as the delimiter.

As the comma is very common in data, i never use the comma. Too many people have gotten ugly surprises.

FWIW, everything i send to UNIX or Win-based goes a "plain text" so there is no issue with bit-settings, binary, or packed-decimal etc.

Nic Clouston · Posted: Sat Jun 30, 2012 12:20 pm

What is in your screenshot - I do not download such things for security purposes and attachments are frowned upon in most forums that I am a member of.

Mind you, after 5 1/2 years in the forum you should be aware of that as it comes up regularly. As does the point a bout commas in CSV file so your should have picked it up when you searched before posting.

vasanthz · Posted: Sat Jun 30, 2012 12:50 pm

Hello,
On your mainframe source file, enclose the TOTAL AMOUNT field within double quotes. This will tell Excel that the amount field is one single field eventhough it has a comma.

something like this,

vasanthz · Posted: Sat Jun 30, 2012 1:20 pm

When creating CSV file on mainframes, the easiest way is,

open Excel,
type a sample model report on Excel with the structure you require.
save the spread sheet as .csv
open the .csv file with notepad,
on the notepad look how the data needs to be represented in mainframe.
now you will have some idea of what needs to be created in mainframe to produce the final report.

Ed Goodman · Active Member Joined: 08 Jun 2011 Posts: 556 Location: USA

There are SO many options here. The quote thing will work if the target Excel field is a text field. You can also remove the comma before sending it, and Excel will put it back in if the field is numeric.

A lot depends on how the final person brings in the file. Do they just double-click? Do they do an Import? Do they assume header row...blah blah blah.

One way that I found works is to use the Excel/XML format:

Jeya Raj · New User Joined: 14 Jan 2007 Posts: 33 Location: USA

Thank you so much for your input. Instead of going thru this way, created as XML file and uploaded as a web report thru Dreamweaver(instead of XL format). Thank you for your time.