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.
what do you call : using array of cfspreadsheet
Also POIutility.cfc does work fine with current CF, but only with xls not with xlsx
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)
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 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.
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.
1 person found this helpful
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.