Hello,
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 DataList() type?
Note: I’m using an embedded Firebird-LibreOffice database, on Debian 10, with LibreOffice 7.0.1.2 (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
Inspired from write page and this thread.
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 7.0.4.2 - english), and on a virtual machine (Mageia 7 - LibreOffice 6.4.7.2 - 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
Following what Ratslinger spotted out in his answer comment, here is the updated file: QueryToCSV.odb.
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.