1

I have a large .xlsx file (20mb) I need to read the data and create tab separated files.

I am having problems with loading this file (OutOfMemoryError) and java heap error by using cfspreadsheet

Update

  • ColdFusion 10
  • Java 7
Adrian J. Moreno
  • 14,350
  • 1
  • 37
  • 44
brahmam
  • 11
  • 3
  • Need some example code of what you're doing. Is this on your local machine or on a server? What version of ColdFusion? What version of Java? – Adrian J. Moreno Nov 11 '20 at 17:02
  • So what are your memory settings? Also xlsx is a zip of xml documents. Have you tried opening the xml instead? See: https://coldfusion.adobe.com/2018/11/extracting-text-html-out-word-docx-files/ (Disclaimer: I did this video and it is for Word, but the concepts should still apply) – James A Mohler Nov 11 '20 at 17:11
  • I am working coldfusion 10 with java7 and its remote machine. in administrator settings heap size was 4098mb. I am able to upload the files to server but when I read its causing the memory out error. I have no issue with 1mb files. and I tried with POIUtilities with same error. – brahmam Nov 11 '20 at 17:34
  • Download the current version of CF 2021 developer edition. It also uses a newer version of Java and almost certainly a newer version of POI. See if that addresses the issue. – James A Mohler Nov 11 '20 at 20:00
  • 1
    This might not be solvable. – Dan Bracuk Nov 11 '20 at 20:31
  • @brahmam Need some more info here. How many rows do these files have? I know older versions of CF have a limit to the number of rows that can be written by `cfspreadsheet`, it might have a limit to what can be read as well. Can you try upgrading the server to use Java 8? Is your CF 10 fully patched? https://coldfusion.adobe.com/2014/10/coldfusion-10-and-11-support-with-java-8/ – Adrian J. Moreno Nov 11 '20 at 22:18
  • 2
    My advice. Don't even bother using `cfspreadsheet`. I've been down that road before in an older version of CF. I can't remember if it was CF9 or CF11, but when dealing with large spreadsheets, `cfspreadsheet` is such a memory hog that there's nothing you can do to prevent the OutOfMemoryError. The solution I found is to use a 3rd party program that accepts command line parameters and `cfexecute` it instead. If you're using Windows, try looking at this https://stackoverflow.com/questions/5256733/convert-xlsx-file-to-csv-using-batch/27315498 – user12031119 Nov 12 '20 at 00:48
  • @AdrianJ.Moreno there were more than 50k rows in each excel. and I must not upgrade either CF or Java. – brahmam Nov 12 '20 at 08:20
  • @JamesAMohler its very old application and no plan to upgrade CF2020 – brahmam Nov 12 '20 at 08:25

0 Answers0