How to open an excel file with multiple sheets in pandas?

Question

I have an excel file composed of several sheets. I need to load them as separate dataframes individually. What would be a similar function as pd.read_csv("") for this kind of task?

P.S. due to the size I cannot copy and paste individual sheets in excel

Possible duplicate of [Using Pandas to pd.read\_excel() for multiple worksheets of the same workbook](http://stackoverflow.com/questions/26521266/using-pandas-to-pd-read-excel-for-multiple-worksheets-of-the-same-workbook) — tutuca, May 01 '17 at 03:42

score 9 · Accepted Answer · edited Aug 17 '21 at 07:41

9

Use pandas read_excel() method that accepts a sheet_name parameter:

import pandas as pd

df = pd.read_excel(excel_file_path, sheet_name="sheet_name")

Multiple data frames can be loaded by passing in a list. For a more in-depth explanation of how read_excel() works see: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html

edited Aug 17 '21 at 07:41

mh-anwar

51
3
6

answered Jul 23 '15 at 09:05

DeepSpace

72,713
11
96
140

2

`sheetname=` has been replaced with `sheet_name=` – BallpointBen Apr 10 '18 at 19:19

score 3 · Answer 2 · answered Jul 04 '19 at 18:37

If you can't type out each sheet name and want to read whole worksheet try this:

                dfname=pd.ExcelFile('C://full_path.xlsx')
                print(dfname.sheet_names)
                df=pd.read_excel('C://fullpath.xlsx')
                for items in dfname.sheet_names[1:]:
                    dfnew=pd.read_excel(full_path,sheet_name=items)
                    df=pd.concat([df,dfnew])

The thing is that pd.read_excel() can read the very first sheet and rest are unread.So you can use this

score 0 · Answer 3 · answered Jul 23 '15 at 09:04

0

exFile = ExcelFile(f) #load file f

data = ExcelFile.parse(exFile) #this creates a dataframe out of the first sheet in file

answered Jul 23 '15 at 09:04

Blue Moon

3,899
14
47
82

How to open an excel file with multiple sheets in pandas?

3 Answers3