How to Prepare a CSV File for CRM Import (Clean It Right the First Time)

Quick Answer

CRMs reject unprepped CSV files for six predictable reasons: encoding isn't UTF-8 (garbled names), headers don't match CRM field names exactly, dates are ambiguous, phone numbers aren't standardized, duplicates exist either in your file or already in the CRM, and required fields have empty values. Fix these six before uploading and you eliminate the most common import failures on the first attempt.

The fix: Run a structured prep sequence — encoding first, then headers, then data standardization, then deduplication, then a 5-row test import. Never skip the test.

Why it matters: A failed CRM import isn't just an annoyance. It can create duplicate records that sales reps call twice, corrupt existing contact data, or introduce dirty phone numbers that invalidate your sender score. Prep costs 20 minutes. Cleanup after a bad import costs days. Even with proper prep, platform-specific picklist rules, custom field validation, and API limits can still reject rows — but the six checks below eliminate the most common failures.

---

What CRM Imports Actually Check

The silent failure column that most guides ignore: Phone standardization. No major CRM validates phone format at import — they accept anything. But if you import (555) 234-5678 in some rows and +15552345678 in others, your CRM has two different-looking records that are the same number. Automations won't match, deduplication won't catch it, and reporting will be wrong.

---

Fast Fix (5 Minutes)

If you need to prepare a CSV for CRM import right now:

1. Open in text editor — confirm file uses commas (not semicolons) as delimiter.
2. Check encoding — open in Notepad++, confirm it shows UTF-8.
3. Review row 1 — confirm it's headers, not data. Headers must match CRM field names.
4. Find empty required fields — search for ,, (consecutive commas = empty field).
5. Run a 5-row test import in your CRM before uploading the full file.

For a thorough prep that survives a full production import, continue below.

---

TL;DR: CRM imports fail silently — the import "succeeds" but data is wrong, missing, or duplicated. The six prep steps in this guide take 20 minutes and prevent the four hours of cleanup that follows a bad import. Run your file through SplitForge Data Cleaner to standardize encoding, headers, dates, and phone numbers locally before uploading to any CRM.

---

You're onboarding 2,000 leads from a trade show. The import wizard says "2,000 records imported successfully." But when your SDRs start working the list, half the phone numbers are in different formats. Some contacts are duplicated from the last import. Three leads have é[email protected] displaying as é[email protected]. And the "Company" field — which your Salesforce workflows depend on — is empty for 400 rows because the CSV header said "Employer" instead of "Company."

The import succeeded. The data is wrong.

Each scenario was reproduced against Salesforce, HubSpot, and Zoho CRM, March 2026.

---

Table of Contents

• What CRM Imports Actually Check
• Step 1: Fix Encoding First
• Step 2: Align Headers to CRM Field Names
• Most Common CRM Header Mapping Traps
• Step 3: Standardize Dates
• Step 4: Standardize Phone Numbers
• Step 5: Remove Duplicates
• Step 6: Populate Required Fields
• Platform-Specific Prep Notes
• What a Failed CRM Import Actually Looks Like
• The Privacy Consideration
• Additional Resources
• FAQ

---

This guide is for: CRM admins, sales ops, and marketing ops teams preparing contact, lead, company, or deal data for import into any major CRM. Assumes basic spreadsheet familiarity.

---

Step 1: Fix Encoding First

Encoding problems are the only prep issue that corrupts data that was never wrong to begin with. Fix encoding before anything else — otherwise you'll prep clean data in a broken file.

Check your encoding: Open the file in Notepad++ (Windows) or TextEdit (Mac). The status bar shows the current encoding. It should say UTF-8. If it says ANSI, Windows-1252, or anything else, convert it.

Fix in Excel: File → Save As → choose CSV UTF-8 (Comma delimited). This option only appears in Excel 2016+ on Windows. If you don't see it, use Google Sheets.

Fix via Google Sheets: Upload the file to Google Sheets. Download it back as File → Download → Comma-separated values. Google Sheets always exports UTF-8.

Fix with SplitForge: SplitForge Delimiter & Encoding Fixer detects and converts encoding locally in your browser. Your contact data never leaves your computer — important when the file contains personal data subject to GDPR or CCPA requirements.

Many CSV tools that handle encoding conversion upload your file to a remote server to process it. For files containing customer names, emails, and phone numbers, that upload can create a data processor relationship under GDPR Article 28, potentially requiring a signed Data Processing Agreement before use. SplitForge processes the file in your browser — for raw file contents, nothing is transmitted to any server.

Step 2: Align Headers to CRM Field Names

Every CRM maps CSV columns to its internal fields by header name match. A column named Email Address won't map to HubSpot's Email property. A column named Company Name won't map to Salesforce's Company field. The mapping step in the import wizard exists precisely because mismatches are common — but unmapped columns are silently dropped.

The right approach: Download your CRM's import template before building your CSV. Use the exact column names from the template as your CSV headers.

Common header mismatches by CRM:

For Salesforce: Download the Data Import Wizard template for your object type (Leads, Contacts, Accounts). The column names in the template are the exact field API names.

For HubSpot: Download the contact import template from Settings → Import → Download template. HubSpot is more flexible — it can auto-map common variations — but exact matches are always safer.

Step-by-step:

1. Download your CRM's template for the record type you're importing.
2. Copy your data into the template (or rename your CSV headers to match).
3. Delete any columns that don't have a corresponding CRM field.
4. Keep columns in any order — CRMs map by header name, not column position.

---

Most Common CRM Header Mapping Traps

These mismatches cause silent column drops on every import. Analysts screenshot this table constantly.

Rule: Download the CRM's own import template. The column names in the template are definitive. Any deviation risks a silent drop.

---

Step 3: Standardize Dates

Dates are the most common source of silent data corruption in CRM imports. 05/03/2026 imports as May 3 on a US account and March 5 on a UK account. The import succeeds, the dates are wrong, and nobody notices until a contract renewal gets missed.

Universal fix: Convert all dates to YYYY-MM-DD before importing. This format is unambiguous in every locale and accepted by every major CRM.

In Excel:

1. Select all date columns.
2. Right-click → Format Cells → Custom → enter YYYY-MM-DD.
3. Save as CSV.

Common date columns in CRM imports:

• Close Date / Expected Close
• Lead Created Date
• Last Contact Date
• Contract Start/End Date
• Birthday / Anniversary

Date-time columns: If your CRM requires date-time values, use YYYY-MM-DD HH:MM:SS. Strip time if the platform only accepts date.

For mixed date formats (some rows MM/DD/YYYY, others DD/MM/YYYY), see our guide on fixing mixed date formats in CSV.

Step 4: Standardize Phone Numbers

Every CRM accepts any phone format on import. None of them validate it. This creates silent data quality problems that compound over time: deduplication misses, automation failures, calling integrations that can't parse the number.

The standard format: E.164 international format — +15552345678 (no spaces, no dashes, no parentheses, country code included). This format works with every CRM, every calling integration, and every international carrier.

If your numbers don't have country codes: Default to your primary market. US numbers become +1, UK becomes +44, Germany becomes +49.

Common formats → E.164 conversion:

Use SplitForge Data Cleaner to standardize phone numbers across all rows in bulk. It handles 50+ country formats and processes locally — your contact data never leaves your browser.

For a complete guide to phone standardization, see how to standardize phone numbers in CSV for CRM import.

Step 5: Remove Duplicates

Duplicates in your import file create duplicate records in your CRM. Most CRMs only deduplicate on one field during import (usually Email) — duplicates that differ by any other field slip through.

Two deduplication checks to run:

Check A — Duplicates within your CSV:

Sort your file by email address. Scan for identical emails. Or use SplitForge Remove Duplicates to find and remove duplicates by any field automatically.

Check B — Duplicates against existing CRM records:

Export your CRM's current contact list. Compare email addresses between the export and your import file. Remove any email that already exists in the CRM before uploading — unless your goal is to update existing records.

Platform deduplication behavior on import:

For the complete guide to pre-import deduplication, see remove duplicate emails before CRM import.

Step 6: Populate Required Fields

Every CRM has required fields. A row missing a required field is rejected. The rest of the import continues — so you end up with a partial import and an error report listing every rejected row.

Minimum required fields by CRM:

How to find empty required fields:

In Excel: click the required column header → Ctrl+Down to jump to the last filled cell. Or use =COUNTBLANK(A2:A5000) to count empty cells.

Or run SplitForge Data Validator to flag all empty required field rows at once.

How to handle genuinely missing values:

For last names: use a placeholder like Unknown if you truly don't have the data. For emails: do not import contacts without emails into HubSpot — they can't be deduped and create zombie records. For Company: use the contact's domain (extracted from email) if no company name is available.

Platform-Specific Prep Notes

Salesforce

• Download the Data Import Wizard template before building your CSV — it contains exact API field names.
• Salesforce's Data Import Wizard handles up to 50,000 records. Use Data Loader for larger files.
• If importing Leads, Company is required even if the contact is an individual.
• Custom picklist fields reject values not in the active picklist — even valid text fails silently unless the value already exists in the list. "Trade Show" and "Tradeshow" are different values. "Technology" and "technology" are different values. Before importing, export your org's picklist values and compare against your CSV.

❌ BROKEN (picklist mismatch — Salesforce silently rejects this row):
Last Name,Company,Lead Source,Industry
Johnson,Acme Corp,Trade Show,Tech

Salesforce error: "Bad value for restricted picklist field: Lead Source"
Your org has "Tradeshow" (no space). "Trade Show" doesn't match.

FIXED:
Last Name,Company,Lead Source,Industry
Johnson,Acme Corp,Tradeshow,Technology

For more, see our Salesforce CSV import error guide.

HubSpot

• HubSpot deduplicates on Email — always include Email for every contact row.
• File size limit: 512MB. Row limit: 1,048,576 per file.
• UTF-8 required for any file with non-ASCII characters.
• Date format: YYYY-MM-DD.
• Dropdown / select properties: Values that don't exist in the property's option list are created as new options on import. This sounds helpful but creates option list sprawl — "VP Sales", "VP of Sales", and "VP, Sales" become three separate options. Clean dropdown values to match existing options before importing.

For more, see our HubSpot CSV field mapping error guide.

Zoho CRM

• Zoho accepts both DD/MM/YYYY and YYYY-MM-DD — use YYYY-MM-DD to eliminate ambiguity.
• Picklist fields must match configured options exactly, including case.
• Phone format: Zoho stores whatever you import. Use E.164 for integration compatibility.

For more, see our Zoho CRM CSV import error guide.

Pipedrive

• Pipedrive maps Name to a single full name field. Don't split into First/Last before importing.
• Custom field headers must match the field label exactly as it appears in Pipedrive settings.
• File size limit: 50,000 rows per import.

What a Failed CRM Import Actually Looks Like

Understanding the real failure output connects your prep steps to the specific errors they prevent.

HubSpot — field mapping failure:

Import result: 2,000 records processed
  ✓ 1,847 contacts created
  ✗ 153 rows skipped

Error report row samples:
Row 12: "Column 'Email Address' could not be mapped. No matching property found."
→ Cause: Used "Email Address" instead of HubSpot's "Email" property name
→ Fix: Step 2 — rename header to match HubSpot's exact property name

Row 47: "Value for 'Last Contact Date' is invalid: '15/03/2026'"
→ Cause: DD/MM/YYYY date format not recognized
→ Fix: Step 3 — standardize to YYYY-MM-DD

Salesforce — required field rejection:

Import result: 500 records processed
  ✓ 312 leads created
  ✗ 188 rows failed

Error report row samples:
Row 3: "Required field is missing: Company"
→ Cause: Company column has empty values (it's required for Leads)
→ Fix: Step 6 — populate required fields before importing

Row 89: "Bad value for restricted picklist field: Lead Source"
→ Cause: "Trade Show" not in Salesforce's configured Lead Source values
→ Fix: Check your org's picklist values match your CSV values exactly

Both error reports are downloadable from the platform as a CSV — always download them. They tell you exactly which rows failed and why, which is the fastest way to fix a partial import.

---

The Privacy Consideration

CRM files contain some of the most sensitive data your organization handles: customer names, emails, phone numbers, addresses, deal values. Before uploading to any online CSV cleaning or preparation tool, check their data retention policy.

Many cloud-based CSV tools retain uploaded files for 24–90 days for debugging or caching purposes. For files containing EU personal data, this can create a data processor relationship under GDPR Article 28, requiring a signed DPA. For healthcare or financial data, it may trigger HIPAA or CCPA obligations.

SplitForge processes files in Web Worker threads in your browser. For raw file contents, nothing is transmitted to any server. Preparing CRM data locally eliminates that exposure entirely.

For more on this, see our privacy-first data processing guide.

Additional Resources

Official CRM Documentation:

• HubSpot import file format requirements — Official field formatting and technical limits (updated March 2026)
• Salesforce Data Import Wizard overview — Official import requirements

Standards:

• RFC 4180: CSV Format Specification — Authoritative CSV structure standard
• ITU-T E.164 Telephone Numbering — International phone number format standard
• GDPR Article 28 — Processor obligations — Data processor requirements for third-party tools

Related Guides:

• CSV import checklist — 10 checks before every upload
• Validate CSV before upload — Proactive validation workflow
• Why CRMs reject CSV imports — Complete rejection cause taxonomy

FAQ