Help with naming CSV exported by macro from base

[GNK]

Hi - I have tried to modify this macro (harvested from the LOhivemind) so that the names of exported CSV will include the result of a query (ie “NUMBER”) so that exports of same query/report can be given differnt file names bassed on values in another table (ie “TABLE”) - i naively tried the follwing modification but dont know how to pass the SQL result (always a single integer value from a 1 row table) to the file-naming part of the macro :/;

I would really apreciate some help to fix it! (LoveYouLibre:)

SUB Export_OBJECT_REPORT_to_csv (oEvent AS OBJECT) 

	DIM oDB AS OBJECT
	DIM stUrl AS STRING
	DIM arg(1) AS NEW com.sun.star.beans.PropertyValue
DIM NUMBER as Integer
	oDB = ThisComponent.Parent
	stDir = Left(oDB.Location,Len(oDB.Location)-Len(oDB.Title))
	stReport = "OBJECT_REPORT" REM or  oField.Tag for button
	oReport = ThisDatabaseDocument.ReportDocuments.getByName(stReport).open
	arg(0).name = "FilterName"
	arg(0).value = "Text - txt - csv (StarCalc)"
	arg(1).name = "FilterOptions"
	arg(1).value = "59,39,0,1,,0,false,true,true,false"
	
stSQLstatement = ("Select ""NUMBER"" from ""TABLE"" ")
oResult = oStatement.executeQuery(stSQLstatement)
NUMBER= oResult.getInteger(1)

stUrl = stDir & "OBJECT_REPORT_"   &   NUMBER   & "_Q.csv"

	oReport.storeToURL(stUrl, arg())
	oReport.Close(True)

END SUB

[RobertG]

Don’t know what you are doing with a report there, but this will help to get the first value of the query.

    oResult = oStatement.executeQuery(stSQLstatement)
    oResult.next
    NUMBER= oResult.getInteger(1)

If there are more rows you get all results by

    oResult = oStatement.executeQuery(stSQLstatement)
    WHILE oResult.next
       NUMBER= oResult.getInteger(1)
    WEND

[GNK]

Thank you Robert! - i was missing 2 lines >

oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
…and…
oResult.next

(… and also had “as Integer” instead of “as Int” …)
PS/
The LO Report is needed because (as far as I can tell) Firebird DB (or at least as embeded in LO) has no native syntax for csv export , unlike HSQL which has “text table”…

The underlying query poduces different versions(data-sets) for the report dependent on a contextually defined global variable …the differnt data-sets end up as data linked to different calc sheets that auto-update, so need individual csv files - rather than have many ‘hard-coded’ repeated code-sets e.g. “Qv1>Report1>macro1>csv1 , Qv2>Report2>macro2>csv2, Qv3>Report3>macro3<csv3 …” etc i wanted a single macro and a single report.

LoveYouLibre!

[Villeroy]

By the way and slightly off topic. There is a way to export csv from HSQL easily without a single line of macro code.

EDIT: OK, it’s a Firebird. I don’t know Firebird.
However, you can link any record set of any database to a Calc range and save that as csv.

oView = ThisComponent.getCurrentController()
oDBR = ThisComponent.DatabaseRanges.getByName("Import1")
oSh = oDBR.getReferredCells.getSpreadsheet()
oView.setActiveSheet(oSh)
REM and then export csv with appropriate filter options

[GNK]

this got me what i needed - thanks!

SUB Export_OBJECT_TABLE1_Q_to_csv (oEvent AS OBJECT) REM needs REPORT also !  (and form button 'additional info' with report name )
DIM oDB AS OBJECT
DIM stUrl AS STRING
DIM arg(1) AS NEW com.sun.star.beans.PropertyValue

oDB = ThisComponent.Parent
stDir = Left(oDB.Location,Len(oDB.Location)-Len(oDB.Title))
stReport = "OBJECT_TABLE1_Q" 
oReport = ThisDatabaseDocument.ReportDocuments.getByName(stReport).open
arg(0).name = "FilterName"
arg(0).value = "Text - txt - csv (StarCalc)"
arg(1).name = "FilterOptions"
arg(1).value = "59,39,0,1,,0,false,true,true,false"

oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
stSQLstatement = ("Select ""TABLE"" from ""LOCAL"" ")
oResult = oStatement.executeQuery(stSQLstatement)
oResult.next
NUMBER= oResult.getInt(1)

stUrl = stDir & "OBJECT_REPORT_" & NUMBER & "_Q.csv"

oReport.storeToURL(stUrl, arg())
oReport.Close(True)

END SUB

[GNK]

ps/ the macro is not mine …it uses a button on a form to inititate the macro (push button) and as the Parent … so for others to use;

it needs an LO Report (‘stReport’) and for the name of that report to be included in the ‘Additional Information’ section of the PushButton controls (the form name is not important)

the line: arg(1).value = “59,39,0,1,0,false,true,true,false” sets csv options > when i find the link to the StarOffice documentation page again i will try to remember to add it here

EDIT: i now noitice the “Additional Info” step mentioned above is not nessescary … but the pop-up is still useful

[RobertG]

Here a hint for the csv-options:

First Numbers are for ASCII-characters.
‘59’ is the separator ;
‘39’ is the mask for Text with'
0 | 1 | 0: system character set is used | first row of data | No specific form of columns (1 will be automatic, 2 will be text)

[Ratslinger]

Hello,

Good you have gotten an answer to your question. I add this to make the next need of a Base file to CSV easier. It uses a Calc file and registered databases. You can output a csv file from any Query of a registered Base file. Queries are more flexible with what may be output. You can easily create a query for a table:

Select * from "MY_TABLE"

My answer here Base - Export data of a stored query in a csv file within a macro . There are a couple of other variations in that post.

That answer also contains the link to the Wiki you note. Additionally it appears that the only reason for you to use a report for the process is to access the csv processing in Calc.

Attached is a slightly improved version of the Calc fie:
RegisteredToCSVModified.ods (14.8 KB)

The macros are easily modified for changing the output directory (currently location of Calc file) and the csv options.

[GNK]

Thank you (again) for the information Ratslinger!

Actually I already found your earlier .ods/.odb on the interweb (+ many others - much thanks LibreHive!)
I already have your .ods as a stand alone button-link within this set of forms, precisely for ‘universal export method’.

For the main defined usage in this aspect of my project (which embeds a ‘data-log-to-charts’ workflow) I wanted a link to a single query, rather than than the full query list (the database as a whole has a ‘large’ number of queries dealing with different types of data within broad archaeological-museum-survey-photogrammetery projects)

The data is prepared from a ‘tuple-like object-list’ in the .odb, with a method to populate ‘blank’ .ods templates with columns derived from user defined attributes in the object-list (using string-searches of concatenated ‘free-form’ attributes, essentially populated from combo-boxes controlled by queries+ filter tables) - these attributes can also be created and added to as part of a data-logging process by a user. Attributes can be ignored or variously combined to create working-sets (using Form controls) prior to export to a spreadsheet where templates for graphs and figures (analytical and publication) are prepared from the different user defined ‘object-sets’ (eg ‘bone_’, ‘large_mammal’, ‘pig_tooth’, ‘ceramic:TF48’,’flint-blade:10cm’ etc etc) , for different ‘project-sets’ ( eg sites, areas, trenches, test-pits, years, investigators etc… )

I had a look to see if I could limit the query list in Ratslingers’ macro (or specify a single or short list in some way) … but alas, I know too little

[thinking about it, various parts of this ‘wide’ database could be reconfigured as separate linked .odb projects, rather than as data-sets within a single .odb - this would bundle only the relevant structures to devices (or software instances), and data archived/complied from across the various .odb’s at a latter stage, e.g. data-loggers vs report-writers, image-archives vs specialist analysis…I am not sure if that approach would be overall ‘better’ beyond the save-times/file-sizes of individual .odb - but I hope that illustrates the the kind of task I was trying accomplish !]

LoveYouLibre!

[edit: it occurs to me that i could change approach and generate a query with another macro, that uses the string variables (from combo-boxes) for column names derived from user data, and maybe tick-boxes to define AND/OR operators > this could then use something like Ratlingers macro for export with a user-defined query name…however since i actually want sets of csv exports with regular names (so that they are pre-linked and updated on .ods template, or can be found in easily within filesystem) i am not sure this level of functionality is best … at present the export ‘filters’ are essentially special objects in the object-list, using the same accumlated attributes from combo-boxes/queries to define the catagory/attribute-sets for export(eg for figure and graph making) that were used initially when logging the data ‘in the field’]