I'd like to have a better base query and report to create csv or spreadsheets directly. Example to get an addressbook from a relational database (consisting of a company and a employee table) into thunderbird: -Make a query that links both tables -Copy the whole table manually in a calc spreadsheet -Export it in csv format, adjust cell delimiters, utf8.... -Take an external csv to vcard converter program and convert it. -Import the whole thing into the email client addressbook -Repeat this with every address change Way to complicated. What is missing: -A calc spreadsheet cannot export to vcard (=csv in one column). The cell delimiter should therefor be CR/LF, that cannot be configured in the exporter. -A base query cannot be saved directly to csv or spreadsheet, although the displayed output seems to be already in calc format. -A base report can be done only in writer format, not in csv. Best solution would be a report that directly produces csv (vcard), without any manual conversions. VCard Format: BEGIN:VCARD VERSION:2.1 N:Mustermann;Erika;;Dr.; FN:Dr. Erika Mustermann ORG:Wikimedia ROLE:Kommunikation TITLE:Redaktion & Gestaltung PHOTO;JPEG:http://commons.wikimedia.org/wiki/File:Erika_Mustermann_2010.jpg TEL;WORK;VOICE:(0221) 9999123 TEL;HOME;VOICE:(0221) 1234567 ADR;HOME:;;Heidestrasse 17;Koeln;;51147;Deutschland EMAIL;PREF;INTERNET:erika@mustermann.de REV:20140301T221110Z END:VCARD
This is typically something that would be handled by an extension, for example, written in Basic or Python. The fact that LO has dropped DB support for Thunderbird addressbooks would seem to me to make this a valid RFE, but I doubt that it would ever be integrated into the core (unless the ESC deems it worthy and someone is prepare to maintain the code that allows this to be done).
Well, all email client addressbooks that I know work with vcards, a database should support this. I don't see the need of an extension, cause vcard could easily be created from a query. The only difference to csv is a CR/LF cell delimiter. More general a report in csv or calc format is nothing unusual. Formatting a report in writer format is much more complicated. I don't understand why a report displays in spreadsheet format but cannot be saved directly? Each of my 3 suggested improvements could help.
Sorry: I don't understand why a QUERY displays in spreadsheet format but cannot be saved directly?
(In reply to saxofon from comment #3) > Sorry: I don't understand why a QUERY displays in spreadsheet format but > cannot be saved directly? Might I suggest the following links as to how to setup a named range to a database query directly from Calc ? https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=80898 https://ask.libreoffice.org/en/question/233958/insert-base-query-results-into-calc/ It seems to me that your question above would be answered by that.
(In reply to saxofon from comment #2) > Well, all email client addressbooks that I know work with vcards, a database > should support this. I don't see the need of an extension, cause vcard could > easily be created from a query. The only difference to csv is a CR/LF cell > delimiter. > Please indicate how many commercial (or even FOSS) DB query interface products allow you to export directly to VCARD ? Personally, I don't know of any, but then I don't know all of the commercial products out there. A quick search via Google doesn't show any immediate results, in fact, all of the initial results I could find involved some kind of programming language, be it PHP, Python, or some similar processing language. Like I said, the RFE has been set to new. However, my personal opinion is that this is not something that would be directly integrated into the core product, rather it could usefully be provided as an extension. Indeed, the solutions I have found so far on the internet show that people tend to use scripting languages to solve this very problem.
Thank you Alex, you are right, it seems to work even with a SQL query directly from the database, without detour over calc. I found this: Create Text Table "Report_txt" AS (Select * FROM "Report") WITH NO DATA; /* Link it to a text file "Report_txt.TXT" with \r\n newline delimiters and without field labels: */ SET TABLE "Report_txt" SOURCE "Report_txt.TXT;encoding=UTF-8;fs=\r\n;ignore_first=true"; INSERT INTO "Report_txt" (Select * From "Report" ); SET TABLE "Report_txt" SOURCE OFF; /* disconnect from underlying data source */ DROP TABLE "Report_txt" IF EXISTS; /* delete */ I try this and mark this bug as resolved. Regards