Cómo importar un archivo Excel en una tabla de base de datos usando python

En el mundo actual, impulsado por los datos, su tratamiento y procesamiento eficientes son tareas esenciales para cualquier organización o individuo. Pythoncon su rico ecosistema de bibliotecas, ofrece potentes herramientas para la manipulación y gestión de datos, como la biblioteca import pandas. Un escenario común es la necesidad de extraer o importar datos de Excel hojas de cálculo y almacenar o insertar datos en una base de datos para su posterior análisis o integración con otros sistemas. En este tutorial, exploraremos cómo crear un script de Python que automatice este proceso, permitiéndole leer sin problemas datos de archivos de hojas Excel e insertarlos en una base de datos. Al final de este tutorial, estarás listo para manejar las tareas de migración de datos de manera eficiente. Comencemos!

Cómo importar un archivo Excel en una tabla de base de datos usando python

1. Comience por instalar la biblioteca IronXL.

2. Carga tu archivo Excel en la memoria utilizando IronXL.

3. Cargue la hoja de cálculo específica con la que desea trabajar.

4. Seleccione el intervalo de datos preciso que desea importar.

5. Establecer una conexión con cualquier base de datos como SQLite o MySQL base de datos de conexión utilizando Python.

6. Cree una nueva tabla en su base de datos SQLite para alojar los datos importados.

7. Inserte las filas seleccionadas del archivo Excel en la tabla SQLite recién creada.

8. Recuperar y seleccionar datos de la tabla SQLite creada para su posterior análisis o procesamiento.

   En este tutorial, utilizaremos IronXLuna biblioteca de Python famosa por su eficacia en el tratamiento de archivos Excel. Al integrar IronXL en nuestro script, garantizamos una extracción perfecta de los datos de las hojas de cálculo de Excel, lo que permite una inserción sin problemas en las bases de datos para su posterior análisis y procesamiento.

¿Qué es IronXL?

IronXL es una biblioteca Python desarrollada por Iron Software, que ofrece una sólida funcionalidad para lectura, generandoy editar archivos Excel directamente en aplicaciones Python. En particular, IronXL destaca por su independencia de la instalación de Microsoft Excel, lo que simplifica la implantación en distintos entornos. Con IronXL, los desarrolladores se benefician de:

Soporte multiplataforma: Disfrute de un funcionamiento perfecto en plataformas Windows, macOS, Linux, Docker, Azure y AWS, lo que garantiza la adaptabilidad a diversas configuraciones de desarrollo.

Importación y exportación de datos: Gestiona fácilmente la importación de datos desde archivos XLS, XLSX, CSV y TSV, con la flexibilidad de exportar hojas de cálculo a estos formatos e incluso a JSON para mejorar la interoperabilidad.

Funciones de cifrado: Garantice la seguridad de los datos aprovechando las funciones de cifrado de IronXL, que permiten proteger archivos XLSX, XLSM y XLTX con contraseñas.

Fórmulas y recálculo: Trabaja sin esfuerzo con Excel fórmulascon la ventaja añadida del recálculo automático cada vez que se edita una hoja, lo que garantiza la precisión y fiabilidad en la manipulación de los datos.

Estilización de celdas: Personaliza la apariencia de las celdas individuales ajustando la fuente estilosademás, puede cambiar el tamaño, los patrones de fondo, los bordes y la alineación, mejorando la presentación visual de sus documentos Excel.

**Con soporte para varios formatos incluyendo XLS, XLSX, XLST, XLSM, CSV, y TSV, IronXL permite a los desarrolladores manejar datos en una multitud de escenarios con facilidad y eficiencia.

Ahora, empecemos por instalar IronXL.

Paso 1: Instale la biblioteca IronXL

El primer paso es instalar la biblioteca IronXL. Ejecute el siguiente comando para instalar IronXL en el símbolo del sistema.

pip install IronXL

pip install IronXL

Paso 2: Cargar el libro de Excel

El siguiente paso es cargar el archivo Excel. Vamos a utilizar el siguiente archivo de Excel para este tutorial.

El siguiente código cargará el archivo Excel existente en memoria.

from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")

from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")

El fragmento de código Python anterior demuestra la carga de un libro de Excel llamado "sample_excel.xlsx" utilizando la biblioteca IronXL. En primer lugar, se importa el módulo Python necesario de IronXL. A continuación, se asigna una clave de licencia para validar el uso de la biblioteca. Puede obtener su clave de licencia gratuita en Sitio web de IronXL. Por último, se emplea el método Cargar para abrir y cargar en memoria el libro de Excel especificado. Esto permite la posterior manipulación de su contenido mediante programación, como la lectura de datos, la modificación de los valores de las celdas o la aplicación de formato.

Paso 3: Seleccionar hoja de cálculo

Para seleccionar una hoja de trabajo en un libro de Excel utilizando IronXL, puede especificar el índice o el nombre de la hoja de trabajo.

worksheet = workbook.WorkSheets[0]

worksheet = workbook.WorkSheets[0]

Esta línea selecciona la primera hoja de cálculo del libro Excel cargado y la asigna a la variable "hoja de cálculo", lo que permite realizar operaciones posteriores en esa hoja de cálculo específica dentro del libro. Esto cargará los datos de una hoja Excel a una variable de la hoja de cálculo.

Paso 4: Abrir la conexión a la base de datos

En este tutorial, estamos utilizando una base de datos SQLite en lugar de un servidor de base de datos MySQL. Para iniciar las operaciones de la base de datos, empezamos por establecer una conexión con la base de datos.

import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

La línea anterior establece una conexión con una base de datos SQLite llamada 'data.db'. Si la base de datos especificada no existe, se creará automáticamente. Esta conexión permite la interacción posterior con la base de datos SQLite, como la ejecución de consultas y la realización de operaciones de manipulación de datos.

Paso 5: Crear una tabla

El siguiente paso es crear una tabla en la base de datos, donde importaremos los datos de un fichero Excel. Para crear una tabla en la base de datos SQLite, puede ejecutar una sentencia SQL utilizando el objeto de conexión.

cursor = conn.cursor() // database objects
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
            FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
            Country TEXT,
            Age Number
                )''')

cursor = conn.cursor() // database objects
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
            FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
            Country TEXT,
            Age Number
                )''')

El fragmento de código anterior inicializa un objeto cursor para ejecutar comandos SQL dentro de la conexión a la base de datos SQLite. Crea una tabla llamada 'cliente' con las columnas 'id', 'Nombre', 'Apellido', 'Sexo', 'País' y 'Edad'. La tabla se crea si aún no existe, respetando los tipos de datos de columna especificados.

Paso 6: Importación de datos a la base de datos mediante Python

Ahora, vamos a insertar datos en nuestra tabla recién creada. Importaremos un archivo Excel e insertaremos sus datos en la base de datos SQLite.

for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()

for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()

El código anterior recorre las filas 2 a 10 de la hoja de cálculo de Excel, extrayendo los valores de las columnas A a F de cada fila. Estos valores se almacenan en una tupla, que representa los datos que se van a insertar en la tabla "cliente". A continuación, el cursor ejecuta un comando SQL INSERT, incorporando la tupla de valores a la tabla. Este proceso se repite para cada fila, importando los datos del archivo Excel a la base de datos SQLite. Por último, conn.commit() confirma la transacción, asegurando que los cambios se guardan y persisten en la base de datos.

Paso 7: Lectura de datos de la base de datos

Para comprobar si los datos se han insertado correctamente, puede leer los datos de la tabla "cliente" de la base de datos SQLite mediante una consulta SELECT. Por ejemplo:

cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

El código anterior ejecuta una consulta SELECT en la tabla 'cliente' de la base de datos SQLite, recuperando todas las filas. Las filas obtenidas se almacenan en la variable 'rows' mediante la función fetchall() método. A continuación, cada fila se imprime de forma iterativa, mostrando los datos insertados en la tabla "cliente". Por último, la conexión a la base de datos se cierra mediante la función close() para liberar recursos.

El Código Completo es como:

import sqlite3
from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")
# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
                    FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
                    Country TEXT,
                    Age Number
                )''')
cursor.execute("DELETE FROM customer")
for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()  
cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

import sqlite3
from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")
# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
                    FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
                    Country TEXT,
                    Age Number
                )''')
cursor.execute("DELETE FROM customer")
for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()  
cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

Conclusión

En conclusión, este tutorial ha demostrado un enfoque automatizado para datos de lectura despliega todo su potencial para la gestión de datos. Aproveche la potencia de Python y IronXL para optimizar sus flujos de trabajo de tratamiento de datos e impulsar sus proyectos con confianza.