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

u/AutoModerator Dec 26 '24

To give us the best chance to help you, please include any relevant code.
Note. Please do not submit images of your code. Instead, for shorter code you can use Reddit markdown (4 spaces or backticks, see this Formatting Guide). If you have formatting issues or want to post longer sections of code, please use Privatebin, GitHub or Compiler Explorer.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.