BUG: large csv files have problems importing

Desktop editors
,
1

Do you want to: Report a bug
OS version: Windows 11
App version: 9.1.0 64-bit (NOT 32-bit)
Downloaded from: ONLYOFFICE website

I have noticed that the spreadsheet program can have difficulty importing large CSV files. Either the program will report lack of memory or simply “error”.

For example, with a particular CSV file, 57MB, ~730,000 lines long with 21 columns. The only data inside are alphanumeric characters, “@”, “.” and “-” (i.e. only ANSI characters). Trying to open the file, the program will bring up the dialog about the charset encoding and delimiter. Clicking okay will eventually cause the program to crash with the error: “Oops! Something went wrong. We lost access to your file due to a lack of memory or some other reason. Please don’t worry and try reopening the file. Close this tab to continue.”

Picking a charset different from UTF-8 didn’t make a difference (e.g. Western European). Changing the EOF of the text file from Windows to Unix also made no difference.

I was able to eventually open the file with OnlyOffice by first manually editing the csv file in a text editor and reducing the number of rows. Reducing the file from ~730k rows down to 484,189 with a resultant file size of 36.7 MB (38,541,782 bytes) (as shown in Windows Explorer properties), and then the program opened the file successfully. But at 484,190 rows, it didn’t work.

In another instance, an even larger CSV file resulted in a different error. That file was, as per Windows Explorer properties, 118 MB (124,561,326 bytes), with 23 columns and 893,000 lines. Trying to open resulted in an error of: “An error has occurred while opening the file. Press “OK” to close the editor.”
Something to note is that this larger CSV file had special characters beyond the ANSI charset (e.g. unicode accented characters), though no binary data or anything like that. Reducing the text file down to 450,000 rows (~64MB) did not help and resulted in the same “An error has occurred” error. Further reducing the file further down to 225,000 rows (~32 MB), allowed the file to be opened successfully.

I noted a post in the forum with a user having difficulties with the “lack of memory” reason that was solved by switching from the 32-bit to the 64-bit version, but in my case I am already using the 64-bit version.

In terms of my machine and running out of memory, while I don’t have a brand-brand-new computer, I don’t think that it is particularly dated either: it is a 13th Generation Intel Core i7, with an embedded GPU Intel Iris Xe, and 32GB of RAM, 1GB SSD. The tests above were conducted with Task Manager saying the system had 22.6 GB of RAM free.

Also for reference, all the CSV files I could successfully open with LibreOffice without issue.

Perhaps it is something to do with the number of rows in addition to the file size. Because I note that I have been able to open some other large CSV files in the 40MB range, with even more columns than the above two example, including even binary data, without issue. However, and maybe this is one of the issues, those successfully imported CSV files had far fewer rows (e.g. less than 80,000 rows).

2

Hello @OnlyTraveler

It might be related to the same issue from this topic:

The work is in progress, but without a sample file we cannot make sure that it is indeed the same issue.

3

Hi @Constantine , thanks for triangulating this!

While I can’t say definitively on my end whether this is the same issue, I did some further testing for you that I hope can help you and your team.

I converted the aforementioned CSV files into XLSX files. I did not edit them at all in terms of content, simply opened in LibreOffice, and saved to XLSX. I tried saving both to “Excel 2007-365” format as well as to “Office Open XML” (there was no difference in the end result below).

The results are as follows:

File 1 with ~730k lines and 57MB CSV

  • Converting to XLSX size brought the file size down to 33.6MB
  • Opening the file eventually DID actually “show” the file (unlike when trying to open the CSV), in terms of the content of the file being initially shown on the screen, BUT no action was possible as everything remains “frozen” – I couldn’t select any cells and all the buttons were greyed out.
  • Despite the interface being greyed out for the file, I was able to click back and forth at the very top bar to switch to the “ONLYOFFICE” tab (taking one to the screen with recent files or to create new file)
  • However, the file never gets responsive, and eventually after some time the file crashes with the “lack of memory” screen as with the CSV file before.

File 2 with ~893k rows and 118MB CSV

  • Converting to XLSX size brought the file size down to 92MB
  • due to some fields containing line-breaks, the total number of rows for the excel is not 893k, but actually around 700k
  • As mentioned in my prior post, opening the CSV did not bring about the “lack of memory” error, but rather instead a different error of “An error has occurred while opening the file. Press “OK” to close the editor.”
  • Now opening this converted XLSX however, it did manage to avoid the “error has occurred” message. But thereafter it behaved exactly the same as the “File 1” excel-variant I described above: it was impossible to click any cell in the file and all the buttons were greyed out; and then eventually it crashed with the same “lack of memory” error.

breaking it

  • I wanted to see if I could replicate the “An error has occurred while opening the file. Press “OK” to close the editor.” error with an excel file.
  • I took I took the converted XLSX of “File 2”, and in LibreOffice, copy & pasted further rows and got to ~1020k rows with a file size of 115MB, but the same number of columns. I couldn’t go any further because LibreOffice seems to have a limit as it gave me an error that there wasn’t enough room.
  • this 1020k row file with 115MB did NOT get the “error has occurred while opening” error, but rather like File 1 excel above: file shown, then “lack of memory” error
  • So then I took the file in a different direction to increase the size: I took File 2 and add several more columns: this excel was 41 columns, ~917, rows and windows explorer showing 217MB (228,240,322 bytes)
  • and THAT file caused the same crash: “An error has occurred while opening the file. Press “OK” to close the editor.”

–

So it seems like it might be the same error, and maybe it also means that above a certain threshhold of combination of rows/columns/MB, that it not only gets a “lack of memory” error, but just fails completely to open with the “An error has occurred”.

I would have liked to have shared the 893k-line CSV file, but the issue is that it is a sensitive document that I am unable to share to any third-parties. And I don’t know how I could easily replace the data in such a large file without being very time consuming. Unless you know of some kind of python script!

Thanks again for responding!

4

We have just released new version 9.2 of Desktop Editors, try updating and checking the situation again. Looking forward to your feedback.

5

Thank you for the update @Constantine!

I have installed v9.2.0. The CSV files still had the same crashing as before, but the excel files below a certain size could be opened, though the largest one still crashed before opening. I list the results for each file as follows:

  • 730k row 57MB CSV file
    – same error: “Oops! Something went wrong. We lost access to your file due to a lack of memory or some other reason”
  • 893k row 118MB CSV file
    – same error: “An error has occurred while opening the file. Press “OK” to close the editor.”
  • 730k row 33.6MB XLSX file (converted in LibreOffice from the 57MB CSV file)
    – opened successfully, but cut/copy/pasting operations cause out of memory crashes (see description below)
  • 893k row 92MB XLSX file (converted in LibreOffice from the 118MB CSV file)
    – opened successfully, but cut/copy/pasting operations cause out of memory crashes (see description below)
  • 917k row, 41 column 217MB XLSX file (the one I mentioned in my prior post under “breaking it”)
    – same error as before: “An error has occurred while opening the file. Press “OK” to close the editor.”

Case of successful opening but crashing on pasting
In the xlsx files I was now able to open, mentioned above, I was able to to edit cells, and enter data into new cells.
But cut/copying and pasting cells led to a crash with the message: “Oops! Something went wrong. We lost access to your file due to a lack of memory or some other reason.”

For example, in the case of the 730k row 33.6MB file, the actual number of rows was 729,355. I navigate to the first empty row, 729,356. I enter the number 1 into cell A729356. I then copy that cell (CTRL+C), and then move to B729356, and press CTRL+V to paste it. The program freezes for some time, and finally will produce the “lack of memory” error.

Note that, if I instead hover over the bottom-right corner of that cell A729355 and click-and-drag that over, that does NOT crash the program.

Summary: the program has better success opening large XLSX files, though there are still crashes with larger files, but for CSV the crashing problems persist.

6

Can you share these two files for analysis?

7

Hi @Constantine , I would have liked to, but the issue is that they contain confidential information that I am not allowed to share with third-parties.

I don’t know how I could easily replace the data in such a large file to anonymize it without taking an extreme amount of time. If you have any tips in this regard, happy to try any suggestions!

8

I don’t know the structure, thus there is nothing I can suggest in terms of anonymizing. As a side note, you can share files via PM to avoid publicly sharing them in topic.

9

The issue there is a CSV import/parsing limitation in OnlyOffice, not file corruption—CSV files are plain text and don’t contain internal workbook structures. Because of that, Excel repair tools don’t apply to CSV import bugs. Where Excel repair does become relevant is with .xlsx/.xls files that have damaged internal XML (for example, PivotTable or relationship errors) and trigger repair messages or freezes in Excel. In those cases, Excel’s built-in Open & Repair or try some third party tools like Stellar Repair for Excel can help restore the workbook structure.

1 Like