1import os
2import pandas as pd
3cwd = os.path.abspath('')
4files = os.listdir(cwd)
5
6## Method 1 gets the first sheet of a given file
7df = pd.DataFrame()
8for file in files:
9 if file.endswith('.xlsx'):
10 df = df.append(pd.read_excel(file), ignore_index=True)
11df.head()
12df.to_excel('total_sales.xlsx')
13
14
15
16## Method 2 gets all sheets of a given file
17df_total = pd.DataFrame()
18for file in files: # loop through Excel files
19 if file.endswith('.xlsx'):
20 excel_file = pd.ExcelFile(file)
21 sheets = excel_file.sheet_names
22 for sheet in sheets: # loop through sheets inside an Excel file
23 df = excel_file.parse(sheet_name = sheet)
24 df_total = df_total.append(df)
25df_total.to_excel('combined_file.xlsx')