Message icon
Skip to footer content
  1. IronXL for Python
  2. IronXL for Python Blog
  3. Using IronXL for Python
  4. How to Import an Excel File into a Database Table Using python
USING IRONXL FOR PYTHON

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

Curtis Chau
Curtis Chau
Updated:

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.

Frequently Asked Questions

How can I import data from an Excel file into a database using Python?

You can use the IronXL library to import data from an Excel file into a database by first loading the Excel file with `WorkBook.Load()`, then selecting the worksheet and establishing a database connection with SQLite to insert the data.

What are the benefits of using IronXL for handling Excel files in Python?

IronXL allows you to handle Excel files without needing Microsoft Excel installed, supports cross-platform operations, provides robust features like encryption and formula recalculations, and efficiently manages data extraction and insertion processes.

How do I install IronXL for use in Python projects?

To install IronXL for Python projects, you can use the command: `pip install IronXL`. This will add IronXL to your Python environment, allowing you to handle Excel files efficiently.

Is it possible to process Excel files in Python without Microsoft Excel installed?

Yes, using IronXL, you can process Excel files without having Microsoft Excel installed. IronXL provides all necessary functionalities to read, edit, and write Excel files independently.

What is the process to create a database table for storing Excel data in Python?

To create a database table in Python, you can use the SQLite `sqlite3` module. After establishing a connection using `connect()`, execute an SQL `CREATE TABLE` statement through a cursor object.

How can I verify if Excel data has been successfully inserted into an SQLite database?

You can verify the insertion by executing a `SELECT` query on the table and using the `fetchall()` method to retrieve and print all rows from the result set.

What steps should be followed for data migration from Excel to a database using Python?

The steps include installing IronXL, loading the Excel file, selecting the worksheet, connecting to the database, creating a table, and iterating through the Excel rows to insert data using SQL `INSERT` commands.

Can IronXL handle Excel formulas and recalculate them in Python?

Yes, IronXL supports Excel formulas and can recalculate them, providing a comprehensive solution for Excel file manipulation within Python applications.

Does IronXL support cross-platform operations for Excel file handling?

Yes, IronXL supports cross-platform operations, including environments like Windows, macOS, Linux, Docker, Azure, and AWS, making it a versatile choice for various development setups.

How can IronXL enhance data workflows in Python applications?

IronXL enhances data workflows by offering efficient data extraction, manipulation, and insertion capabilities, which optimize data management processes and improve the performance of data-driven applications.

Curtis Chau
Curtis Chau
  Chat with engineering team now
Technical Writer

Curtis Chau holds a Bachelor’s degree in Computer Science (Carleton University) and specializes in front-end development with expertise in Node.js, TypeScript, JavaScript, and React. Passionate about crafting intuitive and aesthetically pleasing user interfaces, Curtis enjoys working with modern frameworks and creating well-structured, visually appealing manuals.

...

Read More

Related Articles

30-day trial → Full product. No limits. No card. Start Free Trial