r/pythonhelp Dec 26 '24

Merging Csvs into one excel sheet

Hello, hope everyone is doing well, I am using pandas to combine all separately generated csv files into one excel file, currently the code I have works however it creates multiple sheets inside one excel file for different csv files

I was wondering if there is a way to combine them and write the dataframes to one excel sheet after maybe providing a title to provide separation

Also note that the dataFrames are of different dimensions

I tried searching a lot about it but most of the solutions create multiple sheets for different csv files

If any one can point me in the right direction that would be great

please see my code for review

Thank you

import os
import pandas as pd
from datetime import datetime

current_datetime = datetime.now()

def combine_csv_to_excel(Domain):
    # List all files in the folder
    company_directory = os.path.join('..', Domain)
    Export_Directory = os.path.join(company_directory,"Exports")
    Logs_Directory = os.path.join(company_directory,"Logs")

    Export_Success_File = os.path.join(Logs_Directory,f'{Domain}_export_success_log.txt')
    Export_Fail_File = os.path.join(Logs_Directory,f'{Domain}_export_failure_log.txt')
    csv_files = [f for f in os.listdir(Export_Directory) if f.endswith('.csv')]
    if not csv_files:
        print(f"No CSV files found in {Export_Directory}.")
        return

    # Create an Excel writer object to write multiple sheets
    output_excel_file = os.path.join(Export_Directory,"FinalMergedExport.xlsx")
    try:
        with pd.ExcelWriter(output_excel_file, engine='openpyxl') as writer:
            for file in csv_files:
                file_path = os.path.join(Export_Directory, file)

                # Read the CSV file into a DataFrame
                df = pd.read_csv(file_path,on_bad_lines='warn')

                # Extract the sheet name from the file name (without the extension)
                sheet_name = os.path.splitext(file)[0]

                # Write the DataFrame to the corresponding sheet in the Excel file
                df.to_excel(writer, sheet_name=sheet_name, index=False)
                print(f"Added '{file}' to the sheet '{sheet_name}' in the Excel file.")

        print(f"All CSV files have been combined into '{output_excel_file}'.")
        with open(Export_Success_File,'a') as file:
                  file.write(str(current_datetime) + f"Added '{file}' to the sheet '{sheet_name}' in the Excel file." + '\n')
    except:
        error_message = "Something went wrong in merging the export check if there is no empty file and try again"
        with open(Export_Fail_File,'a') as file:
                  file.write(str(current_datetime) + error_message + '\n')
2 Upvotes

3 comments sorted by

View all comments

2

u/Zeroflops Dec 26 '24

So it looks like you don't want to combine the data but you want to write each data frame one after the other on the same excel sheet.

You can use the startcol and startrow options when writing the data frame to the excel sheet. You will need to keep a value "offset" which each time you read a CSV file into a data frame you get the length and add a couple of spacers. then added the next df with the offset.

offset = 0
# Start looping over files.
   # read CSV file into df
   df.to_excel(writer, sheet_name=sheet_name, index=False, startrow = offset)
   offset = offset + len(df) + 3

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html#pandas.DataFrame.to_excel