CSV Data Type Mismatch: Fix Text in Number Fields Before Import

The import ran. The confirmation screen said "Success." Then you checked the records — and half the number fields were blank.

That's a data type mismatch at its worst: a silent failure. No error message, no rejected row count, just missing data inside a CRM that looks completely healthy until someone runs a report and nothing adds up.

Data type mismatches are the second most common class of CSV import errors, right behind delimiter and encoding issues. They happen when the value in a CSV cell doesn't match the data type the destination field expects — a phone number formatted as (555) 123-4567 going into a numbers-only field, a date written as March 10, 2026 going into a field that expects 2026-03-10, or the word Yes going into a boolean field that only accepts TRUE or 1.

In CSV files we've analyzed across CRM, database, and e-commerce platforms, type mismatch errors account for a significant share of imports that partially succeed — records that get created but with critical fields silently left blank or defaulted to zero. These are harder to catch than hard failures because there's no error to find.

Quick fix checklist — if you're in a hurry, run through these before import:

• Remove all commas, currency symbols, and unit labels from number fields
• Convert all date columns to YYYY-MM-DD
• Replace Yes/No with TRUE/FALSE (CRMs) or 1/0 (databases)
• Verify every picklist value matches the platform's allowed list exactly
• Check text field lengths don't exceed platform limits

For the complete fix walkthrough on each of these, read on. For the complete error taxonomy across all 25+ CSV import failure types, see our CSV import errors complete guide.

---

Table of Contents

• Why Data Type Mismatches Are Dangerous
• How to Identify a Data Type Mismatch
• Type 1: Text in Number Fields
• Type 2: Wrong Date Format
• Type 3: Boolean Fields Written Wrong
• Type 4: Picklist Values That Don't Match
• Type 5: Text Too Long for the Field
• Step-by-Step Fix Guide
• Platform-Specific Data Type Rules
• How to Prevent Type Mismatches Going Forward
• FAQ

---

Why Data Type Mismatches Are Dangerous

Most CSV import errors are obvious. A delimiter mismatch opens your file as one column — you see it immediately. A missing header blocks the import before a single row is processed. You know something's wrong, you fix it, you move on.

Data type mismatches don't always work that way. Some platforms fail loudly. Others fail silently. And a few silently succeed — the import reports zero errors, but the field values are never actually written.

The most dangerous example: Salesforce Data Loader will report a successful import when number fields contain comma-formatted values like 10,000 instead of 10000. According to Salesforce Ben's documentation of common Data Loader errors, the operation completes as a success, but when you inspect the record, the Number of Employees field is blank. No error. No warning. Just missing data.

This is why type validation before import matters more than checking your confirmation screen after.

[Screenshot: Salesforce Data Loader showing a "Success" import result while a number field on the resulting record is blank]

---

How to Identify a Data Type Mismatch

The error messages vary significantly by platform. Here's the full map:

Salesforce Data Loader throws: <Field Name>: invalid number: <Number Value> when a number field receives a value with unsupported formatting (like comma separators). For date fields, it throws INVALID_FIELD: Failed to deserialize field or invalid date when the format doesn't match the expected pattern.

HubSpot shows: The import file contains a numerical value that is too small or too big for number range violations. For property type mismatches, it surfaces errors in the downloadable error report after import — the affected rows are imported but the property receives no value.

MySQL in strict mode (the default since MySQL 5.7) throws ERROR 1292: Incorrect [type] value when a value doesn't match the column's defined type. An empty string in a numeric column, text in an integer field, or a date in the wrong format will all hard-fail. Older MySQL configs would silently coerce the value to a default (0, NULL, or 0000-00-00) — which is arguably worse because the data looks valid but is wrong.

Airtable returns INVALID_VALUE_FOR_COLUMN with the message Field [name] cannot accept the provided value when a cell value doesn't match the field type. Date fields, number fields, and formula fields (which can't accept imports at all) are the most common triggers.

Use this table to diagnose your specific error before diving into fixes:

[Screenshot: HubSpot import error report showing rows imported with blank property values due to type mismatch]

---

Type 1: Text in Number Fields

This is the most common mismatch. It happens when a number column contains anything that isn't a clean integer or decimal: currency symbols ($10,000), percentage signs (85%), thousand-separator commas (1,234,567), unit labels (42 kg), or free-text entries like N/A or TBD.

Here's what the data looks like before and after a clean fix:

# Wrong — common export formats that break number field imports
Revenue,Headcount,Conversion Rate,Deal Size
$1,250,000,145 employees,12.5%,$45,000
N/A,32,TBD,$8,500

# Correct — clean numeric values only
Revenue,Headcount,Conversion Rate,Deal Size
1250000,145,12.5,45000
,32,,8500

Key rules: strip all currency symbols, remove thousand-separator commas, remove percent signs (store the decimal value instead), remove unit labels, and replace N/A/TBD/- with empty cells rather than placeholder text.

The Salesforce comma-separator issue is particularly tricky because 10,000 looks like a valid number to any human reader — it's just not valid to the import parser, which reads it as a string. The fix is to remove all commas from number fields before import, no exceptions.

---

Type 2: Wrong Date Format

Every platform has a specific date format it accepts. None of them agree. This is the most platform-specific type mismatch and the one that produces the widest variety of error messages.

Common format collisions:

# These all represent the same date — but only one works on each platform
March 10, 2026          → text, rejected everywhere
10/03/2026              → ambiguous (is this Oct 3 or Mar 10?)
03/10/2026              → US format, fails on EU-configured systems
10-03-2026              → dashes instead of slashes, fails many parsers
2026-03-10              → ISO 8601, accepted by most platforms
2026-03-10T00:00:00Z    → ISO 8601 with time, required by some APIs

The safest universal format is YYYY-MM-DD (ISO 8601). Most modern platforms accept it. Where they don't, the error message will tell you the expected format — use that exactly.

For platforms with mixed date format support across regions, see our dedicated guide on fixing mixed date formats in CSV columns.

[Screenshot: Before/after example showing a date column reformatted from mixed formats to ISO 8601 YYYY-MM-DD]

---

Type 3: Boolean Fields Written Wrong

Boolean fields accept only two values: true or false. The problem is that different export tools write booleans differently, and the target platform may only accept one specific representation.

# All mean "true" — but platforms accept different versions
Yes         → rejected by most database importers
yes         → rejected by strict platforms
TRUE        → accepted by Excel, Google Sheets, many CRMs
True        → accepted by some platforms, rejected by others
true        → accepted by MySQL, PostgreSQL, most APIs
1           → universally accepted by database importers
✓           → text character, rejected everywhere as a boolean

The safest approach for database imports (MySQL, PostgreSQL, Snowflake) is 1 and 0. For CRM imports (Salesforce, HubSpot), TRUE and FALSE in all caps is the most broadly accepted format. Never use Yes/No unless the platform's documentation explicitly says so.

---

Type 4: Picklist Values That Don't Match

Picklist fields (also called dropdown fields, select fields, or restricted value fields) only accept values that exactly match the predefined options in the platform. This is a type mismatch in the sense that the field expects a controlled vocabulary value and receives something that isn't in that vocabulary.

This is covered in depth in our guide to fixing Salesforce bad value for restricted picklist errors, but the core rule applies everywhere: picklist values must match exactly — same capitalization, same spacing, same punctuation as the option defined in the platform. New York and New york are different values. In Progress and In-Progress are different values.

The most common source of picklist mismatches: exporting from one system where the picklist options were different or more permissive, then importing into a platform with strict picklist validation.

---

Type 5: Text Too Long for the Field

Every field has a maximum character length. When a text value exceeds that limit, the import either hard-fails that row or silently truncates the value — depending on the platform.

Common field length limits:

• Salesforce text fields: 255 characters default (custom fields can go up to 131,072)
• HubSpot single-line text: 65,536 characters
• MySQL VARCHAR: defined at table creation, commonly 255 or 1000 chars
• Airtable single line text: 100,000 characters

The error surfaces as value too large for column in MySQL, or the row simply imports with the field cut off mid-sentence in more lenient platforms. Neither is acceptable for data integrity.

Here's what silent truncation looks like:

# Original comment field value (312 characters)
"Customer contacted support three times regarding delayed shipment.
Issue escalated to logistics team on 2026-02-14. Resolution pending
warehouse confirmation. Follow up scheduled for end of Q1."

# What Salesforce writes to a 255-char text field
"Customer contacted support three times regarding delayed shipment.
Issue escalated to logistics team on 2026-02-14. Resolution pending
warehouse confirmation. Follow up sche"

The import succeeds. No error is thrown. The record looks fine until someone reads it.

---

Step-by-Step Fix Guide

Before you start: run your file through Data Validator to get a complete map of which columns have type issues and where. It detects number formatting problems, date inconsistencies, boolean mismatches, and field length violations in under 4 seconds — without uploading your file anywhere.

Step 1 — Identify every column's expected data type.

Download the platform's field definitions or import template. Map each column in your CSV to its destination field type: text, number (integer vs decimal), date, boolean, picklist, or lookup. Any column without a confirmed type mapping is a risk.

Step 2 — Audit number columns for non-numeric characters.

In Excel or Google Sheets, use =ISNUMBER(A2) on each number column. Any cell returning FALSE contains a non-numeric value. Common culprits: currency symbols, thousand-separator commas, percentage signs, unit labels, and freetext placeholders like N/A. Strip them all. For currency: use a formula like =SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","") to remove symbols and commas.

Step 3 — Standardize all date columns to YYYY-MM-DD.

In Excel: select the column, Format Cells → Custom → type YYYY-MM-DD. In Google Sheets: Format → Number → Custom date and time → set to YYYY-MM-DD. In both tools, verify the result in a text editor — Excel sometimes stores dates as serial numbers internally and displays the formatted version, while the CSV export writes the serial number instead of the formatted text.

Step 4 — Standardize boolean columns.

Replace all Yes/No/True/False/✓/✗ values with the format your target platform expects. For most database imports: 1 and 0. For most CRM imports: TRUE and FALSE. Use Find & Replace in Excel to do this in one pass per column.

Step 5 — Audit picklist columns against the allowed value list.

Export the current picklist options from your platform and paste them into a reference column. Use Excel's COUNTIF to check how many CSV values match the allowed list exactly: =COUNTIF($E$1:$E$20,A2) where column E contains the allowed values. Any row returning 0 is a mismatch.

Step 6 — Check field length limits.

In Excel, use =LEN(A2) on text columns to find the longest value. Compare against your platform's field length limit. For any values that exceed the limit, either truncate them deliberately or split the content into a second field.

Step 7 — Validate the full file before re-uploading.

Run the cleaned file through Data Validator once more to confirm no type issues remain across all columns before attempting the import.

[Screenshot: SplitForge Data Validator showing column-by-column type validation results with specific cells flagged for mismatch]

---

Platform-Specific Data Type Rules

Salesforce

Salesforce enforces strict type checking at the field level. The critical edge cases to know:

Number fields reject comma separators entirely. According to Salesforce Ben's documentation of Data Loader errors, importing 10,000 into a Number field will show as a successful import but leave the field blank — there is no error message. Always strip commas from number values.

Date fields require YYYY-MM-DD for the Data Import Wizard. Data Loader accepts several formats but YYYY-MM-DD is the safest. DateTime fields require YYYY-MM-DDThh:mm:ss — the timezone suffix Z should be omitted unless you are deliberately specifying GMT.

Picklist fields with restricted values (Restrict picklist to the values defined in the value set enabled) will reject any value not in the defined set. The import reports an error for those rows. The fix is to either update the CSV values to match the picklist exactly, or add the new values to the Salesforce picklist before importing.

HubSpot

HubSpot surfaces type errors in the downloadable error report after import — not as a hard failure that stops the process. Per HubSpot's import troubleshooting documentation, affected rows are imported but the property receives no value. Always download and inspect the error report after every HubSpot import.

Number properties reject values outside the field's defined range. Date properties require YYYY-MM-DD. Checkbox (boolean) properties accept true/false in lowercase.

MySQL

MySQL in strict mode (default since 5.7) throws ERROR 1292: Incorrect [type] value for any cell that doesn't match the column's defined type. Common triggers: empty strings in numeric columns (use NULL or 0 instead), text in integer fields, and dates in formats other than YYYY-MM-DD.

MySQL's LOAD DATA INFILE with strict mode enabled is unforgiving — one type error in one row will abort the entire import unless you add IGNORE to the statement (which silently skips bad rows instead). The safer approach is to fix the file before import rather than using IGNORE and losing data.

Airtable

Airtable's type enforcement depends on the field type defined in the base. Formula fields cannot accept imported values at all — attempting to map a CSV column to a formula field will always fail. Single-select and multi-select fields behave like picklists: values that don't match existing options are either created as new options (if the base allows it) or rejected. Date fields require ISO 8601 format.

Shopify

Shopify product and customer CSVs are relatively forgiving on data types compared to CRMs and databases, but still enforce rules on specific fields. Date fields such as Published on online store require YYYY-MM-DD or YYYY-MM-DDTHH:MM:SSZ. Boolean-style fields like Variant Requires Shipping and Published accept TRUE/FALSE (uppercase) or 1/0. Picklist-style fields such as Status and Tags must match exact accepted values or they are silently ignored. Per Shopify's common import issues documentation, type mismatches in Shopify product imports typically result in silent skips rather than hard errors — the field is left blank with no warning.

---

How to Prevent Type Mismatches Going Forward

Define your schema before collecting data. If you know you'll be importing into a platform with strict number fields, ensure your data collection form outputs clean numerics from the start — no currency formatting, no percentage signs, no free-text in numeric columns.

Use Data Validator before every import. Running Data Validator takes 4 seconds and flags type mismatches, number formatting issues, and date inconsistencies before you attempt an upload. Catching these in the browser is faster than diagnosing a partial import after the fact.

Export from the destination platform first. Export a few existing records from your target platform. That export becomes your exact type format reference — if the platform exports dates as YYYY-MM-DD, it expects them back in YYYY-MM-DD.

Build a type conversion checklist for recurring imports. If you're running the same import workflow weekly or monthly, document the exact transformations needed: which columns need comma-stripping, which date columns need reformatting, which boolean columns need value standardization. Run through the checklist before every import rather than rediscovering issues each time.

For a full pre-import validation workflow, see our CSV file validation guide. If your import is also showing column structure issues, check our guide on column count mismatch errors — the two problems often appear together when data has been exported from Excel with mixed column content. And if you're not sure which type of error you're dealing with at all, our CSV import error diagnostics guide walks through the full triage process across every error class.

---

FAQ