Using Python to read data from an Excel file

I recently catalogued my retro gaming collection and took the high tech approach of using Excel to do this. I then decided to over-engineer this further and store the data in Azure Table storage……however I didn’t want to manually re-key all of the games!

I took a look at options for how I could automate reading an Excel file using Python – my plan being to write a script that would extract the data from the Excel file and then write this to Azure Table storage.

My Excel file is pretty basic with a sheet for each of the systems I own, within each sheet is a list of the games for that system:

Here is how I eventually figured out how to read data from an Excel file using Python…..

Step 1 – Install the pre-req modules

For this I needed to install two modules – pandas and openpyxl. I ran the following commands in a terminal to do this:

pip install pandas
pip install openpyxl

Step 2 – Create the script!

I wrote the script below (which took me far longer than I’d like to admit!), which does the following:

• Obtains the names of each sheet within the Excel file – remember I have a separate sheet for each games console.
• Loops through each of the sheets, opening each sheet individually.
• For each sheet, it Iterates through each row in the sheet and prints the row (game).

excelfilepath = 'C:\\Users\\brend\\OneDrive\\Documents\\Retro Games Collection.xlsx' # Define the path to the Excel file to read
excel = pd.ExcelFile(excelfilepath)
sheets = excel.sheet_names # Read the names of each of the sheets within the Excel file

for sheet in sheets: # Loop through each sheet
    print("")
    print(sheet)
    print("----------")
    excel = pd.read_excel(excelfilepath,header = None, sheet_name= sheet) # Open the Excel file and the specific sheet within the sheets list
    i = 0
    while i < len(excel[0]) - 1: # Read the number of rows within the sheet and minus 1 to get the maximum index number for the while loop
        print(excel[0][i]) # Print the row from the specific sheet using the index i
        i += 1

Here is what it looks like in action:

The next step for me is to update the while loop so that it writes each game to Azure Table storage. I’ve written about how to do that using Python here.