Importing 400k rows in an ODS spreadsheet into LibreOffice Base

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,

Solution

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()