18

According to pandas doc for 0.21+, pandas.read_excel has a parameter sheet_name that allows specifying which sheet is read. But when I am trying to read the second sheet from an excel file, no matter how I set the parameter (sheet_name = 1, sheet_name = 'Sheet2'), the dataframe always shows the first sheet, and passing a list of indices (sheet_name = [0, 1]) does not return a dictionary of dataframes but still the first sheet. What might be the problem here?

smci
  • 32,567
  • 20
  • 113
  • 146

7 Answers7

24

It looks like you're using the old version of Python. So try to change your code 

df = pd.read_excel(file_with_data, sheetname=sheet_with_data)

It should work properly.

Sergey Solod
  • 695
  • 7
  • 15
  • 12
    check your version of pandas `pd.__version__` If .21 or newer, use `sheet_name` keyword parameter. If older version, use `sheetname` [pandas.read_excel](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html) – datalifenyc Dec 30 '18 at 17:49
7

You can try to use pd.ExcelFile:

xls = pd.ExcelFile('path_to_file.xls')
df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')
whiplassh
  • 143
  • 1
  • 8
2

This works:

df = pd.read_excel(open(file_path_name), 'rb'), sheetname = sheet_name)

file_path_name = your file
sheet_name = your sheet name

This does not for me:

df = pd.read_excel(open(file_path_name), 'rb'), sheet_name = sheet_name)

Gave me only the first sheet, no matter how I defined sheet_name.

--> it is an known error: https://github.com/pandas-dev/pandas/issues/17107

Alex Riabov
  • 8,655
  • 5
  • 47
  • 48
Torolito
  • 23
  • 6
1

Try at Terminal, type the following first, then re-run your program:

pip install xlrd

Rishabh Sahrawat
  • 2,437
  • 1
  • 15
  • 32
Lee Li Fong
  • 274
  • 1
  • 6
0

I also faced this problem until I found this solution:

rd=pd.read_excel(excel_file,sheet_name=['Sheet2']),

Here excel_file means "file name".

The filename should be the full path to the file.

Make sure to use two backslashes (\\) instead of just one!

In my case, this works.

TheTechRobo the Nerd
  • 1,249
  • 15
  • 28
Suryapratapsinh Thakor
  • 1
  • 1
0

I would just use double quotes like this.

# Returns a DataFrame
pd.read_excel("path_to_file.xls", sheet_name="Sheet1")
zheng yin
  • 1
0
pip install openpyxl

The above command worked for me.

ENIAC
  • 813
  • 1
  • 8
  • 19
Darkside
  • 1
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – AlexK Apr 09 '23 at 20:57