How to make multiple excel files into one file with multiple sheets dynamically with Python

Question

So far I do it pretty manually:

df1 = pd.read_excel('/Users/user/Downloads/1.xlsx')
df2 = pd.read_excel('/Users/user/Downloads/2.xlsx')
df3 = pd.read_excel('/Users/user/Downloads/3.xlsx')

with ExcelWriter('excels.xlsx') as writer:
    df1.to_excel(writer, sheet_name='1')
    df2.to_excel(writer, sheet_name='2')
    df3.to_excel(writer, sheet_name='3')

But ultimately I want to make it so I can automatically get the files pulled in from a folder and just have them become sheets. So far I have:

excel_names = ["1.xlsx", "2.xlsx", "3.xlsx"]

excels = [pd.ExcelFile(name) for name in excel_names]

But I can't figure out how to save them as multiple sheets.

This question does not show enough research effort. An efficient solution to your problem will probably involve pandas.pydata.org/pandas-docs/stable/reference/api/… — tinkerbeast
– tinkerbeast, Commented Jan 2, 2020 at 17:34

anky · Accepted Answer · 2020-01-02 17:26:13Z

3

You can use:

import os
import pandas as pd

folder=r'C:\path_to\your_folder'
path=os.path.join(folder,'excels.xlsx')
writer = pd.ExcelWriter(path, engine='openpyxl')
for i in os.listdir(folder):
    pd.read_excel(os.path.join(folder,i)).to_excel(writer, i[:-5], index=False)
    writer.save()

This gives a file named excels.xlsx in the same folder which has all files as sheetnames in a single file.

answered Jan 2, 2020 at 17:26

anky

75.3k11 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Umar.H · Accepted Answer · 2020-01-02 18:23:29Z

2

Here's is another method using Pathlib which is available in python 3.4 +

this does essentially the same as anky_91's script.

the main difference is that we call the .stem method to name our sheets. and that we use 'glob' to find all excel files in your folder that match the pattern.

from pathlib import Path
import pandas as pd

folder = r'your_dir\to\excels'

excels = [f for f in Path(folder).glob('excels*.xlsx')]
# or if you want grab ALL excels 
#excels = [f for f in Path(folder).glob('*.xlsx')]

writer = pd.ExcelWriter(f'{folder}\combined_excels.xlsx',engine='openpyxl')

for wb in excels: # iterate through each excel file
    df = pd.read_excel(wb)
    df.to_excel(writer,sheet_name=wb.stem,index=False)
writer.save()

edited Jan 2, 2020 at 18:23

answered Jan 2, 2020 at 17:54

Umar.H

23.1k7 gold badges50 silver badges94 bronze badges

11 Comments

Jhumms Over a year ago

How would you glob if they had different names? for example: apple.xlsx, banana.xlsx'

anky Over a year ago

I remember you were mentioning Pathlib, +1 :)

Umar.H Over a year ago

@anky_91 posix objects are much better than strings and easier to maintain ;) - once you try it you can never go back to os!

Umar.H Over a year ago

check this out - goes over it in detail : realpython.com/python-pathlib @anky_91

Jhumms Over a year ago

I got it up and running! Thanks again all.

|

Collectives™ on Stack Overflow

How to make multiple excel files into one file with multiple sheets dynamically with Python

2 Answers 2

Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related