import csv
from urllib.parse import urlsplit, urlunsplit
def cell(value):
# Prevent spreadsheet formula interpretation.
return "'" + value if value.lstrip().startswith(("=", "+", "-", "@")) else value
seen, rows = set(), []
with open("authors.csv", encoding="utf-8-sig", newline="") as f:
reader = csv.DictReader(f)
required = {"name", "profile_url", "topic"}
if not required.issubset(reader.fieldnames or []):
raise ValueError("Missing required columns")
for row in reader:
name = (row.get("name") or "").strip()
raw = (row.get("profile_url") or "").strip()
try:
u = urlsplit(raw)
except ValueError:
continue
if not name or u.scheme not in ("http", "https") or not u.netloc:
continue
url = urlunsplit((u.scheme, u.netloc, u.path, u.query, ""))
if url in seen:
continue
seen.add(url)
rows.append([cell(name), cell(url), cell((row.get("topic") or "").strip())])
with open("authors_clean.csv", "x", encoding="utf-8-sig", newline="") as f:
writer = csv.writer(f)
writer.writerow(["name", "profile_url", "topic"])
writer.writerows(rows)