Ask Your Question
1

Base - Export data of a stored query in a csv file within a macro

asked 2021-01-19 14:51:02 +0100

greb gravatar image

updated 2021-02-03 14:20:26 +0100

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 ... (more)

edit retag flag offensive close merge delete

Comments

@greb,

Have working model in Ubuntu 20 with LO v7.0.4.2 but it still uses Calc to process (user need not touch). Base file needs to be registered. Is this of interest?

Ratslinger gravatar imageRatslinger ( 2021-01-21 04:19:00 +0100 )edit

@Ratslinger,
Thanks for offering. Indeed, I would have preferred a "direct" way, but still, this can be useful.
I will just need to prepare a clear procedure document for the end-user (but there will be mainly only one single person in charge of this, so it could be an option).

greb gravatar imagegreb ( 2021-01-21 08:19:57 +0100 )edit

@greb,

Unfortunately I may have been too quick with my offer. Because of your concern, I tested today in Win 10 and there was a problem. Have not been able to yet resolve and will continue looking for an answer to your situation. Do not want to publish something which doesn't completely work. If not working in Win 10 the problem may also be hiding in Linux.

Ratslinger gravatar imageRatslinger ( 2021-01-21 21:01:39 +0100 )edit

@Ratslinger,
It's ok, no problem. Anyway, thanks for the updated information.
Eventually, I will opt for the "easy" graphical solution (copy query, paste in a Calc spreadsheet and save as CSV), which is actually not so tricky to explain in the application documentation.

greb gravatar imagegreb ( 2021-01-24 18:12:48 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2021-01-25 20:31:10 +0100

Ratslinger gravatar image

updated 2021-01-28 06:27:54 +0100

Hello,

Edit 2021-01-27:

Have replaced all the samples. Changed the csv file names to be the Registered DB name plus the query name. In the standalone (Calc only) added log in when needed for DB's. This will be asked for twice - once to fill the list box (any time list box is to be filled) and once on the first creation after a list box selection. Subsequent runs do not require further sign-in's. Eliminated some unnecessary code.

End Edit

Have found the Windows problem noted in my comment. It is more than Windows. It is a bug which can be avoided and no further concern here. Keep adding to this project but feel it is time for you to test. Can be a nice tool for generating csv files through Base.

Have attach two files below. Place both of these in the same directory. The macro in the Base file will look for the Calc file in the same directory it is in. The name of the Calc file needs to be (based on macro code used) QueryToCSV.ods. The Base file can be named whatever you want. It must be registered with the name QueryToCsv (based upon current macro code).

Operation is simple. Open the form QueryToCSV. There is a button on the form Load Query List. Select and a macro will fill the list box with all queries in the Base file. Using the list box, as soon as an item is selected, the macro is fired off opening the Calc file (seen for a moment & then minimized), then calls the macro in the Calc file with parameters (query and registered DB names) to load the data for the query and create the csv file (same directory as the Calc file). The Calc file is cleared of needing to be saved and the file is closed.

Another csv can then be run. If the same is to be run, reload the list using the push button. Files with the same name are overwritten with each new execution.

Samples (replaced 2021-01-27:

Calc ------ QueryToCSV.ods

Base ----- QueryToCSV.odb

For use in your setup, the Calc file, as already noted, is to be in the same directory as the Base file. The name stays as QueryToCSV.ods. No other changes are needed. You can change the file to not Visible on opening instead of minimized. That is in the macro runMinimised. This sub is attached to the Open Document event of the file. Just remark out the item(s) you wish not to run.

For the Base file, Copy and paste the form QueryToCsv. The external name is not important. Register you Base file. Create a basic module under Standard named Export. Copy the macros in the Base sample and paste in your file. In line #25 of the code, replace QueryToCSV with your registered name. This is a string. Insure the quotes are there.

Save & test.

Have tested this (not extensively) using LO v7.0.4 ... (more)

edit flag offensive delete link more

Comments

@Ratslinger,
Thank you very much for your time and this much documented solution!
I'll have a deeper look and test it during the week, and give feedback.

greb gravatar imagegreb ( 2021-01-25 22:53:41 +0100 )edit

@Ratslinger, indeed very useful, thanks for the research, will test with HSQL a little later and put into my Lo KB. Apropos github,(probably should be a separate post) Does git handle odbs OK for versioning frontends?

gkick gravatar imagegkick ( 2021-01-25 23:08:30 +0100 )edit

@gkick,

Don't have answer for your question.

Ratslinger gravatar imageRatslinger ( 2021-01-25 23:11:32 +0100 )edit

Had a new thought which should not affect this answer but may make the processing easier in the future. Will start looking today at placing all functionality into the Calc file. To be able to a get list of all registered databases. Then once one is selected, list queries available within the selected DB. Once that is selected create the csv file. May take some time finding access to required data.

Ratslinger gravatar imageRatslinger ( 2021-01-26 17:35:18 +0100 )edit

Latest post uses just one file - Calc. It does not accommodate Base files attached to databases requiring user name and/or password. Will look into that if this seems to be a worth while item.

Ratslinger gravatar imageRatslinger ( 2021-01-27 03:59:07 +0100 )edit

@Ratslinger,
Thank you very much for all this review and solution work!
Both solution are working very well.
On my side, not yet sure, but I think I will stay on the Calc (2nd) solution. Indeed, the first solution (form in Base + automatic Calc spreadsheet opening and saving) requires to provide the registered database name within the query. Which may be done at the very end of my work (when database will be finalized and deployed to the partner), but which may be a bugging point during all the working process before.
Or maybe is there a way to automatically check/register the database in local user LibreOffice directly within the macro ? I've seen that maybe the com::sun::star::container Module may be involved in the database registration (but I may be wrong), but this is still far too far from my Basic knowledge to handle this…

greb gravatar imagegreb ( 2021-01-28 11:21:38 +0100 )edit

@Ratslinger,
Another point, but which I think is very specific to my query: two columns of my query result in N/A values.
One of them is the one with multiple lines values, but as this was working well with your attached files, I think it's something else.
And the characteristic in common is that these two columns have been generated with a LIST Firebird SQL function.
Note: when I select the query (Queries tab in LibreOffice Base interface), right click, copy, and paste in a Calc spreadsheet, everything is pasting correctly (ie. no N/A values and correct multilines cells).

greb gravatar imagegreb ( 2021-01-28 11:32:37 +0100 )edit

@greb,

Auto registration is problematic not because of code but rather the many circumstances it presents. Will look as possibly using URL but not clear this is viable.

As for the LIST situation, will try to look at this and see where there may be a problem. The difference is your copy & paste vs using internal connection provided through LO. Not much control in that.

Ratslinger gravatar imageRatslinger ( 2021-01-28 19:18:01 +0100 )edit

@greb,

Don't know what the answer is for your List field situation. In my test the new line is accomplished on a form with Ctrl + Enter. Yours is most likely using ASCII_CHAR(10). Don't know how to get around that. There is no method to have Ctrl in the SQL - not an ASCII character. Just using a new spreadsheet and datasources, the query with List returns N/A.

One thought is that it is a bug. Since this works in a Base query and can display on a form and can copy/paste query to Calc OK, possibly the internal LO connection is not translating correctly.

Ratslinger gravatar imageRatslinger ( 2021-01-28 20:23:43 +0100 )edit

@Ratslinger,
Ok, noted for auto registration. Alternative may so just be to add a small text to explain how to register if the target database is not in the list. End user can do it quickly, easily.
For the LIST issue, indeed, I'm using ASCII_CHAR(10). Ok, noted for the bug, I'll try to see how to work around.
Anyway, thank you very much for all your time, solution and comments!

greb gravatar imagegreb ( 2021-01-28 20:39:20 +0100 )edit
0

answered 2021-01-20 01:48:42 +0100

gkick gravatar image

Hi,

Not familiar with Firebird, however would assume the db supports text tables. If it does you might want to try the following

save your query as a View then embed Firebird flavor SQL of HSQL code as below in a macro

adapted from https://forum.openoffice.org/en/forum...

Create Text Table "vReport_txt" AS  (Select * FROM "tblOnline") WITH NO DATA;
SET TABLE "vReport_txt" SOURCE "vReport_txt.csv";  --  could also add other specs if needed
INSERT INTO "vReport_txt" (Select * From "tblOnline" );
SET TABLE  "vReport_txt" SOURCE OFF;
DROP TABLE "vReport_txt" IF EXISTS;

Tested with WIN 10 LO6.4.7 HSQL 2.51 (split db) In a split db HSQL saves the csv in the database folder - you may need to check out the firebird doco.

edit flag offensive delete link more

Comments

@gkick,

Text tables are not available in Firebird.

Ratslinger gravatar imageRatslinger ( 2021-01-20 04:02:59 +0100 )edit

@gkick and @Ratslinger,
Sorry for the delay of my answer, and thanks for your suggestion @gkick and for the Firebird precision @Ratslinger.

greb gravatar imagegreb ( 2021-01-21 08:16:24 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-01-19 14:51:02 +0100

Seen: 125 times

Last updated: Feb 03