In Tutorial 5 we inserted some sample data into our table named test_table in our test.db in sqlite. We read the data from a file named book1.xls and we used the JXL library to accomplish our task.

 

Now let’s do the same in Python. We will need to import a package called xlrd in order to read excel data using Python. Also note that we can read both xls and xlsx files using this xlrd package. To import the package open a command prompt and

 

type:

 

pip install xlrd

​

Remember we did the same when installing flask in tutorial 6.

 

We will also need to copy test.db and sqlite 3 into our Python workspace. Go back to tutorial 4 where I showed u how to find our current workspace and how to copy our database file into that workspace. We need to do this because in tutorial 4 we inserted sample data into our test_table and for this tutorial we will be using the same table and the same test.db schema so by copying our schema over to our current python workspace the program will be able to insert into the same test_table.

 

Okay now that we got our environment set up, create a new PyDev Module. I named mine as read_excel.

 

Copy and paste the following code into your new module and save. Notice that reading excel using Python requires many less steps than does Java

 

import xlrd

import sqlite3

 

# Open the workbook and define the worksheet

book = xlrd.open_workbook("C:/users/rickd/Desktop/files/book1.xls")

 

#assign the variable first_sheet and it gets first sheet without knowing its name

first_sheet = book.sheet_by_index(0)

 

num_rows = first_sheet.nrows

num_cols = first_sheet.ncols

 

print ("number of rows " , num_rows)

print ("number of cols " , num_cols)

 

# Establish an SQLite connection to test.db located in current workspace

connection = sqlite3.connect('test.db')

 

# Get the cursor, which is used to traverse the database, line by line

cursor = connection.cursor()

 

# Create the INSERT INTO sql query

query = """INSERT INTO test_table (column1, column2, column3, column4) VALUES (?, ?, ?, ?)"""

 

# Create a For loop to iterate through each row in the XLS file, starting at row 1 position 0

​

for row in range(0, first_sheet.nrows): #starting position for row is 0...loop for n number of rows in table

       value_for_col_1 = first_sheet.cell(row,0).value #get val at each location, then go to next row and do the same

       value_for_col_2 = first_sheet.cell(row,1).value

       value_for_col_3 = first_sheet.cell(row,2).value

       value_for_col_4 = first_sheet.cell(row,3).value

      

       # Assign values from each row

       values = (value_for_col_1, value_for_col_2, value_for_col_3, value_for_col_4)

 

       # Execute sql Query.....put assigned vals into query

       cursor.execute(query, values)

 

# Close the cursor

cursor.close()

 

# Commit the transaction

connection.commit()

 

# Close the database connection

connection.close()

 

Click run as Python run..u see number of rows and columns printed in the console.

 

That’s it we are done. Now go back to your workspace and double click on sqlite3 and then .open test.db and then select * from test_table. We will see that the data is inserted. Tutorial 4 explained how to do this part

 

Click here for intro blog about author Rick Delpo