CSV Basics for Ecommerce Imports

What Is a CSV File?

A CSV (Comma-Separated Values) file is a plain text file where each row is a record and columns are separated by commas. The first row contains the column names (the header row). Every row after that contains data.

Here is what a minimal Shopify product CSV looks like in raw text:

Handle,Title,Vendor,Variant Price
blue-shirt,Blue T-Shirt,Acme,19.99
red-shirt,Red T-Shirt,Acme,21.99

CSV files look simple, but platforms like Shopify, WooCommerce, Etsy, eBay, and Amazon are strict about formatting details that spreadsheet editors can silently corrupt. This guide explains every critical rule so your imports go through cleanly the first time.

The Header Row

The first row of every CSV must contain column names (headers). Platforms use these names to map your data to the correct fields during import.

Rules

• Headers are case-sensitive on most platforms. Handle is not the same as handle or HANDLE.
• No leading or trailing spaces. A header of " Title" (with a leading space) will not match "Title". Data in that column is silently ignored.
• No duplicate headers. If two columns share the same name, most parsers use only the last one.
• Column order matters for some platforms. Shopify, for example, expects specific columns in a specific order in its product template.

How to Check

Open your CSV in a plain text editor such as Notepad (Windows), TextEdit (Mac), or VS Code. Look at line 1. It should look like comma-separated names with no blank row above it:

Handle,Title,Body (HTML),Vendor,Type,Tags,...

If the first line starts with three strange characters before the header names, your file has a BOM. See the Encoding section below for how to fix it.

UTF-8 Encoding

Encoding is how text characters are stored as bytes on disk. The wrong encoding causes garbled text -- for example, a right-curly apostrophe stored as UTF-8 but read as Latin-1 produces the three-character sequence that looks like garbage.

Why UTF-8

UTF-8 is the universal standard for ecommerce platforms. Shopify, WooCommerce, Etsy, eBay, and Amazon all require UTF-8. Saving as UTF-8 ensures that:

• Accented characters display correctly.
• Currency symbols and special punctuation do not break.
• The platform importer can read the file without rejecting it.

BOM vs No-BOM

UTF-8 with BOM adds a hidden 3-byte sequence at the very start of the file. Excel on Windows writes this by default when you use "Save As > CSV UTF-8 (Comma delimited)". Most platforms either reject files with a BOM or misread the first column header because of the extra bytes.

How to tell if your file has a BOM: open it in VS Code and check the bottom-right status bar. If it says "UTF-8 with BOM", you have one.

Fix in VS Code: Click the encoding indicator in the status bar, choose "Save with Encoding", then select "UTF-8" (without BOM).

Fix in Excel: Choose "CSV (Comma delimited)" when saving -- not "CSV UTF-8 (Comma delimited)". The plain "CSV (Comma delimited)" option saves without a BOM on most Windows Excel versions.

Fix with Python:

# utf-8-sig strips the BOM on read; plain utf-8 writes without one
with open("input.csv", encoding="utf-8-sig") as f:
    content = f.read()
with open("output.csv", "w", encoding="utf-8") as f:
    f.write(content)

Encoding in Other Tools

Google Sheets: File > Download > Comma Separated Values exports as UTF-8. Some versions add a BOM -- if you see garbled characters at the start of your file, re-save in VS Code without BOM.

LibreOffice Calc: When saving as CSV, choose "Unicode (UTF-8)" in the dialog box.

Numbers (Mac): File > Export To > CSV exports as UTF-8 without BOM on modern macOS.

Quoting Rules and Commas Inside Values

When a cell value contains a comma, wrap the entire value in double-quote characters. This is the CSV standard (RFC 4180).

Basic Example

Title,Price,Description
"Leather Wallet, Brown",29.99,"Handmade in the USA, ships in 2 days"

Without the quotes, the parser sees five columns instead of three and puts the wrong data in the wrong fields.

Quotes Inside Quoted Values

If the value contains a double-quote character, escape it by doubling the quote:

"She said ""this is great"""

This represents the string: She said "this is great"

When Do You Need Quotes?

You need double-quote wrapping when a value contains:

• A comma
• A double-quote character
• A line break inside the value
• HTML tags (some parsers misread angle brackets when unquoted)

You do not need quotes for plain values like 29.99 or Active. Adding quotes around everything is harmless and many tools do it automatically.

Common Quoting Mistakes

| Mistake | Bad example | Correct | |---|---|---| | Comma inside value, no quotes | Wallet, Brown | "Wallet, Brown" | | HTML description unquoted | <p>Great item</p> | "<p>Great item</p>" | | Line break inside value, no quotes | Two separate rows | "Line1\nLine2" in one cell | | Wrong escape for embedded quote | "She said \"great\"" | "She said ""great""" |

Line Endings

Line endings tell the parser where one row ends and the next begins. There are two formats:

| Format | Bytes | Used by | |---|---|---| | CRLF | carriage return + line feed | Windows, Excel | | LF | line feed only | macOS, Linux, most web tools |

Most platforms accept either format. Problems arise when line endings are mixed -- some rows CRLF and others LF. Mixed endings can cause parsers to see extra blank rows or misread multi-line values.

How to Check and Fix

Open the file in VS Code. The status bar shows "CRLF" or "LF". Click it to toggle, then save.

With Python:

with open("input.csv", newline="", encoding="utf-8") as f:
    content = f.read()
# Normalize to LF
content = content.replace("\r\n", "\n").replace("\r", "\n")
with open("output.csv", "w", newline="\n", encoding="utf-8") as f:
    f.write(content)

The Most Common Import-Breaking Mistakes

1. Extra blank rows

Many spreadsheet tools add a blank row at the end. Platforms skip or error on blank rows. Delete them before exporting. In Excel, press Ctrl+End to jump to the last used cell -- if it is blank, delete the empty rows below your data.

2. Summary or total rows

SUM() formulas at the bottom of a sheet appear in the CSV as product rows. Delete summary rows before exporting.

3. Merged cells

Merged cells export as a value in the first cell and blanks in all others. The importer sees the blank cells as empty required fields. Un-merge all cells before exporting.

4. Formula results that export unexpectedly

A cell showing $29.99 from a formula might export as 29.99 or as the raw formula text. Use Paste Special > Values in Excel to replace all formulas with plain numbers before saving as CSV.

5. Trailing spaces in header names

"Title " (trailing space) is not the same as "Title". Data in that column is silently ignored on import. Use Find and Replace (Ctrl+H) to remove trailing spaces from headers.

6. Wrong file format with a .csv extension

Saving as .xlsx but naming it .csv produces a file that is not actually CSV. Open it in a text editor -- if you see binary characters, it is not a real CSV. Re-export using the correct save format.

7. ANSI or Latin-1 encoding

Old Excel sometimes saves as ANSI (Windows-1252). Product names with accented characters will be garbled on import. Always explicitly choose UTF-8 when saving.

Delimiters: Commas, Tabs, and Semicolons

The name "CSV" implies commas, but some files use tabs (TSV) or semicolons instead. Most ecommerce platforms expect true comma-delimited files. Uploading a semicolon-delimited file as if it were a CSV causes every row to appear as a single column.

How to Identify the Delimiter

Open the file in a plain text editor. Look at the first row:

• If column names are separated by , -- comma-delimited (standard CSV)
• If they are separated by ; -- semicolon-delimited (common in European Excel locales)
• If there are no separators and each row is one long string -- the file may be tab-delimited and you opened it in a tool that replaced tabs with nothing

Converting a Semicolon File to Comma-Delimited

In Excel: File > Save As > CSV (Comma delimited). If Excel still saves with semicolons (because your system locale uses semicolons as the list separator), change the list separator in your Windows Region settings, or use the Python method below.

In Python:

import csv

with open("input.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f, delimiter=";")
    rows = list(reader)

with open("output.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f, delimiter=",")
    writer.writerows(rows)

Spreadsheet Auto-Formatting Hazards

Spreadsheet applications like Excel and Google Sheets silently alter certain values when you open or edit a CSV. These changes can corrupt your data before you even make edits.

Leading Zeros Stripped from SKUs and IDs

Excel removes leading zeros from values it interprets as numbers. A SKU like 00123 becomes 123. A postal code like 02110 becomes 2110.

How to prevent it:

• In Excel: before importing the CSV, use the Text Import Wizard (Data > From Text/CSV) and set the column type to "Text" for any column that should preserve leading zeros.
• In Google Sheets: after import, format the affected column as "Plain text" (Format > Number > Plain text) before editing.
• With Python: always read SKU and ID columns with dtype=str if using pandas, or use the standard csv module which never coerces types.

Numbers Converted to Scientific Notation

Long numeric strings (13-digit barcodes, EAN codes) may display as 1.23457E+12 in Excel. When saved as CSV, this notation is written literally, which breaks barcode validation on import.

Fix: Select the affected column in Excel, format it as "Number" with 0 decimal places (not "Scientific"), then save. Alternatively, prefix the value with a single quote (forces text treatment) or format the column as text before pasting the values.

Dates Auto-Converted to Regional Format

A field containing 2024-01-15 may be auto-converted to 15/01/2024 or January 15, 2024 depending on your system locale. Platforms that expect ISO format (YYYY-MM-DD) will reject the reformatted value.

Fix: After opening the file, select date columns and format them as text, or use the Python csv module to read/write without any type coercion.

Prices Formatted with Currency Symbols

Excel sometimes saves a cell formatted as currency as $19.99 instead of 19.99. Most platforms expect plain decimal values without currency symbols.

Fix: In Excel, format price columns as "Number" (not "Currency" or "Accounting") before saving as CSV. Use Find and Replace (Ctrl+H) to remove any $, EUR, or other symbols if they are already in the data.

Blank Rows, Trailing Commas, and Invisible Problems

Extra Blank Rows at the End

Many tools add a blank row after the last data row. Platforms typically skip blank rows, but some reject them as invalid records. Always delete blank rows at the bottom before exporting.

In Excel: press Ctrl+End to jump to the last used cell. If it is blank (an empty row below your data), select those rows and delete them (right-click > Delete Rows).

Trailing Commas

Some export tools write a trailing comma at the end of every row: value1,value2,. This creates an implicit empty extra column. Most parsers handle this gracefully, but strict validators may flag it as a structural issue.

Fix: If trailing commas are present throughout the file, use a Python one-liner to strip them:

with open("input.csv", encoding="utf-8") as f:
    lines = f.readlines()
with open("output.csv", "w", encoding="utf-8") as f:
    for line in lines:
        f.write(line.rstrip(",\r\n") + "\n")

Invisible Characters

Some tools insert zero-width spaces (U+200B) or non-breaking spaces (U+00A0) inside cell values. These are invisible in spreadsheet views but can cause header mismatches or value rejections on import.

Check: Open the file in VS Code and use Find (Ctrl+F) with regex mode. Search for \x{200B} or \x{00A0} to find these characters.

Each platform adds specific requirements on top of the basic CSV standard:

• Shopify: The Handle column groups variant rows. Missing or invalid handles break product grouping.
• WooCommerce: Uses a Type column (simple, variable, variation) to define product structure.
• Etsy: Requires specific allowed values for fields like when_made and who_made.
• eBay: Requires the Action column (Add, Revise, Delete, End) on every row.
• Amazon: Column names vary by product category template; download the exact template for your category from Seller Central.

Quick Pre-Import Checklist

• [ ] Row 1 is headers -- no blank row above it
• [ ] Headers match the platform's expected column names exactly (case-sensitive)
• [ ] No duplicate column names
• [ ] No leading or trailing spaces in header names
• [ ] File saved as UTF-8 without BOM
• [ ] Values with commas, quotes, or newlines wrapped in double quotes
• [ ] No extra blank rows at the bottom
• [ ] No summary rows or formula cells
• [ ] Line endings are consistent (all CRLF or all LF)
• [ ] File extension is .csv and the file contains plain comma-separated text

Next Step: Validate Before You Import

After preparing your file, upload it to the CSV Fixer for automated validation. It checks headers, flags encoding issues, catches platform-specific problems, and auto-fixes safe issues in one click.