• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

converting excel to text file

Guest
Nov 09, 2010 Nov 09, 2010

Copy link to clipboard

Copied

HI All -

I am uploading an excel file using coldfusion. Once the file has been uploaded, I  want to convert it to a text file. What I am trying to do now is once the file is  uploaded, I am reading the excel file and then writing the output to text file.  but the write method creates all junk in the text file.  What is the best way to do this?

Thanks

TOPICS
Advanced techniques

Views

3.1K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Nov 09, 2010 Nov 09, 2010

Copy link to clipboard

Copied

Are you in CF9? If so you can read in the data. Then you could write

it out to a text file.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Nov 09, 2010 Nov 09, 2010

Copy link to clipboard

Copied

No. I am on CF8


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 09, 2010 Nov 09, 2010

Copy link to clipboard

Copied

If you are on CF8 you may want to look into Ben Nadel's POI Utility:

http://www.bennadel.com/projects/poi-utility.htm

--Nathan

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Nov 09, 2010 Nov 09, 2010

Copy link to clipboard

Copied

I am able to parse the excel files.i have another question though. I am trying to wrtie the results of a query to a text file as below:

<cfsavecontent variable="output">

   <cfoutput query="userdata">

     <table>

              <tr>

                      <td>FirstName</td

             </tr>

            <tr>

                    <td>LastName</td>

           </tr>

           <tr>

                      <td>ABCD</td

             </tr>

            <tr>

                    <td>ADEF</td>

           </tr>

      </table>

  </cfoutput>

</cfsavecontent>

<cffile action="write" output="#output#" file="file.txt">

The problem is the html is getting written to the text file. How can I make sure, i get only the header and rows values

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Nov 09, 2010 Nov 09, 2010

Copy link to clipboard

Copied

funandlearning333 wrote:

The problem is the html is getting written to the text file. How can I make sure, i get only the header and rows values

Then don't put HTML into the output content?  Whatever you put into the "output" string variable via <cfsavecontent variable="output"> is going to be written to the file with the output="#output#" parameter.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Nov 10, 2010 Nov 10, 2010

Copy link to clipboard

Copied

I got everything working as long as the extension is .xls

When I am trying to use .xlsx extension, I get the following error

Object Instantiation Exception. An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Error: ''.
The error occurred on line 875.

Is there a work around for this problem?

Thanks

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 10, 2010 Nov 10, 2010

Copy link to clipboard

Copied

The version of the POI libraries bundled with ColdFusion 8 do not support

the OOXML (.xlsx) format. To read .xlsx files you need to use the latest

version of POI, which you can find here: http://poi.apache.org/

For info on using POI with JavaLoader see:

http://cfsearching.blogspot.com/2008/08/how-to-install-poi-on-coldfusion-8.html

(Note: CF 9 supports .xlsx natively via cfspreadsheet.)

--Nathan

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Nov 12, 2010 Nov 12, 2010

Copy link to clipboard

Copied

I placed all the new jar files and changed the code in poiutility.cfc as below:

// Create a file input stream to the given Excel file.
            LOCAL.FileInputStream = CreateObject( "java", "java.io.FileInputStream" ).Init( ARGUMENTS.FilePath );
           
          
            LOCAL.WorkBookFactory = CreateObject("java", "org.apache.poi.ss.usermodel.WorkbookFactory").Init();
            LOCAL.WorkBook = LOCAL.WorkBookFactory.create(LOCAL.FileInputStream);

This works fine for files saved in ealrier version of excel, and if I save the current 2010 version using .xlsx file. But when I save the excel using 'Save it as Microsoft 2000-2003 excel, and try reading it, I get an error as below:

Your InputStream was neither an OLE2 stream, nor an OOXML stream null

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 12, 2010 Nov 12, 2010

Copy link to clipboard

Copied

How are you saving it as Microsoft 2000-2003? The options I get in Excel

2010 when I choose "File -> Save As" list "Excel 97-2003 Workbook (*.xls)"

but no 2000-2003 options.

--Nathan

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Nov 12, 2010 Nov 12, 2010

Copy link to clipboard

Copied

Yes I am sorry, I tested it more and this is whats happening. When I am downloading excel, I get 'Open or Save dialog box', when I open it and then save it as .xls or .xlsx, I am able to read those files with no problem (using poiutility). but when instead of opening, if I save it which gets saved a Excel 97-2003, then try to read them, I get that Invalid stream error.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 12, 2010 Nov 12, 2010

Copy link to clipboard

Copied

Can you open the files with Excel after downloading them? Are you sure they

are Excel files and not .csv or some other format?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Nov 12, 2010 Nov 12, 2010

Copy link to clipboard

Copied

LATEST

yes I am able to open the excel files after downloading.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation