Trouble converting large excel spreadsheet

[mikekaganski]

Hmm, on a development 7.6 pre-alpha version, similarly to @mariosv, I was able to export the document to ODS. As expected, the XML inside was large (almost 10GB); interestingly, the ODS itself was smaller than XLSX (the ODS was 277 MB). Yet, it succeeded on Win10 64 bit. While the release version (7.5.2.2) failed. And further, the resulting ODS can’t be opened in the release 7.5, while it can in development 7.6. So likely, something changed in the development version, and the future version could handle this.

tdf#128244? or rather, tdf#94915 (because it got a fix in 7.6), but they are likely the same.

[Villeroy]

“Contoso” is the name of a virtual company founded by Microsoft. Its business database is hosted here: Release Contoso Data Generator · sql-bi/Contoso-Data-Generator · GitHub
The csv dump (or spreadsheet dump) is output from that database. A csv dump of a database is not the same as the database itself. Loading it into a spreadsheet will not turn a data dump into a database even if Excel may be able to load and save it.
You can’t keep data safe and consistent when they reside in one table of 88 columns, mostly filled with redundant data. What you are supposed to use is the frontend to that database. A database consists of many tables with no redundancies. A database frontend loads a small subset of data to work with even if there are billions of table rows in the underlying database.

The downloadable database includes tools to generate the database and load the spreadsheet dump into the database tables in order to get a usable business database. The spreadsheet is not usable, not even with Excel.

Meanwhile, while writing the above text, I managed to save your csv dump as a 1 GB dBase file (blessed database format from the 90ies) and connect a Base document to the directory containing that file. Editing 88 database fields in a form is cumbersome but works fluently since Base loads only a small set of rows into memory.

[Villeroy]

How to convert the csv dump into a dBase dump which is editable in Base:

1. Open the csv and find all date columns that are represented by integer day numbers and format these day numbers as ISO dates (number format code YYYY-MM-DD).
2. Create a dedicated database directory and save the file as dBase (*.dbf) in that directory. This takes 20 minutes on my bread and butter laptop.
3. File>New>Database… Connect to existing database of type “dBase”, specify the directory where you saved the dbf.
4. Save the database document. This database document does not contain any data. It shows a single table representing your dbf file. All the data are in the dbf.
5. Right-click the table and call the form wizzard.
   5.1 Click the >> button in order to include all table columns
   5.2 Jump to step 5 (“arrange controls”) and choose the last layout option which is least space consuming.
   5.3. Finish the wizard.

6. Open the new input form.