De Excel a sistema: validar y migrar perfiles de suelo

Casi toda entidad que levanta información técnica de suelos empieza igual: una hoja de cálculo por brigada, por año o por proyecto. Funciona mientras son diez archivos. Cuando son cien, nadie sabe cuál es la versión buena, el mismo perfil aparece dos veces con distinto pH y una coordenada cae en el océano. Este artículo describe cómo pasar esa información a una base de datos sin perder la trazabilidad, con un procedimiento que usted puede reproducir.

La experiencia viene de un sistema de gestión de información técnica de suelos que construimos para una entidad pública del orden nacional, donde la captura pasó de formatos dispersos a un modelo único con control de calidad por registro. Puede leer el resumen en el caso SGIA.

¿Qué se pierde cuando los datos viven en hojas de cálculo?

Se pierde lo que nadie ve hasta que hace falta:

• Identidad. El código del perfil se escribe a mano y se repite o se escribe distinto («P-012», «P012», «p 12»).
• Significado de los vacíos. Una celda vacía puede ser «no aplica», «no se midió» o «se olvidó». Para el análisis posterior son tres cosas distintas.
• Origen. Cuando un valor parece raro, no se sabe de qué archivo, hoja y fila salió.
• Control de cambios. Alguien corrigió una celda y no quedó rastro de quién ni cuándo.

Una migración que solo «copia y pega» a una tabla traslada todos esos problemas a un lugar más caro de arreglar.

¿Qué principios sigue una migración que conserva la trazabilidad?

Son cuatro y se aplican en este orden.

1. Nada entra sin validación. Cada fila pasa por reglas declaradas antes de tocar la base de datos.
2. Nada se corrige en silencio. Lo que falla se rechaza con el motivo y se devuelve al responsable del dato.
3. Todo lo que entra deja huella. Cada registro guarda archivo, hoja, fila de origen, huella del contenido, quién cargó y cuándo.
4. La carga es repetible. Ejecutarla dos veces con los mismos archivos produce el mismo resultado, sin duplicados.

¿Por qué validar antes de cargar y no después?

Cargar primero y limpiar después parece más rápido, pero tiene un costo oculto: los datos malos ya están en la base, alguien los consultó, un informe los usó y ahora hay que explicar por qué cambiaron. Validar antes de cargar mantiene la base como un lugar donde todo lo que existe cumplió las reglas, y deja los problemas en un informe aparte, fácil de repartir entre quienes levantaron cada dato.

Hay una ventaja adicional para la gestión del proyecto. El informe de rechazos convierte una discusión subjetiva («los datos están malos») en una lista concreta: cuántas filas, de qué archivos, por qué regla. Con esa lista el coordinador puede priorizar, asignar responsables y medir el avance semana a semana. Sin ella, la limpieza de datos se vuelve una tarea sin fin y sin dueño.

Finalmente, validar antes permite negociar las reglas con tiempo. Cuando un especialista ve que la regla del pH rechaza el diez por ciento de las filas, puede decidir si el rango es demasiado estrecho o si el dato realmente está mal. Esa conversación es mucho más barata antes de la carga que con la base ya en producción.

¿Qué reglas de validación conviene declarar?

Las reglas se escriben como una tabla, no como condiciones dispersas en el código. Así las puede revisar quien conoce el dominio (el edafólogo, el coordinador) sin leer Python. Estas son de ejemplo; los rangos deben ajustarlos los especialistas de su entidad.

Regla	Campo	Tipo	Condición	Acción
R01	perfil_id	Unicidad	No vacío, sin repetidos en el lote ni en la base	Rechazar
R02	fecha_muestreo	Fecha	Fecha válida, no futura, posterior a 1950	Rechazar
R03	latitud	Rango	Entre -4,3 y 13,5 grados	Rechazar
R04	longitud	Rango	Entre -79,1 y -66,8 grados	Rechazar
R05	profundidad_cm	Rango	Entre 0 y 500	Rechazar
R06	ph	Rango	Entre 2,5 y 11	Rechazar
R07	materia_organica_pct	Rango	Entre 0 y 100	Rechazar
R08	textura	Dominio	Valor de la lista controlada de texturas	Rechazar
R09	drenaje	Dominio	Valor de la lista controlada de clases de drenaje	Rechazar

Las coordenadas se validan primero contra un rectángulo que contiene al país. Es una barrera gruesa que atrapa los errores más comunes (signos invertidos, grados y minutos mezclados, latitud por longitud). Una validación fina contra el polígono oficial se hace después, dentro de la base de datos, con PostGIS.

¿Cómo se ve el script de validación y carga?

El siguiente script lee todas las hojas de los archivos de una carpeta, aplica las reglas, escribe el informe de rechazos y carga lo válido en PostgreSQL. Requiere pandas, openpyxl y psycopg 3. Está pensado como punto de partida: ajuste las columnas, los rangos y las listas controladas.

import hashlib
import sys
from datetime import date
from pathlib import Path

import pandas as pd
import psycopg

DOMINIOS = {
    "textura": {"A", "AF", "AL", "ArA", "F", "FA", "FAr", "FL", "L", "Ar"},
    "drenaje": {"excesivo", "bueno", "moderado", "imperfecto", "pobre", "muy pobre"},
}
RANGOS = {
    "latitud": (-4.3, 13.5),
    "longitud": (-79.1, -66.8),
    "profundidad_cm": (0, 500),
    "ph": (2.5, 11.0),
    "materia_organica_pct": (0.0, 100.0),
}
COLUMNAS = ["perfil_id", "fecha_muestreo", *RANGOS, *DOMINIOS]


def revisar_fila(fila):
    """Devuelve la lista de (campo, regla, valor) que la fila incumple."""
    fallas = []
    for campo, (minimo, maximo) in RANGOS.items():
        numero = pd.to_numeric(str(fila[campo]).replace(",", "."), errors="coerce")
        if pd.isna(numero) or not minimo <= numero <= maximo:
            fallas.append((campo, f"rango {minimo} a {maximo}", fila[campo]))
    for campo, permitidos in DOMINIOS.items():
        if str(fila[campo]).strip() not in permitidos:
            fallas.append((campo, "dominio", fila[campo]))
    fecha = pd.to_datetime(fila["fecha_muestreo"], errors="coerce")
    if pd.isna(fecha) or not date(1950, 1, 1) <= fecha.date() <= date.today():
        fallas.append(("fecha_muestreo", "fecha", fila["fecha_muestreo"]))
    return fallas


def leer_hojas(carpeta):
    for ruta in sorted(Path(carpeta).glob("*.xl*")):
        hojas = pd.read_excel(ruta, sheet_name=None, dtype=str)
        for nombre_hoja, tabla in hojas.items():
            tabla = tabla.reindex(columns=COLUMNAS)
            tabla["origen_archivo"] = ruta.name
            tabla["origen_hoja"] = nombre_hoja
            tabla["origen_fila"] = tabla.index + 2  # fila 1 = encabezado
            yield tabla


def main(carpeta, dsn, usuario):
    lote = pd.concat(leer_hojas(carpeta), ignore_index=True)
    lote["perfil_id"] = lote["perfil_id"].str.strip().str.upper()
    rechazos = []
    for i, fila in lote.iterrows():
        for campo, regla, valor in revisar_fila(fila):
            rechazos.append((i, fila["origen_archivo"], fila["origen_hoja"],
                             fila["origen_fila"], campo, regla, valor))
    # R01: identificador no vacío y sin repetidos dentro del lote
    repetidos = lote["perfil_id"].isna() | lote["perfil_id"].duplicated(keep=False)
    for i in lote.index[repetidos]:
        f = lote.loc[i]
        rechazos.append((i, f["origen_archivo"], f["origen_hoja"],
                         f["origen_fila"], "perfil_id", "unicidad", f["perfil_id"]))

    informe = pd.DataFrame(rechazos, columns=[
        "indice", "archivo", "hoja", "fila", "campo", "regla", "valor"])
    informe.drop(columns="indice").to_csv("informe_rechazos.csv", index=False)

    validas = lote.drop(index=informe["indice"].unique())
    filas = []
    for _, f in validas.iterrows():
        huella = hashlib.sha256("|".join(str(f[c]) for c in COLUMNAS).encode()).hexdigest()
        filas.append((
            f["perfil_id"],
            pd.to_datetime(f["fecha_muestreo"]).date(),
            float(str(f["latitud"]).replace(",", ".")),
            float(str(f["longitud"]).replace(",", ".")),
            float(str(f["profundidad_cm"]).replace(",", ".")),
            float(str(f["ph"]).replace(",", ".")),
            float(str(f["materia_organica_pct"]).replace(",", ".")),
            f["textura"].strip(), f["drenaje"].strip(),
            f["origen_archivo"], f["origen_hoja"], int(f["origen_fila"]),
            huella, usuario,
        ))
    with psycopg.connect(dsn) as conexion, conexion.cursor() as cur:
        cur.executemany(SQL_CARGA, filas)
    print(f"Filas leídas: {len(lote)} | cargadas: {len(filas)} | rechazos: {len(informe)}")


SQL_CARGA = """
INSERT INTO suelos.perfil (
    perfil_id, fecha_muestreo, latitud, longitud, profundidad_cm, ph,
    materia_organica_pct, textura, drenaje,
    origen_archivo, origen_hoja, origen_fila, huella_fila, cargado_por)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (perfil_id) DO NOTHING
"""

if __name__ == "__main__":
    main(sys.argv[1], sys.argv[2], sys.argv[3])

La tabla de destino lleva las columnas de auditoría. Es SQL para PostgreSQL 16 con PostGIS:

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE SCHEMA IF NOT EXISTS suelos;

CREATE TABLE suelos.perfil (
    perfil_id            text PRIMARY KEY,
    fecha_muestreo       date NOT NULL CHECK (fecha_muestreo >= DATE '1950-01-01'),
    latitud              numeric(9,6) NOT NULL CHECK (latitud BETWEEN -4.3 AND 13.5),
    longitud             numeric(9,6) NOT NULL CHECK (longitud BETWEEN -79.1 AND -66.8),
    profundidad_cm       numeric(6,1) NOT NULL CHECK (profundidad_cm BETWEEN 0 AND 500),
    ph                   numeric(4,2) NOT NULL CHECK (ph BETWEEN 2.5 AND 11),
    materia_organica_pct numeric(5,2) NOT NULL CHECK (materia_organica_pct BETWEEN 0 AND 100),
    textura              text NOT NULL,
    drenaje              text NOT NULL,
    geom                 geometry(Point, 4326)
        GENERATED ALWAYS AS (ST_SetSRID(ST_MakePoint(longitud, latitud), 4326)) STORED,
    -- auditoría
    origen_archivo       text NOT NULL,
    origen_hoja          text NOT NULL,
    origen_fila          integer NOT NULL,
    huella_fila          char(64) NOT NULL,
    cargado_por          text NOT NULL,
    cargado_en           timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX perfil_geom_gix ON suelos.perfil USING gist (geom);

Dos decisiones merecen explicación. Las reglas están dos veces: en el script, para producir el informe de rechazos con un motivo legible, y en la base, mediante CHECK, como segunda barrera que ningún programa futuro puede saltarse. Y la columna geom se calcula sola, de modo que latitud y longitud nunca se contradicen con la geometría.

¿Cómo se usa el informe de rechazos?

El archivo informe_rechazos.csv es el verdadero producto de la primera pasada. Cada línea dice qué archivo, qué hoja, qué fila, qué campo y qué regla falló, con el valor encontrado. Se entrega al responsable del levantamiento, que corrige en la hoja original o justifica el dato. Se vuelve a ejecutar el script y la carga procesa solo lo que antes faltaba.

En nuestra experiencia, el informe de la primera carga es largo y el de la tercera es corto. Esa curva es la mejor medida de que el proceso funciona: mide la calidad del dato, no la suposición de que está bien.

¿Qué queda por hacer después de la carga inicial?

• Validación espacial fina. Comprobar con PostGIS que cada punto cae dentro de la unidad territorial declarada.
• Semáforo de calidad. Guardar por registro el resultado de las reglas, para que el coordinador lo vea en los listados.
• Captura en origen. Que la regla se aplique al escribir el dato, en un formulario con catálogos controlados, y no meses después.
• Bitácora de cambios. Una tabla de historial para registrar cada modificación posterior.

¿Cómo empezar en su entidad?

Si tiene hojas de cálculo con información técnica y necesita llevarlas a una base de datos confiable, podemos revisar una muestra, proponer la tabla de reglas con sus especialistas y entregar el procedimiento de carga documentado. Conozca nuestro servicio de estructuración de bases de datos y escríbanos desde contacto.