TableMender
Practical guide

Hand off a Shopify-style CSV without losing SKUs

Keep storefront SKUs, barcodes and variant IDs as text, then clean duplicates with safe keys.

By · Updated November 3, 2026 · AI-assisted drafting

Quick answer. Preserve SKU, barcode and variant id as Text, then clean duplicates with a key that includes variant or inventory context—not SKU alone.

Try a file with a known result

Original synthetic teaching data; no customer records. Download the small sample, then load it into the matching tool. Your own files remain in your browser.

Settings for this example

Load shopify-shaped.csv in CSV to Excel with Comma and a header. Keep SKU, Barcode and Variant ID on Text. Optionally set Inventory Qty or Price to Number. For duplicate review, clear input and open the same file in CSV clean with an explicit multi-column key.

Expected results to compare

  1. The preview has 3 data records and 6 columns.
  2. SKU-00127, barcode 0123456789012 and the full variant id remain intact.
  3. Two rows share SKU-00127, the same variant id and different inventory quantities. Keep-conflicts retains both. Keep-first on SKU, barcode or variant id removes the quantity-0 row. Empty-record removal removes 0 rows.

Not affiliated with Shopify. Column names illustrate a common export shape; compare with your live admin headers.

Open the matching tool →

Storefront CSVs mix identifiers and quantities

Marketplace and storefront exports—Shopify product or order CSVs are a common example—usually combine SKU, barcode, variant id, inventory counts and prices in one file. The spreadsheet injuries are familiar: leading zeros on SKUs, long variant ids in scientific notation, and barcodes rewritten as numbers. This guide shows a compact synthetic export shaped like those handoffs without copying any vendor’s proprietary template.

Handle,SKU,Barcode,Variant ID,Inventory Qty,Price
blue-mug,SKU-00127,0123456789012,401234567890123456,12,9.50
blue-mug,SKU-00127,0123456789012,401234567890123456,0,9.50
red-mug,SKU-00904,0987654321098,409876543210987654,3,11.00

Handles and SKUs are invented. Barcodes and variant ids are synthetic digit strings for teaching length and padding. Notice the duplicate SKU across two inventory rows: cleaning duplicates needs a key that includes location or variant context, not SKU alone. Download /samples/shopify-shaped.csv.

Before and after: what a general-format open costs you

After a casual Excel open, the same rows often look like:

Handle    SKU        Barcode        Variant ID      Inventory Qty  Price
blue-mug  SKU-00127  123456789012   4.01235E+17     12             9.5
blue-mug  SKU-00127  123456789012   4.01235E+17     0              9.5
red-mug   SKU-00904  9.87654E+11    4.09877E+17     3              11

Barcode leading zeros vanish; variant ids become exponents; prices may lose trailing zeros in display. A text-first handoff from CSV to Excel keeps every field as characters, including both inventory rows:

shopify-shaped.csv kept as text in CSV to Excel
HandleSKUBarcodeVariant IDInventory QtyPrice
blue-mugSKU-001270123456789012401234567890123456129.50
blue-mugSKU-00127012345678901240123456789012345609.50
red-mugSKU-009040987654321098409876543210987654311.00

Handoff workflow that keeps codes intact

  1. Download the export from the admin and keep that file as the source of truth.
  2. Open CSV to Excel. Preview with Comma and a header. Confirm SKU zeros, barcode length and the full variant id.
  3. Leave SKU, Barcode and Variant ID on Text. Choose Number only for Inventory Qty or Price when you need arithmetic.
  4. If you must remove duplicate rows, open CSV clean only after the identifiers still match the admin export. On this teaching file, Variant ID does not separate the two blue-mug rows. Read the conflict review before keep-first. See duplicate keys.

Convert a storefront CSV safely → · Clean duplicates with explicit keys →

Step-by-step troubleshooting

  1. SKU still padded but barcode shortened. Leave barcode on Text; do not use Number to “sort better.”
  2. Variant ID shows as 4.01E+17 in the CSV itself. The admin export was already rewritten. Re-download from the admin; local tools cannot expand exponents back into digits (scientific notation guide).
  3. HTML descriptions break columns. Multiline body HTML needs quoted-field parsing — quotes and line breaks and CSV check & repair.
  4. Accented titles look wrong. Fix encoding before types — encoding guide.
  5. Dedup removed the quantity-0 blue-mug row. That is keep-first. Handle, SKU, Barcode, Variant ID and Price each form one conflicting group here, because those fields match and Inventory Qty does not. Restore original preview and stay on keep-conflicts. Inventory Qty alone finds no duplicate group, and it is not a product key.

What not to do

Do not open the admin CSV in Excel and Save As CSV “to clean it” before the handoff—that path often drops zeros and shortens ids permanently (Save As pitfalls). Do not mark every column as Number to make sorting look right. Do not assume SKU uniqueness across variants or locations when removing duplicates.

TableMender is not affiliated with Shopify or other marketplaces. Product names describe common export shapes. Always compare your live admin columns with the preview before trusting a download.

Honest limits

This site does not import Shopify Admin APIs, push inventory back to a store or validate GTIN check digits. Synthetic samples omit many real template columns (SEO, image URLs, metafields). Uneven records still block export; the tool will not pad missing cells to force a download. If the only copy already lost zeros, re-export from the admin rather than guessing.

Worked rows in the teaching file

Load shopify-shaped.csv in CSV to Excel with Comma and a header. You should see three data rows and six columns. Confirm SKU-00127, barcode 0123456789012 (13 characters, leading zero) and Variant ID 401234567890123456 (18 digits). Inventory Qty 0 on the second blue-mug row is a real quantity, not an empty record — do not treat it as a blank row in CSV clean.

Leave SKU, Barcode and Variant ID on Text. Number is appropriate for Inventory Qty, including the quantity 0. Price 9.50 and 11.00 can be Number or two-decimal display. Two-decimal display changes appearance. The stored numeric text stays 9.50 and 11.00.

HTML bodies, extra columns and inventory zero

A live product export often includes Body HTML with commas, quotes and line breaks. Parse those fields before arguing about types — quotes and line breaks. Metafields, image URLs and SEO columns are ordinary text here; TableMender does not fetch those URLs. A live catalog often repeats Handle across variants that have distinct variant ids. This teaching file reuses one variant id for two inventory quantities, so compare your admin headers with the preview before you treat any column as unique.

FAQ grounded in the tools

Is this an official Shopify template? No. Column names are shaped like common storefront exports so you can practice the identifier injuries. Compare your live admin headers before trusting a download.

Can TableMender push the XLSX back to the shop? No. There is no Admin API client, no inventory webhook and no GTIN validator.

The CSV already shows 4.01E+17. Local tools cannot expand that exponent. Re-download from the admin. See scientific notation and repair versus re-export.

Number export on shopify-shaped.csv

Set one column to Number in CSV to Excel, read the list, then set it back to Text. The header is source record 1. The list shows each failing cell, up to eight, which is every issue in this three-row file.

One Number column at a time on shopify-shaped.csv
ColumnResult
SKUSource records 2, 3 and 4: use a plain decimal, otherwise choose Text. SKU-00127 contains letters and a hyphen.
BarcodeSource records 2, 3 and 4: Leading zeros would be lost. All three barcodes start with 0 and are 13 digits, so the 15-significant-digit guard is not the message.
Variant IDSource records 2, 3 and 4: More than 15 significant digits. Both ids are 18 digits and do not start with zero.
Inventory QtyAccepted. 3 numeric cells, including 0. Download is allowed.
PriceAccepted as Number and as two-decimal display. 3 numeric cells: 9.50, 9.50 and 11.00.

After Preview, with every column still on Text, preflight warns: leading zeros in column 3 (3 cells) and 16+ digit tokens in column 4 (3 cells). The how-to link on that row opens long digit IDs, because an 18-digit variant id is present and the file has no exponent text. The warning text still names the barcode zeros. Whole-row duplicate preflight stays clear: the two blue-mug rows differ by inventory quantity, so they are not exact copies.

Duplicate keys on shopify-shaped.csv

Open the same file in CSV clean with Comma, a header and duplicate removal on. Empty-record removal removes 0 rows. Quantity 0 is a stored quantity.

Named keys on the two blue-mug rows
Key columnKeep conflicts (default)Keep first
Handle, SKU, Barcode, Variant ID or Price1 conflicting group. Differing column is 5, Inventory Qty. 0 rows removed. 3 data records stay.Removes 1 row: the later quantity-0 blue-mug. Quantity 12 remains.
Inventory Qty0 groups. 12, 0 and 3 are different values. 3 data records stay.Same result. This key does not identify a product.

Variant ID is shared by both blue-mug rows in this file (401234567890123456). Choosing it does not protect the quantity-0 row under keep-first. Use keep-conflicts, read the review, and keep both rows unless you have a business rule that says one quantity replaces the other. See duplicate keys.

Very long variant ids: long IDs as text. SKU padding: SKU codes. Matching tools: CSV to Excel, CSV clean, CSV check & repair.

Sources:

Examples are original teaching data. Tool-specific behavior describes this version of TableMender; software interfaces can vary by version.

← All guides