In der heutigen datengetriebenen Welt sind die effiziente Handhabung und Verarbeitung von Daten unerlässliche Aufgaben für jede Organisation und jeden Einzelnen. Python bietet mit seinem reichhaltigen Ökosystem von Bibliotheken leistungsstarke Werkzeuge zur Datenmanipulation und -verwaltung, wie die pandas-Bibliothek. Ein häufiges Szenario ist der Bedarf, Daten aus Excel -Tabellen zu extrahieren oder zu importieren und diese Daten in einer Datenbank zu speichern oder einzufügen, um sie weiter zu analysieren oder in andere Systeme zu integrieren. In diesem Tutorial erfahren Sie, wie Sie ein Python-Skript erstellen, das diesen Prozess automatisiert und Ihnen ermöglicht, Daten nahtlos aus Excel-Tabellendateien zu lesen und in eine Datenbank einzufügen. Am Ende dieses Tutorials sind Sie in der Lage, Datenmigrationsaufgaben effizient zu bewältigen. Lass uns beginnen!

Wie man eine Excel-Datei mit Python in eine Datenbanktabelle importiert

1. Beginnen Sie mit der Installation der IronXL-Bibliothek.
2. Laden Sie Ihre Excel-Datei mit IronXL in den Speicher.
3. Laden Sie die gewünschte Tabellenkalkulation.
4. Wählen Sie den genauen Datenbereich aus, den Sie importieren möchten.
5. Stellen Sie mithilfe von Python eine Verbindung zu einer beliebigen Datenbank wie z. B. einer SQLite- oder MySQL-Datenbank her.
6. Erstellen Sie eine neue Tabelle in Ihrer SQLite-Datenbank, um die importierten Daten aufzunehmen.
7. Fügen Sie die ausgewählten Zeilen aus der Excel-Datei in die neu erstellte SQLite-Tabelle ein.
8. Daten aus der erstellten SQLite-Tabelle abrufen und auswählen, um sie weiter zu analysieren oder zu verarbeiten.

In diesem Tutorial verwenden wir IronXL , eine Python-Bibliothek, die für ihre Effizienz bei der Verarbeitung von Excel-Dateien bekannt ist. Durch die Integration von IronXL in unser Skript gewährleisten wir die nahtlose Extraktion von Daten aus Excel-Tabellen, wodurch ein reibungsloses Einfügen in Datenbanken zur weiteren Analyse und Verarbeitung ermöglicht wird.

Was ist IronXL?

IronXL ist eine von Iron Software entwickelte Python-Bibliothek, die robuste Funktionen zum direkten Lesen , Generieren und Bearbeiten von Excel-Dateien innerhalb von Python-Anwendungen bietet. IronXL zeichnet sich insbesondere durch seine Unabhängigkeit von der Installation von Microsoft Excel aus, was die Bereitstellung in verschiedenen Umgebungen vereinfacht. Mit IronXL profitieren Entwickler von folgenden Vorteilen:

Plattformübergreifende Unterstützung: Profitieren Sie von einem reibungslosen Betrieb auf Windows-, macOS-, Linux-, Docker-, Azure- und AWS-Plattformen und gewährleisten Sie so die Anpassungsfähigkeit an unterschiedliche Entwicklungsumgebungen.

Datenimport und -export: Datenimport aus XLS-, XLSX-, CSV- und TSV-Dateien ist einfach, und Arbeitsblätter können flexibel in diese Formate und sogar in JSON exportiert werden, um die Interoperabilität zu verbessern.

Verschlüsselungsfunktionen: Gewährleisten Sie die Datensicherheit durch die Nutzung der Verschlüsselungsfunktionen von IronXL, die den Schutz von XLSX-, XLSM- und XLTX-Dateien mit Passwörtern ermöglichen.

Formeln und Neuberechnung: Arbeiten Sie mühelos mit Excel- Formeln und profitieren Sie zusätzlich von der automatischen Neuberechnung bei jeder Bearbeitung eines Tabellenblatts. Dies gewährleistet Genauigkeit und Zuverlässigkeit bei der Datenverarbeitung.

Zellformatierung: Passen Sie das Erscheinungsbild einzelner Zellen an, indem Sie Schriftarten , -größen, Hintergrundmuster, Rahmen und Ausrichtung ändern und so die visuelle Darstellung Ihrer Excel-Dokumente verbessern.

Breites Spektrum an Dokumentformaten: Dank der Unterstützung verschiedener Formate wie XLS, XLSX, XLST, XLSM, CSV und TSV ermöglicht IronXL Entwicklern die einfache und effiziente Datenverarbeitung in einer Vielzahl von Szenarien.

Beginnen wir nun mit der Installation von IronXL.

Schritt 1: IronXL-Bibliothek installieren

Der allererste Schritt ist die Installation der IronXL-Bibliothek. Führen Sie den folgenden Befehl in der Eingabeaufforderung aus, um IronXL zu installieren.

pip install IronXL

pip install IronXL

Schritt 2: Excel-Arbeitsmappe laden

Im nächsten Schritt laden wir die Excel-Datei. Für dieses Tutorial verwenden wir die folgende Excel-Datei.

Der folgende Code lädt die vorhandene Excel-Datei in den Speicher.

from ironxl import *  # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV

# Assign a license key (retrieved from IronXL website)
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook into memory
workbook = WorkBook.Load("sample_excel.xlsx")

from ironxl import *  # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV

# Assign a license key (retrieved from IronXL website)
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook into memory
workbook = WorkBook.Load("sample_excel.xlsx")

Der obige Python-Codeausschnitt demonstriert das Laden einer Excel-Arbeitsmappe mit dem Namen "sample_excel.xlsx" mithilfe der IronXL-Bibliothek. Zunächst wird das benötigte Python-Modul aus IronXL importiert. Anschließend wird ein Lizenzschlüssel zugewiesen, um die Nutzung der Bibliothek zu bestätigen. Sie können Ihren kostenlosen Lizenzschlüssel von der IronXL-Website herunterladen. Schließlich wird die Load-Methode verwendet, um die angegebene Excel-Arbeitsmappe in den Speicher zu laden. Dies ermöglicht die anschließende programmatische Bearbeitung des Inhalts, wie z. B. das Lesen von Daten, das Ändern von Zellwerten oder das Anwenden von Formatierungen.

Schritt 3: Arbeitsblatt auswählen

Um in einer Excel-Arbeitsmappe mit IronXL ein Arbeitsblatt auszuwählen, können Sie den Index oder den Namen des Arbeitsblatts angeben.

# Select the first worksheet in the loaded Excel workbook
worksheet = workbook.WorkSheets[0]

# Select the first worksheet in the loaded Excel workbook
worksheet = workbook.WorkSheets[0]

Diese Zeile wählt das erste Arbeitsblatt der geladenen Excel-Arbeitsmappe aus und weist es der Variablen worksheet zu, wodurch nachfolgende Operationen auf diesem spezifischen Arbeitsblatt innerhalb der Arbeitsmappe durchgeführt werden können. Dadurch werden Excel-Daten aus einem Excel-Arbeitsblatt in eine Arbeitsblattvariable geladen.

Schritt 4: Datenbankverbindung öffnen

In diesem Tutorial verwenden wir eine SQLite-Datenbank anstelle eines MySQL-Datenbankservers. Um Datenbankoperationen zu initiieren, stellen wir zunächst eine Verbindung zur Datenbank her.

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')

Die obige Zeile stellt eine Verbindung zu einer SQLite-Datenbank mit dem Namen 'data.db' her. Falls die angegebene Datenbank nicht existiert, wird sie automatisch erstellt. Diese Verbindung ermöglicht die nachfolgende Interaktion mit der SQLite-Datenbank, beispielsweise das Ausführen von Abfragen und das Durchführen von Datenmanipulationsoperationen.

Schritt 5: Erstellen Sie eine Tabelle

Im nächsten Schritt erstellen wir eine Datenbanktabelle, in die wir Daten aus einer Excel-Datei importieren. Um eine Tabelle in der SQLite-Datenbank zu erstellen, können Sie eine SQL-Anweisung mithilfe des Verbindungsobjekts ausführen.

# Create a cursor object for database operations
cursor = conn.cursor()

# Define and execute SQL to create a table if it doesn't exist
cursor.execute('''
CREATE TABLE IF NOT EXISTS customer (
    id INTEGER,
    FirstName TEXT,
    LastName TEXT,
    Gender TEXT,
    Country TEXT,
    Age INTEGER
)
''')

# Create a cursor object for database operations
cursor = conn.cursor()

# Define and execute SQL to create a table if it doesn't exist
cursor.execute('''
CREATE TABLE IF NOT EXISTS customer (
    id INTEGER,
    FirstName TEXT,
    LastName TEXT,
    Gender TEXT,
    Country TEXT,
    Age INTEGER
)
''')

Der obige Codeausschnitt initialisiert ein Cursor-Objekt, um SQL-Befehle innerhalb der SQLite-Datenbankverbindung auszuführen. Es wird eine Tabelle mit dem Namen "customer" und den Spalten "id", "FirstName", "LastName", "Gender", "Country" und "Age" erstellt. Die Tabelle wird erstellt, falls sie noch nicht existiert, wobei die angegebenen Spaltendatentypen berücksichtigt werden.

Schritt 6: Datenimport in die Datenbank mithilfe von Python

Nun fügen wir Daten in unsere neu erstellte Tabelle ein. Wir werden eine Excel-Datei importieren und deren Daten in die SQLite-Datenbank einfügen.

# Iteratively insert data from Excel worksheet into SQLite database
for i in range(2, 11):
    # Extracting values from columns A to F in Excel worksheet
    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
    )
    # Executing SQL INSERT command
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)

# Commit data insertion to the database
conn.commit()

# Iteratively insert data from Excel worksheet into SQLite database
for i in range(2, 11):
    # Extracting values from columns A to F in Excel worksheet
    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
    )
    # Executing SQL INSERT command
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)

# Commit data insertion to the database
conn.commit()

Der obige Code durchläuft die Zeilen 2 bis 10 des Excel-Arbeitsblatts und extrahiert für jede Zeile Werte aus den Spalten A bis F. Diese Werte werden in einem Tupel gespeichert, das die Daten darstellt, die in die Tabelle "customer" eingefügt werden sollen. Anschließend führt der Cursor einen SQL-INSERT-Befehl aus, der das Werte-Tupel in die Tabelle einfügt. Dieser Vorgang wiederholt sich für jede Zeile und importiert so effektiv Daten aus der Excel-Datei in die SQLite-Datenbank. Schließlich wird mit conn.commit() die Transaktion bestätigt, um sicherzustellen, dass die Änderungen in der Datenbank gespeichert und dauerhaft gesichert werden.

Schritt 7: Daten aus der Datenbank lesen

Um zu überprüfen, ob die Daten korrekt eingefügt wurden, können Sie Daten aus der Tabelle 'customer' in der SQLite-Datenbank mit einer SELECT-Abfrage lesen. Zum Beispiel:

# Execute a SELECT query to retrieve all data from the 'customer' table
cursor.execute("SELECT * FROM customer")

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print each row to verify inserted data
for row in rows:
    print(row)

# Close the database connection to release resources
conn.close()

# Execute a SELECT query to retrieve all data from the 'customer' table
cursor.execute("SELECT * FROM customer")

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print each row to verify inserted data
for row in rows:
    print(row)

# Close the database connection to release resources
conn.close()

Der obige Code führt eine SELECT-Abfrage auf der Tabelle 'customer' in der SQLite-Datenbank aus und ruft alle Zeilen ab. Die abgerufenen Zeilen werden in der Variablen 'rows' mithilfe der fetchall()-Methode gespeichert. Anschließend wird jede Zeile nacheinander ausgegeben, wobei die in die Tabelle "Kunde" eingefügten Daten angezeigt werden. Schließlich wird die Datenbankverbindung mit der close()-Methode geschlossen, um Ressourcen freizugeben.

Der vollständige Code lautet wie folgt:

import sqlite3
from ironxl import *  # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV

# Assign a license key (retrieved from IronXL website)
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook into memory
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')

# Create a cursor object for database operations
cursor = conn.cursor()

# Define and execute SQL to create a table if it doesn't exist
cursor.execute('''
CREATE TABLE IF NOT EXISTS customer (
    id INTEGER,
    FirstName TEXT,
    LastName TEXT,
    Gender TEXT,
    Country TEXT,
    Age INTEGER
)
''')

# Clear any existing data from the table
cursor.execute("DELETE FROM customer")

# Iteratively insert data from Excel worksheet into SQLite database
for i in range(2, 11):
    # Extracting values from columns A to F in Excel worksheet
    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
    )
    # Executing SQL INSERT command
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)

# Commit data insertion to the database
conn.commit()

# Execute a SELECT query to retrieve all data from the 'customer' table
cursor.execute("SELECT * FROM customer")

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print each row to verify inserted data
for row in rows:
    print(row)

# Close the database connection to release resources
conn.close()

import sqlite3
from ironxl import *  # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV

# Assign a license key (retrieved from IronXL website)
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook into memory
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')

# Create a cursor object for database operations
cursor = conn.cursor()

# Define and execute SQL to create a table if it doesn't exist
cursor.execute('''
CREATE TABLE IF NOT EXISTS customer (
    id INTEGER,
    FirstName TEXT,
    LastName TEXT,
    Gender TEXT,
    Country TEXT,
    Age INTEGER
)
''')

# Clear any existing data from the table
cursor.execute("DELETE FROM customer")

# Iteratively insert data from Excel worksheet into SQLite database
for i in range(2, 11):
    # Extracting values from columns A to F in Excel worksheet
    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
    )
    # Executing SQL INSERT command
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)

# Commit data insertion to the database
conn.commit()

# Execute a SELECT query to retrieve all data from the 'customer' table
cursor.execute("SELECT * FROM customer")

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print each row to verify inserted data
for row in rows:
    print(row)

# Close the database connection to release resources
conn.close()

Abschluss

Zusammenfassend hat dieses Tutorial einen automatisierten Ansatz zur Datenmanipulation aufgezeigt, insbesondere zum Extrahieren und Einfügen von Excel-Daten in eine Datenbank. Dieser Prozess steigert nicht nur die Effizienz des Datenmanagements, sondern erschließt auch dessen volles Potenzial für die Datenverarbeitung. Nutzen Sie die Leistungsfähigkeit von Python und IronXL, um Ihre Daten-Workflows zu optimieren und Ihre Projekte mit Zuversicht voranzutreiben.