?
Solved

convert xls to csv

Posted on 2003-03-07
8
Medium Priority
?
1,516 Views
Last Modified: 2013-12-24
Here is a custom tag I am using to convert an XLS file to a CSV.  Could some one please adjust the code so it will convert the first sheet in a workbook regardless of the sheet's name.  It presently works ony if the first sheet is named sheet1.  I tried commenting out

      objSheet = objSheets.Item("#attributes.sheetName#");  

this line but it still would not convert a  first sheet let us say it was named xxx.

 I just need it to look at the XLS file and convert the first sheet nomatter what the names is.    Try testing it renaming the the sheet name from sheet1 to "whatever"

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
    */
        objSheets.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
  • 5
  • 3
8 Comments
 
LVL 10

Expert Comment

by:substand
ID: 8090729
here you go:
__---------------------------------------------------------

<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
 
LVL 10

Expert Comment

by:substand
ID: 8090740
all i did was comment out the line you said you commented out, but you had another problem:

objSheets did not have a save method, objSheet did.

but now it works for me.

of course, i didn't use it as a tag, i just put the code in a regular cfm file

0
 

Author Comment

by:pgobos
ID: 8091014
Please try it has a tag. I rename sheet1 to "whatever" and it fails. I rename it back to sheet1 and it works. Oh by the way when it fails I have go in and kill the excel process because it locks the file.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
LVL 10

Expert Comment

by:substand
ID: 8091362
yeah, i know about that =)

ok, i tried it as a tag and it still works

i named my sheet "craa"

did you try my file?

0
 

Author Comment

by:pgobos
ID: 8091508
yes I tried you code and it works. But get this I must be losing my mind. If I rename the tag to somthing other then
excel2csv.cfm it works. Si I renamed it to excelcsv.cfm and now it works. What gives?
0
 
LVL 10

Expert Comment

by:substand
ID: 8091675
i don't know about that.  i didn't have to rename it or anything.  =)  maybe you typed it wrong when you saved it to the customtags directory, or you typed it wrong when you used the tag?

<cf_excel2csv>

0
 
LVL 10

Accepted Solution

by:
substand earned 750 total points
ID: 8091684
cool tag by the way.  i never really thought of doing that.  i can see how it could help in a lot of situations that i've run up against...



0
 

Author Comment

by:pgobos
ID: 8091715
I do not know it seems all my problems related to the file name. I probably am losing my mind, time to go drink some beer. Anyways it is working agin Thanks for your input and interest.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
What You Need to Know when Searching for a Webhost Provider
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

609 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