?
Solved

convert xls to csv

Posted on 2003-03-07
8
Medium Priority
?
1,513 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
[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
  • 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

741 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