Was: How to import a big amount of data into LibreOffice Base [locked]

[Villeroy]

I spent too much time to not answer the meanwhile locked How to import a big amount of data into LibreOffice Base - #12 by rdiez
Same Ubuntu version as yours with the latest LO.

1. New database table in embedded HSQL:

CREATE TABLE T (A CHAR(1), B TINYINT, ID INT IDENTITY);

2. Spreadsheet with =CHAR(RANDBETWEEN.NV(65;90)) and RANDBETWEEN.NV(0;255) in A1:B250000
3. Copy A1:B250000 and paste to the table icon in the database window.
4. Dialog: Append data to table T, no column labels, [Create]
5. Minimize the LO windows to avoid stupid messages like “LibreOffice does not respond”. They have nothing to do with LO. It is the environment which can not distinguish busy from hanging. It remains silent when the app in question runs in the background (not active window).
6. Do something else. After 10 minutes I came back and queried SELECT COUNT(*) FROM “T” and the answer was 250,000.
7. Trying to close and save any view on that database, Base tries to wrap the whole binary thing (> 5 MB) back into the database and Ubuntu tries to tell me again that this application hangs.
8. I lose patience, let Ubuntu kill LO, start again but this time I do not click anything in the Base window in order to avoid “installation” of the embedded database (yes, embedded databases install into a temp. directory which causes all the trouble).
9. I call my Python macro to convert the database into an external HSQLDB and all the trouble is gone. I can query, edit, delete record sets in the Base GUI quickly and without problems. Apache OpenOffice Community Forum - [Python] Macro to extract and reconnect embedded HSQLDB - (View topic)

Lesson learned: Do not use any type of embedded database except for small demos. Use an external relational database such as PostgreSQL, MySQL, HSQL, H2, whatever works well with Base as a frontend. Firebird is not type compatible with Base as mentioned by @Ratslinger.

EDIT: Link to my test database t77775.odb
Download and extract the zip to a trusted directory according to Tools>Options>Security>[Macro Security…]. Open the database document. The embedded macro connects the document to the t77775.* files in the database folder using the HSQL driver of your office suite. Open/edit the table, run queries, do what you like.

[Ratslinger]

@Villeroy

Firebird is not type compatible with Base as mentioned by @Ratslinger.

Have not stated this. In the almost three year old post this refers to, did state there were some issues with copy/paste with certain data types but there was a work around. This may even be corrected by now.
.
Firebird embedded was the first test noted in the edited answer.

[Villeroy]

How do you get these data into embedded Firebird? Since 1 hour I’m waiting for the copy/paste to finish.

[Ratslinger]

Just a simple copy/paste - current test about 2 min 45 sec.
.
Re-tested earlier to see what changes to LO may have affected this. Current -

Version: 7.3.3.2 / LibreOffice Community
Build ID: d1d0ea68f081ee2800a922cac8f79445e4603348
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

Tests (rough) for copy/paste from Calc & table view to get to last record:

Firebird embedded abt 2:45
To file end abt 1 sec

PostgreSQL abt 1 min
To file end in blink-of-eye

HSQLDB split abt 1:45
To file end in a couple of seconds.

Last Firebird embedded test I rebooted (for complete clean test), opened my browser, created new Base file and did copy/paste. Set a few fields to test numeric & decimal types (both now work):

Resulting Base file was about 3GB.
.
I need to note here that since original post (almost 3 yrs ago) have newer (but not great) AMD processor , motherboard, and increased from 4 to 16GB memory.

[Ratslinger]

Just tried your 250K record from Calc to Firebird - about 11 minutes (corrected time as first run was not correct in Col B). Created in same Base file as my previous creation.
.
Base file now about 4.5GB

[Villeroy]

This works for me on Windows but not Ubuntu Linux.
I download my database to a Windows box, enable experimental LO features, create an embedded FB and drag the table icon from my database into the table section of the embedded FB. The transfer takes ~ 10 minutes.
Doing the same on my Ubuntu box does not finish within an hour. It works well with embedded HSQL as target database. Strangely, it does not finish when the target is stand-alone HSQL.
I copy several thousands of records from dBase to external HSQL on a regular basis. Never tried 250,000 records. There seems to be a scaling issue on LInux boxes.

[Ratslinger]

All my current tests above were on:

.
LO noted previously

[Villeroy]

@Ratslinger

All my current tests above were on:

I’m not prepared to upgrade my system right now. I hope to find some time in summer for leaving *buntu forever.

[Ratslinger]

Don’t recall your OS version but out of curiosity I went back to mine of:

with LO:

.
and my 81K record file again ran in just a few minutes from Calc to a new Firebird Base file.
.
I also note that the original test shows it was with LO v 6.2.0.3.
’
With my old hardware in original test it was abut 45 minutes. Would imagine the 250K record file should be similar to the 11 minute run as tested earlier.

[Villeroy]

I tested with latest commuity version 7.3. and to a smaller extent on a Windows box.
Like so often, the problem does not occur with Windows nor with ppa version 6.0 of my Ubuntu 18.4. With 7.3 on Linux copy&paste of 250,000 small records takes (a lot) more than an hour.
With Windows or with the Linux ppa version, the same operation takes 10 minutes on a bread and butter machine.

[Ratslinger]

Used 7.3.3.2 (TDF) on 18.4 & your 250K Calc records took between 10 & 11 minutes. Wasn’t closely watching. That’s enough for me with this testing…