How to Import Excel File into Database using Python

In the realm of data-driven applications, as a Python developer, I often need to import data from external sources, such as Excel files, into databases. Python, with its robust libraries and packages, provides me with an efficient and straightforward way to accomplish this task.

In this article, I will explore how to import an Excel file into a database using Python, empowering me to seamlessly integrate data from spreadsheets into my applications.

Throughout this step-by-step guide, I will leverage the power of Python and its libraries to extract data from Excel files, transform it into a suitable format, and efficiently load it into a database.

Whether you are a seasoned Python developer like me or just starting your journey with the language, this tutorial will equip you with the knowledge and skills to import Excel files into databases with ease.

So, if you're ready to harness the power of Python and streamline your data import workflows by integrating Excel files into databases, let's dive in together and learn how to import Excel files using Python step by step.

Prerequisite:

Before we begin the step-by-step guide, there are a couple of prerequisites that we need to fulfill.

1. xlrd: xlrd is a library for reading data and formatting information from Excel files.
2. PyMySQL: pyMySQL is a python library for connecting to a MySQL database server from Python.

 

xlrd is a library for reading data and formatting information from Excel files. It allows us to extract data from Excel spreadsheets effectively. To install xlrd, open your terminal or command prompt and run the following command

pip install xlrd

 

PyMySQL is a Python library for connecting to a MySQL database server from Python. It provides a straightforward way to establish a connection and interact with a MySQL database. To install PyMySQL, run the following command in your terminal or command prompt

pip install PyMySQL

By installing these two libraries, xlrd and PyMySQL, we will have the necessary tools to import Excel files into a MySQL database using Python. These libraries will enable us to read data from Excel files and establish a connection to the MySQL database, respectively.

Once we have these prerequisites set up, we can proceed with the step-by-step guide to importing Excel files into a database using Python.

 

Now that we have installed the required libraries, let's create a Python script to import an Excel file into a database. Follow the steps below.

import xlrd
import MySQLdb

# Open the workbook and define the worksheet
book = xlrd.open_workbook("myfile.xls")
sheet = book.sheet_by_name()

# Establish a MySQL connection
database = MySQLdb.connect (host="localhost", user = "root", passwd = "root", db = "mysql")

# Get the cursor, which is used to traverse the database, line by line
cursor = database.cursor()

# Create Table
product_details_table = ("CREATE TABLE IF NOT EXISTS product_details(id int,product_id varchar(255) NOT NULL,product_name text,product_price varchar(255),product_rating BLOB,product_star_rating float,product_url LONGTEXT, PRIMARY KEY (product_id))")

# Execute create table query
cursor.execute(product_details_table)

# Create the INSERT INTO SQL query
query = "INSERT INTO product_details (product_id,product_name,product_price,product_rating,product_star_rating,product_url) VALUES (%s,%s,%s,%s,%s,%s)"

# Create a For loop to iterate through each row in the XLS file

    for r in range(1,sheet.nrows):
        product_id = sheet.cell(r,0).value
        product_name = sheet.cell(r,1).value
        product_price = sheet.cell(r,2).value
        product_rating = sheet.cell(r,3).value      
        product_star_rating = sheet.cell(r,4).value        
        product_url = sheet.cell(r,5).value

# Assign values from each row        
values = (product_id,product_name,product_price,product_rating,product_star_rating,product_url)

# Execute sql Query
cursor.execute(query, values)

# Close the cursor
cursor.close()

# Commit the transaction
database.commit()

# Close the database connection
database.close()

# Print results
print ""
print "Done!"

Remember to replace 'myfile.xls' with the actual path to your Excel file and customize the table structure and database credentials as needed.

After executing this script, it will create the table (if it doesn't exist) and insert the data from the Excel file into the MySQL database table. The final "Done!" message will indicate the successful completion of the import process.

 

In this comprehensive guide, we explored the process of importing Excel files into databases using Python. By following the step-by-step example, we learned how to extract data from an Excel file, establish a connection to a MySQL database, create a table, and efficiently import the data into the database.

This knowledge empowers us to seamlessly integrate data from spreadsheets into our database-driven applications.

Python, with its powerful libraries such as xlrd for Excel file parsing and MySQLdb for MySQL database connectivity, provides a robust and straightforward solution for importing data.

By leveraging these libraries, we can handle large volumes of data from Excel files, transform it as needed, and efficiently store it in a database for further analysis and processing.

I hope this guide has provided you with a solid understanding of importing Excel files into databases using Python.

 

---

You might also like:

• Read Also: How To Export CSV File In Laravel 10 Example
• Read Also: Python: A Journey into the World's Most Popular Language
• Read Also: Python for Beginners: Getting Started with Python
• Read Also: Convert HTML to PDF in Python: Step-by-Step Guide

---