Copy excel column data from one worksheet to a worksheet in an existing workbook

Question

I'm trying to automate the process of copying data in excel columns from one worksheet to a worksheet in an existing master excel file. I need to copy the data to each corresponding worksheet in the master file. I've decided to incorporate mapping in my python code to do this. I'd also like the script to trigger when an excel file is put into the project directory but that part is not important at the moment.

When I run the file, it runs fine with no errors, but it doesn't copy the column data and paste it into the master excel file and I can't figure out why it is not working. Anyone able to point me in the right direction? Thanks in advance for any help here.

import openpyxl
import os
from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler

# Set the list of file names and corresponding worksheet/column mappings
file_mappings = {
    "Adams_Survey.xlsx": [
        {"source_worksheet": "1 General County Information", "target_worksheet": "1 General County Information", "column": "B"},
        {"source_worksheet": "2 Special Events-Exposure", "target_worksheet": "2 Special Events-Exposure", "column": "B"},
        {"source_worksheet": "3 Fair", "target_worksheet": "3 Fair", "column": "B"},
        {"source_worksheet": "4 Utilities", "target_worksheet": "4 Utilities", "column": "B"},
        {"source_worksheet": "5 Public Works", "target_worksheet": "5 Public Works", "column": "B"},
        {"source_worksheet": "6 Solid Waste - Facilities", "target_worksheet": "6 Solid Waste - Facilities", "column": "B"},
        {"source_worksheet": "7 Parks and Recreation", "target_worksheet": "7 Parks and Recreation", "column": "B"},
        {"source_worksheet": "8 Health Department", "target_worksheet": "8 Health Department", "column": "B"},
        {"source_worksheet": "9 Law Enforcement", "target_worksheet": "9 Law Enforcement", "column": "B"},
        {"source_worksheet": "10 Human Resources - Employment", "target_worksheet": "10 Human Resources - Employment", "column": "B"},
        {"source_worksheet": "11 Cyber Security", "target_worksheet": "11 Cyber Security", "column": "B"},
        {"source_worksheet": "12 Emergency Management", "target_worksheet": "12 Emergency Management", "column": "B"}
    ],
    # Add more file mappings as needed
}

# Set the name of the existing workbook to paste the columns into
existing_workbook_name = "SurveySelfAssess.xlsx"

# Load the existing workbook
existing_wb = openpyxl.load_workbook(existing_workbook_name)

class FileHandler(FileSystemEventHandler):
    def on_created(self, event):
        if event.is_directory:
            return
        elif event.src_path.endswith('.xlsx'):
            file = os.path.basename(event.src_path)

            if file.startswith('~$') or file.startswith('$'):
                return
            
            # Check if the file has a mapping
            if file in file_mappings:
                mappings = file_mappings[file]

                # Load the workbook
                wb = openpyxl.load_workbook(file)

                for mapping in mappings:
                    source_worksheet_name = mapping["source_worksheet"]
                    target_worksheet_name = mapping["target_worksheet"]
                    column_letter = mapping["column"]

                    # Get the source worksheet
                    ws = wb[source_worksheet_name]

                    # Find the column to copy based on the first row
                    col_to_copy = None
                    for col in range(1, ws.max_column + 1):
                        if ws.cell(row=1, column=col).value == "Column to Copy":
                            col_to_copy = col
                            break

                    # If the column to copy is found
                    if col_to_copy:
                        # Get the target worksheet
                        existing_ws = existing_wb[target_worksheet_name]

                        # Get the column number to paste into
                        existing_column_number = openpyxl.utils.column_index_from_string(column_letter)

                        # Copy the column values to the existing worksheet
                        for row in range(1, ws.max_row + 1):
                            existing_ws.cell(row=row, column=existing_column_number, value=ws.cell(row=row, column=col_to_copy).value)

                        print(f"Column 'Column to Copy' copied from '{file}', worksheet '{source_worksheet_name}' to column {column_letter} in '{existing_workbook_name}', worksheet '{target_worksheet_name}'")
                    else:
                        print(f"Column 'Column to Copy' not found in '{file}', worksheet '{source_worksheet_name}'")
            else:
                print(f"No mapping found for '{file}'")


if __name__ == "__main__":
    event_handler = FileHandler()
    observer = Observer()
    observer.schedule(event_handler, path='.', recursive=False)
    observer.start()
    try:
        while True:
            pass
    except KeyboardInterrupt:
        observer.stop()
    observer.join()

# Save the existing workbook after all operations
    existing_wb.save(existing_workbook_name)

Answer 1

After doing some testing, I think you want your write here. Your existing code works fine, but doesn't do the write until after you end the while loop.

                        print(f"Column 'Column to Copy' copied from '{file}', worksheet '{source_worksheet_name}' to column '{column_letter}' in '{existing_workbook_name}', worksheet '{target_worksheet_name}'")
                        
                        # Save the existing workbook after all operations
                        existing_wb.save(existing_workbook_name)

I suppose doing the write inside of a handler may be an issue if your file is very large. As an alternative, set a flag after all the copying and then do the write inside the while.