QGIS Geopackage Attribute Fields – How to Search and Replace in All Attribute Fields at Once

csvgeopackageqgisreplacestring

The context

I have a Geopackage file opened in QGIS. It was created by importing a CSV file. However, when importing, something regarding encoding went wrong: I have several special characters like Umlauts (Ä, Ö, Ü) that are not correctly recognized (like Ä for Ä). A possibility would be to fix this in the innitial CSV file and reimport. However, I already made several changes in the attributes, so I don't want to repeat the whole work.

The question

How can I search/replace all string fields in the attribute table to replace the relevant characters?

What I tried

What works is doing it on a filed-per-field basis, using Field calculator with this expression:

replace("fieldname", '[character to be replaces]', '[replace with character]')

As I have a lot of fields, I would like to to it for all fields at once. Exporting the attributes as CSV, changing it there and reimport is a risk as I don't want to create new confusions with delimiters, special characters etc. The data is assembled from different sources and this is no 100% consistent, so I already spent a lot of time harmonizing the data. Reimport a CSV is always tricky to be sure to have all fields in the right field data formats, defining the correct delimiters etc.

Best Answer

If you dont mind using a little PyQGIS. As usual when working with edit(layer) code, make a backup before experimenting.

layer = iface.activeLayer()
# Define replacements: First one is the character to replace and second one to be replaced by
replacements = {
    "a": "B",
    "Ä": "Ä`",
    "8%": "ü",
    "hello":"goodbye"
    }
with edit(layer):
    for feat in layer.getFeatures():
        for field in feat.fields():
            if field.type() == 10: # if string
                fieldvalue = feat[field.name()]
                for k,v in replacements.items():
                    fieldvalue = fieldvalue.replace(k,v)
                layer.changeAttributeValue(feat.id(), layer.fields().indexOf(field.name()), fieldvalue) # why isnt there a field.index() method????
Related Question