Description: I'm looking into building a stock portfolio management application. I want the account data to be in a local database, but there are several tables that need to be updated daily with new data. For example the price_master table looks like the following: SYMBOL PRICE DIVIDEND YIELD PAY_DATE PE_RATIO FCX 39.525 0.15 1.56% 11/01/2022 14.60 ADM 92.435 0.40 1.72% 12/07/2022 12.86 This data is available from a CSV download file and it only takes a half minute to copy/past the appropriate columns into a CALC workbook that I've setup for this. Although the data in these tables change, there is never any reason for the application to make the changes. A read only table is all that is needed. Data that is managed by the application should be in the embedded HSQLDB database. The security_master table is a good example of this: SYMBOL DESCRIPTION FCX Freeport-McMoran Inc Class B ADM Archer Daniels Midland Co Common My use case is in finance, but I can readily think of many use cases in other fields where this capability would be very useful. This enhancement would allow BASE to function as a reporting engine bringing together multiple data sources. Steps to Reproduce: 1. N/A Enhancement request 2. 3. Actual Results: N/A Enhancement request Expected Results: N/A Enhancement request Reproducible: Always User Profile Reset: No Additional Info: N/A Enhancement request
Do you know you could connect to *.csv-tables directly with internal HSQLDB? https://books.libreoffice.org/en/BG73/BG7302-CreatingADatabase.html#toc72
That looks like it will work. I'll check it out. Adding this functionality to the GUI to make it easier it all that is left to do. Much appreciated and thank you, Carlisle
(In reply to Carlisle Branch from comment #2) > That looks like it will work. I'll check it out. > > Adding this functionality to the GUI to make it easier it all that is left > to do. UX team: seems useful, any ideas on how it should look like?
(In reply to Carlisle Branch from comment #2) > That looks like it will work. I'll check it out. Carlisle, was the tip from comment 1 beneficial for your workflow?
I got waylaid by projects and set it aside, but it’s still something I’m planning to do. The solution given should work for me. If you could make this feature easy and more intuitive within the graphical interface, you would take BASE to a whole new level. The ability to connect to multiple data sources and then generate reports has almost an infinite number of use cases across so many fields since you always want to get data from where it’s being maintained and so often that isn’t going to be in a single database.
[Automated Action] NeedInfo-To-Unconfirmed
Hm... this CREATE TEXT TABLE() works (only for the deprecated HSQLDB) and creates a table with a different icon, so I guess there is some difference but I cannot read it in some properties dialog. The subsequent commands SET TABLE and SOURCE fail with "1: Unexpected token: SOURCE in statement [SOURCE] at /usr/src/debug/libreoffice-fresh/libreoffice-24.2.5.2/connectivity/source/drivers/jdbc/Object.cxx:173". UI-wise I could imagine to add another link in the Tasks view "Connect to CSV file..." that opens the file, reads the column names from the first row, and guesses the data format from the following (Gabriel implemented something similar recently). Eventually it's pretty easy to change the field type. I wonder if other DB types such as Firebird can deal with CSV too.
(In reply to Heiko Tietze from comment #7) > > I wonder if other DB types such as Firebird can deal with CSV too. It is dependend on firebird.conf. Default for external files is set to "NONE". It's also the default of our internal Firebird database. If a database will work with external files there will be security problems. People, who will use this function, should know what they do. So it won't be enabled for the internal Firebird database. Such a table should only be used to import the data into a database, not to write data to the table. It could be changed external while writing data internal and you will result in dataloss.