Test in production without watermarks.
Works wherever you need it to.
Get 30 days of fully functional product.
Have it up and running in minutes.
Full access to our support engineering team during your product trial
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!
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.
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.
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
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.
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.
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.
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.
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.
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()
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.
IronXL is a Python library developed by Iron Software, offering robust functionality for reading, generating, and editing Excel files directly within Python applications. It stands out for its independence from Microsoft Excel installation and supports cross-platform use.
You can install IronXL for Python using the command: `pip install IronXL`.
Benefits of using IronXL include cross-platform support, robust data import and export functions, encryption capabilities for data security, support for Excel formulas and recalculation, and versatile document format handling.
To load an Excel file using IronXL, use the `WorkBook.Load()` method after setting up the library with a valid license key. This method loads the Excel workbook into memory for further manipulation.
You can connect to an SQLite database in Python using the `sqlite3` module. Use the `connect()` function with the database name to establish a connection.
The steps include installing IronXL, loading the Excel file, selecting the worksheet, establishing a database connection, creating a table, and then inserting the Excel data into the database table.
Yes, IronXL does not require Microsoft Excel to be installed on your machine, making it easier to deploy across different environments.
To create a table in an SQLite database using Python, execute an SQL 'CREATE TABLE' statement through a cursor object obtained from your database connection.
You can verify data insertion by executing a SELECT query on the table and fetching the results using the `fetchall()` method, which retrieves all rows from the result set.
Yes, IronXL supports cross-platform operations, including Windows, macOS, Linux, Docker, Azure, and AWS, ensuring it can be used in a variety of development environments.