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 ...
Joined: 26 Apr 2004 Posts: 4652 Location: Raleigh, NC, USA
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.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
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.
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
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.
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
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.
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
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.
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:
Code:
SORT FIELDS=COPY
OUTFIL FTOV,OUTREC=(C' <Row>',/,
C' <Cell><Data ss:Type="String">',
002,002,C'</Data></Cell>',/, county
C' <Cell><Data ss:Type="String">',
007,006,C'</Data></Cell>',/, workerid
C' <Cell><Data ss:Type="String">',
016,015,C'</Data></Cell>',/, name last
C' <Cell><Data ss:Type="String">',
034,015,C'</Data></Cell>',/, name first
C' <Cell><Data ss:Type="String">',
052,001,C'</Data></Cell>',/, name mi
C' <Cell><Data ss:Type="String">',
056,012,C'</Data></Cell>',/, indv
C' <Cell><Data ss:Type="String">',
071,009,C'</Data></Cell>',/, ssn
C' <Cell><Data ss:Type="String">',
083,003,C'</Data></Cell>',/, sanc type
C' <Cell><Data ss:Type="String">',
089,010,C'</Data></Cell>',/, sanc begin dte
C' <Cell><Data ss:Type="String">',
102,010,C'</Data></Cell>',/, sanc end dte
C' <Cell><Data ss:Type="String">',
115,010,C'</Data></Cell>',/, sanc fail dte
C' <Cell><Data ss:Type="String">',
128,010,C'</Data></Cell>',/, sanc compl dte
C' <Cell><Data ss:Type="String">',
141,003,C'</Data></Cell>',/, sanc reason cde
C' <Cell><Data ss:Type="String">',
147,001,C'</Data></Cell>',/, match level
C' </Row>'),
REMOVECC,
HEADER1=(C'<?xml version="1.0"?>'/,
C'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"',/,
C'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >',/,
C' <Worksheet ss:Name="Non-Compliance Information">',/,
C' <Table>',/,
C' <Row>',/,
C' <Cell><Data ss:Type="String">County</Data></Cell>',/,
C' <Cell><Data ss:Type="String">Worker</Data></Cell>',/,
C' <Cell><Data ss:Type="String">Last Name</Data></Cell>',/,
C' <Cell><Data ss:Type="String">First Name</Data></Cell>',/,
C' <Cell><Data ss:Type="String">MI</Data></Cell>',/,
C' <Cell><Data ss:Type="String">Individual</Data></Cell>',/,
C' <Cell><Data ss:Type="String">SSN</Data></Cell>',/,
C' <Cell><Data ss:Type="String">Sanction Type</Data></Cell>',/,
C' <Cell><Data ss:Type="String">Begin Date</Data></Cell>',/,
C' <Cell><Data ss:Type="String">End Date</Data></Cell>',/,
C' <Cell><Data ss:Type="String">Failure Date</Data></Cell>',/,
C' <Cell><Data ss:Type="String">Compliance Date</Data></Cell>',/,
C' <Cell><Data ss:Type="String">Reason Code</Data></Cell>',/,
C' <Cell><Data ss:Type="String">Match Level</Data></Cell>',/,
C' </Row>'),
TRAILER1=(C' </Table>',/,
C' </Worksheet>',/,
C'</Workbook>')
That gets around most of the issues, but not all of them. And I never actually dealt with a numeric field.
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.