How to Import an Excel File into Python using pandas

You can easily import an Excel file into Python using pandas. In order to accomplish this goal, you’ll need to use read_excel.

In this short guide, I’ll review the steps to import an Excel file into Python using a simple example.

But before we start, here is a template that you may use in Python to import your Excel file:

import pandas as pd

df = pd.read_excel (r'Path where the Excel file is stored\File name.xlsx') #for an earlier version of Excel, you may need to use the file extension of 'xls'
print (df)

And if you have a specific Excel sheet that you’d like to import, you may then apply:

import pandas as pd

df = pd.read_excel (r'Path where the Excel file is stored\File name.xlsx', sheet_name='Type here the name of your Excel sheet')
print (df)

Let’s now review an example that includes the data to be imported into Python.

The Data to be Imported into Python

Suppose that you have the following table stored in an Excel file (where the Excel file name is ‘Product List’):

ProductPrice
Desktop Computer$700.00
Tablet$250.00
iPhone$800.00
Laptop$1,200.00

How would you then import the above data into Python?

You may follow the steps below to import an Excel file into Python.

Steps to Import an Excel File into Python using pandas

Step 1: Capture the file path

First, you’ll need to capture the full path where your Excel file is stored on your computer.

In my case, I stored the Excel File on my Desktop:

C:\Users\Ron\Desktop\Product List.xlsx

In the Python code, to be provided below, you’ll need to modify the path name to the location where your Excel file is stored on your computer.

Do not forget to include the file name (in our example, it’s ‘Product list‘ as highlighted in blue). You will also need to include the Excel file extension (in our case, it’s ‘.xlsx‘ as highlighted in green).

Step 2: Apply the Python code

And here is the Python code tailored to our example. I included some notes within the code to clarify some of the components used.

import pandas as pd

df = pd.read_excel (r'C:\Users\Ron\Desktop\Product List.xlsx') #(use "r" before the path string to address special character, such as '\'). Don't forget to put the file name at the end of the path + '.xlsx'
print (df)

Step 3: Run the Python code

Run the Python code and you’ll get the following:

How to Import an Excel File into Python using pandas

Notice that we got the same results as those that were stored in the Excel file.

Note: you will have to install xlrd if you get the following error when running the code:

ImportError: Install xlrd >= 1.0.0 for Excel support

You may then use the PIP install method to install xlrd as follows:

pip install xlrd

Optional Step: Selecting subset of column/s

Now what if you want to select a specific column or columns from the Excel file?

For example, what if you want to select only the Product column? If that’s the case, you can specify this column name as below:

import pandas as pd

data = pd.read_excel (r'C:\Users\Ron\Desktop\Product List.xlsx') 
df = pd.DataFrame(data, columns= ['Product'])
print (df)

Run the code (after adjusting the file path), and you would get only the Product column:

Read Excel using Pandas

You can specify additional columns by separating their names using a comma, so if you want to include both the Product and Price columns, you can use this syntax:

import pandas as pd

data = pd.read_excel (r'C:\Users\Ron\Desktop\Product List.xlsx') 
df = pd.DataFrame(data, columns= ['Product','Price'])
print (df)

You’ll need to make sure that the column names specified in the code exactly match with the column names within the Excel file. Otherwise, you’ll get NaN values.

So far you have seen how to import an Excel file into Python by specifying the path name within the code.

But did you know that you could also import the Excel file without specifying the path?

In the final section below, I’ll share with you the code to create a simple interface that will allow you to import Excel files in a streamlined manner.

Simple Interface to Import an Excel file

You may use the code below to import an Excel file into Python:

import tkinter as tk
from tkinter import filedialog
import pandas as pd

root= tk.Tk()

canvas1 = tk.Canvas(root, width = 300, height = 300, bg = 'lightsteelblue')
canvas1.pack()

def getExcel ():
    global df
    
    import_file_path = filedialog.askopenfilename()
    df = pd.read_excel (import_file_path)
    print (df)
    
browseButton_Excel = tk.Button(text='Import Excel File', command=getExcel, bg='green', fg='white', font=('helvetica', 12, 'bold'))
canvas1.create_window(150, 150, window=browseButton_Excel)

root.mainloop()

Once you run the code, you’ll see a display with a single button to import the Excel file:

read_excel

Simply click on the button and then choose the location where your Excel file is stored.

Conclusion

You just saw how to import an Excel file into Python using pandas.

At times, you may need to import a CSV file into Python. If that’s the case, you may want to check the following tutorial that explains how to import a CSV file into Python using pandas.

You may also check the pandas documentation to find out more about the different options that you may apply in regards to read_excel.