How to automate removing trailing spaces in a Base text field?

[wehmann12]

How does one automate removing trailing spaces in a text field in a table? If the answer is use a Basic script, can you point me to something that would serve as an example? For reference, I am quite familiar with VBA in Excel.

As an example where I ended up with trailing spaces, I populated a Base table from a spreadsheet with integer numbers in one column (tree tag numbers). I chose Integer as the field type. Later, I joined this field to another that had numbers as text, in a different table. The join failed, because of the different data types. So I edited the table and changed from Integer to Text. Doing that I ended up with trailing spaces. Those trailing spaces caused join problems as well. To fix this, I copied the table, pasted it into Calc, and from there pasted back into Base, this time with that field as text. That got rid of the trailing spaces.

Why did I choose Integer as the field type, you might ask. If I chose to sort based upon that field, the result is “nicer” when that field is treated as an integer than when it is treated as text. Did I anticipate the join problems? No.

This experience makes me interested in how I might remove trailing spaces without leaving Base to do so. It might even motivate me to learn to use macros in Base.

[Ratslinger]

Hello,

While you can do a lot with macros in Base, there is a steep learning curve. The UNO API is vast and requires much reading and cross referencing. It is a good long term project. After 4+ years I have a bit of a handle on it.

I would recommend rather putting effort into SQL. Here is a table I have been using for your questions:

Data in column addr has various spaces at the fields end. This can be seen with a Query:

Now I can use an SQL function to remove those spaces and display the result:

You can use this as input to update the fields to eliminate the spaces.

Taking a direction such as this will produce results faster than trying to learn macros. Also be aware that SQL functionality can vary depending upon the database used. HSQLDB embedded is very old and Firebird embedded, because it is a more current version, will have much more functionality.

For specifics on SQL, best to refer to the database documentation for the version used. For example, here is link to HSQLDB v1.8 embedded (included currently with Base) → Hsqldb v1.8 User Guide

Edit:

Of course if you want more information on Base macros, see the answer in this post → To learn LibreOffice Base are there introductions or tutorials?. That will provide you with a good deal to start with.

[wehmann12]

I learned that I can do e.g. UPDATE “Table1” SET “descr” = RTRIM(“descr”) in my test table “Table1” which has field “descr” which has trailing blanks.

[mauricio]

If you used Firebird, try in Tools->SQL…

UPDATE "TABLE" SET "FIELD"=TRIM("FIELD")

The same with code:

Sub main()
	doc = ThisComponent
	conn = doc.DataSource.getConnection("", "")
	
	sql = "UPDATE ""contactos"" SET ""numbers""=TRIM(""numbers"")"
	cursor = conn.prepareStatement(sql)
	result = cursor.executeUpdate()
	doc.store()
	MsgBox result
End Sub

With Python

def main():
    doc = XSCRIPTCONTEXT.getDocument()
    conn = doc.DataSource.getConnection('', '')

    sql = 'UPDATE "contactos" SET "numbers"=TRIM("numbers")'
    cursor = conn.prepareStatement(sql)
    result = cursor.executeUpdate()
    doc.store()
    return