import pandas as pd
import getpass
def compare_excel_files(file1_path, file2_path, output_path):
# Read Excel files
df1 = pd.read_excel(file1_path)
df2 = pd.read_excel(file2_path)
# Initialize list to store results
results = []
# Iterate over unique values in column A of file 1
unique_values_file1 = df1['A'].unique()
for value in unique_values_file1:
# Check if value exists in column A of file 2
if value in df2['A'].values:
# Get corresponding row in file 2
corresponding_row_file2 = df2[df2['A'] == value].iloc[0]
# Initialize row for results
result_row = {'Value (File 1)': value}
# Compare values in other columns
for col in df1.columns[1:]: # Exclude column A
value_file1 = df1.loc[df1['A'] == value, col].iloc[0]
value_file2 = corresponding_row_file2[col]
if value_file1 == value_file2:
result_row[f'{col}'] = f'{value_file1} (File 1) | {value_file2} (File 2)'
result_row['OK'] = 'OK'
else:
result_row[f'{col}'] = f'{value_file1} (File 1) | {value_file2} (File 2)'
result_row['OK'] = 'KO'
results.append(result_row)
else:
# Add information for value not found in file 2
result_row = {'Value (File 1)': value}
for col in df1.columns[1:]:
value_file1 = df1.loc[df1['A'] == value, col].iloc[0]
result_row[f'{col}'] = f'{value_file1} (File 1) | Not Found (File 2)'
result_row['OK'] = 'KO'
results.append(result_row)
# Write results to a text file
with open(output_path, 'w') as file:
file.write(f"Username: {getpass.getuser()}\n")
file.write(f"Number of unique values in File 1 (Column A): {len(unique_values_file1)}\n")
file.write(f"Number of unique values in File 2 (Column A): {len(df2['A'].unique())}\n\n")
if results:
df_results = pd.DataFrame(results)
file.write(df_results.to_string(index=False))
else:
file.write("No differences found.")
if __name__ == "__main__":
file1_path = input("Enter path to the first Excel file: ")
file2_path = input("Enter path to the second Excel file: ")
output_path = input("Enter the path to save the output txt file: ")
compare_excel_files(file1_path, file2_path, output_path)
print("Comparison complete. Results saved to", output_path)