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
Follow this workflow:
Open QGIS, load your Shapefile layer, and select it (i.e., make it active) in the ToC.
Open the QGIS Python console.
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 == NULL or uv == QDateTime() or uv == QDate() or uv == '': 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.
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)