How to delete empty fields from a Shapefile with QGIS or ogr2ogr?

Question

I have a Shapefile with a lot of empty columns but many of them are empty. Using QGIS or ogr2ogr, how do I remove all attribute columns where everything is NULL?

Answer 1

Follow this workflow:

1. Open QGIS, load your Shapefile layer, and select it (i.e., make it active) in the ToC.

2. Open the QGIS Python console.

3. Copy the following code snippet and paste it in the QGIS Python console:

    from qgis.PyQt.QtCore import QDate, QDateTime
   
    lyr = iface.activeLayer()
    lstDelete = []
   
    for idx in lyr.dataProvider().attributeIndexes():
        uv = list(lyr.dataProvider().uniqueValues( idx ))
        if len( uv ) == 1:
            if uv[0] == NULL or uv[0] == QDateTime() or uv[0] == QDate() or uv[0] == '':
                lstDelete.append( idx )
   
    if not lyr.dataProvider().capabilities() & QgsVectorDataProvider.DeleteFeatures:
        print "This layer provider does not support deleting attributes."
    else:
        lyr.dataProvider().deleteAttributes( lstDelete )
        lyr.updateFields() # Update the layer structure
   

This should delete all your empty fields for the active layer.

Answer 2

You can do it rather handy with ogr2ogr and ogrinfo. Solution is only partial because it does not check when it drops the column if it is empty or not. If you ask to drop a non-empty column it will be dropped.

Test with a shapefile which has 3 features. Column "empty" contains only nulls. This can be verified with ogrinfo. Here are two tests, first proves that there are nulls, the second proves that there are only nulls.

ogrinfo -so alter.shp -sql "select * from alter where empty is null"
INFO: Open of `alter.shp'
      using driver `ESRI Shapefile' successful.

Layer name: alter
Geometry: Point
Feature Count: 3

ogrinfo -so alter.shp -sql "select * from alter where empty is not null"
INFO: Open of `alter.shp'
      using driver `ESRI Shapefile' successful.

Layer name: alter
Geometry: Point
Feature Count: 0

What is not self-evident is that it is possible to update data with SQL by using the ogrinfo tool. Here are three commands: ogrinfo before update, ogrinfo command that drops a column and finally second ogrinfo command that proves that column "empty" really disappeared.

ogrinfo -so -al alter.shp
...
non_empty: String (1.0)
empty: String (1.0)

ogrinfo alter.shp -sql "alter table "alter" drop column "empty""
INFO: Open of `alter.shp'
      using driver `ESRI Shapefile' successful.

ogrinfo -al -so alter.shp
...
non_empty: String (1.0)