跳到正文
DEV Community· Muse AI Lab·· 3 小時前AI 評分37

CSV 匯入屢次失敗：我記錄了每項修正及變更紀錄

My CSV import kept failing — so I documented every single fix (with the change log)

AI 導讀

一份模擬供應商資料的 58 行產品 CSV 無法匯入電商後端，原因包括 Shift-JIS 編碼、分號分隔、多種日期格式、價格格式不一及重複資料。作者將檔案轉為 UTF-8 和逗號分隔，統一欄名、日期及價格，移除重複行和空行，並記錄每項變更；清理後輸出 50 行，另標記 3 行待人工判斷。

正文

Muse AI Lab

Every import guide says the same thing: "just upload your CSV." Nobody talks about what happens when the file itself is the problem.

I recently cleaned a 58-row product CSV (demo data I built to mirror a real supplier file) that failed to import into an e-commerce backend. Here is exactly what was wrong with it, and what a careful fix looks like.

The patient

  • Encoded in Shift-JIS (it came from a Japanese Windows machine), so product names turned to mojibake in a UTF-8 system
  • Semicolon-delimited, because the spreadsheet that exported it used a locale where commas are decimal separators
  • Five different date formats in one column: 2026/08/14, 09-05-2026, Sep 17 2026, 20260928, 2026-09-11
  • Prices like $142.48, 210.80 and 68.03 in the same column
  • Header row: SKU, Product_Name, CATEGORY, Price (USD), DATE_ADDED — no two headers formatted alike
  • 4 duplicate rows, 2 completely empty rows, 2 rows with required fields missing

The change log (this is the deliverable that matters) 

Header: [' SKU ', 'Product_Name', 'CATEGORY', 'Price (USD)', 'stock_qty', 'DATE_ADDED', 'Brand', 'Supplier_Email']
     -> ['sku', 'product_name', 'category', 'price_usd', 'stock_qty', 'date_added', 'brand', 'supplier_email']

- encoding: Shift-JIS -> UTF-8
- delimiter: semicolon -> comma
- headers_renamed: 7
- dates_normalized: 48
- prices_cleaned: 28
- emails_normalized: 37
- duplicates_removed: 4
- empty_rows_dropped: 2
- input rows: 58 -> output rows: 50

Flagged (NOT silently dropped):
- line 55 [SKU-1008]: duplicate SKU with a conflicting price — kept the first, flagged for a human decision
- line 58 [SKU-9001]: missing price
- line 59 [(no sku)]: missing SKU

Three rules I follow when fixing data files

  1. Never invent a value. A missing price is flagged, not guessed. The moment a cleanup tool "helpfully" fills in data, you can't trust any of it.
  2. Every change gets logged. If a fix can't be explained row by row, it didn't happen carefully enough.
  3. Deterministic tools first, AI second. Date parsing, dedup and encoding conversion are rule problems. Save judgment for the flagged rows — that's where humans (or an AI with the log in hand) actually add value.

The cleaned file imports on the first try, and the flagged list is three rows long instead of a 58-row mystery.

If your import is failing right now

Check, in this order: encoding (open the file and look for garbled text), delimiter, date format consistency, and duplicate keys. That covers most "the system just says error" cases I've seen.

And if you'd rather hand it off: I offer this exact cleanup as a small fixed-price service — send the broken file, get back the cleaned CSV plus the change log. Details: https://www.getly.store/services/csv-rescue-clean-fix-broken-import-files

The sample data above is fictional demo data, created to show the process and the report format.

來源：DEV Community · dev.to

#教學/實踐