Combine individual files into one file of multiple tabs
Here we got 37 spreadsheet files within a folder and I want all the files to be in a single spreadsheet file with each file on a separate worksheet.
This requirement is different from merging the file into a single excel worksheet file. What is required here is to have each file as a worksheet within one excel file as seen below.
The code is as follow:-
It makes use of the ExcelWriter pandas method. The parameter "options={'strings_to_urls': False}" is set to allow writing of cell values that will have more than 225 characters.
import glob import pandas as pd
folder = r"C:\Users\Yusuf_08039508010\Documents\Distinguished Senators" senators_files = glob.glob(folder + '/*.xlsx') len(senators_files) # Writing multiple dataframes to worksheets... writer = pd.ExcelWriter('DistinguishedSenators.xlsx', engine='xlsxwriter', options={'strings_to_urls': False}) for sheet in senators_files: print("Writting sheet...", sheet) sheetname = sheet.split('\\')[-1].split('.')[0] sheet_df = pd.read_excel(sheet) sheet_df = sheet_df.head(-1) print(sheet_df.shape) sheet_df.to_excel(writer, sheet_name=sheetname, index=None) # Save each df to excel writer.save()
Related Materials
No comments:
Post a Comment