import pandas as pd
import os
import shutil
from datetime import datetime
from openpyxl import load_workbook
# Input File Paths
all_classes_path = r'C:\Users\107-5404\OneDrive - JGC Group\Desktop\Input\
[Link]'
mapping_path = r'C:\Users\107-5404\OneDrive - JGC Group\Desktop\Input\
class_sheets_mapping_.csv'
# Verify if the mapping file exists
if not [Link](mapping_path):
print(f"Mapping file not found: {mapping_path}. Creating an empty mapping
file.")
[Link](columns=['Tag Sub Type ID', 'Tag Number']).to_csv(mapping_path,
index=False)
mapping_df = [Link](columns=['Tag Sub Type ID', 'Tag Number']) #
Initialize mapping_df with empty columns
ism_path = r'C:\Users\107-5404\OneDrive - JGC Group\Desktop\ISM\
Ruwais_LNG_EPC_Goyal_testing v0.0.0.102 of 2025.08.29 - AVEVA ISM Standard Excel
[Link]'
mtr_template_path = r'C:\Users\107-5404\OneDrive - JGC Group\Desktop\
Vendor_Template\MTR for PO_VendorTemplate.xlsx'
output_base = r'C:\Users\107-5404\OneDrive - JGC Group\Desktop\Output_Test'
# Load Excel Data
# Check if input files exist
for file_path in [all_classes_path, mapping_path, ism_path]:
if not [Link](file_path):
raise FileNotFoundError(f"Input file not found: {file_path}")
# Load Excel Data
try:
all_classes_df = pd.read_excel(all_classes_path)
mapping_df = pd.read_csv(mapping_path)
ism_df_func = pd.read_excel(ism_path, sheet_name='ISM Functional Classes')
ism_df_attr = pd.read_excel(ism_path, sheet_name='ISM Attributes')
ism_df_func_attr = pd.read_excel(ism_path, sheet_name='ISM Functional Class
Attributes')
except Exception as e:
raise ValueError(f"Error loading input files: {e}")
# Ensure required columns exist before merging
required_columns = ['Tag Sub Type ID', 'Tag Number']
for col in required_columns:
if col not in all_classes_df.columns:
raise KeyError(f"Column '{col}' is missing in 'all_classes_df'. Please
check the input file.")
if col not in mapping_df.columns:
raise KeyError(f"Column '{col}' is missing in 'mapping_df'. Please check
the mapping file.")
# Join All Classes with Mapping on 'Tag Sub Type ID' and 'Tag Number'
merged_df = [Link](
all_classes_df,
mapping_df,
left_on=['Tag Sub Type ID', 'Tag Number'],
right_on=['Tag Sub Type ID', 'Tag Number'],
how='left'
)
# Ensure 'PO Number' column exists in merged_df
if 'PO Number' not in merged_df.columns:
raise KeyError("'PO Number' column is missing in the merged DataFrame. Please
check the input files and mappings.")
# Extract all PO Numbers
po_numbers = merged_df['PO Number'].dropna().unique()
# Today's date as string
today_str = [Link]().strftime('%Y-%m-%d')
for po in po_numbers:
po_folder = [Link](output_base, str(po))
date_folder = [Link](po_folder, today_str)
[Link](date_folder, exist_ok=True)
po_df = merged_df[merged_df['PO Number'] == po]
# Data transformation based on ISM mapping and Class/Attribute requirements can
be added here
# Example: Filtering for mandatory attributes
for idx, row in po_df.iterrows():
# Additional transformation as required by your workflow logic
pass
# Load the MTR Vendor Template (ensure no changes to template formatting)
base, ext = [Link]([Link](mtr_template_path))
output_file = [Link](date_folder, f"{po}{ext}")
# Copy template to output location
[Link](mtr_template_path, output_file)
# Now fill in the new file - ONLY edit values, do not alter
structure/validation
wb = load_workbook(output_file, data_only=False)
ws = [Link] # or choose by sheet name if necessary
# Example: Fill in data in specified columns/rows (this must be tailored!)
# Adjust row and column numbers in [Link](row=X, column=Y, value=...)
# for i, row in po_df.iterrows():
# [Link](row=START_ROW + i, column=X, value=row['Column_Name'])
[Link](output_file)
print("Processing complete. Files generated in Output folder.")