Python Excel Data Manipulation

import pandas as pd

EXCEL_FILE = "uds_services.xlsx"


# -----------------------------
# Create Excel Template
# -----------------------------
def create_excel_template():
data = {
"Service_ID": [],
"Sub_Function": [],
"DID": [],
"Description": [],
"Data": []
}

df = pd.DataFrame(data)
df.to_excel(EXCEL_FILE, index=False)
print(f"Excel template created: {EXCEL_FILE}")


# -----------------------------
# Read and Parse Excel
# -----------------------------
def parse_excel():
df = pd.read_excel(EXCEL_FILE)

# Remove completely empty rows
df = df.dropna(how="all")

# Replace NaN with None
df = df.where(pd.notnull(df), None)

structured_data = []

for _, row in df.iterrows():
service_dict = {
"service_id": row["Service_ID"],
"sub_function": row["Sub_Function"],
"did": row["DID"],
"description": row["Description"],
"data": row["Data"]
}

# Remove None values
service_dict = {k: v for k, v in service_dict.items() if v is not None}

structured_data.append(service_dict)

return structured_data