Nachrichtensymbol
Zum Fußzeileninhalt springen
  1. IronXL für Python
  2. IronXL für Python Blog
  3. Verwendung von IronXL für Python
  4. Wie man eine Excel-Datei in eine Datenbanktabelle mit Python importiert
VERWENDUNG VON IRONXL FüR PYTHON

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

Curtis Chau
Curtis Chau
Aktualisiert:

In today's data-driven world, efficient handling and processing of data are essential tasks for any organization or individual. Python, with its rich ecosystem of libraries, offers powerful tools for data manipulation and management such as the pandas library. One common scenario is the need to extract or import data from Excel spreadsheets and store or insert data in a database for further analysis or integration with other systems. In this tutorial, we'll explore how to create a Python script that automates this process, allowing you to seamlessly read data from Excel sheet files and insert it into a database. By the end of this tutorial, you'll be ready to handle data migration tasks efficiently. Let's begin!

How to Import an Excel File into a Database Table Using Python

  1. Begin by installing the IronXL library.
  2. Load your Excel file into memory using IronXL.
  3. Load the specific spreadsheet you wish to work with.
  4. Select the precise data range that you intend to import.
  5. Establish a connection with any database such as SQLite or MySQL connection database using Python.
  6. Create a new table within your SQLite database to accommodate the imported data.
  7. Insert the selected rows from the Excel file into the newly created SQLite table.
  8. Retrieve and select data from the created SQLite table for further analysis or processing.

In this tutorial, we will use IronXL, a Python library renowned for its efficiency in handling Excel files. By integrating IronXL into our script, we ensure seamless extraction of data from Excel spreadsheets, enabling smooth insertion into databases for further analysis and processing.

What is IronXL?

IronXL is a Python library developed by Iron Software, offering robust functionality for reading, generating, and editing Excel files directly within Python applications. Notably, IronXL stands out for its independence from Microsoft Excel installation, simplifying deployment across different environments. With IronXL, developers benefit from:

Cross-Platform Support: Enjoy seamless operation on Windows, macOS, Linux, Docker, Azure, and AWS platforms, ensuring adaptability to diverse development setups.

Data Import and Export: Easily handle data import from XLS, XLSX, CSV, and TSV files, with the flexibility to export worksheets to these formats and even to JSON for enhanced interoperability.

Encryption Features: Ensure data security by leveraging IronXL's encryption capabilities, allowing for the protection of XLSX, XLSM, and XLTX files with passwords.

Formulas and Recalculation: Work effortlessly with Excel formulas, with the added benefit of automatic recalculation every time a sheet is edited, ensuring accuracy and reliability in data manipulation.

Cell Styling: Customize the appearance of individual cells by adjusting font styles, sizes, background patterns, borders, and alignment, enhancing the visual presentation of your Excel documents.

Wide Range of Document Formats: With support for various formats including XLS, XLSX, XLST, XLSM, CSV, and TSV, IronXL empowers developers to handle data in a multitude of scenarios with ease and efficiency.

Now, let's begin by installing IronXL.

Step 1: Install IronXL Library

The very first step is to install the IronXL library. Run the following command to install IronXL in the command prompt.

pip install IronXL
pip install IronXL
SHELL

Step 2: Load Excel Workbook

The next step is to load the Excel file. We will be using the following Excel file for this tutorial.

How to Import an Excel File into a Database Table Using python: Figure 1 - Sample Excel File Input

The following code will load the existing Excel file in memory.

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")
PYTHON

The above Python code snippet demonstrates loading an Excel workbook named "sample_excel.xlsx" using the IronXL library. Firstly, the necessary Python module is imported from IronXL. Then, a license key is assigned to validate the library usage. You can get your free license key from the IronXL Website. Finally, the Load method is employed to open and load the specified Excel workbook into memory. This enables subsequent manipulation of its contents programmatically, such as reading data, modifying cell values, or applying formatting.

Step 3: Selecting Worksheet

To select a worksheet in an Excel workbook using IronXL, you can specify the worksheet index or name.

# 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]
PYTHON

This line selects the first worksheet in the loaded Excel workbook and assigns it to the variable worksheet, allowing subsequent operations to be performed on that specific worksheet within the workbook. This will load Excel data from an Excel sheet to a worksheet variable.

Step 4: Open the Database connection

In this tutorial, we're utilizing an SQLite database instead of a MySQL database server. To initiate database operations, we start by establishing a connection to the database.

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

The above line establishes a connection to an SQLite database named 'data.db'. If the specified database doesn't exist, it will be created automatically. This connection enables subsequent interaction with the SQLite database, such as executing queries and performing data manipulation operations.

Step 5: Create a table

The next step is to create a database table in the database, where we will import data from an Excel file. To create a table in the SQLite database, you can execute an SQL statement using the connection object.

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

The above code snippet initializes a cursor object to execute SQL commands within the SQLite database connection. It creates a table named 'customer' with columns 'id', 'FirstName', 'LastName', 'Gender', 'Country', and 'Age'. The table is created if it doesn't already exist, adhering to the specified column data types.

Step 6: Importing data into the Database using Python

Now, we will insert data into our newly created table. We will import an Excel file and insert its data into the SQLite database.

# 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()
PYTHON

The above code iterates over rows 2 to 10 in the Excel worksheet, extracting values from columns A to F for each row. These values are stored in a tuple, representing the data to be inserted into the 'customer' table. The cursor then executes an SQL INSERT command, incorporating the values tuple into the table. This process repeats for each row, effectively importing data from the Excel file into the SQLite database. Finally, conn.commit() commits the transaction, ensuring the changes are saved and persisted in the database.

Step 7: Reading data from the Database

To verify if the data was inserted correctly, you can read data from the 'customer' table in the SQLite database using a SELECT query. For example:

# 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()
PYTHON

The above code executes a SELECT query on the 'customer' table in the SQLite database, retrieving all rows. The fetched rows are stored in the 'rows' variable using the fetchall() method. Then, each row is printed iteratively, displaying the data inserted into the 'customer' table. Finally, the database connection is closed using the close() method to release resources.

How to Import an Excel File into a Database Table Using python: Figure 2 - Read from Database Output

The Complete Code is as follows:

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()
PYTHON

Conclusion

In conclusion, this tutorial has demonstrated an automated approach to data manipulation, specifically extracting and inserting Excel data into a database. This process not only enhances the efficiency of data management but also unlocks its full potential for data handling endeavors. Embrace the power of Python and IronXL to optimize your data workflows and propel your projects forward with confidence.

Häufig gestellte Fragen

Wie kann ich Daten aus einer Excel-Datei mit Python in eine Datenbank importieren?

Sie können die IronXL-Bibliothek verwenden, um Daten aus einer Excel-Datei in eine Datenbank zu importieren, indem Sie zuerst die Excel-Datei mit `WorkBook.Load()` laden, dann das Arbeitsblatt auswählen und eine Datenbankverbindung mit SQLite herstellen, um die Daten einzufügen.

Welche Vorteile bietet die Verwendung von IronXL für die Verwaltung von Excel-Dateien in Python?

IronXL ermöglicht die Verwaltung von Excel-Dateien, ohne dass Microsoft Excel installiert sein muss, unterstützt plattformübergreifende Operationen, bietet robuste Funktionen wie Verschlüsselung und Formelneuberechnungen und verwaltet Prozesse zur Datenextraktion und -einfügung effizient.

Wie installiere ich IronXL zur Verwendung in Python-Projekten?

Um IronXL für Python-Projekte zu installieren, können Sie den Befehl `pip install IronXL` verwenden. Dadurch wird IronXL in Ihrer Python-Umgebung hinzugefügt, sodass Sie Excel-Dateien effizient verwalten können.

Ist es möglich, Excel-Dateien in Python zu verarbeiten, ohne Microsoft Excel installiert zu haben?

Ja, mit IronXL können Sie Excel-Dateien verarbeiten, ohne dass Microsoft Excel installiert ist. IronXL bietet alle notwendigen Funktionen, um Excel-Dateien unabhängig zu lesen, zu bearbeiten und zu schreiben.

Wie läuft der Prozess ab, um eine Datenbanktabelle für die Speicherung von Excel-Daten in Python zu erstellen?

Um eine Datenbanktabelle in Python zu erstellen, können Sie das SQLite-`sqlite3`-Modul verwenden. Nachdem Sie eine Verbindung mit `connect()` hergestellt haben, führen Sie eine SQL-`CREATE TABLE`-Anweisung über ein Cursor-Objekt aus.

Wie kann ich überprüfen, ob Excel-Daten erfolgreich in eine SQLite-Datenbank eingefügt wurden?

Sie können die Einfügung überprüfen, indem Sie eine `SELECT`-Abfrage zu der Tabelle ausführen und die `fetchall()`-Methode verwenden, um alle Zeilen aus dem Result-Set abzurufen und zu drucken.

Welche Schritte sollten beim Datenmigrationsprozess von Excel zu einer Datenbank unter Verwendung von Python befolgt werden?

Die Schritte umfassen die Installation von IronXL, das Laden der Excel-Datei, das Auswählen des Arbeitsblattes, das Herstellen einer Verbindung zur Datenbank, das Erstellen einer Tabelle sowie das Durchlaufen der Excel-Zeilen, um Daten mit SQL-`INSERT`-Befehlen einzufügen.

Kann IronXL mit Excel-Formeln umgehen und diese in Python neu berechnen?

Ja, IronXL unterstützt Excel-Formeln und kann diese neu berechnen, was eine umfassende Lösung für die Manipulation von Excel-Dateien innerhalb von Python-Anwendungen bietet.

Unterstützt IronXL plattformübergreifende Operationen für die Verwaltung von Excel-Dateien?

Ja, IronXL unterstützt plattformübergreifende Operationen, einschließlich Umgebungen wie Windows, macOS, Linux, Docker, Azure und AWS, was es zu einer vielseitigen Wahl für verschiedene Entwicklungsumgebungen macht.

Wie kann IronXL Datenworkflows in Python-Anwendungen verbessern?

IronXL verbessert Datenworkflows, indem es effiziente Datenextraktions-, -manipulations- und -einfügefunktionen bietet, die Datenmanagementprozesse optimieren und die Leistung datengetriebener Anwendungen verbessern.

Curtis Chau
Curtis Chau
  Jetzt mit dem Ingenieurteam chatten
Technischer Autor

Curtis Chau hat einen Bachelor-Abschluss in Informatik von der Carleton University und ist spezialisiert auf Frontend-Entwicklung mit Expertise in Node.js, TypeScript, JavaScript und React. Leidenschaftlich widmet er sich der Erstellung intuitiver und ästhetisch ansprechender Benutzerschnittstellen und arbeitet gerne mit modernen Frameworks sowie der Erstellung gut strukturierter, optisch ansprechender ...

Weiterlesen

Verwandte Artikel

30-Tage-Testversion → Volles Produkt. Keine Grenzen. Keine Karte. Testversion