In this article, we will see how to import an excel file into a database using python. Here, we will learn how to read data from an excel sheet and insert it into a database table using python script. You can insert data into a database using python script. python script to read excel files and insert them into the database

So, let's see how to import data from an excel file into MySQL using python, import data into a database using python, import data from excel into MySQL using python xlrd, and how to install xlrd in python.

Prerequisite:

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.

 

In this step, we will install PyMySQL using the following command.

pip install PyMySQL

 

Now, we will install xlrd library using the following command.

pip install xlrd

 

In this step, 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
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!"

 

---

You might also like:

• Read Also: How To Export Excel File In Laravel 9
• Read Also: How To Convert HTML To PDF In Python
• Read Also: Laravel 9 Upload Large CSV File Using Queue
• Read Also: How To Convert Excel To JSON In Javascript