Linked Questions

Popular Questions

I tried using openpyxl and had a lot of trouble, now im trying pandas and an getting tons of tracebacks. Seems like copy pasting merged cells is hard and very specific, either that or im just a nooby. Can anyone help with tips or solutions?

Heres my code with pandas

import os
import pandas as pd

# Set the paths for the interim and finals folders
interim_folder = r'C:\Users\Jake_\Downloads\Code\Interims'
finals_folder = r'C:\Users\Jake_\Downloads\Code\Finals'

# Get a list of all files in the interim folder
interim_files = os.listdir(interim_folder)

# Iterate over each file in the interim folder
for file_name in interim_files:
    # Check if the file is an Excel file
    if file_name.endswith('.xlsx'):
        interim_file_path = os.path.join(interim_folder, file_name)
        finals_file_path = os.path.join(finals_folder, file_name)

        # Read the interim Excel file using pandas with the xlrd engine
        interim_data = pd.read_excel(interim_file_path, sheet_name='Financial Report Interim Mar 10', engine='xlrd')

        # Copy the required data to a new DataFrame
        finals_data = pd.DataFrame()
        finals_data['Column B'] = interim_data.iloc[3:8, 1]  # Copy B4:B8
        finals_data['Column D'] = interim_data.iloc[5:201, 3]  # Copy D6:D200
        finals_data['Column E'] = interim_data.iloc[5:201, 4]  # Copy E6:E200
        finals_data['Column F'] = interim_data.iloc[5:201, 5]  # Copy F6:F200
        finals_data['Column G'] = interim_data.iloc[5:201, 6]  # Copy G6:G200

        # Write the data to the finals Excel file
        writer = pd.ExcelWriter(finals_file_path, engine='openpyxl')
        finals_data.to_excel(writer, sheet_name='Final Financial Report July 7', index=False)
        writer.save()

and heres the tracebacks i get

What im trying to do: I have about 100 files in a folder called interims - and each of those files has a corresponding file thats literally the same name a folder called finals. I want to copy a specific few cells from a specific worksheet called "Financial Report Interim Mar 10" from each of the files in the interim folder and paste into a worksheet called "Final Financial Report July 7" in the corresponding files in the finals folder. Have been trying and failing for like 6 hours.

Thanks

Tried using openpyxl and now pandas, still no luck at all.

Related Questions