Limitaciones y Problemas de Excel en la Carga de Datos

Introducción

Todo usuario de Excel debe tener ciertas nociones sobre cómo almacena los datos. Son conceptos sencillos, pero que por desidia se desconocen, no se ponen en práctica, o, intencionadamente se incurre en malas prácticas por falta de tiempo o simplemente porque los datos "quedan más bonitos" con esas malas prácticas.

Además, los usuarios deberían, también, conocer las abundantes "pifias" y comportamientos extraños de esta Hoja de Cálculo.

Estas malas prácticas, junto con las "pifias" y comportamientos extraños de Excel, unidos a usuarios sin conocimientos matemáticos ni de informática, dan lugar a frecuentes discusiones y problemas en la comprensión de resultados y análisis de datos.

Así, se ha decidido crear este capítulo con el fin de "alfabetizar" esa inmensa mayoría de usuarios que se creen en posesión de la verdad absoluta porque tienen una serie de números muy bonitos guardados en "una base de datos Excel" (expresión que da risa, si, pero que los usuarios la dicen creyéndose ser el mísmísimo Bill Gates). Una Hoja de Cálculo NO ES una Base de Datos. En internet pueden buscar ambos conceptos.

Por supuesto que esto no es un curso de Excel. Simplemente se van a describir, someramente, una serie de conceptos necesarios para que una carga de datos funcione lo mas convenientemente posible. Además se describen determinados bugs y comportamientos extraños con el fin de comprender qué ocurre cuando se producen determinados errores durante una subida de datos a una base de datos, intedpendientemente del cargador que deseen utilizar.

Formato de Celdas

Uno de los desafíos más críticos es la naturaleza 'débilmente tipada' de Excel. A diferencia de una base de datos SQL que impone esquemas estrictos, Excel permite mezclar valores numéricos, cadenas de texto y errores de fórmula en una misma columna. Esta falta de validación estructural provoca fallos de integridad durante la migración, ya que un campo definido como INTEGER o DECIMAL en el destino rechazará cualquier celda que contenga texto incidental o formatos inconsistentes.

El primer concepto que hay que entender es el formato de celda. Excel cuenta con una serie de formatos predefinidos que se pueden aplicar a las celdas de un libro de Excel, y también permite a los usuarios crear formatos personalizados. Estos formatos no solo sirven para la presentación; también influyen en cómo Excel almacena los datos internamente y cómo deben interpretarse.

La selección del "Formato de Celda" para un dato tiene una gran influencia en como Excel guarda y presenta un determinado dato. Nos referimos a la pestaña "Número" dentro del cuadro de diálogo que aparece al dar formato a una celda.

Partimos de un sistema operativo Windows 10 con una configuración regional para Español (España). Ello implica que tiene definido como "Símbolo decimal" la coma "," y como "Símbolo de separación de miles" el punto ".". Precisamente lo contrario a lo que se define en las culturas anglosajonas, y, que es fuente de innumerables errores, ya sean por parte de los usuarios como por parte de Microsoft, como se describe en "El extraño caso de OLEDB con Decimal y Moneda". Esta configuración también define un formato de fecha corta por defecto: "dd/MM/aaaa" (día/mes/año).
También se parte de un Office 2019 en Español (España) instalado en el mismo sistema operativo.

Como ejemplo radical de la influencia de dicho formato, si en una celda con formato "General" se anota una fecha, por ejemplo 02/01/2023, Excel lo toma como un dato de tipo fecha, y automáticamente cambia el formato de la celda a "Fecha" e internamente almacena el número 44928. Si a dicha celda se le modifica el formato para ser de tipo "Texto", aparece dicho número, lo que para el usuario puede que le parezca que no tiene lógica ninguna.
Evidentemente, este tipo de detalles se han de tener muy en cuenta cuando se generen Libros Excel de forma automatizada o mediante programación.

Excel To Database trabaja con los datos guardados internamente y con el formato de celda para deducir como interpretarlos correctamente. Así, en el caso anterior, el dato 44928 sólo será interpretado como una fecha si el formato de la celda es "Fecha", y su interpretación no dará lugar a ninguna duda. Si el formato es distinto de "Fecha", por ejemplo "Texto", se interpretará como una cadena de caracteres.
Si, por el contrario, internamente se encuentra almacenado el dato "02/01/2023" (sin las comillas dobles), lo razonable es que se corresponda con una celda cuyo formato es "Texto", es decir, el dato es un texto puro y duro. Si esta cadena se ha de cargar en una columna de tipo Fecha, habrá que realizar una conversión del texto a fecha. Sin embargo, el formato de celda no informa de cómo se debe interpretar el dato, es decir, se desconoce si el 02 se corresponde con el mes, el día o el año. Esto dará lugar a fallos en la carga de datos debido a la imprecisión en la interpretación del dato, motivado, generalmente, a la desidia o malas prácticas por parte del usuario o programa generador del Libro Excel.

Con el "Símbolo decimal" y el "Símbolo de separación de miles" ocurren todavía cosas más extrañas, las cuales necesitarían de un tratado únicamente dedicado a ello.
Para las celdas con datos numéricos y formato de celda "General" o "Número", por ejemplo, Excel muestra el dato según la configuración regional definida para el sistema operativo. Sin embargo, internamente, el formato con que se almacena el dato no tiene porque coincidir con la presentación en la celda, al igual que ocurre con las fechas. Esto hace que cambiando el formato de celda ocurran cosas similares a lo descrito para las fechas.

Como norma general, se recomienda al usuario que tenga especial cuidado con el formato que asigna a las celdas del Libro Excel que desea cargar en la base de datos. La desidia y las malas prácticas no tienen cabida con este cargador, y probablemente con ningún otro.

Precisión Excel

Otro de los problemas que tiene Excel es la precisión o exactitud de los datos que almacena. El detalle del problema se describe en: La aritmética de punto flotante puede dar resultados inexactos en Excel y Numeric precision in Microsoft Excel - Wikipedia.
Sin profundizar en el tema, y para tener una pequeña idea de los problemas que en esos artículos se detallan, pensemos que ocurre con los números que en base 2 tienen un número infinito de dígitos (en base 10 podría ser 1/3 = 0,333333333333....). Simplificando mucho el problema y cómo funciona Excel, obtenemos que Excel almacena de ellos los 15 primeros dígitos significativos. Las consecuencias de este echo son insospechadas, y entre ellas está la carga de este tipo de datos con determinados proveedores de datos (OLE DB, ODBC, ...) y/o drivers (drivers para MS Access, SQL Server, ...).

Dicho problema tiene una solución difícil. Microsoft aporta una solución para la realización de cálculos: La aritmética de punto flotante puede dar resultados inexactos en Excel: Método 2: Precisión de pantalla, How to Set Rounding Precision In Excel y en What does 'Set precision as displayed' do in Excel?.
Sin embargo, esta solución no aporta nada para el problema que se plantea durante la carga de datos.

Lo normal en este tipo de casos es que el proveedor de datos redondee o trunque el dato para poder ser insertado en la base de datos. Sin embargo, dependiendo del driver y del tipo de datos de la base de datos, el redondeo que se aplica puede no ser el mismo. En unos casos será del tipo MidpointRounding.AwayFromZero, mientras que en otros puede ser del tipo MidpointRounding.ToEven. Ver MidpointRounding Enumeración.
Cuando Excel To Database aplique un redondeo, éste será siempre del tipo MidpointRounding.AwayFromZero.

También se ha de destacar que Excel To Database intentará cargar el dato contenido en la Celda en uno de los Tipos de Datos de .NET. Esto hará que existan dos precicisiones diferentes: la de Excel por un lado, y la de .NET por otro.
En futuras versiones esta funcionalidad cambiará para cargar los datos en las estructuras de datos apropiadas para cada base de datos.

En Especificaciones y límites de Excel se especifican los límites de Excel.
En dicha información nos asalta la duda de qué es el "Número permitido mediante fórmula" y por qué es "mas grande" que lo que Excel permite. Además, nos preguntamos ¿cómo se maneja ese número?
Suponemos que dichos números se refiere a la precisión que Excel maneja internamente para la realización de cálculos, pero no son valores que se encuentren almacenados en las celdas. Así, dichos límites no serán tenidos en cuenta en Excel To Database.

En Resumen de tipos de datos (Visual Basic) se especifican los límites de los tipos de datos de .NET.
Estos son los límites reales que tiene Excel To Database, ya que los valores almacenados en las celdas de Excel se deberán cargar en estos tipos de datos.
Los límites del tipo de datos "Double"se parecen mucho a los límites de Excel para "Número permitido mediante fórmula". Es por ello por lo que se dedujo la suposición del párrafo anterior.

Como se puede observar, los límites admitidos por Excel To Database son mayores que los permitidos por Excel. Así, si se desea que Excel albergue datos (numéricos o de hora) mayores de los permitidos se han de colocar en celdas con Formato de Celda "Texto" teniendo en cuenta que Excel To Database reconoce como "Símbolo decimal" el punto "." y como "Símbolo de separación de miles" la coma "," (el convenio definido en las culturas anglosajonas).

Fechas Excel

Las fechas y Excel no se llevan demasiado bien. Debido a decisiones tomadas en los tiempos en que se comenzó a desarrollar Excel resulta bastante complicado comprender qué ocurre con las fechas. Se recomienda la lectura de los siguientes artículos:

• My First BillG Review
• Dates And Times In Excel
• Eric's Complete Guide To VT_DATE
• Diferencias entre el sistema de fechas 1900 y 1904 en Excel Excel To Database no funcionará correctamente con la opción de 1904. En futuras versiones se añadirá esta funcionalidad.
• Excel asume incorrectamente que el año 1900 es un año bisiesto

Además de los problemas mencionados anteriormente se le une que el funcionamiento de Excel es completamente diferente del funcionamiento de VBA (Visual Basic para Aplicaciones). Como ejemplo se puede consultar el siguiente artículo: Integers and dates map differently in VBA than in the Worksheet

A todos estos problemas también hay que añadir los problemas que poseen los proveedores de datos:

• .NET (System.Data.SqlClient, Microsfot.Data.SqlClient, Oracle Data Provider for .NET, ...)
• OLE DB (Microsoft.ACE.OLEDB.16.0, Microsoft.ACE.OLEDB.12.0, Microsoft.Jet.OLEDB.4.0, MSDAORA, SQLOLEDB, MSOLEDBSQL, MSOLEDBSQL19, SQLNCLI11, OraOLEDB.Oracle, ...)
• ODBC ({Microsoft Access Driver (*.mdb, *.accdb)}, {ODBC Driver 18 for SQL Server}, {ODBC Driver 17 for SQL Server}, {SQL Server}, {SQL Server Native Client 11.0}, SQORA32.dll, ...)
• Y los problemas de las clases de .NET asociadas a los proveedores OLE DB y ODBC (System.Data.OleDb, System.Data.Odbc)

el lio con las fechas está completamente asegurado.

Por último, indicar que los Formatos de Celda para Fecha que comienzan por un "*" son formatos dependientes de la configuración regional de fecha y hora especificados en el sistema operativo. Como las pruebas se han realizado con un Sistema Operativo con una configuración regional de "Español (España)" el formato de Fecha Corta es: "dd/MM/aaaa" (día/mes/año). Formato que Excel tomará por defecto para las celdas cuyo Formato de Celda sea "Fecha".

La ambigüedad en el formato de fechas es un riesgo silencioso. Un valor como '01/02/2026' es técnicamente válido tanto para el formato DD/MM/YYYY como MM/DD/YYYY. Sin una lógica de pre-procesamiento clara, el sistema no tiene forma de discernir la intención del usuario, lo que puede derivar en una base de datos con cronologías corruptas que son extremadamente difíciles de auditar a posteriori.

• Datos de Tiempo. Fecha de Referencia: 30/12/1899.

  Cuando en Excel se introducen datos de tiempo en formato "Hora", "Personalizado" o "Texto", se le añade por defecto la fecha de referencia 30/12/1899. Sin embargo, cuando se le aplica la función "YEAR" muestra el año 1900.
  La función "YEAR"(AÑO) no funciona para fechas anteriores a 1 de enero de 1900. Ya les vale, señores de Microsoft.

  En MS Access, para que sólo aparezcan datos de tipo "Hora", también se utiliza la fecha de referencia 30/12/1899.

  Cuando se importan datos de tipo Hora desde Excel a MS Access, SQL Server u Oracle con Excel To Database el comportamiento de dicha importación es dependiente del Formato de Celda y del tipo de datos de la columna donde se deben cargar dichos datos.
  Para MS Access, cuando Excel To Database interpreta un dato como hora, le añade la fecha de referencia (30/12/1899) de forma automática, aunque internamente, en Excel se almacene otra fecha.
  Para SQL Server y Oracle, el campo debe admitir datos de "sólo hora" para poder cargar datos de Excel interpretados como Hora.

• 1900 El año que no fue bisiesto.

  Puede parecer que el error de bulto incluido con Excel para el año 1900 existe también para los años terminados en "00" y que no son divisibles por 400 (que no fueron bisiestos: 1800, 1700, ...). El año 2000 si fue bisiesto, ya que es divisible por 400.

  "Aparentemente", Excel acepta dichas fechas sin problemas. No muestra ningún error para las fechas 29 de febrero de los años 1900, 1800, 1700, ... Sin embargo, existe una sutil diferencia: el 29/02/1900 se almacena en Excel como un número, lo que quiere decir que es interpretado como una fecha correcta (las fechas en Excel se almacenan como números). Simplemente hay que cambiar el formato de celda a "General" o "Numero" y aparecerá el número 60 (número de días transcurridos desde la fecha de referencia 31/12/1899 hasta el 29/02/1900 si hubiese existido). Esto no ocurre para el 29/02/1800, 1700, ..., lo que indica que no son fechas válidas para Excel.
  Este comportamiento se fijó en los inicios del desarrollo de Excel donde se pretendía tener compatibilidad con las Hojas de Cálculo de Lotus 1 2 3 que dominaban el mercado. Son bien conocidas las diferentes estrategias que Microsoft ha empleado para maximizar la rentabilidad frente a la calidad.

  Excel To Database no contiene ninguna programación específica para el cálculo de las fechas, sino que utiliza el método DateTime.FromOADate(Double) para el cálculo de fechas con una corrección para los días que van desde el 1 de Enero de 1900 hasta el 1 de Marzo de 1900. Excel asume que el año 1900 fue un año bisiesto cuando no lo fue, y es necesaria dicha corrección a la hora de interpretar los datos. Ver: Converting Double to DateTime?
  Asi, Excel To Database traducirá 29/02/1900 como 28/02/1900 y será esa la fecha que inserte en la base de datos.

  En Pruebas MS Access: Fechas se puede ver el comportamiento de Excel para estas fechas (rango "B2:D2"). Mientras que los datos contenidos en el rango "B2:C2" no pueden ser convertidos a número, no son fechas válidas para Excel, el contenido de la celda D2 si es considerado una fecha válida.
  Excel debería indicar de alguna forma que el dato introducido en la celda no es una dato de fecha válido. De esta forma no se tendría que utilizar el procedimiento manual de cambiar el Formato de Celda a "Texto" para comprobar si la fecha es válida o no.

• Procesamiento de Fechas de Excel To DataBase

  Cuando Excel To Database debe cargar un dato en un campo de tipo Fecha y/u Hora se realizan los siguientes pasos:

  • Excel reconoce el dato como una Fecha

    1. Del Formato de Celda se obtiene información para matizar el dato: Es sólo Fecha, es sólo Hora, es Fecha y Hora.
    2. El dato es convertido al tipo de datos adecuado de .NET (DateTime, DateTimeOffset o TimeSpan).
    3. Si el dato cumple las restricciones impuestas por la Base de Datos y el Driver (Por ejemplo, ver: ODBC y el chovinismo anglosajón: 1753):
       • El dato se asigna al DataTable (estructura que alberga los datos para su posterior carga en la base de datos)
       • Se realiza la carga de datos.
    4. Si falla el punto 2º o el 3º:
       • Opción "Sin errores de conversión" NO marcada: se asigna la cadena de caracteres que representa la fecha directamente al DataTable.
         Es muy probable que se produzca un Error de Conversión de Tipos que hará que no se inicialice la fase de carga de datos.
       • Opción "Sin errores de conversión" marcada: se asigan el Valor por defecto indicado en el Panel de Mapeos al DataTable
       • Se continua con la lectura de datos y si no se produce ningun error se inicia la fase de carga de datos.

    Durante todo este proceso se pueden producir errores inesperados que pueden hacer que se detenga la fase de lectura de datos o bien la fase de carga de datos. Excel To Database informará de ello de forma pertinente.

  • Excel NO reconoce el dato como una Fecha

    1. Del Formato de Celda se obtiene información para matizar el dato: Es sólo Fecha, es sólo Hora, es Fecha y Hora, o no se dispone de información.
    2. Se intenta convertir el dato a uno de los tipos de datos de .NET (DateTime, DateTimeOffset o TimeSpan).
    3. Si la conversión resulta exitosa, se continúa como se indica en el punto 3º del subapartado anterior.
    4. Si la conversión NO resulta exitosa:
       • Si es la primera vez que ocurre este proceso, Excel To Database informará de los formatos que ha aplicado para intentar realizar dicha conversión. Las sucesivas veces que ocurra no se presenta esta información por ser demasiado larga.
         Los primeros formatos que aplica Excel To Database son los correspondientes a una serie de formatos definidos que Microsoft llama Cultura Invariante, y a continuación los formatos definidos en la Configuración Regional del Panel de Control de Windows.
       • Se continúa como se indica en el punto 4º del subapartado anterior.

    Durante todo este proceso se pueden producir errores inesperados que pueden hacer que se detenga la fase de lectura de datos o bien la fase de carga de datos. Excel To Database informará de ello de forma pertinente.

Preguntas Frecuentes

¿Por qué mis datos de Excel cambian de tipo al importarlos?

Excel no obliga a respetar un tipo de dato por columna. Si una celda tiene un formato distinto (por ejemplo, texto en una columna numérica), la base de datos lo rechazará para proteger la integridad del esquema.

¿Cómo evitar errores de fecha al cargar un Excel?

El error más común es la ambigüedad entre formatos regionales (UK vs US). Es vital estandarizar a formato ISO (YYYY-MM-DD) antes de la carga para evitar interpretaciones erróneas.

¿Qué pasa si mi Excel tiene celdas vacías o nulos?

En bases de datos, un espacio en blanco no es lo mismo que un NULL. Excel To Database gestiona estas discrepancias para asegurar que la lógica de tu base de datos se mantenga consistente.