How to Clean a CSV for Shopify Customer Import: The Complete Guide

Shopify's Customer CSV Format Explained

Shopify uses a specific CSV format for customer imports. Unlike other platforms that let you map columns during upload, Shopify expects exact column headers. If your headers do not match, the import will either fail entirely or silently skip columns, leaving customer records incomplete. Before you do anything else, make sure your CSV uses these exact column names.

The asterisk on Email and Phone means that at least one of the two is required. Every customer record must have either a valid email address or a valid phone number. Records missing both will be rejected during import. This is one of the most frequently missed requirements, especially when importing B2B data where phone numbers were never collected.

Shopify's Strict Validation Rules

Shopify applies several validation rules during import that are not immediately obvious from the documentation. Understanding these rules before you clean your data will save you from multiple failed import attempts.

Email is the unique identifier. Shopify uses email to determine whether a customer already exists. If you import a row with an email that matches an existing customer, the import will update that customer rather than creating a new one. This means duplicate emails in your CSV will result in only the last row being applied. If your dataset has 500 entries for "info@company.com" because multiple contacts share a company email, only the final row's data will persist.

Province codes must match the country. This is the single most confusing validation in Shopify imports. A Province Code of "ON" is valid for Canada (Ontario) but will fail if the Country Code is "US". Shopify validates province codes against the country and rejects the entire address if they do not match. If you have international customers, you must ensure every Province Code is a valid ISO 3166-2 subdivision code for the corresponding Country Code.

Tags must be comma-separated. If you use semicolons, pipes, or other delimiters for tags, Shopify will treat the entire string as a single tag. A tag value of "vip;wholesale;2026" becomes one tag instead of three. Tags are also case-sensitive during import but normalized to lowercase in the Shopify admin, so "VIP" and "vip" will merge into one tag after import.

Phone numbers need country codes. Shopify requires phone numbers to include country codes. A number like "5551234567" without a +1 prefix will either be rejected or misinterpreted depending on your store's country setting. International numbers without proper country codes will be stored but will not work for SMS marketing or order notifications.

The 5 Most Common Shopify Import Failures

1. Duplicate Customers Created by Email Variations

Shopify deduplicates on exact email match. If your CSV contains "john@example.com" and "John@Example.com", Shopify will treat these as the same customer (email matching is case-insensitive). However, "john @example.com" (with a space) will create a separate record. Invisible whitespace, including non-breaking spaces and tabs, is the most common source of unwanted duplicates. A customer list exported from another platform may contain hundreds of these invisible variations that look identical in a spreadsheet but create separate records on import.

2. Province Code and Country Mismatch

This failure is especially common when importing international customer lists. You have a customer in London with Province set to "England" and Province Code set to "ENG", but Country Code is blank or set to "US". Shopify rejects the address entirely. Even within the United States, using "Calif." or "California" in the Province Code column instead of "CA" will cause failures. The Province column accepts full names, but Province Code must be the standardized two-letter or three-letter ISO code.

3. Phone Numbers Without Country Codes

Exporting customers from a POS system, email marketing platform, or legacy CRM often produces phone numbers in local format: "(415) 555-1234" or "07700 900000". Shopify needs these in E.164 format with the + prefix and country code. Without the country code, Shopify cannot determine the correct international format. This breaks SMS notifications, abandoned cart recovery messages, and any Shopify Flow automation that relies on phone data. Use NoSheet's phone formatter to convert all numbers to E.164 in bulk before importing.

4. Encoding Problems with International Characters

Customer names like "Muller" instead of "Müller", "Francois" instead of "François", or garbled characters like "é" replacing "é" are encoding failures. These happen when your CSV was saved in Windows-1252 or Latin-1 encoding instead of UTF-8. Shopify requires UTF-8 encoding. If you export a CSV from Excel on Windows, it defaults to ANSI encoding, which corrupts any character outside the basic ASCII range. Always save as "CSV UTF-8 (Comma delimited)" in Excel, not just "CSV."

5. Missing Both Email and Phone

Shopify requires at least one contact method per customer. Records with neither an email nor a phone number are silently dropped during import. No error message, no warning — just missing customers. This is especially common when migrating from platforms that used account IDs instead of email or phone as the primary identifier. If your source data is thin, validate before import that every row has at least one of these fields populated.

The 6-Step Cleaning Checklist for Shopify Customer Imports

Follow this checklist in order before every Shopify customer import. Each step builds on the previous one.

1. Validate email addresses. Run every email through syntax validation. Fix common domain typos (gmial.com, outlok.com, yaho.com). Remove entries that are clearly fake (test@test.com, noemail@noemail.com). Trim all whitespace including invisible characters. NoSheet's email validator handles all of this in a single pass.
2. Format phone numbers with country codes. Convert every phone number to E.164 format. Strip parentheses, dashes, dots, and spaces. Prepend the + and country code. If your customer base is primarily US-based, default to +1 for numbers without country codes. For international stores, you will need to infer the country code from the address data or tag the rows that need manual review.
3. Standardize province and state codes. Replace full state names, abbreviations, and non-standard codes with ISO 3166-2 codes. "California" becomes "CA". "Ont." becomes "ON". "NSW" stays "NSW" for Australia. Cross-reference every Province Code against its Country Code to ensure the combination is valid.
4. Use ISO country codes. Replace country names with ISO 3166-1 alpha-2 codes. "United States" becomes "US". "United Kingdom" becomes "GB" (not "UK", which is not a valid ISO code). "South Korea" becomes "KR". Inconsistent country naming is one of the top reasons address validation fails during Shopify import.
5. Deduplicate on email address. Sort by email and remove duplicate rows. When duplicates exist, keep the row with the most complete data (most fields filled). If you have multiple addresses for one customer, keep the most recent or the one with the shipping address. Shopify only stores one default address per customer on import.
6. Trim whitespace from every field. Remove leading and trailing spaces from every single cell. Remove double spaces within names and addresses. Replace non-breaking spaces with regular spaces. This prevents duplicate creation, address validation failures, and display issues in the Shopify admin.

How NoSheet Automates Shopify Import Preparation

Every step in the checklist above maps directly to a NoSheet operation. Upload your customer CSV, apply the operations, preview the results, and download a file that is ready for Shopify. Here is what the workflow looks like in practice:

Email validation catches syntax errors, flags common domain typos, and removes invalid entries. Phone formatting converts any number format to E.164 with automatic country code detection based on the number pattern and the address data in the same row. Deduplication uses email as the match key and intelligently selects the most complete row when duplicates are found.

The entire process takes minutes, not hours. A 20,000-customer CSV can be cleaned and validated in under two minutes. NoSheet processes everything in your browser using a Rust-powered engine, so your customer data never leaves your machine. This is particularly important for e-commerce businesses handling customer addresses, emails, and phone numbers that fall under privacy regulations like GDPR and CCPA.

For more platform-specific import guides, see our articles on cleaning CSVs for HubSpot and our comprehensive CSV data cleaning guide.

Related Resources