I need to perform a background task that will open an Access DB using ODBC from base, execute one of the Access select queries, and return the result set to a CSV file. Is this possible using Open Office? If so, where do I begin to look for help with the solution. I mean documents, I can figure it out If I get a jump on where to look.
Also, I need to do the above without MS Office installed. Can Open Offices' Base perform the required work without MS Office being installed?
Can I use the command line to open base and run a macro?
-
- Posts: 2
- Joined: Fri Dec 31, 2021 1:50 pm
Can I use the command line to open base and run a macro?
OpenOffice 4.1.11
Windows 10 Professional
Windows 10 Professional
-
- Volunteer
- Posts: 1547
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Can I use the command line to open base and run a macro?
If you are new to Base, I recommend using LibreOffice rather than OpenOffice, because the Base module in OpenOffice hasn't seen any updates for over a decade.
https://www.libreoffice.org/
You can assign a startup macro by selecting the following from the main Base menu:
Tools>Customize>Events>Open Document> Macro
and select the macro you want to run
When you first open Base select the Open existing database option
From the list select Microsoft Access to connect to the tables in a .mdb file
Or Microsoft Access 2007 to connect to the tables in an .accdb file.
You can query the tables, but it must be a Base query which may have slightly different syntax than an Access query, but if your familiar with SQL it shouldn't be too difficult to get working.
Base is a small front end for a HSQL database, and doesn't have any tools for exporting to CSV, however the HSQL back end can work directly with CSV.
To communicate directly with the HSQL back end select the menu option Tools>SQL. This bypasses the Base front end.
Here is an example, creating a text table with a few fields and connecting that table with a .csv file
When that has successfully Executed, in the Tables area of Base select View>Refresh Tables to inform the Base front end of the changes and your new table will be shown in the list of tables, of course with no data.
To populate the table Execute something like
Select Save and the data should be written into the .csv file.
The OO/LO API is very different from the Access API. It's quite complex and opaque , so this is the macro code I used in my test database named TestCSV
Each time I run it, a list of records are added to the csv file. Since this macro is highly dependent on the database, it is best to save the macro with the database, rather than a global library like My Macros.
A resource I used to get the csv connection
http://www.hsqldb.org/doc/1.8/guide/
Look here for macro programming information. There is a book that is a free download that is pretty much the bible on OO/LO macros, unfortunately it doesn't cover the Base module.
http://www.pitonyak.org/oo.php
https://www.libreoffice.org/
You can assign a startup macro by selecting the following from the main Base menu:
Tools>Customize>Events>Open Document> Macro
and select the macro you want to run
When you first open Base select the Open existing database option
From the list select Microsoft Access to connect to the tables in a .mdb file
Or Microsoft Access 2007 to connect to the tables in an .accdb file.
You can query the tables, but it must be a Base query which may have slightly different syntax than an Access query, but if your familiar with SQL it shouldn't be too difficult to get working.
Base is a small front end for a HSQL database, and doesn't have any tools for exporting to CSV, however the HSQL back end can work directly with CSV.
To communicate directly with the HSQL back end select the menu option Tools>SQL. This bypasses the Base front end.
Here is an example, creating a text table with a few fields and connecting that table with a .csv file
Code: Select all
create text table "MyTableName" (
"tdate" Date,
"Field1" VARCHAR(50),
"Field2" DECIMAL(10,2),
"Field3" VARCHAR(20)
);
SET TABLE "MyTableName" SOURCE "SomeFileName.csv;fs=,;ignore_first=true";
To populate the table Execute something like
Code: Select all
Insert into "MyTableName" ( "tdate", "Field1", "Field2", "Field3")
Select "Date", "FieldA", "FieldB", "FieldC"
FROM "NameOfAccessTable"
The OO/LO API is very different from the Access API. It's quite complex and opaque , so this is the macro code I used in my test database named TestCSV
Code: Select all
REM INSERT RECORDS INTO DATABASE TEXT TABLE
SUB UpdateCSV()
Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim Result
Dim strSQL As String
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("TestCSV")
Conn=DB.getConnection("","")
Stmt=Conn.createStatement()
strSQL= "INSERT INTO ""MyTableName"" ( ""tdate"", ""Field1"", ""Field2"" , ""Field3"") Select ""Date"", ""FieldA"", ""FieldB"", ""FieldC"" FROM ""NameOfAccessTable"" where ""FieldA"" = 'DITECH'"
Stmt.executeUpdate(strSQL)
Conn.close()
End Sub
A resource I used to get the csv connection
http://www.hsqldb.org/doc/1.8/guide/
Look here for macro programming information. There is a book that is a free download that is pretty much the bible on OO/LO macros, unfortunately it doesn't cover the Base module.
http://www.pitonyak.org/oo.php
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Posts: 2
- Joined: Fri Dec 31, 2021 1:50 pm
Re: Can I use the command line to open base and run a macro?
I will try this and post results. Probably will take me a while to process this information.
Thank you so much!!!
Thank you so much!!!
OpenOffice 4.1.11
Windows 10 Professional
Windows 10 Professional
Re: Can I use the command line to open base and run a macro?
Hello,
i tried the proposal from UnklDonald418, wasn't successfull, because the .csv, written by my HSQLDB, contained lot's of empty rows, when i opend it with an editor or Calc.
Any try to use e.g.CHECKPOINT DEFRAG didn't work on that .csv TextTable.
But the question was, if one could
First in words:
and the starbasic macro
R
i tried the proposal from UnklDonald418, wasn't successfull, because the .csv, written by my HSQLDB, contained lot's of empty rows, when i opend it with an editor or Calc.
Any try to use e.g.CHECKPOINT DEFRAG didn't work on that .csv TextTable.
But the question was, if one could
I think, i have found a solution for that, but it's a bit complicated.perform a background task that will open an Access DB using ODBC from base, execute one of the Access select queries, and return the result set to a CSV file.
First in words:
- a batch file containing the path to Commandline_extract_CSV.odb document, which is linked to an access database must be startet.
- the batch file asks for the input of a parameter, in my test it were the beginning letters of the surname from the persons of a contacts table
- after input of the letter/s the batch starts OpenOffice in background, and gives two parameters to Openoffice
- the path to Commandline_extract_CSV.odb
- the beginning letters of the surnames
- the local Office Suite contains a starbasic macro, named CreateCSV_ACCESS, located in macro library Standard, there in Module1.
- OpenOffice will now load the .odb file and the macro CreateCSV_ACCESS contains an area which does the parameter query on the database, opening an output file named OUT.CSV and writes this file.
Code: Select all
@echo off
set /p V1=enter first letters of Surname:
ECHO.
set sOfficepath=C:\Program Files (x86)\OpenOffice 4\program\soffice.exe
set sFilePath=C:\Users\<USERNAME>\Desktop\CommandLineCSV_Export\Commandline_extract_CSV.odb
REM the FilePath is given to the Starbasic Macro as a parameter, so OpenOffice will load the database in background
set sMacropath=macro:///Standard.Module1.CreateCSV_ACCESS(%sFilePath%,%V1%)
REM another way would be to load the database using this batchfile, but then OpenOffice will appear
REM "%sOfficepath%" "%sFilePath%" "%sMacropath%"
ECHO "%sOfficepath%" -invisible -norestore -nologo "%sMacropath%"
"%sOfficepath%" -invisible -norestore -nologo "%sMacropath%"
ECHO Ready, all rows with Surname, beginning with "%V1%" written to File OUT.CSV!
PAUSE
exit
Code: Select all
Sub CreateCSV_ACCESS(sUrl,sFilter)
sUrl = converttoUrl(sUrl)
dim args(1) as new com.sun.star.beans.PropertyValue
args(0).Name = "Hidden"
args(0).Value = True
args(1).Name = MacroExecutionMode
args(1).Value = 4
oComponent = StarDesktop.loadComponentFromURL(sUrl,"_blank", 0, args)
sURLFolder = Replace(oComponent.URL,oComponent.Title,"")
oDatasource = oComponent.Datasource
oConnection = oDatasource.getConnection("","")
oStatement = oConnection.createStatement()
sSQL = "SELECT `ID`, `Vorname`, `Nachname`, `Strasse`, `PLZ`, `Ort` FROM `T_Contacts` WHERE `Nachname` like '" & sFilter & "*'"
oResult = oStatement.executeQuery(sSQL)
nColumns = oResult.Columns.Count - 1
sURL = convertFromUrl(sURLFolder & "OUT.CSV")
f = FreeFile()
Open sURL for Output as #f
dim aRow(nColumns) as String
dim sRow as String
nCounter = 0
while oResult.next
for i = 0 to nColumns
aRow(i) = oResult.Columns(i).getstring
next i
sRow = join(aRow,";")
print #f, sRow
ncounter = ncounter + 1
wend
Close #f
oConnection.close
oComponent.close(true)
'stardesktop.terminate
End sub
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO