Ask Your Question
0

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

asked 2017-10-24 17:56:28 +0100

CS gravatar image

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!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-10-24 19:27:03 +0100

Ratslinger gravatar image

updated 2017-10-24 19:28:36 +0100

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.

edit flag offensive delete link more
0

answered 2017-10-24 23:08:12 +0100

peterwt gravatar image

updated 2017-10-24 23:10:07 +0100

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.

edit flag offensive delete link more

Comments

@peterwt There is a way to to write to text files - answer by @pierre-yves samyn.

Ratslinger gravatar imageRatslinger ( 2017-10-24 23:39:40 +0100 )edit

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

peterwt gravatar imagepeterwt ( 2017-10-25 13:09:10 +0100 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2017-10-25 14:21:46 +0100 )edit

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

peterwt gravatar imagepeterwt ( 2017-10-25 16:27:56 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-10-24 17:56:28 +0100

Seen: 232 times

Last updated: Oct 24 '17