Importing 400k rows in an ODS spreadsheet into LibreOffice Base

Question

I have a ODS Speadsheet that contains just over 400k rows which I want to import into LibreOffice Base as a Table.

As a test, I can import 10 rows easily by dragging the sheet to the Table area in Base. If I try and do this with the full spreadsheet I get errors. It seems to create the table but no data is imported. If I try to open the table it gives the error:

The data content could not be loaded.

S1000 General error java.lang.NullPointerException in statement [SELECT * FROM "Table2"]

During the apparent data load a warning is issued too:

Warning

S1000 General error java.lang.NullPointerException in statement [INSERT INTO "Table2" ( "ID","COL1","COL2","COL3","COL4","COL5","COL6","COL7","COL8","COL9","COL10","COL11","COL12","COL13","COL14","COL15","COL15","COL16","COL17","COL18") VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]
Continue anyway?

I have also tried creating an empty table first with the correct table fields and appending the data but that too fails.

What options are there for loading a large amount of data into Base?

Is there a Macro method of writing the data from ODS to Base, or even chucking up the data into say 50,000 rows and importing/appending in smaller bites?

I can open the ODS sheet in a new database, but then it is read-only as it is using the ODS as a data source. Having got that far though is it possible to copy the data from a read-only database to another fresh standalone database? Macro again maybe?

Thanks,

Never tried this myself - did you try exporting the ods to csv and importing from there? — tohuwawohu, Jul 31 '22 at 11:06
Yes, I tried that one, and it gives the same error messages. That's why I'm thinking Macro route but don't know how to do that. I'm hoping someone might have some code for importing into a table, even if the ODS needs splitting up into smaller pieces first. — Packwood, Jul 31 '22 at 12:42

Jim K · Accepted Answer · 2022-08-01T21:34:25.780

Base is a front end to a database engine. You don't have to do everything by going through the front door, and it sounds like in your case the front door isn't wide enough! So use what the engine can offer instead (and choose a good engine).

For example, if you use Base with PostgreSQL (which Ratslinger over at ask.libreoffice.org has said is possibly the best engine for Base), then use this approach to import a CSV file: https://stackoverflow.com/a/2987451/5100564. Note: I have not tested this, but it seems like it would work for large files.

Personally, I have used Base with a MySQL text-based storage engine. I simply moved the text file into the database directory and MySQL was able to use it as a fully editable table. It looks like MySQL can use CSV as well as several other text formats. Then it should be straightforward to convert it into a standard InnoDB table.

Whether you get it into PostgreSQL or a MySQL InnoDB table, the 400k rows should feel quite manageable, and millions of rows shouldn't be a problem if the table grows. However, if you decide instead to use an embedded setup with HSQLDB 1.8 or Firebird as the engine, then you still may have trouble, because Base must zip up everything into a single file after each change, and this often fails and crashes.

A macro should work as well, although writing Base macros is notoriously difficult for beginners. Still, it shouldn't require too many lines of code. If you do try this and get stuck, people like me can offer corrections, but I'm not going to take the time to write the macro for you. It looks like there is a python tag in your profile, and LibreOffice macros can be written in Python-UNO.

EDIT:

Here is some Base code in Python that I wrote some time ago.

def createDbTable():
    oDoc = XSCRIPTCONTEXT.getDocument()
    db = oDoc.DataSource
    conn = db.getConnection("","")  #username & password pair
    stmt = conn.createStatement()
    strSQL = (
        'CREATE TABLE Persons ('
        'PersonID int, LastName varchar(255), FirstName varchar(255),'
        'Address varchar(255), City varchar(255))')
    stmt.execute(strSQL)
    conn.close()

Thank you that's given me some options to explore. Is there a starter guide to Base macros somewhere (with a few examples, maybe)? — Packwood, Aug 01 '22 at 13:49
I got started at https://www.pitonyak.org/database/, although it is not as complete as Andrew's main macro document. Here is an example python macro: https://wiki.documentfoundation.org/Macros/Base/py002. Also for python: http://christopher5106.github.io/office/2015/12/06/openoffice-libreoffice-automate-your-office-tasks-with-python-macros.html and get the APSO extension. — Jim K, Aug 01 '22 at 21:12
I went down the PostgreSQL route and have successfully imported my 400k rows. However, now I have a connection to that database in LibreOffice Base it also shows two additional tables "information_schema" and "pg_catalog" both of which have a large number of views. I guess these come from the PostgreSQL database. Can I safely delete them from Base and not destroy my underlying PostgreSQL database? — Packwood, Aug 04 '22 at 11:20
I think this answers your question in the previous comment (and the answer is "no"): https://dba.stackexchange.com/questions/295005/hide-pg-catalogs-in-postgresql-from-end-users. Also, it sounds like maybe you don't quite get that Base is simply a front-end to the PostgreSQL database, not two different databases. Beyond that, post a new question and explain why you would want to do that, as perhaps you could achieve your goal with a Base form. — Jim K, Aug 04 '22 at 13:59

Importing 400k rows in an ODS spreadsheet into LibreOffice Base

1 Answers1