python – How to read a .xlsx file using the pandas Library in iPython?

python – How to read a .xlsx file using the pandas Library in iPython?

I usually create a dictionary containing a DataFrame for every sheet:

xl_file = pd.ExcelFile(file_name)

dfs = {sheet_name: xl_file.parse(sheet_name) 
          for sheet_name in xl_file.sheet_names}

---

Update: In pandas version 0.21.0+ you will get this behavior more cleanly by passing sheet_name=None to read_excel:

dfs = pd.read_excel(file_name, sheet_name=None)

---

In 0.20 and prior, this was sheetname rather than sheet_name (this is now deprecated in favor of the above):

dfs = pd.read_excel(file_name, sheetname=None)

The following worked for me:

from pandas import read_excel
my_sheet = Sheet1 # change it to your sheet name, you can find your sheet name at the bottom left of your excel file
file_name = products_and_categories.xlsx # change it to the name of your excel file
df = read_excel(file_name, sheet_name = my_sheet)
print(df.head()) # shows headers with top 5 rows

python – How to read a .xlsx file using the pandas Library in iPython?

pd.read_excel(file_name) 

sometimes this code gives an error for xlsx files as: XLRDError:Excel xlsx file; not supported

instead , you can use openpyxl engine to read excel file.

df_samples = pd.read_excel(rfilename.xlsx, engine=openpyxl)