LibreBase - problem running a query using two tables (based on text files)

[CS1]

I need to run a query in design view which uses two tables (I want to ‘link’ one item in one table to the same item in the other table, in order to ‘restrict’ the query to geographic areas in both).

Frustratingly, I can’t run this very simply query because query design view will only let me use one table at a time. The window for ‘add table’ disappears completely after one has been selected and it’s not possible to select two, even though I can see both tables clearly on the ‘tables’ tab. Is this because both tables are basically just links to text files and is there any way around the problem? For a smaller dataset I copied and pasted both sets of data into a new Base document/project as new separate tables, but it is not possible to do this with the larger text file based dataset as it is far too big (it won’t even open in Calc).

Please help!

[Ratslinger]

Hello,

Libre Base using text files IS limited to single table queries. From your question it appears the text files contain more than a million records (“it won’t even open in Calc”). Even if you load this into a default Base file you will soon have problems as the embedded DB is prone to problems with large amounts of data. Moving up to a split DB is a possibility and reduces the chances of error since the data is removed from the .odb - posted here. The split DB also upgrades the HSQL to 2.x (from the embedded 1.8). With this you may be able to load the data (csv?) using ‘sqltool’ - see this post.

Other databases have more friendly commands such as MySQL -

LOAD DATA LOCAL INFILE 'YOUR_FILE' INTO TABLE YOUR_TABLE FIELDS TERMINATED BY ','

loads a comma separated text file into a table.

[peterwt]

From LO Help -

“The following database types are read-only types in LibreOffice Base. From within LibreOffice Base it is not possible to change the database structure or to edit, insert, and delete database records for these database types:


Spreadsheet files

Text files

Address book data”

So you can only view records. Also you cannot link Tables in Queries.

[Ratslinger]

@peterwt There is a way to to write to text files - answer by @PYS.

[peterwt]

That’s interesting @Ratslinger. I assume that the Tables are still read only and records cannot be edited, deleted or new records added.

[Ratslinger]

@peterwt No, that’s the point. They work similar to regular tables. You can add & delete data from them, use them in queries with other tables. Personally had little experience with using them.

[peterwt]

@Ratslinger Thank’s that’s god to know.