An 800-row product sheet looks like a 20-minute import until the importer stops somewhere past the halfway mark and says the SKU is duplicated. It does not say which line. That is usually the moment people start deleting rows until something passes.

Most of those failures come from the file, not the store. The encoding, the price column, the image URL column, the variation rows and the SKU column account for nearly all of them. Clean those in a script first and the import turns into a non-event.

Everything below is plain Python 3 and works on any CSV you are about to feed into an admin importer. Only the column names are WooCommerce-flavoured.

pip install charset-normalizer requests

Read the file as bytes and let the library guess

A CSV exported from Excel on a Chinese or Japanese Windows machine is GB18030, not UTF-8. A CSV exported from Excel on macOS often has a UTF-8 BOM. Open a GB18030 file with encoding="utf-8" and you get either a UnicodeDecodeError on line 400 or, worse, mojibake that imports fine and renders as 中 on the product page three days later.

import csv, io, re, sys, unicodedata from pathlib import Path from urllib.parse import urlparse from charset_normalizer import from_bytes import requests ZERO_WIDTH = dict.fromkeys(map(ord, "\u200b\u200c\u200d\ufeff"), None) ODD_SPACES = {"\u00a0": " ", "\u2007": " ", "\u202f": " ", "\u3000": " "} def decode_any(path): raw = Path(path).read_bytes() guess = from_bytes(raw).best() if guess is None: sys.exit(f"no usable encoding for {path}") return str(guess).lstrip("\ufeff") # drop the BOM if there is one  def clean(value): if value is None: return "" v = unicodedata.normalize("NFKC", value) v = v.translate(ZERO_WIDTH) for bad, good in ODD_SPACES.items(): v = v.replace(bad, good) return re.sub(r"[ \t\r\n]+", " ", v).strip()

NFKC folds full-width letters and digits to ASCII, which is what you want in a SKU (ＡＢ－１２ becomes AB-12). It also rewrites some typographic punctuation, so skip it on the description column if the client cares about curly quotes.

Non-breaking spaces and zero-width characters are the other silent killer. They survive copy-paste from a chat app, look identical on screen, and make SKU == "AB-12" false for no visible reason.

Prices need one decision: which separator is the decimal one

1.299,00, 1,299.00, 1299, $1299.00, 1299,00 EUR all arrive in the same file when several people filled in the sheet. The rule that survives contact with real data: if both separators appear, the rightmost one is the decimal separator. If only one appears and it is followed by exactly three digits, it is a thousands separator. Everything else is decimal.

def parse_price(raw): s = clean(raw).replace(" ", "") if not s: return None, "empty" s = re.sub(r"[^\d.,\-]", "", s) # drop currency symbols and letters  neg = s.startswith("-") s = s.lstrip("-") if not any(c.isdigit() for c in s): return None, f"no digits in {raw!r}" last_dot, last_comma = s.rfind("."), s.rfind(",") if last_dot > -1 and last_comma > -1: dec = "." if last_dot > last_comma else "," thou = "," if dec == "." else "." elif last_dot > -1 or last_comma > -1: sep = "." if last_dot > -1 else "," digits_after = len(s) - s.rfind(sep) - 1 if s.count(sep) == 1 and digits_after in (1, 2): dec, thou = sep, None else: dec, thou = None, sep else: dec = thou = None if thou: s = s.replace(thou, "") if dec and dec != ".": s = s.replace(dec, ".") try: value = round(float(s), 2) except ValueError: return None, f"unparsable {raw!r}" return (-value if neg else value), None

Run this before the import and eyeball the rows where the input contained a separator. A 1.299 that should have been 1.299 euros and got read as 1299 is a refund, not a rounding error.

Image URLs fail in ways a browser hides

A URL that opens in your browser can still break the import. The three that bite: a content-type that is text/html because the host serves a "hotlinking not allowed" page, an unescaped space from a filename like red shoe.jpg, and an http URL on an https store (mixed content, image silently dropped).

Check with a HEAD request, fall back to a streamed GET because plenty of CDNs answer HEAD with 403 or 405, and cache the result so 40 variations sharing one parent image do not make 40 requests.

SESSION = requests.Session() SESSION.headers["User-Agent"] = "csv-preflight/1.0" def check_image(url, cache): if url in cache: return cache[url] problem = None p = urlparse(url) if p.scheme not in ("http", "https"): problem = "not an absolute http(s) URL" elif " " in url: problem = "unescaped space in URL" elif "." not in p.netloc: problem = "host has no dot" else: try: r = SESSION.head(url, timeout=10, allow_redirects=True) if r.status_code in (403, 405, 501): r = SESSION.get(url, timeout=10, stream=True, allow_redirects=True) r.close() if r.status_code >= 400: problem = f"HTTP {r.status_code}" else: ctype = r.headers.get("content-type", "").split(";")[0].strip().lower() if not ctype.startswith("image/"): problem = f"content-type {ctype or 'missing'}" except requests.RequestException as exc: problem = exc.__class__.__name__ # Timeout, SSLError, TooManyRedirects  cache[url] = problem return problem

Split gallery columns on commas before checking, and strip quotes around each part — "img1.jpg, img2.jpg" pasted out of a spreadsheet carries both.

Variation rows are where the SKU logic breaks

A variable product in a CSV is two kinds of row: the parent (Type=variable) and one row per combination (Type=variation) whose Parent column holds the parent SKU. Four things go wrong, in this order of frequency:

  • a variation points at a parent SKU that exists in the shop but not in the file, or was renamed in one place only;
  • two rows share a SKU, usually the parent's price row copied down into the variations;
  • the variation has no attribute value, so the importer creates a combination that cannot be selected on the front end;
  • the parent has no price and no variation has one either, so the product imports at 0.

Build the parent index first, then validate the variations against it. Do not validate in file order — a variation can legally sit above its parent.

def check_skus_and_variants(rows, header): problems, seen, parents = [], {}, {} for line, row in rows: sku = row.get("SKU", "") if sku: if sku in seen: problems.append((line, "SKU", sku, f"duplicate, first seen on line {seen[sku]}")) else: seen[sku] = line if row.get("Type", "").lower() == "variable" and sku: parents[sku] = line attr_cols = [h for h in header if h.lower().startswith("attribute")] for line, row in rows: if row.get("Type", "").lower() != "variation": continue parent = row.get("Parent", "") if parent and parent not in parents: problems.append((line, "Parent", parent, "parent SKU not present in this file")) if attr_cols and not any(row.get(c) for c in attr_cols): problems.append((line, "", "", "variation has no attribute value")) if not row.get("Regular price") and not parents.get(parent): problems.append((line, "Regular price", "", "no price on the variation")) return problems

Finding the line that actually failed

The importer's error message counts data rows, starting at 1 for the first row after the header. csv.reader.line_num counts physical lines in the file, starting at 1 for the header. If any field contains a line break inside quotes — a description with <br> typed as a real newline — the two numbers drift apart and you end up staring at the wrong record.

Report both, and let the reader pick.

There is a second, sharper signal: the column count. An unquoted comma inside a description pushes every later field one column to the right, so the row arrives with more fields than the header. Catch that before anything else, because every field-level check after it is meaningless.

def load_rows(path): text = decode_any(path) reader = csv.reader(io.StringIO(text)) header = [clean(h) for h in next(reader)] good, bad = [], [] for rec in reader: line = reader.line_num if not any(clean(c) for c in rec): continue if len(rec) != len(header): bad.append((line, "", "", f"expected {len(header)} columns, got {len(rec)}")) continue good.append((line, {h: clean(v) for h, v in zip(header, rec)})) return header, good, bad def main(src, report="import-report.csv"): header, rows, problems = load_rows(src) problems += check_skus_and_variants(rows, header) cache = {} for line, row in rows: for col in header: if "image" not in col.lower(): continue for url in [u.strip().strip('"') for u in row.get(col, "").split(",") if u.strip()]: why = check_image(url, cache) if why: problems.append((line, col, url, why)) if "price" in " ".join(header).lower() and row.get("Regular price"): _, why = parse_price(row["Regular price"]) if why: problems.append((line, "Regular price", row["Regular price"], why)) problems.sort() with open(report, "w", newline="", encoding="utf-8") as fh: w = csv.writer(fh) w.writerow(["physical_line", "column", "value", "problem"]) w.writerows(problems) print(f"{len(rows)} clean rows, {len(problems)} problems -> {report}") if __name__ == "__main__": main(sys.argv[1])

A note on what the report means. physical_line is the line in the file; the importer's row number is that minus one, minus any multiline rows above it. Sort the report by physical_line, open the sheet, and look at the columns named — nine times out of ten the value in the value column is visibly wrong once you are looking at the right cell.

Fix the sheet, re-export as UTF-8 with all fields quoted, run the script again until the report is empty, then import. A clean file imports in one pass and, more importantly, the data that lands in the database is the same data you reviewed.

We publish free WordPress plugins (translation, structured data, crawler visibility, digital-goods checkout) at https://aumcreate.com/plugins?utm_source=devto&utm_medium=content&utm_campaign=devto-csv-import-field-cleaning