import os import pandas as pd def search_part_numbers(part_number_file, excel_folder, output_csv): # Step 1: Read part numbers from file with open(part_number_file, 'r') as f: part_numbers = [line.strip() for line in f if line.strip()] unmatched_parts = set(part_numbers) results = [] headers = None actual_col_count = 0 # Step 2: Loop through Excel files for filename in os.listdir(excel_folder): if filename.endswith(".xlsx") and unmatched_parts: file_path = os.path.join(excel_folder, filename) try: # Load only 'CODAC' sheet df = pd.read_excel(file_path, sheet_name="CODAC", dtype=str) df.fillna('', inplace=True) if headers is None: headers = df.columns.tolist() actual_col_count = len(headers) if "Part No" in df.columns: # Find only the part numbers still unmatched matched_df = df[df["Part No"].isin(unmatched_parts)] for _, row in matched_df.iterrows(): part_no = row["Part No"] if part_no in unmatched_parts: unmatched_parts.remove(part_no) row_data = row.tolist() # Align columns if len(row_data) < actual_col_count: row_data += [""] * (actual_col_count - len(row_data)) elif len(row_data) > actual_col_count: row_data = row_data[:actual_col_count] row_data.append(filename) results.append(row_data) except Exception as e: print(f"Error processing {filename}: {e}") # Step 3: Write CSV if results: headers_with_file = headers + ["Source File"] pd.DataFrame(results, columns=headers_with_file).to_csv(output_csv, index=False) print(f"✅ Done! Results saved to {output_csv}") else: print("⚠️ No part numbers matched in any files.") # Example usage search_part_numbers( part_number_file="part_numbers.txt", # Path to text file with part numbers excel_folder="path_to_excel_files", # Folder containing Excel files output_csv="output_results.csv" # Output CSV file path )