?
Solved

convert xls to csv not working on CFMX

Posted on 2003-03-13
16
Medium Priority
?
550 Views
Last Modified: 2013-12-24
Here is a custom tag that works fine in CF5.  I cannot get this to work in CFMX, I am running Windows XPProf and OfficeXP. If you have the same setup can you please test this code. On the CF support forum I have seen similar references that this might be a know bug with MX, something to do with the Com object. Thanks


<cfsetting enablecfoutputonly="Yes">
<!-------------------------------------------------------------------------
// Filename:      excel2csv.cfm
// Purpose:      converts a MS Excel document into a CSV file
// Created:      13/12/01
// Author:           Jordan Thomas
// Notes:           This file requires the Excel.Application objects to be
//                    installed on the server. Just install Excell on the server
//                    to get these.
// Usage:          <cf_excel2csv
//                              excelFileIn=""
//                              CSVFileOut=""
//                              sheetName=""  (optional, defaults to "sheet1")
//                              showConfirmation="1/0"  (optional, defaults to 1)
//                              >
-------------------------------------------------------------------------->

<!--------------------------------------------------------- Constants ---->

<!------------------------------------------------ Initial Parameters ---->
<cfparam name="attributes.sheetName" type="string" default="sheet1">
<cfparam name="attributes.showConfirmation" type="boolean" default="0">

<!--------------------------------------------------- UDF Declaration ---->

<!---------------------------------------------- Access/Security Code ---->

<!--------------------------------------------------------- Main Code ---->
<!--- Try to connect to the Word application object --->
<cftry>
  <!--- If it exists, connect to it --->
  <cfobject
      action="CONNECT"
      class="Excel.Application"
      name="objExcel"
      type="COM">
<cfcatch type="ANY">
<!--- The object doesn't exist, so create it --->
      <cftry>
          <cfobject
              action="CREATE"
              class="Excel.Application"
              name="objExcel"
              type="COM">
        <!--- Word isn't installed, or ColdFusion doesn't have access to it --->
        <cfcatch type="ANY">
            <cfoutput>
                <font size="-1"><b>Cannot create Excel Object</b><br>
            <ol>
                     <li>Make sure Excel is installed on the ColdFusion Server.</li>
                   <li>Make sure that ColdFusion has permissions to
                      use the Excel COM objects.</li>
                </ol>
                </font>
                </cfoutput>
            <cfabort>
        </cfcatch>
      </cftry>
</cfcatch>
</cftry>

<cftry>

<cfscript>

// this code will open up the excel spread sheet and then save it as a CSV file.

  // Excel file path
  ExcelFilePath = "#attributes.excelFileIn#";
 
  // Destination path for new Excel spreadsheet
  // We leave off the extension since we'll declare the file type later on
  CSVFilePath = "#attributes.CSVFileOut#";
 
  // Open Excel in the background
  objExcel.Visible = false;  
 
  // Disable alerts such as: 'Save this document?'
  objExcel.DisplayAlerts = false;              
 
  // Get the 'Documents' collection
  objBooks = objExcel.workbooks;  
 
  // Open the Excel spreadsheet
  objBook = objBooks.Open(ExcelFilePath);  
   
    // Get the sheets collection
    objSheets = objBook.WorkSheets;

    // Get the first sheet
    objSheet = objSheets.Item(Val(1));  
   
    // Get a named sheet
    //objSheet = objSheets.Item("#attributes.sheetName#");      
 
   
    /* Save it as a new document -- the extension will automatically
     be appended based on the file type we choose.
     Some of the file types to convert to:
     1 = .xls
     3 = .txt (tab delimetered)
     6 = .csv
  */
     objSheet.SaveAs(CSVFilePath, Val(6));  
   
  // Close the spreadsheet
  objBook.Close();    
 
  // Quit Excel
  objExcel.Quit();
   
    if (attributes.showConfirmation EQ 1) {
         writeOutput("<font size=""-1"" face=""Verdana,Geneva,Arial,Helvetica,sans-serif""><b>File Created:</b> ""#attributes.CSVFileOut#"" <b>from file:</b> ""#attributes.excelFileIn#""<p></font>");
    }
</cfscript>

<cfcatch>
     <!--- error creating file --->
     <cfoutput>
<font face="Verdana,Geneva,Arial,Helvetica,sans-serif" size="-1">there was an error creating your
    new CSV filesfrom your Excel Spreadsheet
    <P>#cfcatch.message#</P> <P>Caught an exception, type = #cfcatch.type# </P> <P>The contents of the tag stack are:</P> <cfloop index = i from = 1 to = #ArrayLen(cfcatch.tagContext)#> <cfset sCurrent = #cfcatch.tagContext[i]#> <BR>#i# #sCurrent["ID"]# (#sCurrent["LINE"]#,#sCurrent["COLUMN"]#) #sCurrent["TEMPLATE"]# </cfloop>

    </cfoutput>
   

</cfcatch>
</cftry>

<cfsetting enablecfoutputonly="no">
0
Comment
Question by:pgobos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 

Author Comment

by:pgobos
ID: 8129313
On XP Prof I cannot even get the excel processes to start

I get:
 Cannot create Excel Object

Make sure Excel is installed on the ColdFusion Server.
Make sure that ColdFusion has permissions to use the Excel COM objects

When I got this message on a 2000 machine I changed the log on in MX application server properties to the logged on user and at least the excel process would start even thogh the file conversion still fails.

I changed the log on info on the XP machine but no luck.

So on the 2000 box I can get half way there, but on the XP box I cannot even get started.

Again the tag works fine on CF5 irregardless of the operating system
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8134573
Hi,

I have Win XP prof & Off 2000 -
this is what i got

there was an error creating your new CSV filesfrom your Excel Spreadsheet
Element EXCELFILEIN is undefined in ATTRIBUTES.
Caught an exception, type = Expression

The contents of the tag stack are:
1 ?? (69,0) D:\Projects\CARO\Content\test.cfm

i am still trying !
Anand
0
 

Author Comment

by:pgobos
ID: 8166024
OK maybe I should rephrase my question.  Code needed to convert XLS to a CSV in ColdfusionMX.

This custom tag I posted clearly does not work in MX.  Is there a bug in MX concerning the excel object?

Thanks for any help
0
Video: Liquid Web Managed WordPress Comparisons

If you run run a WordPress, you understand the potential headaches you may face when updating your plugins and themes. Do you choose to update on the fly and risk taking down your site; or do you set up a staging, keep it in sync with your live site and use that to test updates?

 
LVL 17

Expert Comment

by:anandkp
ID: 8166086
yes - even i have ColdFuisionMX - thats y i mentioned that the error i got is different from what u r getting & it seems in my case - if i make some chnages to the file that i have pasted above - things wld work !

0
 

Author Comment

by:pgobos
ID: 8178008
Anand Did you get it to work?  Please post the changes you made. I understood that you have MX I was just wondering if you new about a bug. I have read in several places about people haveing problems with the com object when used with word and excel  But I cannot seem to find a definitive answer. I need a method in MX to convert a XLS to a csv.

Thanks for any help
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8178921
no what i meant was that - i ccopy pasted ur code as itis on my machine with XP/CFMX/Access2000 & i got a different error from what u got.

so i wanted u to look at the configuration setting - if incase u had made some chanegs to it.

i have used com object on CFMX & i have it working with excel as well.

i'll look into this & get back to u

K'Rgds
Anand
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8194080
ok this is what i found

the MX has some bug with the COM object tag & i realised all my COM object tags werent functioning properly in CFMX

i found this - so thought wld update u as well

ColdFusion MX Updater Release 3
Release Notes Part 2 - ColdFusion MX Issues Fixed in this Release
March 19, 2003

http://www.macromedia.com/support/coldfusion/releasenotes/mx/releasenotes_mx_updater.html

http://www.macromedia.com/support/coldfusion/releasenotes/mx/releasenotes_mx_updater01.html

http://www.macromedia.com/support/coldfusion/releasenotes/mx/releasenotes_mx_updater02.html

http://www.macromedia.com/support/coldfusion/releasenotes/mx/releasenotes_mx_updater03.html

COM-Related Issues
The following issues with COM have been fixed:
 
Issue ID
ColdFusion MX did not properly handle COM objects that have an invalid ProgID value and generated an "AutomationException: 0x80020005 - Type mismatch" error. One symptom of this problem was that the Microsoft Word COM object saveAs() method did not work with 2 arguments. This error was introduced in Updater 1. 49279  
All COM objects were treated as having Java stubs or not having Java stubs based on the last object created. For example, if an object without a stubbed object was invoked and then a stubbed object was invoked, a subsequent use of the non-stubbed object would throw a null pointer exception. 48903
Passing input arguments to a COM object would fail if the typeifno flags for the argument in the object specified input=false, output=false, and returnValue=false. This problem affected the use of some third-party OCX objects. ColdFusion now assumes that if output=false and returnValue=false, the argument is an input argument. 37009
COM objects could be released prematurely after you provided the object as an argument to a method of another object. 48900
 
Hope after updating the patch thigns work fine for u !

K'Rgds
Anand
 
0
 

Author Comment

by:pgobos
ID: 8197195
Thanks for the links Anand.  Have you updated your setup and does the tag now work? Unfortunately I am away form my work machine for about a week and will not be able to test it, until next Monday

thanks  Peter

PS Thanks for the help,if the updates work the points are yours
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8200283
no i havent got the updates done yet - ive notified my sysadmin & he will be the one to carry them out - i just thought I wld inform u - as soon as i came to know abt it ... so

if thigns work out for me - they shld work out for u as well ...

wld keep u posted on this

Cheers
Anand
0
 
LVL 17

Accepted Solution

by:
anandkp earned 1050 total points
ID: 8202268
GUESS what

GOOD NEWS - the patch did the trick

i just ran my COM object on CFMX server & to my surprise - it finally worked.

i'd say - u take a look at it & see for urself to believe it

K'Rgds
Anand
0
 

Author Comment

by:pgobos
ID: 8204507
That is great thanks for the help.  If I have any problems with it when I get back to the office I will let you know.
I should be able to test it early next week.

Thanks  Peter
0
 

Author Comment

by:pgobos
ID: 8270043
On my XP machine this is the error I am getting in MX. In Mx I can not get the excel process to start and again on this xp machine the code works fine with CF5.

On my XP CFmx I think it is a permission thing or something
any thought would be helpful and yes I did apply the updat


Cannot create Excel Object

Make sure Excel is installed on the ColdFusion Server.
Make sure that ColdFusion has permissions to use the Excel COM objects.
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8274070
Do u have MS excel installed on the server ???

if its not there - what will the com object instantiate ???

K'Rgds
Anand
0
 

Author Comment

by:pgobos
ID: 8275300
I am running Win XP Office XP and CFMX. I can get the word com object to initiate but not the excel object.  Yes excel is loaded on the server.

Are you running Office XP because now I think there is a bug with Office XP and the excel com object. Like I said above I can initiate the word object. Weird don't you think.

Oh By the way I can initiate the Excel object on a machine running WIN 2000 Office 2000 CFMX.
0
 

Author Comment

by:pgobos
ID: 8275580
Anand

You mentioned you could get the excel com object to initiate, but could you also run the excel to csv confersion successfully.  On the win 2000 office 2000 cfmx
machine the excel object initiates but the xls to csv conversion fails.

Thanks for any help
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8289585
in that case - there cld be some error/conditions with the object itself !

chk up on them & then c what parameters r ebing send & how it works

since i cld only get my COM object to work perfectly once u loaded the CFMX updates & everything else works fine for me !

K'Rgds
Anand
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
Suggested Courses
Course of the Month14 days, 4 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question