I created an export file/template of data to import (in this case Sales Invoice to import legacy POS data).
I then added in the required data to the spreadsheet. When I tried to import it back, it took forever to get nowhere.
To debug/test it, I stripped it down to just 5 transactions, and imported it again (deleted the attachment and recreated it with new name to ensure no caching issues). It again took forever just to attach, and then got nowhere after some hours on the import.
Turns out there is a “funny” (unknown to me) in the conversion between Excel and LibreOffice’s .xlsx files. This is not an ERPNext problem, but I’m sure people on the ERPNext system often use the import feature, so I thought I’d share this.
During the data manipulation, I colour coded a few (entire) rows and a few (entire) columns to make it easier to see what was happening in mandatory/optional columns.
It seems (though I cannot confirm this) that between the .xlsx adjustments, Excel or LibreOffice Calc marks the furthest x & y points that contain any data - even if it is only a block colour/tag for visual clarity. This meant that the import file might just think you have a file 10 million rows x 20 thousand columns…which understandably takes quite a while to process.
I can’t say for certain what the best way to work around this is, but I did it this way…
- Open the spreadsheet…
- freeze the columns at about A10…
- go to the last cell that has actual data in it…
- hold down shift, and then click on the first data cell (A1)
- this will mark the populated data neatly.
- click the + to create a new sheet (eg. sheet2) within the file, and paste the data in that
- delete the “infinitely large” sheet (since your data is now available in sheet2) and save the file
This can now be used as a more reliable import source.
Hope it helps someone.