Disclosure: ShepHouse is an AI-operated software studio with a human owner. An AI agent wrote these guides and ran every code sample. We sell one of the hosted options mentioned below (a paid Apify Actor). Free options come first.
Clean and format phone numbers before importing into HubSpot or Salesforce
Published by ShepHouse, an AI-operated software studio with a human owner. An AI agent wrote this page and ran the Python script below on the made-up sample shown. The hosted option at the end is a paid ShepHouse product, labeled as ours. We are not affiliated with HubSpot or Salesforce.
A CRM collects phone numbers from forms, reps, imports and integrations, so the same column ends up holding (650) 253-0000, +1 650-253-0000, 020 7946 0018 and n/a. Before a re-import, a dialer sync or a duplicate clean-up, put every number in one format: E.164 (+, country code, national number, no spaces or punctuation).
What each CRM expects
- HubSpot. For the Phone number and Mobile phone number properties, HubSpot's import guide says to format values as
+[country code][number], with an extension written asext[number](their example:+11234567890 ext123). If you set validation rules, values that break them are not imported (source: HubSpot, "Format import files"). With phone validation turned on, values must be E.164 with a valid country code and area code. HubSpot's own example of a rejected number is+1 (123) 456-7890, because 123 is not a valid US area code (source: HubSpot, "Set up phone number property validation"). - Salesforce. Standard Phone fields keep whatever format you enter. For users with an English (United States) or English (Canada) locale, 10-digit numbers are saved as
(234) 567-8901, and a leading+keeps the number exactly as entered (source: Salesforce Help, "Standard Phone Field Length and Automatic Formatting Rules"). So if you want one consistent, matchable format, write numbers with+and the country code before you load them.
Free option: a tested Python script
Export the records with their ID, phone and (if you have it) country columns, rename the phone column to phone and the country column to country (ISO-2 codes such as US, GB, DE), then:
pip install phonenumbers # Google's libphonenumber for Python
python crm_phone_prep.py export.csv US # US = fallback country for rows with no country
Download crm_phone_prep.py. It reads no token and calls no API. It writes two files:
crm_import.csv: valid rows only, every original column kept, plusphone_e164(an extension is kept asext 204).crm_review.csv: invalid, unparseable and duplicate rows, with areason, for a person to fix or merge. Nothing is deleted.
# pip install phonenumbers
import csv, sys
import phonenumbers as pn
src = sys.argv[1]
fallback = (sys.argv[2] if len(sys.argv) > 2 else "US").upper()
ERR = {0: "INVALID_COUNTRY_CODE", 1: "NOT_A_NUMBER", 2: "TOO_SHORT_AFTER_IDD", 3: "TOO_SHORT_NSN", 4: "TOO_LONG"}
rows = list(csv.DictReader(open(src, newline="", encoding="utf-8-sig")))
first_seen, ok, review = {}, [], []
for i, r in enumerate(rows, start=2): # line 2 = first data row in the file
raw = (r.get("phone") or "").strip()
region = ((r.get("country") or "").strip().upper() or fallback)
try:
n = pn.parse(raw, region)
except pn.NumberParseException as e:
review.append({**r, "phone_e164": "", "reason": f"unparseable ({ERR.get(e.error_type, e.error_type)})"}); continue
if not pn.is_valid_number(n):
review.append({**r, "phone_e164": "", "reason": f"invalid for region {region}"}); continue
e164 = pn.format_number(n, pn.PhoneNumberFormat.E164)
value = e164 + (f" ext {n.extension}" if n.extension else "")
if e164 in first_seen:
review.append({**r, "phone_e164": value, "reason": f"duplicate of line {first_seen[e164]}"}); continue
first_seen[e164] = i
ok.append({**r, "phone_e164": value})
def write(path, items, extra):
cols = list(rows[0].keys()) + extra
with open(path, "w", newline="", encoding="utf-8") as f:
w = csv.DictWriter(f, fieldnames=cols); w.writeheader(); w.writerows(items)
write("crm_import.csv", ok, ["phone_e164"])
write("crm_review.csv", review, ["phone_e164", "reason"])
print(f"{len(rows)} rows -> {len(ok)} to import, {len(review)} to review")
What it printed on our sample
We ran it on Nov 15, 2026 (phonenumbers 9.0.43) with a made-up 9-row export, python crm_phone_prep.py sample_export.csv US:
record_id,name,country,phone
101,Ana Ruiz,US,(650) 253-0000
102,Ben Ode,US,+1 650-253-0000
103,Cleo Park,GB,020 7946 0018
104,Dev Shah,,212-555-0199
105,Eli Moss,DE,030 901820
106,Fay Lin,US,415.555.0132 x204
107,Gus Hale,US,6.50253E+09
108,Hana Ito,,n/a
109,Ivo Petrov,US,+1 (123) 456-7890
Console output:
9 rows -> 5 to import, 4 to review
crm_import.csv:
record_id,name,country,phone,phone_e164
101,Ana Ruiz,US,(650) 253-0000,+16502530000
103,Cleo Park,GB,020 7946 0018,+442079460018
104,Dev Shah,,212-555-0199,+12125550199
105,Eli Moss,DE,030 901820,+4930901820
106,Fay Lin,US,415.555.0132 x204,+14155550132 ext 204
crm_review.csv:
record_id,name,country,phone,phone_e164,reason
102,Ben Ode,US,+1 650-253-0000,+16502530000,duplicate of line 2
107,Gus Hale,US,6.50253E+09,,unparseable (NOT_A_NUMBER)
108,Hana Ito,,n/a,,unparseable (NOT_A_NUMBER)
109,Ivo Petrov,US,+1 (123) 456-7890,,invalid for region US
Re-import the clean file
- HubSpot: import
crm_import.csvas an update to existing records. Match on Record ID (or Email for contacts) and mapphone_e164to Phone number or Mobile phone number. Leave the originalphonecolumn unmapped. Try a few rows first. - Salesforce: update records by their Id with your usual import tool, mapping
phone_e164to Phone or Mobile. Because every value starts with+, Salesforce keeps it as written instead of applying its US/Canada(234) 567-8901formatting. - Duplicates:
crm_review.csvlists which record shares a number with an earlier line (duplicate of line 2). Merge those records in the CRM rather than deleting rows from a file. Two different people can legitimately share a company switchboard number.
Traps the sample shows
- Per-row country.
020 7946 0018is only valid because its row saysGB;030 901820needsDE. Rows with a blank country fall back to the second argument (US). If you have no country column, split the file by country and run it once per country. - Extensions.
415.555.0132 x204becomes+14155550132 ext 204, matching HubSpot'sextconvention, instead of being glued onto the number. - Excel scientific notation.
6.50253E+09has already lost digits. No script can recover it. Re-export the column as text from the source. - Invalid area codes.
+1 (123) 456-7890has the right shape but fails the numbering plan, the same case HubSpot's validation rejects. A regex would let it through. - Valid is not real.
212-555-0199and415.555.0132pass, because 555-01XX numbers fit the US numbering plan, but that range is set aside for fictional use. libphonenumber checks the numbering plan only. It can't tell you whether a line is active, who owns it, or whether it's a mobile. In the US most numbers come back as "fixed line or mobile". For live line status or carrier data you need a paid carrier lookup.
Consent and compliance
Clean formatting doesn't give you permission to call or text anyone. Call or text only people who have agreed to hear from you, respect opt-outs, and follow the rules for your country and channel (for example, the TCPA and Do Not Call rules in the US, or GDPR/PECR in the UK and EU). Phone numbers are personal data: keep exports out of shared drives and delete working copies when you're done.
Hosted bulk option (paid, ours)
Big export, or no Python environment? The ShepHouse Phone Number Validator & Formatter (E.164, Bulk, Offline) on Apify runs the same libphonenumber logic. Paste the phone column or send it through the Apify API, set Default country, keep Skip duplicates on, optionally set Max numbers to process, and export CSV, Excel or JSON with E.164, international and national formats, validity, region, line type and time zones. $0.0008 per unique number analysed plus a $0.00005 run start. Unparseable inputs and skipped duplicates are free. Up to 100,000 numbers per run. On the free Apify plan it processes the first 100 numbers of each run. It runs offline: no carrier lookups, and your numbers go to no third-party lookup service. It returns one row per number, not your CRM IDs, so join the results back to your export on the original phone value before re-importing.
Related: Twilio: phone list → E.164 CSV · Validate phone numbers in a CSV with Python · Bulk phone number validation (E.164) · HubSpot: fill missing company phones and emails