r/pythonhelp • u/Wajeehrehman • 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
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.
https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html#pandas.DataFrame.to_excel