creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

[64waves]

Good morning all, I have an existing report that emails a .csv file to the user who asks for the report. Now they want this report sent as a .xls. I change the extension in my pgm, but when they go to open they get a format warning message from Excel 2007.

I colleague suggested sending the .csv to an IFS folder, then saving the .csv as an .xls, then emailing it to the user.

Does this sound like it would work or is there another way to go.

Any help would be appreciated.

[jtaylor___]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

I figure Excel doesn't like just changing the extension because the file is actually still a CSV.

Here are a couple of ways to generate XLS files directly:

http://www.scottklement.com/poi/

http://www.easy400.net/xlscgi/html/page1.htm

[64waves]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

Well, I doubt I have either of these things loaded and also doubt I could get them added to my system at this time. Can a .txt or .csv file be sent to a folder on the IFS, copied to a .xls file on the IFS, then sent as an attachment in my original pgm? I am looking at CPYTOIMPF, CPYFRMSTMF etc.

[GLS400]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

Hi 64Waves:

Client access's file transfer can create an excel spread sheet using the biff8 file type
Look it up on the internet....it's been quite some time since i last used it.

I think there is an issue with column headings but like i said it's been a while

GLS

[DeadManWalks]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

You can not copy any text file to a .xls and have it be an xls file. Just giving a file the .xls extension does not change it to that file. The links provided are for Free software for making .xls files. What can be better than something that does what you need and its free.

[64waves]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

You can not copy any text file to a .xls and have it be an xls file. Just giving a file the .xls extension does not change it to that file. The links provided are for Free software for making .xls files. What can be better than something that does what you need and its free.

Thanks for all the input everyone, I never used the CGIDEV2 tools, we were so behind on PTF's I just assumed we didn't have it . So I am on the verge of getting this to compile. I have added the .XML template and other info but have one more issue. I create my data on the fly using sql and it resides in qtemp. Now I have to read it and write my excel lines but cannot figure out how to define it so the pgm sees it.

Getting message 'RNF5183 The file does not have Input specifications and the
Result-Field is not specified. '

Here are some snippets:

                                    
H Option(*srcstmt: *nodebugio)      
H Debug                             
H DatFmt(*iso) TimFmt(*hms)         
H Indent('->') Dftactgrp(*NO)       
H BndDir('CGIDEV2') ACTGRP(*NEW)    
                                    
FDetails   IF   F  200        disk  

. . . .


  Exec SQL                                                     
   create table qtemp/Details (                                
    HAWB           CHAR ( 9) ccsid 37 NOT NULL WITH DEFAULT,   
    PICKUP         CHAR (14) ccsid 37 NOT NULL WITH DEFAULT,   
    ORG            CHAR ( 3) ccsid 37 NOT NULL WITH DEFAULT,   
    SHIPPER        CHAR (30) ccsid 37 NOT NULL WITH DEFAULT,   
    BILLPTY        CHAR (30) ccsid 37 NOT NULL WITH DEFAULT,   
    CITY           CHAR (25) ccsid 37 NOT NULL WITH DEFAULT,   
    DEST           CHAR ( 3) ccsid 37 NOT NULL WITH DEFAULT,   
    PIECES         dec (6,0) NOT NULL WITH DEFAULT,            
    CHGWEIGHT      dec (8,0) NOT NULL WITH DEFAULT,            
    PRODUCT        CHAR ( 4) ccsid 37 NOT NULL WITH DEFAULT,   
    AGENTC         CHAR ( 7) ccsid 37 NOT NULL WITH DEFAULT,   
    AGNTNAME       CHAR (30) ccsid 37 NOT NULL WITH DEFAULT,   
    DELAREA        CHAR ( 3) ccsid 37 NOT NULL WITH DEFAULT,   
    DELMILES       DEC (6,0) NOT NULL WITH DEFAULT,            
    PKUPEXPENSE    dec (9,2) NOT NULL WITH DEFAULT );          
                                                               
Exec Sql                                                        
  insert into qtemp/Details                                     
   select HAWB  ,                                               
    substr(char(pudate),5,2) || '/' || substr(char(pudate),7,2) 
      || '/' || substr(char(pudate),3,2) as pickup,             
   OGAP00,SNAM00,NAME9 ,CCTY00,DSAP00,TPCS00,CHWT00,PROD00,     
   case when agent# > 0 then                                    
   RIGHT('000' || RTRIM(SUBSTR(agent#,1,7)), 7)                 
    else ' ' end,                                               
          agnnam, darea, destmil, cost28                        
     FROM qtemp/whtglov                                         
       order by PUDATE;                                         
                                                                
  //============================================================== 
  //  Write data to .xls file                                      
  //-------------------------------------------------------------- 
                                                                   
p XMLWrite        b                                                
d XMLWrite        pi                                               
                                                                   
 /Free                                                             
                                                                   
                  Setll *Start Details;                            
                   Read Details;                                   
                    DoW Not %EOF(Details);                         
                                                                   
                    updHTMLvar('HAWB':Hawb);                       
                    updHTMLvar('PICKUP':pickup);                   
                    updHTMLvar('ORG':org);                         
                    updHTMLvar('SHIPPER':shipper);                 
                    updHTMLvar('BILLABLE':billpty);                
                    updHTMLvar('CONS CITY':city);                  
                    updHTMLvar('HAWB DEST':dest);                  
                  updHTMLvar('PIECES':pieces);                        
                  updHTMLvar('CHG WGT':chgweight);                    
                  updHTMLvar('PRODUCT':product);                      
                  updHTMLvar('DEL AGENT':agentc);                     
                  updHTMLvar('AGENT NAME':agntname);                  
                  updHTMLvar('DEL AREA':delarea);                     
                  updHTMLvar('DEL MIL':delmiles);                     
                  updHTMLvar('DEL EXP':pkupexpense);                  
                  wrtsection('line');                                 
                  numRows += 1;                                       
                  Read Details;                                       
               EndDo;                                                 
                                                                      
                                                                      
// Write end section                                                  
                       wrtsection(end);                               
                                                                      
// Write to IFS....                                                   
                                                                      
   rc = WrtHtmlToStmf( '/tmp/HD_DEL_EXP_' + %editc(fromdate:'X') +    
                               '_' + %editc(todate:'X') +'_'+ DSAP + '.xls');  

 /end-free           
p XMLWrite        e

------------------------------------------------------------------------

Any help would be appreciated.

[Scott M]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

You have defined the Details file as program-described so you need to define the format in Input specs. Better yet, define it as externally defined and have a copy available at compile time so your program will compile. Also define it as USROPN, then open it in the XMLWrite procedure before using it.

[64waves]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

Good afternoon, I have gotten past that last issue and now have my .xml file that represents my report on the IFS.

I had a .xml template based on my Original Excel sheet, populated it with the proper data, checked that all my tags are correct in the XML editor, now I am trying to send.

What commands are necessary to send this as an attachment to an email to arrive as .xls? I user MMAIL or SNDEMAIL commands.

Thank you.

[DeadManWalks]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

MMAIL/EMLSTMF
or
MMAIL/EMLSTMF2

[64waves]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

Ok, it is giving me an empty spreadsheet.

Here is some detail of my file: (From wrklnk folder)
The data cell tags are not coming through on my post.

Browse : /tmp/HD_DEL_EXP_20140101_20140501_FLL.xml

'AGENT NAME ' ----- Heading area
'DEL AREA '
'DEL MIL '
'DEL EXP '
' '
''
'057820224 '
'01/01/14 '
'ORD ' ---- First line
'WALMART.COM 7074 '
'WALMART.COM '
'BOCA RATON '
'FLL'
'1 '
'136 '
'B '
'0011810'
'FREIGHT SERVIC '
'B '

This is the command I tried:

MMAIL/EMLSTMF SUBJECT('HD test') FROMNAME('SHARON') FROMADDR('S
HARON@XXXXX.COM') TO('SHARON@XXXXXXX.COM'
/'Sharon'/*TO) STMF('/tmp/HD_DEL_EXP_20140101_20140501_FLL.xml')


When I try to open in Excel, it is empty.

This is getting frustrating.

[tomliotta]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

Is that part of the file you are showing us? It doesn't look like XML.

Or is that what you meant by this comment:

The data cell tags are not coming through on my post.

[Henrik Rutzou]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

You need to imbed the xml in html tags. Go to advanced when you create the answer
and insert the xml in HTML /HTML section

<myElement>abcdefg</myElement>

[64waves]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

Is that part of the file you are showing us? It doesn't look like XML.

Or is that what you meant by this comment:

Correct Tom, I have "'<'Cell>'<'Data ss:Type="String">" before and "<'/Data>'<'/Cell>" after them.
( All the single and double quotes are added so it will show in the post )

I am following this example I found on the web:

http://www.itjungle.com/fhg/fhg081308-story01.html

But it doesn't say how to email after it is created. I have it created on my IFS with all the proper tags.

The template is .xml but the finished product is .xls

[Henrik Rutzou]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

EMLSTMF sends the content of the IFS file as a mail body, you should use SENDMIME instead that sends
the file as an attachment

[64waves]

Re: creating .CSV in a pgm, need to convert to .XLS and avoid Excel 2007 warning

Good morning, I have it emailing but cannot open it.

I noticed in my created .xls that it has the name of the template as the worksheet value. I need this to be a changeable name based on the dates entered by the user of the report. Example "HD_DEL_EXP_20140305_20140514_TPA.xls" Is the 'Worksheet' line of the .xls file changeable?

'<'Worksheet ss:Name='HD_DEL_EXP_20140305_20140514_TPA.xls '>'