How to Clean a CSV for Airtable Import: Prevent Auto-Detection Disasters

How Airtable's Auto-Detection Works (and Why It Breaks Your Data)

When you import a CSV into Airtable, it scans the first several hundred rows of each column and attempts to determine the field type automatically. If most values in a column look like numbers, Airtable creates a Number field. If they look like dates, it creates a Date field. If they look like URLs, it creates a URL field. This behavior cannot be disabled during import. There is no option to tell Airtable "treat this column as plain text."

The problem is that Airtable's type guessing is aggressive and often wrong. It makes decisions based on what data looks like rather than what it is. A column of phone numbers like "5551234567" looks like a column of large integers. A column of ZIP codes like "01234" looks like the number 1234. A column of product SKUs like "2026-03-15" looks like a date. Once Airtable assigns the wrong type during import, your data is already corrupted, and there is no undo.

This is fundamentally different from how CRMs like Salesforce or HubSpot handle imports. Those platforms let you explicitly map CSV columns to predefined fields with known types. Airtable's approach is designed for simplicity, but it creates a trap for anyone importing structured business data. The solution is to format your CSV so that Airtable's auto-detection reaches the correct conclusion for every column.

The 5 Worst Auto-Detection Failures

1. Phone Numbers Become Integers

This is the most common and most destructive Airtable import failure. A phone number like "5551234567" is imported as the integer 5,551,234,567. A number like "+15551234567" is imported as 15,551,234,567 (the plus sign is interpreted as a positive number indicator). Once this happens, the phone number is stored as a numeric value and loses all formatting. Even if you change the field type to "Single line text" after import, the value remains "5551234567" without the + prefix or any formatting.

International numbers fare even worse. A UK number like "07700900000" becomes 7,700,900,000 (the leading zero is dropped because integers do not have leading zeros). There is no way to recover that leading zero from within Airtable. The fix is to prefix every phone number with a character that forces text detection. Adding a + sign at the beginning (which you should have anyway for E.164 format) is not sufficient because Airtable strips it. Instead, format numbers as +1 555 123 4567 with spaces, which forces Airtable to treat the column as text. See our guide on why spreadsheets mangle phone numbers for more detail on this issue.

2. ZIP Codes Lose Leading Zeros

US ZIP codes in the northeast start with zero: 01234, 02345, 06789. When Airtable detects a column of ZIP codes as numeric, "01234" becomes "1234" and "06789" becomes "6789". These are now invalid ZIP codes that will fail address validation in any downstream system. The fix is to ensure your ZIP code column contains at least some values that force text detection, or to pad all ZIP codes to exactly 5 digits and include a non-numeric character prefix that you strip after import.

The more practical approach is to format ZIP codes with a dash for ZIP+4 format (e.g., "01234-5678") which forces text detection, or to include the word "ZIP" in the column header and ensure at least one value contains a letter. Canadian postal codes (which mix letters and numbers like "K1A 0B1") naturally force text detection, so mixed US-Canada datasets are actually safer than US-only datasets.

3. Dates Become Text (or Text Becomes Dates)

Airtable's date detection is inconsistent. A column with dates in "2026-03-15" format will usually be detected correctly as dates. But a column with "March 15, 2026" format may be detected as text. The reverse problem is worse: a column of product codes like "2026-03-15-BLUE" might be partially detected as dates, with Airtable extracting "2026-03-15" and discarding the rest.

Mixed date formats within a single column are catastrophic. If rows 1-100 use MM/DD/YYYY and rows 101-200 use DD/MM/YYYY, Airtable will pick whichever format it sees first and apply it to all rows. The rows that do not match will either be imported as the wrong date (March 4 becomes April 3) or left blank. The safest format for Airtable date detection is ISO 8601: YYYY-MM-DD. Use the date standardizer to normalize all dates before import.

4. Duplicates from Multiple Imports

Airtable has no built-in deduplication during import. Every row in your CSV becomes a new record, regardless of whether an identical record already exists in the base. If you import the same file twice by accident, you now have double the records. If you import an updated file that contains both new and existing records, the existing records appear twice. Unlike CRMs that deduplicate on email or ID, Airtable treats every import row as a brand new record.

This means deduplication must happen before import, not after. Clean your CSV to remove internal duplicates, and if you are doing incremental imports, filter out records that already exist in your Airtable base. NoSheet's dedup operation handles the first part. For incremental imports, export your existing Airtable data, merge it with the new data in NoSheet, deduplicate, and then re-import the clean combined dataset.

5. Attachment URLs Not Resolving

If your CSV contains URLs to images or files and you want Airtable to import them as attachments, the URLs must be publicly accessible direct links. Airtable will attempt to download the file at each URL during import. Links that require authentication, links that redirect, and links to Google Drive or Dropbox sharing pages (as opposed to direct download links) will fail silently. The attachment field will simply be empty for those rows. This is rarely a CSV cleaning issue, but it catches people off guard during migration from other platforms.

The Airtable Record Limit You Need to Know

Airtable's free plan limits each base to 1,200 records. The Team plan raises this to 50,000 records per base. The Business plan allows up to 125,000 records per base. The Enterprise Scale plan goes up to 500,000 records per base. These are hard limits. If your CSV has 60,000 rows and you are on the Team plan, the import will stop at 50,000 and silently discard the remaining 10,000 rows.

Before importing, check your current record count and your plan's limit. If your CSV will push you over the limit, you need to either upgrade your plan, split the data across multiple bases (which breaks relational links), or clean the data more aggressively to reduce the row count. Removing duplicates, filtering out inactive records, and consolidating related rows can often reduce a dataset by 20-40 percent.

Airtable's Linked Record Import Challenges

Airtable's power comes from linked records, the ability to connect records across tables (like connecting a Contact to a Company). However, CSV import does not natively support linked records. If your CSV has a "Company" column with company names, Airtable will import it as a text field, not as a link to records in a Companies table.

To import linked records, you need to import the "parent" table first (Companies), then import the "child" table (Contacts) with a column that exactly matches the primary field values in the parent table. After import, you can convert the text field to a Linked Record field. The values must match exactly, including casing and whitespace. "Acme Corp" in the Contacts table will not link to "Acme Corp " (with trailing space) or "ACME Corp" in the Companies table. This makes pre-import data cleaning absolutely critical for relational datasets.

The Complete Pre-Import Cleaning Checklist for Airtable

Follow this checklist in order before every Airtable import.

1. Format phone numbers to force text detection. Use E.164 format with spaces: +1 555 123 4567. The spaces prevent Airtable from interpreting the column as numeric. NoSheet's phone formatter handles this automatically.
2. Pad ZIP codes to preserve leading zeros. Ensure all US ZIP codes are exactly 5 digits (or 10 for ZIP+4). Pad with leading zeros where needed. Consider using ZIP+4 format with the dash to force text detection.
3. Standardize dates to ISO 8601. Convert every date column to YYYY-MM-DD format. This is the format Airtable's auto-detection handles most reliably. Do not mix formats within a single column.
4. Deduplicate before import. Airtable does not deduplicate during import. Remove all internal duplicates from your CSV. Choose a dedup key (email, ID, name combination) and resolve duplicates before uploading.
5. Standardize values for future linked records. If you plan to use linked records, ensure the linking values are exactly consistent between tables. Trim whitespace, standardize casing, and remove abbreviation variations.
6. Verify file size against your plan's record limit. Check that your CSV row count plus your existing records will not exceed your plan's per-base limit. If it will, clean more aggressively or split across bases.
7. Prefix numeric-looking text columns. For columns like SKUs, product codes, or account numbers that contain only digits, add a non-numeric prefix (even a single quote) or ensure at least one row contains a letter to force text detection.
8. Save as UTF-8 CSV. Airtable handles UTF-8 encoding well. Files in other encodings will produce garbled characters for any non-ASCII content.

How NoSheet Prevents Airtable's Auto-Detection Disasters

The core challenge with Airtable imports is that you cannot control how Airtable interprets your data. The only thing you can control is how your data is formatted before Airtable sees it. NoSheet gives you that control.

Phone formatting converts raw phone numbers into the spaced E.164 format that Airtable correctly detects as text. ZIP code padding ensures leading zeros are preserved. Date standardization converts mixed formats to consistent ISO 8601 so Airtable's date detection works correctly. Deduplication removes internal duplicates since Airtable will not do this for you.

The entire workflow takes minutes. Upload your CSV, apply the operations, preview the changes to verify Airtable will detect each column correctly, and download the clean file. Everything runs in your browser, so your data stays on your machine. NoSheet's Rust-powered processing engine handles files with tens of thousands of rows without slowing down, keeping you well within Airtable's import limits.

For related guides, see our article on why spreadsheets change phone numbers and the comprehensive CSV data cleaning guide.

Related Resources