pyexcelerate consumes lot of memory when huge data needs to be written

[Anuradha-26]

I have a business need where I have huge data , more than 3 lac rows per worksheet. Writing to xlsx file using pyexcelerate is very fast , but it consumes lot of memory while writing . For me memory is a constraint. So i'm forced to use xlswriter which has constant_memory = True. It will be great if you can add a feature like that to pyexcelerate. Or can i do something to optimize memory? I use this call work_book.new_sheet(sheet_name, data=sheet_rows) to write to xlsx file. sheet_rows can be a very huge data structure.

[kevmo314]

If you're constrained on memory, have you considered writing to csv and then converting to xlsx? We don't have an easy way to bound the memory usage as it would cripple the write speed.

[Anuradha-26]

I did not use csv as we have some headers with different colors etc. Are you aware how i can convert csv to xlsx in a pythonic way? Thanks for the suggestion. I can try

[kevmo314]

If you need header styles, then you're out of luck and cannot go via csv. How much memory does your machine have?

[Anuradha-26]

6GB. Report generation is our last step. All before steps (there are lots of them) take 4GB. Report alone takes 2GB (for 3 lac records in 2 reports each) and just overshoots 6GB and we get Out of Memory error.

[kevmo314]

What sorts of data are you writing? The library shouldn't be using that much memory unless you're passing objects to it. If you're doing that, you might want to serialize everything to strings before passing it to PyExcelerate.

Also, could you try the latest dev branch? It includes some performance optimizations that may help here.

[Anuradha-26]

I convert everything to strings before we write report. But we have lot of columns( from A to AB). There are 4 xls sheets each with 2 lac records. Sample row: str1 str1 longstr str1 str2 longstr IntraFrequency 475 ? ? 0 0 0 0 0 0 0 0 0 0 (N/A) 0 (N/A) N/A N/A N/A N/A N/A N/A N/A

Overall report size is 84MB

[kevmo314]

Yeah I would say try the latest dev branch and see if it helps.

[kevmo314]

Just checking in, have you had a chance to try the dev branch?

[Anuradha-26]

Nope very busy with regular work. Will definitely try and let you know

On 18-Jul-2017 7:54 PM, "Kevin Wang" notifications@github.com wrote:

Just checking in, have you had a chance to try the dev branch?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/kz26/PyExcelerate/issues/57#issuecomment-316080374, or mute the thread https://github.com/notifications/unsubscribe-auth/AYSyFAKquazrK01L2vsgIXWDymcOba68ks5sPMAkgaJpZM4OL9zT .

[yunfan]

i'd have same issue here, under the same situation

i think constante memory is really a important option because for such job, user could wait for extra seconds but the server cannot provide too many memory than it have.

so i think its better to gave user the choice of which strategy they want someone need fast, while others need constant memory or constant CPU ?

[emersonbferreira]

Did you find any solution to this "problem"?