De Excel a SQL: Primeros pasos

Anteriormente, se tenía la costumbre y facilidad de trabajar con Excel cualquier tipo de información que tuviéramos disponible. Esto llevó a que todos los registros y datos estuvieran respaldados y almacenados en hojas de trabajo .xlsx y, por ende, resultara complicado compartirlos con otra persona, trabajarlos de manera asíncrona y tener backup de la información allí alojada.

Hoy en día, tenemos herramientas que potencian esto y permiten que los datos tengan un respaldo en caso de acontecimientos inesperados, así como accesibilidad para todos sin importar la distancia y/o lugar donde se encuentren.

La gran dificultad que enfrentamos en la actualidad es que los usuarios de Excel siguen en aumento y, con ello, se crean cada vez más documentos, los cuales empiezan a ser utilizados como fuente principal, haciendo más complejo su correcto manejo y creando una serie de problemas que impiden desarrollar un trabajo sin fricciones. Esto se profundiza ahora cuando los equipos y personas ya se han acostumbrado, en parte, a trabajar de manera descentralizada.

Solo como dato curioso, según EarthWeb, actualmente Excel tiene alrededor de 1.5 billones de usuarios. Esto implica una gran cantidad de información que no está respaldada, estructurada ni ligada de alguna manera. ¡Increíble!

En este artículo, vamos a revisar los primeros pasos para migrar del uso de Excel a bases estructuradas donde podamos:

• Gestionar mayor cantidad de información.
• Respaldar los datos.
• Trabajar de manera colaborativa con equipos (evitando alterar lo original).
• Crear y generar reportes de BI de una manera más inteligente, rápida y efectiva.

Normalmente, un documento de Excel se ve de esta manera:

Está compuesto por hojas y éstas, a su vez, tienen columnas que representan los atributos de cada hoja. Esto puede ser fácilmente modificable o corruptible por personas al trabajar de manera asíncrona. Claro, puedes decir que está la opción de bloquear celdas y hojas, evitando que se modifiquen por personas no autorizadas, pero ¿a qué grado se puede hacer esto? ¿Qué pasa si la compañía y los procesos crecen y en algún momento olvidas bloquear alguna celda u hoja en el documento?

Es por ello que la información se debe manejar para que sea posible aceptar grandes cantidades de datos, los cuales se actualizarán frecuentemente. Para ello, debemos seguir diversos pasos que nos serán de gran ayuda al ejecutar los procesos. Principalmente, debemos:

1. Educar a los usuarios de negocio: Con educar me refiero a que debemos concientizar a las personas del área a que utilicen un mismo formato/plantilla para almacenar la información y guardar cada uno de los datos (aquí es muy importante que hagamos énfasis en generar este formato nosotros y lo expliquemos a cada uno de los involucrados para que sepan cómo utilizarlo, así como las implicaciones que tendría el no usarlo de manera adecuada). Esta parte es de las más complicadas, ya que muchas veces no se quieren cambiar los procesos o la manera de hacerlo y, por ello, se dificulta la estandarización de los procesos.
   
2. Crear y automatizar la parte de carga de información: Para esto, es necesario conocer cómo ejecutar la lectura del archivo previamente elaborado y determinar la manera ideal para que la información que allí se encuentra sea fácilmente leída y guardada. En esta etapa, lo que se espera es guardar la información obtenida a través de las plantillas en una base datos estructurada donde podamos ejecutar las consultas y tener el registro de ésta.

A continuación, haremos nuestro ejemplo simulando un caso de la vida real:

• Utilizaremos MySQL Workbench como DBMS (Database Management System). Lo podemos descargar aquí.
  
• Una vez descargado el archivo instalador, es necesario seguir las instrucciones en pantalla para configurarlo:

     - Developer Setup.

     - Ejecutar todo lo que figura en pantalla.

           - Root Password: Asegúrate de guardar y anotar este password.

     - Si la configuración y los pasos fueron correctos, al final te solicitará tu clave para probar la conexión y verás un mensaje de configuración exitosa.

• Cuando ya queda configurado, es necesario que hagamos el proceso para crear nuestra base de datos local:

     - Abre MySQL Workbench y da clic en la instancia local: Aquí debes ingresar con el usuario root y la contraseña definidos previamente.

      - Ahora solo deberás seleccionar en los iconos superiores la opción de create Schema.

      - Una vez ingresado el nombre de tu schema, haz clic en apply (lo pide dos veces) y después en Finish para ejecutar la sentencia SQL que creará tu esquema:

CREATE SCHEMA `sample_schema_jr` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;

      - Completado eso, aparece del lado Izquierdo tu nuevo esquema:

1) Ya que nuestro esquema ha quedado configurado, será necesario trabajar en el archivo que será llenado por los Business Users. En este caso, vamos a trabajar con la siguiente fuente de datos:

• Vamos a utilizar un archivo de un Ecommerce obtenido en Kaggle:
• En este ejemplo, solo haré la carga de uno de los .csv: olist_order_items_dataset.csv
• Ten presente que las columnas y tipos de datos que tenga la primera carga serán los empleados por Workbench para crear la tabla por defecto. Se puede cambiar o agregar después, pero eso lo abordaremos en otra sesión.

2) Una vez que tenemos el template y todo listo para almacenar la información, debemos hacer la primera carga a SQL, la cual creará nuestra tabla. Para ello:

• Da clic derecho en el esquema que creamos y selecciona Data Import Wizard.

• Después,  creamos una nueva tabla. Por defecto, utiliza el nombre del archivo como nombre de la tabla (lo podemos cambiar si queremos).

• Después seguimos con la configuración de los campos de la tabla. Aquí modificamos el formato de cada atributo y cada atributo se llamará según se llame la columna en el .csv. En este caso, no modificaremos nada: todo lo dejamos tal cual lo recomienda Workbench, ya que no será necesario que interactuemos con los datos.

• Finalmente, seleccionamos next y veremos que empieza el guardado de la información.

• Esto puede llevar un tiempo, ya que es bastante información la que almacena. Sin embargo, en otro artículo escirbiré sobre cómo hacer esto,  pero desde la terminal para hacer un Bulk Insert de la información. Una vez que finaliza la importación, nos aparecerá un mensaje de éxito, damos clic en Finalizar y ¡a trabajar!

3) Ahora que ya tenemos la información, haremos unos queries sencillos con el objetivo de validar que todo se haya insertado correctamente. Para ello:

• Damos clic en el botón resaltado:

• Escribiremos lo siguiente para contar el total de registros que tenemos. Este debe coincidir con el total de nuestro csv:

select count(*) from olist_order_items_dataset;

• Revisamos 5 registros de nuestro dataset para ver que las columnas y los datos se hayan almacenado correctamente:

Select * from olist_order_items_dataset limit 5;

• Corroboramos en la fecha min sea la correcta:

select min(shipping_limit_date) from olist_order_items_dataset;

• Al igual que la fecha máxima, la revisamos:

select max(shipping_limit_date) from olist_order_items_dataset;

• Validamos que el total de los productos sea igual al que tenemos en el Excel:

select sum(price) from olist_order_items_dataset;

Con todo bien, simularemos una carga manual del mismo archivo, pero como si fuera otro día, esto con el objetivo de actualizar la información en nuestra tabla. Para ello, modificaremos las configuraciones iniciales de SQL de la siguiente manera:

• Vamos a la pantalla de conexiones y damos clic en editar conexión:

• Después en la pantalla de Manejar las conexiones, seleccionamos la pestaña Advanced (debajo de Connection Method) y, en Others, agregamos OPT_LOCAL_INFILE=1:

• Cerramos la sesión de SQL que tenáamos abierta anteriormente y la volvemos abrir.
• Con eso, nuestros cambios serán guardados y ahora, para ejecutar la carga del archivo, vamos a crear una copia de la plantilla anteriormente usada y le modificamos algunos campos, simulando que fue una operación nueva.
• Guardamos el nuevo archivo y usaremos el siguiente código en SQL para ejecutar la carga:

LOAD DATA LOCAL INFILE 'C:/Users/juram/Downloads/archive/olist_order_items_dataset_2023.csv'

INTO TABLE olist_order_items_dataset

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS (`order_id`, `order_item_id`, `product_id`, `seller_id`, `shipping_limit_date`, `price`, `freight_value`);

• La primera línea es la dirección del archivo que creamos.
  
• Después, le decimos dónde queremos insertarlo.
  
• Definimos el separador de los datos.
  
• Asignamos un agrupador para que los espacios no causen problemas.
  
• Decimos que cada línea está determinada por un salto de línea.
  
• Ignoramos los encabezados.
  

Una vez que lo ejecutamos, podemos validar los cambios que hayas hecho. Por ejemplo, yo agregué 2 registros con fecha 2023 y así lo puedo visualizar:

select * from

olist_order_items_dataset

order by shipping_limit_date desc;

¡Listo! Hemos terminado. Recuerda que ésta es una manera sencilla y rápida para migrar archivos de Excel a una base de datos estructurada, la cual posteriormente podemos conectar con alguna herramienta de BI y generar reportes de manera más ágil.

Estaré atento a cualquier duda. ¡Gracias por leer!