[Python] Best way to parse a bunch of excel files, extract information (not placed...

Helllo!

I am currently trying to take a bunch of differnet-formatted excel files with information (Names, Jobs, Phone #s, Emails, etc) and turn them all into one large master sheet with a uniform formatting. I tried using python to automate this/solve this issue but I am having a lot of trouble getting my code to recognize specific data entries as a specific data type (mistaking a country for a name or a phone # for an email, etc) I am not that experienced at coding practically like this, but I was wondering if anyones done this before or has tips on how to create a tool that could do this. Thank You!

(P.S. I've attached what ChatGPT and Me (mostly chat lol) have come up with)


#!/usr/bin/env python3
import os
import re
import sys
import datetime
import pandas as pd

# ─── USAGE & INPUT PATH ────────────────────────────────────────────────────
if len(sys.argv) != 2:
print("Usage: python extract_contacts_master.py <folder-of-excels>")
sys.exit(1)

INPUT_DIR = sys.argv[1]
if not os.path.isdir(INPUT_DIR):
print(f"❌ Folder not found: {INPUT_DIR}")
sys.exit(1)

# ─── OUTPUT CONFIG ─────────────────────────────────────────────────────────
OUTPUT_DIR = os.path.expanduser("~/Downloads")
TS = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
OUT_CSV = os.path.join(OUTPUT_DIR, f"contacts_master_{TS}.csv")

# ─── PATTERNS ──────────────────────────────────────────────────────────────
NAME_RE = re.compile(r"^[A-Z][a-z]*(?:\s*[A-Z][a-z]*)+$") # Names with capitalized words
PHONE_RE = re.compile(r"\b(?:\+?1[\s.-]?)?(?:\(\d{3}\)|\d{3})[\s.-]?\d{3}[\s.-]?\d{4}(?:\s*(?:ext|x|ext\.)\s*\d+)?(?:\.\d)?\b") # Expanded for various formats: (123) 456-7890, 123-456-7890, +1 123.456.7890, 1234567890, with ext
EMAIL_RE = re.compile(r"[\w\.-]+@[\w\.-]+\.\w+")
ORG_KEYWORD_RE = re.compile(r"\b(?:inc|llc|ltd|corp|company|partners|group|management|capital|llp|bank|llc|fund|investments|advisors|group|co|lp|partners|associates|firm)\b", re.I) # Expanded for common org words
TITLE_SET = {"partner", "counsel", "associate", "patent counsel", "special counsel", "senior counsel", "of counsel", "managing partner", "office managing shareholder", "managing director", "senior associate", "senior vice president", "vice president", "executive director", "chief operating officer", "chief financial officer", "chief compliance officer", "director", "senior director", "principal", "senior principal", "portfolio manager", "analyst", "senior analyst"} # Expanded title set
CITY_PATTERN = re.compile(r"^(?:[A-Z][a-z]+(?:\s[A-Z][a-z]+)?(?:\sMetropolitan\sArea)?|Greater\s[A-Z][a-z]+\sArea|San\sFrancisco\sBay\sArea|Los\sAngeles\sMetropolitan\sArea|New\sYork\sCity\sMetropolitan\sArea|United\sStates|USA)$") # Pattern for cities/locations
DNC_TERMS = {"dnc", "do not call"}

# ─── HELPERS ──────────────────────────────────────────────────────────────
def choose_engine(fname):
return "openpyxl" if fname.lower().endswith(".xlsx") else "xlrd"

def find_header(df):
# Scan first 50 rows for a “name-like” cell
for i, row in df.head(50).iterrows():
for v in row.dropna().astype(str):
if NAME_RE.match(v.strip()):
return i
return None

def score_col(col, regex):
vs = col.dropna().astype(str)
return sum(bool(regex.search(v)) for v in vs) / len(vs) if len(vs) else 0

def score_org(col):
vs = col.dropna().astype(str)
return sum(bool(ORG_KEYWORD_RE.search(v)) for v in vs) / len(vs) if len(vs) else 0

def score_title(col):
vs = col.dropna().astype(str).str.lower()
return sum(any(t in v for t in TITLE_SET) for v in vs) / len(vs) if len(vs) else 0

def score_city(col):
vs = col.dropna().astype(str)
return sum(bool(CITY_PATTERN.match(v)) for v in vs) / len(vs) if len(vs) else 0

def pick_columns(df):
cols = df.columns
if len(cols) == 0:
return None, None, None, None, None

# Avoid columns that look like cities
non_city_cols = [c for c in cols if score_city(df[c]) < 0.5]

name_col = max(non_city_cols, key=lambda c: score_col(df[c], NAME_RE)) if non_city_cols else None

remaining = [c for c in non_city_cols if c != name_col] if name_col else non_city_cols
if not remaining:
return name_col, None, None, None, None

phone_col = max(remaining, key=lambda c: score_col(df[c], PHONE_RE)) if remaining else None

remaining = [c for c in remaining if c != phone_col] if phone_col else remaining
email_col = max(remaining, key=lambda c: score_col(df[c], EMAIL_RE)) if remaining else None

remaining = [c for c in remaining if c != email_col] if email_col else remaining
title_col = max(remaining, key=lambda c: score_title(df[c])) if remaining else None

remaining = [c for c in remaining if c != title_col] if title_col else remaining
org_col = max(remaining, key=lambda c: score_org(df[c])) if remaining else None

return name_col, phone_col, email_col, org_col, title_col

def split_full_name(name):
if not name:
return "", ""
# Remove suffixes and nicknames
name = re.sub(r',\s*(Jr\.|Sr\.|II|III|IV)\b', '', name, flags=re.I)
name = re.sub(r'\s*\([^)]+\)', '', name).strip()
# Skip if it looks like a title, location, or organization
if name.lower() in TITLE_SET or CITY_PATTERN.match(name) or ORG_KEYWORD_RE.search(name.lower()):
return "", ""
parts = name.split()
if len(parts) < 2:
return name, ""
return " ".join(parts[:-1]), parts[-1]

def clean_phone(phone):
if not phone:
return ""
phone = str(phone).strip().rstrip('.0')
# Normalize format: add dashes if 10 digits
digits = re.sub(r'\D', '', phone)
if len(digits) == 10:
return f"{digits[:3]}-{digits[3:6]}-{digits[6:]}"
elif len(digits) == 11 and digits.startswith('1'):
return f"1-{digits[1:4]}-{digits[4:7]}-{digits[7:]}"
return phone # Keep original if not matching

def extract_from_sheet(df, fname, sheet):
name_col, phone_col, email_col, org_col, title_col = pick_columns(df)
if name_col is None:
print(f"Skipping sheet {sheet} in {fname}: No columns or no name column found")
return []
contacts = []
for _, row in df.iterrows():
# Skip DNC
combined = " ".join(str(v).lower() for v in row if not pd.isna(v))
if any(term in combined for term in DNC_TERMS):
continue

name = str(row[name_col]).strip() if name_col is not None and pd.notna(row[name_col]) else ""
if not name:
continue
first, last = split_full_name(name)
if not first and not last:
continue

phone = clean_phone(row[phone_col] if phone_col is not None and pd.notna(row[phone_col]) else "")
email = str(row[email_col]).strip() if email_col is not None and pd.notna(row[email_col]) else ""
org = str(row[org_col]).strip() if org_col is not None and pd.notna(row[org_col]) else ""
title = str(row[title_col]).strip() if title_col is not None and pd.notna(row[title_col]) else ""

# Infer org from email if missing and not an email provider (e.g., skip gmail, yahoo)
if not org and email and EMAIL_RE.match(email):
domain = email.split('@')[-1].split('.')[0].lower()
if domain not in {'gmail', 'yahoo', 'hotmail', 'outlook', 'icloud', 'me', 'aol'}:
org = domain.capitalize()

# Skip if no meaningful data
if not (phone or email or org or title):
continue

contacts.append({
"First Name": first,
"Last Name": last,
"Phone #": phone,
"Organization": org,
"Title": title,
"Country": "", # Blank as per request
"Email": email
})
return contacts

# ─── MAIN ─────────────────────────────────────────────────────────────────
all_contacts = []
files = sorted(f for f in os.listdir(INPUT_DIR) if f.lower().endswith((".xlsx", ".xls")))
print(f" Found {len(files)} files in: {INPUT_DIR}\n")

for fn in files:
path = os.path.join(INPUT_DIR, fn)
engine = choose_engine(fn)
try:
xls = pd.ExcelFile(path, engine=engine)
except Exception as e:
print(f"⚠️ Skipping {fn}: cannot open ({e})")
continue

for sheet in xls.sheet_names:
if "dnc" in sheet.lower():
print(f"Skipping DNC sheet: {sheet} in {fn}")
continue
try:
raw_df = pd.read_excel(path, sheet_name=sheet, header=None, engine=engine).dropna(how="all")
hdr = find_header(raw_df)
if hdr is not None:
df = pd.read_excel(path, sheet_name=sheet, header=hdr, engine=engine).dropna(how="all")
else:
df = raw_df # Fallback to no header
except Exception as e:
print(f"⚠️ Skipping sheet {sheet} in {fn}: {e}")
continue

print(f"✅ Parsing {fn} → sheet “{sheet}”")
contacts = extract_from_sheet(df, fn, sheet)
all_contacts.extend(contacts)

if not all_contacts:
print("\n❌ No contacts extracted.")
sys.exit(1)

df_out = pd.DataFrame(all_contacts).drop_duplicates()
df_out.to_csv(OUT_CSV, index=False)
print(f"\n✅ Done! Extracted {len(df_out)} contacts → {OUT_CSV}")

Continue reading...