Text tables are not available in Firebird.
@gkick and @Ratslinger,
Sorry for the delay of my answer, and thanks for your suggestion @gkick and for the Firebird precision @Ratslinger.
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.2 on Ubuntu 20.04. Also tested on Windows 10. Used both Firebird embedded (your sample) and a PostgreSQL database. Others should also work.
Most tested files were generated in very short order. Did run from the PostgreSQL file an 81,000+ record table (20+ fields). The file was created in less than 25 seconds. During that time the Calc file was visible but blank. However all processed without a problem.
Would appreciate your feedback on this as it seems to be a fairly useful tool to create csv files from a database. Have made one of the table records last names a multi-line entry. Seems to work.
Have used bits and pieces of my code and code from others to create this. Can’t even say where every line came from.
Here is a link for csv arguments (calc macro) → Filter Options
A couple of other links used:
Have used this before but just forgot. This helped jog my memory:
Edit 2021-01-26:
Here is a single Calc file to process queries to csv. When the document is opened the registered DB names are retrieved and fill the list box. When an item is selected, it accesses the registered DB gets the available queries and fills the Query list box. The push button runs the process and the csv file is created in the same directry as where the Calc file resides.
Replaced 2021-01-27
Sample ----- RegisteredToCSV.ods
It may need more in the way of error checking but seems to work well.
1 Like
@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.
@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?
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.
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,
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…
@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).
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.
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,
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!
@Ratslinger,
Have solved the LIST field issue, using a CAST function, see my question post.
As well, have sorted out the CSV export using only ODB macro, mainly using what you submitted in the 2 options of your answer.
Thank you very much again, it was a great guide!
Thank you for the sample posted. Will look a bit closer at this today. However I do see a problem with your checking if the database is registered. The registered name is not good for this verification. A database file name is not necessarily the registered name. The registered name can be whatever you want. You cannot have two registered items with the same name even if they are in different locations. The important issue here is the location of the registered Base file. Even if the name is justified (which it is not) if the file was moved, that registration is no longer valid. It need to be registered again and the old should be removed.
See my answer in this post → BASIC+Firebird: How to list non-registered .odb’s ?
Edit:
All else seems to work fine. Only issue I see is the registration.
@Ratslinger,
Thanks again for feedback. Indeed, the ODB file registration checking was problematic and due to error.
I’ve modified it (see today’ edit in my initial post), using indeed the post you referred to.
Thanks.
This Calc macro is very useful RegisteredToCSV.ods - but how would I edit the macro to change the delimeter from “,” to “|”?
Thanks.
@MyGrove
There are comments in the code and one leads to this post → Filter Options.
.
There you find that the Field Separator is the first token. In the original code this is a 44 for a comma. If you want to change it to a pipe, the code is 124.
.
Therefore, in Sub Sheets_To_CSV , change this line:
args(1).Value = "44,34,76,1"
to:
args(1).Value = "124,34,76,1"
This was tested and works.
1 Like
Brilliant, thank you.
Edit:
The below response answer a question received directly by mail, mentioning an error:
A Scripting Framework error occurred while running the Basic script Standard.Export.Query_To_CSV.
Message: wrong number of parameters!
while running the exporting to CSV macro in the QueryToCSV.odb file on a Windows computer.
I’m on Linux and have no problem to run the macro correctly.
I just try to run it in a Windows machine and indeed, got an error (but not the one mentioned with scripting framework). It says that the problem come from the line 116 of the macro file :
oDoc.storeToURL(sURL,args)
Querying what is the output of sURL variable, it seems that there is a problem, with duplication of the folder path in the url output :
file:///C:/Users/user/Desktop/file:///C:/Users/user/Desktop/QueryToCSV_Table1ALL.csv
which is coming from line 108. Modifying to:
sURL = sODBName & "_" & sQuerySelection & ".csv"
worked for me (ie. it saved the result of the query to a csv file).
I didn’t get the same error message as you, but I hope this might help?
Best.