CODEWITHSUNDEEP
pythonpandasjupyter-notebook
Jhumms
Jhumms

Reputation: 19

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

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.

Upvotes: 1

Views: 420

Answers (2)

Umar.H
Umar.H

Reputation: 23099

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()

Upvotes: 2

anky
anky

Reputation: 75080

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.

Upvotes: 3

Related Questions