I’m building a small “app” for a group. I’ve defined a Base table and have built some forms for it. Now I need to add an export feature that uses the results of a SQL query (against the Base table) and saves that data to an external CSV file.
I’ve see several postings that describe how to do this, but they all apply to older versions of LibreOffice and Base. I’m running Version: 6.3.3.2, and none of the previous solutions seem to work.
Any recent examples would be appreciated.
Hello,
It would be nice if you provided some of those examples. Don’t see any reason why they would not work as the basics of Base and LO have remained fairly consistent for a long time.
Just ran a simple test on Ubuntu 18.04 with LO v6.4.1.2
Base is registered so data is accessible from other modules. Opened a new Calc file. Accessed the Data Sources (View->Data Sources). Selected my Query. Dragged the data to the sheet. File->Save As using .csv filter.
Here is an example that should work based on other older solutions to this problem. This could all be put in a macro so the end user just pushes a button or runs the stored SQL there are shorter examples as well but this should work.
The source table is Members which holds thousands of records (and would be problematic with a Cut/Paste solution.
The text tables should only hold the limited set of records that are identified in the last select statement (i.e. only members that have paid for mailings based on the MailThru field on the Members table).
create text table “MailData” (“LastName” varchar(20), “FirstName” varchar(20), “Address” varchar(40), “City” varchar(20), “State” varchar(20), “Zip5” varchar(5), “Zip4” varchar(4))
set table “MailData” source “MailData.csv;encoding=UTF-8”
insert into “MailData” SELECT “Members”.“FirstName” “FirstName”, “Members”.“LastName” “LastName”, “Members”.“Address” “Address”, “Members”.“City” “City”, “Members”.“State” “State”, “Members”.“Zip5” “Zip5”, “Members”.“Zip4” “Zip4” FROM “Members” “Members” WHERE (“Members”.“MailThru” >= ‘2020/04’ ) ORDER BY “Zip5”, “Zip4”
NOTE: The above solution DOES work if you’re using the HSQLDB database and NOT the Firebird database. So, the lesson learned is if you want to do anything but basic stuff make sure you build your Base DB using the HSQLDB and not the Firebird DB.
Also, it appears that the tables I created are based on Firebird as underlying DB. Maybe Firebird doesn’t support the Text option in SQL.
I can not find a .csv export method for libre+firebird either…have we missed something?is there an alternative approach ? :/…i am thinking of a work around using a HSQLDB with just the table required for .csv export (simply pasted from another active firebird-LO instance), and then running a macro from there…>>but quickly testing i find issues with bringing CLOB back to MEMO…sigh:(