Importing Excel File into Database Using Python

In today's data-driven world, I often find myself dealing with large amounts of information that needs to be organized and analyzed efficiently. Excel spreadsheets have been my go-to tool for managing data, but as the volume grows, I realize the importance of centralizing and storing it in a database for better scalability and accessibility.

Thankfully, Python, with its extensive library ecosystem, offers powerful solutions to seamlessly import Excel files into databases.

In this article, I will guide you through the step-by-step process of importing an Excel file into a database using Python.

Together, we will explore how to extract data from Excel spreadsheets and transfer it into a database management system (DBMS) like MySQL, PostgreSQL, or SQLite.

Whether you're working with financial data, customer records, or any other tabular data, this guide will equip you with the necessary knowledge and skills to efficiently handle the import process.

In this article, we will guide you through the process of reading data from an Excel file and inserting it into a database table using Python scripts.

Before we get started, there are a couple of prerequisites you need to have in place. Firstly, we will be using the xlrd library, which enables us to read data and format information from Excel files. You can easily install this library using pip.

Secondly, we will be working with a MySQL database, so we need to have the PyMySQL library installed. This library allows us to establish a connection to a MySQL database server from Python.

Once you have these prerequisites set up, we can dive into the main process of importing an Excel file into a database.

We will walk you through the steps of reading the Excel file, extracting the data, and utilizing Python scripts to insert it into the respective database table.

By the end of this article, you will have a clear understanding of how to use Python to read Excel files and import their data into a database.

This knowledge will empower you to efficiently handle data import tasks and streamline your data management workflows.

So, let's get started and unlock the potential of Python for importing Excel files into databases.

Before we begin, there is one prerequisite we need to address: installing the PyMySQL library. PyMySQL allows us to establish a connection to a MySQL database server from Python.

To install PyMySQL, simply run the following command.

pip install PyMySQL

With PyMySQL successfully installed, we can move on to the exciting part of importing Excel files into a database using Python.

 

To proceed with importing Excel files into a database using Python, we need to install the xlrd library.

The xlrd library allows us to read data and format information from Excel files, which is essential for extracting data to be inserted into the database.

pip install xlrd

By executing this command, the xlrd library will be installed, enabling us to seamlessly read Excel files in our Python scripts.

Once the installation is complete, we can move on to the exciting part of importing Excel files into the database.

We will guide you through the necessary steps, providing clear explanations and practical examples to ensure your understanding of the process.

In the upcoming sections, we will demonstrate how to read data from an Excel file, establish a connection to your chosen database, and insert the extracted data into the appropriate table using Python.

This knowledge will empower you to efficiently handle Excel file imports and optimize your database management workflows. 

 

 

Now that we have the necessary libraries installed, it's time to create a script that will enable us to import an Excel file into a database using Python. This script will automate the process, making it convenient and efficient.

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

Let's dive into the code and understand its functionality:

1. Importing the necessary libraries:

   • The xlrd the library is imported to handle reading data from Excel files.
   • The MySQLdb library is imported to establish a connection to the MySQL database.

2. Opening the workbook and defining the worksheet:

   • The open_workbook() function from the xlrd library is used to open the Excel file, "myfile.xls".
   • The sheet_by_name() function is called to retrieve a specific worksheet within the workbook. You need to provide the name of the worksheet as an argument.

3. Establishing a MySQL connection:

   • The connect() function from MySQLdb is used to establish a connection to the MySQL database.
   • The necessary connection parameters such as the host, user, password, and database name are provided.

4. Getting the cursor:

   • The cursor() function is called on the database connection object to obtain a cursor object. The cursor is used to traverse the database line by line.

5. Creating the table:

   • The variable product_details_table stores the SQL query to create a table named product_details if it doesn't already exist.
   • The execute() method of the cursor is used to execute the create table query.

6. Creating the INSERT INTO SQL query:

   • The variable query stores the SQL query for inserting data into the product_details table. The query uses placeholders (%s) for values that will be dynamically assigned later.

7. Looping through the rows in the Excel file:

   • A for loop is used to iterate through each row in the Excel sheet, starting from the second row (index 1).
   • Data is extracted from each cell in the row using the cell() function and stored in respective variables.

8. Assigning values and executing the SQL query:

   • The values variable is assigned with the extracted data from each row.
   • The execute() method of the cursor is used to execute the SQL query with the assigned values.

9. Closing the cursor, committing the transaction, and closing the database connection:

   • The close() method is called on the cursor object to close it.
   • The commit() method is called on the database connection to commit the transaction and save the changes.
   • The close() method is called on the database connection to close it.

10. Printing the result:

    • A simple message is printed to indicate the completion of the script.

This script essentially opens the Excel file, establishes a connection to the MySQL database, creates a table if it doesn't exist, reads data from the Excel file, and inserts it into the database table. Finally, it commits the changes and closes the database connection.

 

---

You might also like:

• Read Also: Python for Beginners: Getting Started with Python
• Read Also: Python: A Journey into the World's Most Popular Language
• Read Also: AJAX CRUD Operations In Laravel 10: Step-by-Step Guide
• Read Also: Building Complete CRUD Application in Laravel 10

---