An example to update geonode metadata using a excel file

excel.py

"""
Utilities for reading Microsoft Excel files.
"""
#requires
#pip install xlrd

import xlrd
import datetime

class ExcelDictReader(object):
    """
    Provides an API that lets you iterate over every row in an Excel worksheet,
    much like csv.DictReader. This assumes that the worksheet is a simple table
    with a single header row at the top.

    header_row_num is the zero-indexed row number of the headers. (Note that
    you can specify the headers manually by using the "custom_headers"
    argument.)

    start_row_num is the zero-indexed row number of where the data starts.

    use_last_header_if_duplicate, either True or False, dictates the behavior
    to use in the case of duplicate column headers. If True, then the *last*
    column's value will be used. If False, then the *first* column's value will
    be used. Note that there's no way to access the other column, either way.

    custom_headers, if given, will be used instead of the values in
    header_row_num. If you provide custom_headers, the value of header_row_num
    will be ignored.

    Example usage:
        reader = ExcelDictReader('/path/to/my.xls', 0, 0, 1)
        for row in reader:
            print row

    This yields dictionaries like:
        {'header1': 'value1', 'header2': 'value2'}
    """
    def __init__(self, filename, sheet_index=0, header_row_num=0, start_row_num=0,
            use_last_header_if_duplicate=True, custom_headers=None):
        self.workbook = xlrd.open_workbook(filename)
        self.sheet_index = sheet_index
        self.header_row, self.start_row = header_row_num, start_row_num
        self.use_last_header_if_duplicate = use_last_header_if_duplicate
        self.custom_headers = custom_headers

    def __iter__(self):
        worksheet = self.workbook.sheet_by_index(self.sheet_index)
        if self.custom_headers:
            headers = self.custom_headers
        else:
            headers = [v.value.strip() for v in worksheet.row(self.header_row)]
        for row_num in xrange(self.start_row, worksheet.nrows):
            data_dict = {}
            for i, cell in enumerate(worksheet.row(row_num)):
                value = cell.value

                # Clean up the value. The xlrd library doesn't convert date
                # values to Python objects automatically, so we have to do that
                # here. Also, strip whitespace from any text field.
                # cell.ctype is documented here:
                # http://www.lexicon.net/sjmachin/xlrd.html#xlrd.Cell-class
                if cell.ctype == 3:
                    try:
                        value = datetime.datetime(*xlrd.xldate_as_tuple(value, self.workbook.datemode))
                    except ValueError:
                        # The datetime module raises ValueError for invalid
                        # dates, like the year 0. Rather than skipping the
                        # value (which would lose data), we just keep it as
                        # a string.
                        pass
                elif cell.ctype == 1:
                    value = value.strip()

                # Only append the value to the dictionary if
                if self.use_last_header_if_duplicate or headers[i] not in data_dict:
                    data_dict[headers[i]] = value

            yield data_dict

fixmetadata.py

#!/usr/bin/env python
from django.core.management import setup_environ
from geonode import settings
# setup the environment before we start accessing things in the settings.
setup_environ(settings)

from excel import ExcelDictReader
from geonode.maps.models import Layer
from django.template.defaultfilters import title

INDEX_FILE = '/home/geonode/tmp/haitiindex.xls'


def update():
    total = 0
    updated = 0
    found = 0
    reader = ExcelDictReader(INDEX_FILE, 0, 0, 1)
    for row in reader:
        total += 1
        try:
            # try to get the object first to trigger the DoesNotExist exception
            layer= Layer.objects.get(name=row["name"])
            print "Found %s, proceeding with update" % row["name"]
            found +=1
            layer.title = title(row["title"])
            layer.save()
            updated += 1
        except Layer.DoesNotExist:
            print "Could not find %s" % row["name"]
        except Exception:
            print "Something bad happened with %s" % row["name"]

    print "There were %d total layers, %d of them were in GeoNode\
           and %d of them were succesfully updated" % (total, found, updated)

if __name__ == "__main__":
    update()

output.txt

Found hti_riv_Topo50_pl, proceeding with update
Found hti_hydroname_TNC_pt, proceeding with update
Found hti_lak_uk_pyp, proceeding with update
Found hti_wetlands_TNC_py, proceeding with update
Found hti_sea_py, proceeding with update
Found Cote, proceeding with update
Found hti_cont50m_srtm_pl_Project, proceeding with update
Found hti_topo_TNC_pt, proceeding with update
Could not find hti_natural_osm_plp
Could not find htidom_coast_nga_proto_ln
Could not find htidom_coast_nga_utm
Could not find htidom_coast_noaa-gshhs_ln
Could not find htidom_coast_noaa-gshhs_utm
Could not find htidom_coast
Could not find Hti_EQ_epic20Jan2010p
Could not find 0ts_UTM, 1ts_UTM, 2ts_UTM, 3ts_UTM, 4ts_UTM, 5ts_UTM
Could not find curves20n
Could not find AVHRR_1km_LANDCOVER_1981_1994.GLOBAL1.img
Could not find HT_AVHRR_1Km.img
Found HT_landuse_UTM, proceeding with update
Could not find ht_lanuse_UTM
Found Roads_LL, proceeding with update
Found Roads_UTM, proceeding with update
Could not find ppt_utm
Found station_LL, proceeding with update
Found station_UTM, proceeding with update
Found Grand_Bassin_Versant, proceeding with update
Could not find couche_géologique
Found country, proceeding with update
Found Limites_Communes, proceeding with update
Found Limites_Departements, proceeding with update
Found Limites_Sections_Communales, proceeding with update
Could not find Carte_Topo50_Haiti.img
Could not find Carte_Topo50_Haiti.tif
Could not find Carte_Topographique_Bloc1_A.tif
Could not find Carte_Topographique_Bloc1_B.tif
Could not find Carte_Topographique_Bloc1_C.tif
Could not find Rivière
Could not find Localité
Could not find localité_princ_sec
Found Zone_Propice_Inondation_100000, proceeding with update
Could not find Géomorphologie
Found Occupation_98, proceeding with update
Could not find Potentialité
Found Route, proceeding with update
Found Erosion, proceeding with update
Found Landslides_UTM, proceeding with update
Found Licuefact_UTM, proceeding with update
Found localisation_gliss, proceeding with update
Found menace_exceptionnelle_region, proceeding with update
Found menace_frequente_region, proceeding with update
Found menace_rare_region, proceeding with update
Found PFloodAreas, proceeding with update
Found zone_propicie_inondation_100000, proceeding with update
Could not find Secheresse
Could not find auter_houlef
Found 20100224_OCHA_shelter_sites, proceeding with update
Found camps_21100305F, proceeding with update
Found implantation_camps_pap, proceeding with update
Found health_facilities1_Project, proceeding with update
Could not find intensity2500_Project
Could not find intensity500_Project
Could not find imn500
Could not find imn2500
Could not find dem_H30.tif
Could not find SLOPE30.tif
Could not find rrel30.tif
Could not find fhum30.tif
Could not find flito30.tif
Could not find fpend30
Could not find frrel30.tif
Could not find IMM500.tif
Could not find gliss_MV.tif
Could not find intensity500
Found Litho_fact, proceeding with update
Found Chef_Lieu_Communal, proceeding with update
Found Chef_Lieu_Departemental_Haiti_font_point, proceeding with update
Found fleuve, proceeding with update
Found INONDA, proceeding with update
Found Principaux_Bassins_Versants, proceeding with update
Could not find menace_int1.tif
Could not find menace_int.tif
Could not find psus_glisse.tif
Could not find psismi1.tif
Could not find plic1.tif
Could not find pinonda1.tif
Could not find paut_houl1.tif
There were 87 total layers, 40 of them were in GeoNode           and 40 of them were succesfully updated