python combine multiple excel files

Solutions on MaxInterview for python combine multiple excel files by the best coders in the world

showing results for - "python combine multiple excel files"
Cedric
01 Mar 2020
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')