How To Import Excel File Into Database Using Python

  Jul 2023
  Category: Python
How To Import Excel File Into Database Using Python

Hi Developers ,


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

Install PyMySQL

we will install PyMySQL using the following command.

pip install PyMySQL

Install xlrd

we will install xlrd library using the following command.

pip install xlrd

Create Script for Excel File Import into the Database

we will create a python script to import excel file data into the database using python.

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

# 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

# Commit the transaction

# Close the database connection

# Print results
print ""
print "Done!"

We hope it helps everyone. Thanks for supporting ITSolutionsGuides and keep supporting us also follow us in social media platforms.

Subscribe for NewsLetter

Be the first to know about releases and tutorial news and solutions.

We care about your data in our privacy policy.


ITSolutionsGuides was started mainly to provide good and quality web solutions for all the developers. We provide tutorials to support all the developers and also we try to provide solutions to the errors we face while coding.

Contact US

ITSolutionsGuides, provide users with an easy-to-use form to reach out for support or inquiries.

whatsapp  gmail  instagram-new--v1  facebook-circled  twitter-circled  linkedin  github  pinterest 

Copyright © 2023 - 2024 All rights reserved | ITSolutionsGuides