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.


4 Answers 4


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()  
        for feat in layer.getFeatures():
            f = feat.fields()
            num = f.count()
            for i in range(num):
                if feat[i] == '-99999997':
                    feat[i] = '0'
  • Column by column:

    def by_column():
        layer = qgis.utils.iface.activeLayer()  
        for field in layer.dataProvider().attributeIndexes():
            for feature in layer.getFeatures():
                attrs = feature.attributes()
                if attrs[field] == '-99999997':
                    feature[field] = '0'
  • layer = qgis.utils.iface.activeLayer() File "<input>", line 1 layer = qgis.utils.iface.activeLayer() ^ IndentationError: unexpected indent layer.startEditing() False for feat in layer.getFeatures(): f = feat.fields() num = f.count() for i in range(num): if feat[i] == '-99999997': feat[i] = '0' File "<input>", line 6 feat[i] = '0' ^ IndentationError: expected an indented block. Is what I get back.....
    – Willem
    Commented Feb 16, 2016 at 9:39
  • @Willem - Apologies buddy, forgot to indent after the if statement. Should work now =)
    – Joseph
    Commented Feb 16, 2016 at 10:08
  • 1
    thanks sorted it out myself although Im not a Python user, but know a little bit of javascript and PHP + internet. It is running now and takes a while and see a lot of true :-)
    – Willem
    Commented Feb 16, 2016 at 10:31
  • @Willem - Awesome, glad you found another solution! You should post it as an answer as it could be helpful to others =)
    – Joseph
    Commented Feb 16, 2016 at 10:35
  • Tried the script but after one hour or more it wasnt ready so I try to terminate the script. But there seems be some kind of loop. I had to close qgis. Tried it on a smaller file. But there wasnt any change in the values after the script ran.
    – Willem
    Commented Feb 16, 2016 at 14:36

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.

  • Thats what I did/ do allready with 'case when "P_ANT_ARU" is -99999997 then '0' else "P_ANT_ARU" end' what you suggest is the 'same' way and not what I asked :-) Ill give the Python code a try.
    – Willem
    Commented Feb 16, 2016 at 9:29

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.

  • Can be solved in QGIS.
    – Zoltan
    Commented Feb 14, 2016 at 17:29
  • 1
    As I understand, the author of the question has many attributes in his shapefile and wants to edit all of them at the same time. As far as I know, this can't be easily done in QGIS. That's why I suggested an external tool. EDIT: comment moved to the right section.
    – ArMoraer
    Commented Feb 14, 2016 at 18:15
  • It's very easy to break your shapefile doing this if the row order changes
    – Ian Turton
    Commented Feb 14, 2016 at 19:57
  • Yes I agree with @iant. Done that in my 'earlier days' with GIS and that went wrong.
    – Willem
    Commented Feb 16, 2016 at 9:32

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.