I would like to export data of a stored query to a csv (or any character delimited) file. I know how to do by graphically copy or drag and drop the query content to a Calc spreadsheet and then save it in csv.
But what I need is to handle all these actions within a macro, that only requires from the end-user to just click on a button in the form.
I’ve note found how to do in my searches (but may have not searched well…?), and would need some advices to guide me.
Would this need for example to use the
SaveDataToFile(FilePath as String, DataList()) LibreOffice function (Tools > UCB)? But then, how to get the query data (several rows and several columns) matching the
Note: I’m using an embedded Firebird-LibreOffice database, on Debian 10, with LibreOffice 18.104.22.168 (but need that the macro works well in a Windows environment).
Here is an example file with table, query, form (with button) prepared, but with empty macro, if it is useful: export_csv.odb
Thanks in advance for any help, and please ask if any more detail required!
[EDIT] - 2021-01-24
A potential solution would be to use a SQL query, and LibreOffice open and write function.
As an example, if it can be useful for some (even if I’m not sure this code is the best code than can be found):
Sub ExportCsv GlobalScope.BasicLibraries.loadLibrary("Tools") Dim iCount As Integer Dim sValue As String Dim oDoc as Object Dim sUrl_Source as String Dim sFolder as String ' Initialize the file (??) iCount = Freefile ' Get the odb document oDoc = ThisComponent.Parent ' Get the url (folder + filename) of the current database file sUrl_Source = oDoc.URL ' Extract the folder of the current database file sFolder = DirectoryNameoutofPath(ThisComponent.Parent.getUrl(),"/") sFolder = ConvertFromUrl(sFolder) ' Open the text file Open sFolder & "/test.txt" For Output As iCount ' Specific SQL handling declaration and preparation Dim oDatasource as Object Dim oConnection as Object Dim oSQL_Command as Object ' Activate the SQL connection oDatasource = thisDatabaseDocument.CurrentController If Not (oDatasource.isConnected()) Then oDatasource.connect() End if oConnection = oDatasource.ActiveConnection() oSQL_Command = oConnection.createStatement() ' Check if file already indexed in the application stSql = "SELECT * FROM ""Query1""" oResult = oSQL_Command.executeQuery(stSql) While oResult.next sValue = oResult.getString(1) & "," & oResult.getString(2) Write #iCount,sValue Wend Close #iCount End Sub
See as well for eg. the attached file
However, on my case (not exactly the same as the attached file), I have some cell in my table where there are more than 1 line (I deliberately input newline characters). In those case, only the first line in the cell is reproduce during the write process.
Of course a function on type “write2csv” could be very useful, but at least, this can do some part of the job for some, hopefully.
[EDIT] - 2021-02-02
I’ve been through the different options and files submitted below in the answer by Ratslinger.
Eventually, I wrote a macro who doesn’t need to “go out” of the user form within the ODB (ie. it does not need for the user to open a specific ODS file but s/he stays within the Base application), nor to pre-create an ODS file, nor attach macro to it.
It uses in addition the macro and form built by Ratslinger of the below QueryToCSV.odb file below which allow to load the embedded queries of the ODB file in a list box.
In the following is what the updated macro does:
- get the name of the selected query in the form list box.
- get the name of the ODB file and check if registered in LibreOffice using this post (if not, the sub is ended and the user explained how to register it)
- load a new blank ODS file (source post, create a range (source doc here and import the query result in it
- export the file with arguments for CSV saving
- close (ie. delete because it was not saved) the ODS file
Here is the single file: QueryToCSV.odb
The only requirement from the user is to register the file as a database source in LibreOffice.
I’ve been testing it on my computer (Debian 10 - LibreOffice 22.214.171.124 - english), and on a virtual machine (Mageia 7 - LibreOffice 126.96.36.199 - french).
In addition, I could fix the mentioned problem of the printing of the first line only of multilines values (which is not in the example files, but on a personal working file).
These ones are originally computed thanks to a
LIST() Firebird SQL function, using an ASCII_CHAR(13) (newline) separator.
It could be solved by converting the
LIST() result in a
VARCHAR() type, eg:
CAST(LIST("my_table"."my_field", ASCII_CHAR(13)) AS VARCHAR(400)) AS "my_modified_field"
(400 has been taken arbitrarily).
Hoping this can be useful for someones.
[EDIT] - 2021-02-03
In order to deal with the registration issue (ie. base checking on URL matching better than registered name), I’ve attached a function (inspired by answer here), which is called within the main macro and simply produces as a result either a blank string if the file is not registered (then shows the message to explain how to register, and ends sub), or the name the ODB file is registered with (this may be different from the ODB file name, indeed).
Tested with registration, no registration, and registration with a different name from the ODB file. All worked.