Python – Inserting data from Excel file into Database table

On this post we will do an exercise in which we will read data from an Excel file and insert this data into a SQL Server database table.

Consider the situation in which you want open an excel file to perform some mathematical operations and after that insert the results into a database table.

Note that in this example we will not focus on complex mathematical operations, we will just perform a silly operation like multiply by 10. Our objective here is to show the integration between an external file, a python program and a database table.

The Excel File

We will consider a simple excel file with just one worksheet and five columns containing numbers

Save the file into a location that will be referred by the python code. You don´t need to specify the same path, there´s a reason for this path below which will be explained in the next post. For the moment just save the file into a location that you will remember.

The Database Table

Create a table into SQL Server database in order to save the results.

The Python Program

Now we will finally “link the points”. We will implement the algorithm represented on the flowchart below.

1 – Import packages

We will import the package XLRD for excel manipulation and PYODBC for database interaction.

2 – Database string connection

We will provide the settings and the credentials to connect to SQL Server Database.

3 – Excel – read, multiply by 10 and store.

I know, multiplying by 10 looks silly, but as I said previously, the focus here is to show the integration between platforms. I am sure that you can enhance this example with smart statistic operations.

In the code below, you will open the excel file created previously and store its contents into data structures to be used later.

4 – Insert into Database Table

After loading values from excel file into a vector of vector, we have to read this vector line by line to insert its contents into a database table

Save this code somewhere you can remember later.

Execute the program

Now all you need to do is to execute your program.

… and that´s the result you can see on the console

Let´s check the database to see if the data is there.

That´s it. You can use this example for more complex operations.
On the next post we will use this solution to integrate with another solution. We will be able to create an interface in which we can upload the file and see the final results in a webpage.

I hope this can be helpful for you. Have a good day.

See the code for this solution below:

SQL OBJECT

CREATE_TABLE Download

PYTHON CODE

I am sorry for that, but I was not able to upload the file with the python code due to the security policies of the host service. Please see the code below and pay atention to the identation.

import package xlrd for excel
from xlrd import open_workbook
import package pyodbc for database
import pyodbc
# Specify the settings to connect to the database
conn = pyodbc.connect('Driver={SQL Server};'
                       'Server=LENOVO\SQLEXPRESS;'
                       'Database=PROTO;'
                       'UID=user01;'
                       'PWD=user01;'
                       'Trusted_Connection=yes;')
#Open the excel file on the specified location
wb = open_workbook('C:\WEBPROJECTS\WebPython\WebPython\LOAD_TEST.xls')
#For each worksheet in the excel file
for sheet in wb.sheets():
     # get the number of rows
     number_of_rows = sheet.nrows
     # get the number of columns
     number_of_columns = sheet.ncols
     # declare a vector to store the data in each line
     items = []
     # Initialize a loop from 1 to the number of rows 
     for row in range(1, number_of_rows):     
         # initialize the variable to store the values     
         values = []     
         for col in range(number_of_columns):         
             # assign the value of combination row(x) and col(y), the worksheet cell, to the variable value         
             value  = (sheet.cell(row,col).value)         
             try:             
                # multiply the content of the specified worksheet cell by 10
                  value = str(int(value)*10)         
             except ValueError:             
                pass         
             finally:             
                # add the cell to the vector "values"             
                values.append(value)     
         item = values     
         # append the vector to the vector items
          items.append(item)
#open a connection to the database
cursor = conn.cursor()
# Now we have a list of vectors represented by "items"
# We will run through this list to insert its elements into the database table
# clear the database table before inserting data
cursor.execute(''' DELETE FROM PROTO.dbo.TB_VALUES ''')
#for each item (vector) existent inside the list "items"
for item in items:
     print(item)
     # note that for each column of the table we have a respective position of the vector ( item[n] )
     cursor.execute(''' INSERT INTO PROTO.dbo.TB_VALUES (COL1, COL2, COL3, COL4, COL5)
                     VALUES  ( '''+item[0]+''','''+item[1]+''','''+item[2]+''','''+item[3]+''','''+item[4]+''') ''')
     # commit the transaction to persist the data on the database table.
     conn.commit()