cfspreadsheet 400000 lines

[plarts]

How to read an Excel file ".xlsx" of 420 000 lines ?

I used cfspreadsheet but,

over 4000 lines  it fails, saying nothing.

I tried the CFC (custom tag) : POIutility.cfc from Ben Nadel.

but it seems it does not work with xlsx,

does work fine with ".xls" , I can read 65 000 lines

because ".xls" it limited to 65 000 lines.

so I have no solution to read 400 000 :lines.

Thanks for help. ideas ?

Pierre.

[Kaif_Akbar]

Hi Plarts,

In such kind of scenario, you can try to populate the large data by using array of cfspreadsheet so that it can work for your requirement.

But as populating/processing excel sheet for such kind of scenario may also need more memory so you may increase max heap allocation to CF server if required.

~

Kaif Akbar

[plarts]

Thanks,

what do you call : using array of cfspreadsheet

Also POIutility.cfc  does work fine with current CF, but only with xls not with xlsx

Pierre.

[plarts]

Waiting for a soltution to get an automated process,

I did this manually, converting from Excel to Access,

then accessing Access DB.

But I will need soon a way to automate this, means to be able to read

the 400 000 lines of Excel sheet.

Any advise, what tool to try ? (CF tags or custom tags , or any thing else in CF)

Thnaks, Pierre.

[Carl Von Stetten]

@plarts,

You're operating in "edge case" territory, as processing 400,000 line Excel files is not all that common in ColdFusion (as you say, the older .xls format was limited to 65,000 rows and hitting that limit was not all that common).  Where are these Excel spreadsheets coming from?  Is there a more appropriate format for storing that data besides Excel?

-Carl V.

[Reed_Powell-ttnmOb]

Carl is correct.  What are you trying to do with the data?  I presume it is going into a database?  If you are talking about either MS SQL Server or Oracle, then you already have great ETL tools available.  Even if you need to take a couple of hours to learn the basics of SSIS for MS SQL Server in order to process a spreadsheet and write the data to a table, it will serve you well.

Having said that, the CFSPREADSHEET tag is close to the top of my list of biggest CF disappointments in recent years.  I can open a HUGE spreadsheet on my laptop, manipulate it, sort it, save it, etc.  But CF running on my server just falls apart trying to do the same thing.

-reed

[plarts]

This big spreadsheet is the input data I have (4 big files), coming from the client,

and the client get it from the government. These data will go to a database, yes.

I can have it in text file too.

I will try again with the text file.

I can't remember what happened.

I let you know when I have other results.

Thanks.

[Carl Von Stetten]

@plarts,

If you can get it in a decent text file format (.csv or the like), there are techniques to load the file line-by-line rather than all-at-once (using the FileOpen() function and then using the FileReadLine() functions inside a loop).  That should significantly improve the memory footprint of processing the data.  That is if you are compelled to use ColdFusion to to this.  As @Reed suggested, using the ETL tools (SSIS for SQL Server or whatever Oracle uses) is more efficient and will reduce your headaches.  With SSIS, you can save your import design and reuse it over and over again to add data or make updates to your database.

-Carl V.