How To Import Excel File Into Database in Python

Spread the love

Hello Guys,

In this article, we’ll guide you through the process of Importing Excel Files Into a Database using Python. We’ll explore how a Python script can efficiently extract data from an Excel spreadsheet and seamlessly integrate it into a database table. Python scripts can be a powerful tool for populating databases with ease. We’ll cover essential steps such as installing the ‘xlrd‘ library in Python, importing data into a database with Python, and specifically, importing data from an Excel file into a MySQL database using Python in conjunction with ‘xlrd‘. Discover how to streamline your data import process with Python and MySQL.

Prerequisite:

Excel Data Access with xlrd Library

xlrd is a powerful Python library designed for efficient data extraction and formatting retrieval from Excel files.

Seamless MySQL Connection with PyMySQL Library

PyMySQL, the Python library, offers a streamlined approach to establish connections with MySQL database servers from within your Python applications.

Import Excel File Into Database examples:

Step 1: Install PyMySQL

The following command will be used to install PyMySQL in this phase.

Also Read: How to install Docker on Windows 10/11 STEP BY STEP

pip install PyMySQL

Step 2: Install xlrd

The following command will be used to install the xlrd library right now.

pip install xlrd

Step 3: Create a Script for Excel File Import into the Database

In this phase, we’ll construct a Python script to import data from an Excel file into a database.

Also Read: How To Generate Invoice PDF In Laravel 10

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

I hope it can be useful to you.

Leave a Comment