1

I have two file paths, C:\users\path1 and C:\users\path2. Each path has the same 10 excel files, with the same names. For example, each path has the following files:

P1 P2 P3, etc.

however the data in the files is not the same. The files in path2 have multiple sheets of data and the files in path1 just have one sheet.

How can I set up a python loop to go through both paths, and based on matching filenames, add the sheet from path1 into the file in path2?

Currently, I don't have any code set up.

lexi1024
  • 17
  • 3

1 Answers1

1

I'm not sure what you mean exactly by add sheets (if it's a word document or something you're going to need something more special) but if it's just data this is fine. If either way this should form a good starting point for you:

import pandas as pd
from pandas import ExcelWriter
import os

d1 = "asset\\path1"
d2 = "asset\\path2"

output_dir = "asset\\path3"

for fname in os.listdir(d1):
    sheets = []
    i = 0
    while True:
        try:
            sheets.append(pd.read_excel(os.path.join(d1, fname),sheet_name=i,encoding='sys.getfilesystemencoding()'))
        except IndexError:
            break
        except Exception as e:
            raise e
        i+=1
    sheets.append(pd.read_excel(os.path.join(d2, fname),sheet_name=0,encoding='sys.getfilesystemencoding()'))

    with ExcelWriter(os.path.join(output_dir,fname)) as writer:
        for n, df in enumerate(sheets):
            df.to_excel(writer,'sheet%s' % n, index = False, header=False)
        writer.save()
Andrew Louw
  • 679
  • 5
  • 10
  • Thank you, and yes the files i'm referring to are purely excel files. I will update that in the description. – lexi1024 Apr 29 '19 at 16:46
  • This might be better done in a different way than python, if you right click on an excel file and click "open with -> notepad" you'll see that there is custom compression in the file, so you can't just append. Instead you should use pandas "read_excel" to open each sheet in each file into a dataframe and then combine them all using this: https://stackoverflow.com/questions/14225676/save-list-of-dataframes-to-multisheet-excel-spreadsheet – Andrew Louw Apr 29 '19 at 16:59
  • This works great, however it deletes the existing sheets of data in the filepath2 files. Is there anyway to adjust the code to add the new data as a separate sheet instead of replacing the existing data? – lexi1024 Apr 29 '19 at 17:56
  • Sorry, I don't understand - the filepath2 sheets are blank after you run this? That really shouldn't be happening and it's not the behaviour I'm getting. Can you elaborate more please? – Andrew Louw Apr 29 '19 at 18:04
  • So before any code is run, the files in filepath2 contain 3 or 4 sheets of data, and the files in filepath1 just have one sheet of data (the sheet that i want added to the filepath2 files). When the code is run, the files in filepath2 seem to just have the new sheet from filepath1 and not the existing sheets they had before the code was run. Let me know if that is still confusing and I can attach files for reference. Thank you for the help. – lexi1024 Apr 29 '19 at 18:10
  • I think the issue is that I wrote this to append filepath2 to the end of filepath1 and save the result in output_dir , to fix you can just swap the path names or variable names and set the save directory to whichever one you want to save over. (please upvote the solution if it solves things for you) – Andrew Louw Apr 29 '19 at 18:19
  • This is working after I adjusted the path names however, it's not keeping any of the existing formatting or sheet names. For example, all of the sheets in both files were labeled, and now they are 'sheet1', 'sheet2', etc. The formatting is also gone. Cells were highlighted, bolded, etc. Now there is only plain text. Any suggestions for this issue? – lexi1024 Apr 29 '19 at 23:58
  • Unfortunately I don't think it's possible to keep formatting, the libraries we're using Imports the data into a pandas DataFrame which doesn't have any formatting and then prints the data out again as a new excel document. It's possible a different library might help but I don't know of an, sorry. I'd recommend you don't use python for this at all, consider a macro: https://www.excel-easy.com/vba/examples/import-sheets.html . – Andrew Louw Apr 30 '19 at 10:07