CODEWITHSUNDEEP
pythonexcelpandasdataframeexport-to-excel
tenebris silentio
tenebris silentio

Reputation: 519

Python Pandas - loop through folder of Excel files, export data from each Excel file's sheet into their own .xlsx file

I have a folder of Excel files, many of which have 3-4 tabs worth of data that I just want as individual Excel files. For example, let's say I have an Excel file with three tabs: "employees", "summary", and "data". I would want this to create 3 new Excel files out of this: employees.xlsx, summary.xlsx, and data.xlsx.

I have code that will loop through a folder and identify all of the tabs, but I have struggling to figure out how to export data individually from each sheet into its own Excel file. I have gotten to the point where I can loop through the folder, open each Excel file, and find the name of each sheet. Here's what I have so far.

import pandas as pd
import os

# filenames
files = os.listdir()    
excel_names = list(filter(lambda f: f.endswith('.xlsx'), files))

excels = [pd.ExcelFile(name, engine='openpyxl') for name in excel_names]
sh = [x.sheet_names for x in excels] # I am getting all of the sheet names here
for s in sh:
    for x in s:
        #there is where I want to start exporting each sheet as its own spreadsheet

#df.to_excel("output.xlsx", header=False, index=False) #I want to eventually export it obviously, this is a placeholder

Upvotes: 2

Views: 2377

Answers (1)

It_is_Chris
It_is_Chris

Reputation: 14063

import pandas as pd
import glob

# get the file names using glob 
# (this assumes that the files are in the current working directory)
excel_names = glob.glob('*.xlsx')
# iterate through the excel file names
for excel in excel_names:
    # read the excel file with sheet_name as none
    # this will create a dict
    dfs = pd.read_excel(excel, sheet_name=None)
    # iterate over the dict keys (which is the sheet name)
    for key in dfs.keys():
        # use f-strings (only available in python 3) to assign 
        # the new file name as the sheet_name
        dfs[key].to_excel(f'{key}.xlsx', index=False)

Upvotes: 2

Related Questions