Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Keep comma inside CSV file when send an CSV email attachment

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> FAQ & Off Topics
View previous topic :: :: View next topic  
Author Message
Jeya Raj

New User


Joined: 14 Jan 2007
Posts: 33
Location: USA

PostPosted: Sat Jun 30, 2012 1:16 am    Post subject: Keep comma inside CSV file when send an CSV email attachment
Reply with quote

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.
Back to top
View user's profile Send private message

superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4648
Location: Raleigh, NC, USA

PostPosted: Sat Jun 30, 2012 1:25 am    Post subject: Reply to: Keep comma inside CSV file when send an CSV email
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jun 30, 2012 8:26 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Sat Jun 30, 2012 12:20 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Sat Jun 30, 2012 12:50 pm    Post subject:
Reply with quote

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,
Code:
CHO,   "$ 200.00",   1,   $200
JOH,   "$ 14,533.00",   57,   $255.3


Hope it helps.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Sat Jun 30, 2012 1:20 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Ed Goodman

Active Member


Joined: 08 Jun 2011
Posts: 556
Location: USA

PostPosted: Mon Jul 02, 2012 8:18 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Jeya Raj

New User


Joined: 14 Jan 2007
Posts: 33
Location: USA

PostPosted: Mon Jul 09, 2012 5:55 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> FAQ & Off Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us