You export customer data from your CRM and load it into your marketing automation platform. The dates are wrong. The phone numbers have extra characters. Some names have garbled special characters. The data was fine in the CRM — what happened?
Cross-system data format problems are one of the most common and most frustrating data quality issues. They're predictable, preventable — and almost always the result of the same small set of causes.
Why Data Formats Change Between Systems
1. Different Default Encoding Standards
The most common source of garbled text — "é" instead of "é," "’" instead of an apostrophe — is a character encoding mismatch. Your CRM exports in Windows-1252 (Latin-1) encoding. Your marketing platform expects UTF-8. When UTF-8 reads Windows-1252 bytes, some characters appear as garbled sequences.
Sohovi finds gaps, duplicates, and format errors in your CRM data — so your team is working from records they can trust.
The fix: always export as UTF-8. When receiving external files, detect and convert encoding before processing.
2. Regional Date Format Differences
A US system exports dates as MM/DD/YYYY. A European system or a system with European regional settings reads DD/MM/YYYY. March 5th becomes May 3rd silently — no error, no warning, just wrong data.
The fix: always use ISO 8601 (YYYY-MM-DD) for any date field that will cross a system boundary. It's the only date format that's unambiguous across all regional conventions.
3. Phone Number Format Stripping
Different systems store phone numbers in different formats. A CRM may store "+1 (555) 123-4567." When this is exported to a CSV and reimported to another system that expects plain digits, the parentheses, spaces, and dashes may be interpreted as delimiters — splitting one field into multiple columns or truncating the number.
The fix: standardize to E.164 format (+15551234567) at every system boundary.
4. Field Mapping Mismatches
When data moves between systems via an integration, API, or manual import, the field mapping — which source field maps to which destination field — may be wrong or incomplete. A "first_name" field in the source maps to "full_name" in the destination. A "company" field is dropped because there's no corresponding destination field.
The fix: document and validate field mappings before any integration or import. Test with sample data before running at scale.
Sohovi lets you set up validation rules for any column and instantly see which rows fall outside them — no code or SQL required.
5. Different Field Length Limits
One system allows 255 characters in a company name field. Another allows 100. When data moves from the longer system to the shorter one, company names that exceed the limit are truncated — silently. "International Business Machines Corporation" becomes "International Business Machines Corp."
The fix: know the field constraints of your destination system and validate source data against those constraints before importing.
Frequently Asked Questions
Q: Why does my data look different after moving between systems? The most common causes are: character encoding mismatches (Windows-1252 vs. UTF-8), regional date format differences (MM/DD/YYYY vs. DD/MM/YYYY), phone number format stripping during import, field mapping mismatches, and field length truncation in the destination system.
Q: What is character encoding and why does it cause format problems? Character encoding is the specification that maps byte values to text characters. Different encodings represent the same characters using different byte sequences. When a file encoded in one standard is read using a different standard, certain characters — especially accented letters and special punctuation — appear as garbled sequences.
Q: How do I prevent date format problems between systems? Use ISO 8601 (YYYY-MM-DD) for any date that will cross a system boundary. It's the only date format that is unambiguous regardless of regional settings or locale. Configure every system's date output to use this format when exporting.
Q: What is the E.164 phone number format and why should I use it? E.164 is the international standard for phone numbers: "+" followed by country code and subscriber number, no formatting characters. "+15551234567" in E.164. It's unambiguous, parseable by any telephony system, and doesn't get distorted when stored as a string or moved between systems.
Q: What is a field mapping document and how does it prevent format problems? A field mapping document specifies exactly how each field in the source system translates to a field in the destination system — including field name, data type, transformation required, and what to do when a source field has no destination equivalent. Documenting and validating this mapping before any integration prevents silent field mismatch errors.
Q: What causes text to appear garbled when moving between systems? The most common cause is a character encoding mismatch — Windows-1252 or Latin-1 encoded text read as UTF-8. The solution is to detect the correct encoding, convert to UTF-8 before processing, and configure all systems to export as UTF-8 by default.
Q: How does field truncation damage data quality? When a source value exceeds the destination field's character limit, the extra characters are silently removed. A truncated company name or address may look like a different entry than the original. Truncation also breaks deduplication — "International Business Machines Corporation" and "International Business Machines Corp" match the same company but may not be recognized as duplicates.
Q: What is the best way to test that a system integration is handling data format correctly? Test with a carefully designed sample: include records with accented characters, records with long field values, records with each date and phone format variant, and records with null values in different fields. Validate the output against the expected schema and value format before running the integration at scale.
Q: What is a data format specification and when should I create one? A data format specification defines the expected format for every field in a dataset — data type, character encoding, date format, phone format, field length limits. Create one for any integration or import that will run regularly. Share it with source data providers to prevent format surprises.
Q: How do spreadsheet tools like Excel contribute to format problems? Excel changes data formats during import and display: dates may be auto-converted to Excel's internal date format and displayed differently than stored, phone numbers may have leading zeros removed if interpreted as numbers, and the encoding used when saving CSV files depends on system locale settings. Excel is a common source of format problems when used as an intermediate step between systems.
Cross-system format problems are almost always predictable and preventable. Establish format standards for every field that crosses a system boundary, validate before importing, and test with edge cases before running at scale.
