Clash Royale CLAN TAG#URR8PPP

How do I use Pandas for reading multiple xlsx files and outputting into one in individual file in multiple sheets? [closed]

The .xlsx files are all found in one directory. There is only .xlsx file in this directory. I need to take each of the individual .xlsx files and insert it into a single sheet.

.xlsx

.xlsx

.xlsx

The example looks like this:

.xlsx

.xlsx

.xlsx

The final result should be one Excel file with 4 sheets.

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer
1

The process for doing this is:

0. Setup

Install required packages:

pip install pandas
pip install xlsxwriter


Then import pandas into the Python file you're working in:

import pandas as pd


1. Read in the .xlsx files

.xlsx

a. Each by name:

df1 = pd.read_excel('./excelfile1.xlsx')


etc

b. Read all in current directory in:

import os, re
dfs =
for fname in os.listdir():
if re.search(r'.xlsx$', fname):
dfs.append(pd.read_excel(fname))


2. Create a new file and add existing files as sheets

writer = pd.ExcelWriter('./newfilename.xlsx', engine='xlsxwriter')
sheet_names = ['sheet1', ...]
for df, sheet_name in zip(dfs, sheet_names):
df.to_excel(writer, sheet_name=sheet_name)
writer.save()


This will create a new Excel file in the current directory called newfilename.xlsx with each of your existing Excel files as sheets.

newfilename.xlsx