I want to create a spreadsheet if it doesnt exist with that name but if it does then open it. Then read a csv and see if the 3rd item in the row exists as a column header. If not create it. if it does add to end of column. this is what I was given to kind of start off
import os
import pandas as pd
#import xlsxwriter
File names
csv_filename = “ANZ.csv”
excel_filename = “ANZ.xlsx”
Read the CSV file
csv_data = pd.read_csv(csv_filename)
Check if the Excel file exists
if os.path.exists(excel_filename):
# Read the existing Excel file
excel_data = pd.read_excel(excel_filename)
# Compare the two dataframes
# Identify rows in the CSV that are not in the Excel file
new_rows = csv_data[~csv_data.apply(tuple,1).isin(excel_data.apply(tuple,1))]
if not new_rows.empty:
# If there are new rows, append them to the Excel file
with pd.ExcelWriter(excel_filename, mode='a', if_sheet_exists='overlay', engine='openpyxl') as writer:
new_rows.to_excel(writer, index=False, header=False, startrow=len(excel_data)+1)
print("Added new rows to the existing Excel file.")
else:
print("No new rows to add.")
else:
# If the Excel file doesn’t exist, create it with the data from the CSV
with pd.ExcelWriter(excel_filename, engine=‘xlsxwriter’) as writer:
csv_data.to_excel(writer, index=False)
print(f"Created new Excel file ‘{excel_filename}’ with data from CSV.")
Printing the first 5 rows of the CSV data
print(‘\nFirst 5 rows of the CSV data:\n’)
print(csv_data.head())