SKU and UPC Cleanup in Excel Exports: Keep Leading Zeros, Kill Duplicate Keys

If your SKU column lost its leading zero or a UPC now reads 8.85911E+11, Excel treated a product key as a number instead of text. SKU, UPC, EAN, and GTIN columns must be stored as text so leading zeros survive and 12- to 14-digit codes never collapse into scientific notation. The reliable fixes are to never open the raw CSV in Excel, to pad codes back to their correct length (UPC-A is 12 digits, EAN-13 is 13), and to audit for duplicate keys after trimming whitespace and normalizing case before you upload.

This is a narrower problem than general leading-zero loss. Product keys are what marketplaces and inventory systems match on, so a single mangled or duplicated key does not just look wrong, it corrupts an inventory sync or throws a listing error at upload. This guide covers the marketplace-specific side: correct code lengths, duplicate-key detection, and the failures each corruption causes downstream.

Why does Excel change UPCs to scientific notation and drop leading zeros?

The CSV file itself is fine. The damage happens the moment Excel parses it. When Excel sees a cell that looks numeric, it stores it as a number, and numbers have no concept of a leading zero or a fixed width. Two things break at once.

The save step is the dangerous part. For a 12- to 15-digit code, Excel still holds the exact value in memory, so reformatting the column as Text or as a number with no decimals recovers the digits, as long as you do it before you save. The trap is saving back to CSV while the cell shows scientific notation, because Excel can serialize 8.85911E+11 into the file and reopening it leaves only those few significant figures. Codes longer than 15 digits are worse: Excel caps precision at 15 significant digits and truncates the rest to zero the moment they are entered. Either way, the safest move is to never open the raw export in Excel at all, or to import it with the identifier columns explicitly typed as Text.

What length should each product key be?

SKUs are internal and can be any format you choose, so consistency matters more than length. Barcodes are standardized by GS1 and have fixed widths, which is exactly what makes padding back a lost leading zero safe: you know the target length. All of these are members of the GTIN family and share the same modulo-10 check digit as the final digit, calculated right to left by multiplying alternating digits by 3 and 1.

Because the check digit lives in the last position, left-padding a code that lost its leading zero is safe: you are restoring the high-order digits, not touching the check digit. A code showing 12345678905 with 11 digits is almost certainly a UPC-A that lost one leading zero, so pad it back to 012345678905 at 12 digits. If a code is several digits short, do not blindly pad it, because that usually signals scientific-notation rounding already destroyed the real digits and you need the original source.

Before and after: scientific-notation UPCs and inconsistent SKUs

Here is a typical products export after a round trip through Excel. Notice the UPCs in exponential form, the SKU that lost its zero, and two rows that are the same product key with only whitespace and case differences.

sku,upc,title
ABC-001 ,8.85911E+11,Blue Widget
abc-001,884912000238,Blue Widget
7845,1.23457E+11,Red Gadget
DEF-42,00884912000245,Green Gizmo

The problems: row 1 and row 2 are the same SKU (ABC-001 with a trailing space versus lowercase abc-001), so they will be read as two different products. Both UPCs in scientific notation have already lost their exact digits. The SKU 7845 lost a leading zero. After cleaning against the correct source values, the file should look like this, with every key stored as text:

sku,upc,title
ABC-001,885911234567,Blue Widget
ABC-002,884912000238,Blue Widget
007845,012345678905,Red Gadget
DEF-042,884912000245,Green Gizmo

Every UPC is now a full 12-digit string, the duplicate SKU has been resolved into two distinct keys, the leading zeros are back, and DEF-42 was standardized to the same DEF-0NN width as the rest of the catalog. This only works if the UPCs were recovered from an uncorrupted source, which is the whole reason to catch the problem before scientific notation rounds the digits away.

How to detect duplicate SKUs and UPCs after normalization

The trap with duplicate keys is that they are usually not exact duplicates. A naive de-dupe misses them because the raw strings differ by things a human eye ignores: a trailing space, a different case, or a stray non-breaking space pasted from a supplier sheet. ABC-001 with a trailing space and abc-001 are the same product to you and to your marketplace, but not to a plain string comparison.

The fix is to normalize first, then compare. Trim leading and trailing whitespace, collapse case to a single convention (uppercase is common for SKUs), and only then look for collisions. Do the normalization on a working copy so you can see which raw rows map to the same normalized key, rather than silently overwriting.

Why duplicate keys break inventory syncs and marketplace listings

SKUs and UPCs play different roles, so duplicates fail in different ways, and knowing which one you are looking at tells you how urgent it is.

Duplicate SKUs corrupt inventory sync

The SKU is the primary key your inventory and listing systems match on. If two rows share a SKU, an inventory sync cannot tell which quantity is authoritative. Depending on the system, the second row overwrites the first, the quantities get summed into a wrong total, or the update is rejected outright. The visible symptom is stock counts that drift out of sync for one product no matter how often you re-upload, because every sync re-applies the ambiguous key.

Duplicate UPCs trigger listing errors

Marketplaces treat a UPC or GTIN as a globally unique product identifier, so the same UPC on two different SKUs usually gets rejected at upload with a duplicate-identifier or 'this product already exists' style error, or the second listing is suppressed. This is common when a variant export repeats the parent UPC across every child row, or when a code was rounded by scientific notation so two genuinely different products now collide on the same mangled number.

A repeatable workflow for clean product keys

The goal is to make the identifier columns text once, then run the same audit every time so a corrupted key never reaches the upload. The order matters: preserve first, normalize second, de-dupe last.

This is exactly the class of cleanup PipeSheets is built for. You upload the export, and it loads CSV and XLSX without the leading-zero and scientific-notation mangling Excel introduces, so the keys stay as text from the start. A saved pipeline can trim whitespace on the key columns, apply a case transform so SKUs collapse to one convention, and use find and replace to normalize inconsistent separators. The before-and-after preview shows detected column types, so you can confirm the UPC column is text and not a number before you download the cleaned file.

PipeSheets will not calculate GTIN check digits or map your columns to a specific marketplace template for you, and it does not de-duplicate on its own, so treat it as the tool that guarantees your keys survive intact and are consistently formatted. The duplicate audit is something you drive using the counts, and the padding decisions still need a human who knows which codes lost a leading zero versus which were rounded. Get the preservation and normalization right first, and the duplicate check becomes straightforward instead of a guessing game against corrupted data.