ExcelToRecord List Issue

Hello,

I am having an issue with ExcelToRecord List, it breaks down when reading a large excel file and throws either OutOfMemoryError or 524 Gateway Error. After analysis, I have found out that the issue is in the excel file being used. It contains almost a millions rows, out of which only 150 rows had data and rest are just empty.  I have also noticed that when I import such file, the platform become unavailable for a short duration.  

  1. How to limit ExcelToRecord to read only those rows that has data?
  2. How to read the number of rows in Excel before initialing ExcelToRecord List, by using a function / extension available?

I have attached excel file that was used in the import process.

 Thank you

Regards, Wasif


Demo.xlsx

mvp_badge
MVP

Hi Wasif,


In order to count the number of rows, you need to process the file which throws the error.

Still when you have a file with so many lines, this reflects on the size of the document and that you can obtain before using the ExcelToRecordList action. For that, you can use BinaryDataSize action from BinaryData extension:



You can use that to compare to a threshold that fits your scenario. If it is over this threshold then you avoid going the conversion to a record list, otherwise you process the Excel.


Hope it helps.


Kind Regards,
João

Hello João,

Yes, We do have size validation that makes sure the file size is not more than 4 MB. 

The excel in question is 2.69 MB, which is decent considering it has more than million rows. The issue is that the empty rows are not increasing the file size by alot. If they were, file size validation would have taken care of it.

Thank you

Regards, Wasif   


mvp_badge
MVP

Hello Wasif.

Let's do this in parts.

If the page blocks while you are processing the file and you get OutOfMemory, you should process it asynchronously.
The user uploads a file and starts a timer in the background to process it. That way the page doesn't wait to read the content (you can have a small block checking when the file gets processed to notify the user).

To check the number of rows you have to open the file somehow. And if the empty rows are counted, this won't solve your problem.
My suggestion is for you to use a different tool and Advanced Excel is on top of my mind. This will allow you to:
1) count rows;
2) ignore empty rows;
3) import rows almost as the native component.

This will probably solve all your issues. It is not as simple/pretty as ExcelToRecordList, but also your file isn't perfect.


Hi Wasif Iqbal

Sometimes this occurs because we have some logic associated with the file, meaning before the record created on our DB we have some validations and do this 1 Million times will cause the time-out.

What you can do is create a temp entity with the same structure as the excel file, then do a copy from the excel file without any logic, and after run those validations and store them on the correct entity. 

In the end, clear the temp entity.

Regards

Gonçalo Almeida

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.