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

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

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.

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

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]

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

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

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

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

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.

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

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.