Ask Your Question

Base: run query on connected spreadsheet?

asked 2017-09-29 02:11:58 +0200

DinoGuy gravatar image

updated 2017-09-29 02:16:29 +0200


I created a a Base database that links to a huge .ods spreadsheet of weather data organized into date, time, and temperature fields. The link works fine and creates a properly formatted table in which I can see all the data.

The problem occurs when I try to run a query on the table. I want to display the maximum daily temperatures. So I add the date and temp fields to the query. But when I try to access the GROUP or MAXIMUM functions in the drop-down menu, I find the drop-down menu is empty. In fact, there are no functions available in queries at all.

Am I doing something wrong, or is this a limitation of connected databases in Base?

Is there a work-around?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-09-29 03:47:25 +0200

Ratslinger gravatar image

updated 2017-09-29 03:59:22 +0200

There are many limitations with Base tables which are linked from spreadsheets. One is that query functions are limited to COUNT(*) - that's it.

Turning Base into an actual database by creating a table with the sheet data (have to start with a new Base file) you get all the functions. You register the DB then you can access the data from a spreadsheet via Data Sources.


I should add, the table data in your Base file is basically a text file (internally specified as file-based-database). There is no real database associated with this process. The functions you are looking for are in the database which in the case of a default base file would be HSQLDB.

edit flag offensive delete link more

answered 2017-09-29 04:34:04 +0200

DinoGuy gravatar image

Okay. Thanks for the answers.

I wanted to use a connected spreadsheet because the dataset is very large (10 fields, 268,000 records, 14Mb filesize) and Base becomes slow and very unstable when I try to bring the data into an HSQLDB embedded database.

I'm guessing by your reply then it is infeasible for me to work with this dataset in Base?

edit flag offensive delete link more


An embedded DB is certainly not the way to go for something this size. And you are correct in its' instability. However a split DB would be a better solution. See this post to create a new empty split DB - click here. Also, this includes a newer version of HSQLDB providing better SQL functionality.

Ratslinger gravatar imageRatslinger ( 2017-09-29 05:23:59 +0200 )edit

@DinoGuy In the future, please reply to an answer with a comment to the answer you are replying to (can be multiple answers). If room is needed to further explain a question, edit your question.

Ratslinger gravatar imageRatslinger ( 2017-09-29 05:30:09 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-29 02:11:58 +0200

Seen: 138 times

Last updated: Sep 29 '17