1

I have two excel files (.xls) in the "Files" folder. I want to take each sheet of them both and put them into one separate workbook, called masterFile.xls. The code below downloads some example files so you can see what I'm working with.

import pandas as pd
import os
import requests
 
resp = requests.get("https://www.ons.gov.uk/file?uri=%2femploymentandlabourmarket%2fpeopleinwork%2femploymentandemployeetypes%2fdatasets%2fsummaryoflabourmarketstatistics%2fcurrent/a01dec2021.xls")
output = open("1.xls", 'wb')
output.write(resp.content)
output.close()

resp = requests.get("https://www.ons.gov.uk/file?uri=%2femploymentandlabourmarket%2fpeopleinwork%2femploymentandemployeetypes%2fdatasets%2femploymentunemploymentandeconomicinactivityforpeopleaged16andoverandagedfrom16to64seasonallyadjusteda02sa%2fcurrent/a02sadec2021.xls")
output = open("2.xls", 'wb')
output.write(resp.content)
output.close()

cwd = os.path.abspath('')
files = os.listdir(cwd)

for file in files:
    if file.endswith('.xls'):
        excelFile = pd.ExcelFile(file)
        sheets = excelFile.sheet_names
        for sheet in sheets:
            data = pd.read_excel(excelFile,sheet_name = sheet)
            data.to_excel("masterFile.xls",sheet_name = sheet)

Each time it adds the sheet, it replaces whatever was already there instead of adding a new sheet.

  • 1
    Does this answer your question? [creating multiple excel worksheets using data in a pandas dataframe](https://stackoverflow.com/questions/21981820/creating-multiple-excel-worksheets-using-data-in-a-pandas-dataframe) – Asish M. Dec 21 '21 at 17:40
  • Not really, I more want to be able to transfer from multiple sheets in multiple books into one book –  Dec 21 '21 at 19:08
  • 1
    are the sheet names common across the files? if they are separate, how is it different from the linked question? you have one df per sheet that you want to write to a workbook. – Asish M. Dec 21 '21 at 19:44
  • I stand corrected, it does work. It's not ideal because the file format has to change halfway through which has caused some formatting issues, but for my use it's good enough, thanks –  Dec 21 '21 at 20:36

0 Answers0