How to select and change only certain (same)values in attributetable in QGIS

Question

I have an attributetable. Spread all over it are numbers like -99999997. I want to replace them by zero (0). There a many columns in which there are such numbers. I have used the expression (an example with one column P-Ant_ARU)

case when   "P_ANT_ARU"  is  -99999997  then '0' else    "P_ANT_ARU"  end

for every feature in the column but I wonder if there is a faster way to use in the field calculator to get rid of all the -99999997 in one or perhaps two steps, because there are a lot of columns :-)

I have also tried toint(0) but that doesn't work because you have to choose a field, and I want a value to be changed. The same problem occurs with the plugin QuickMultiAttributeEdit.

So how can I select all those numbers in the whole table and make them zero.

Answer 1

Below are a couple of methods you could use in the Python Console which replaces all values in the Attribute Table of -99999997 with 0. As they are within a function, there shouldn't be anything printed in the console (incase this somehow freezes QGIS). After only limited testing, I found that changing attributes on a column by column basis was a bit faster.

• Row by row:

  def by_row():
      layer = qgis.utils.iface.activeLayer()  
      layer.startEditing()   
      for feat in layer.getFeatures():
          f = feat.fields()
          num = f.count()
          for i in range(num):
              if feat[i] == '-99999997':
                  feat[i] = '0'
                  layer.updateFeature(feat)
      layer.commitChanges()
  
  by_row()
  

• Column by column:

  def by_column():
      layer = qgis.utils.iface.activeLayer()  
      layer.startEditing()   
      for field in layer.dataProvider().attributeIndexes():
          for feature in layer.getFeatures():
              attrs = feature.attributes()
              if attrs[field] == '-99999997':
                  feature[field] = '0'
                  layer.updateFeature(feature)
      layer.commitChanges()
  
  by_column()
  

Answer 2

Make an attribute selection in the attribute table e.g.

P_MAROKKO = -99999997

Then use the field calculator, check Selected features only and the existing field and assign the value.

Answer 3

Open the .dbf file of your shapefile with Excel, LibreOffice Calc or something equivalent. Then, a simple "Search and Replace" should do the job.

Watch out the encoding format when opening and saving the .dbf file, though.

EDIT: Important note (following iant's comment): make a backup copy of your shapefile before manually editing the .dbf file, since this operation could break your shapefile.

Answer 4

After trying to use the Python script which didnt work I went to the next solution mentioned above: open the .dbf in Open Office Calc and with search/ replace. It was a matter of seconds when the results were ready. No problem at all with the shapefile, so that's what I will use. My first fears I think of using an external program to do the job where based on the use of Excel , Calc did the thing for me. For smaller amounts of data in one column I will use the _case when "P_ANT_ARU" is -99999997 then '0' else "P_ANT_ARU" end_ thing. Learned about Python though...