Stop Storing Tracking Numbers as Numbers: a float64 Bug Hiding in Your CSV Pipeline

If any part of your stack reads a shipping CSV, this one is probably already in your data and nobody has noticed. A tracking number is a label, but almost every tool in the chain is happy to treat a long run of digits as a quantity - and quantities get rounded.

The failure, in two shapes

9214490424550000386431   ->  9.2144904245500004E+21
9400150206217912         ->  9400150206217910

The first is loud. The second is the one that costs you a week: same length, no exponent, no warning - the final digit is now a zero. Every downstream lookup returns "not found" for parcels that are completely fine, and the first instinct is to blame the carrier's API.

Why

IEEE-754 double precision carries about 15-17 significant decimal digits. Look at what common formats need:

Format	Length	All digits?	Survives a float?
USPS IMpb	22	yes	no
FedEx Ground	15	yes	borderline
DPD parcel number	14	yes	yes, but still reformatted
UPS 1Z + 16	18 chars	no	safe
S10 postal (AA999999999CC)	13 chars	no	safe

The pattern that matters: the formats that survive are the ones containing letters, because a letter forces the value to stay a string. The all-digit formats are the exposed ones - and that includes the single most common barcode in the world, the 22-digit USPS IMpb.

This is also why the bug feels intermittent. A column of 1Z... numbers round-trips perfectly, someone concludes the pipeline is fine, and then the first USPS batch of the month is silently mangled.

In code

Pandas will do this to you by default:

# wrong - pandas infers int64/float64 and your 22-digit numbers are gone
df = pd.read_csv("shipments.csv")

# right - say it explicitly
df = pd.read_csv("shipments.csv", dtype={"tracking_number": "string"})

# or, when you do not want to name every column
df = pd.read_csv("shipments.csv", dtype=str)

The same rule in the other languages:

• JS/TS: never Number(tn) or parseInt(tn). A 22-digit value is already past Number.MAX_SAFE_INTEGER (9007199254740991, 16 digits), so the damage happens before you can validate it. If you need a numeric type, use BigInt - but you almost never do, because you never do arithmetic on a tracking number.
• SQL: VARCHAR, not BIGINT. Leading zeros are real and significant in several postal formats, and a numeric column eats them without comment.
• JSON: quote it. An unquoted 22-digit literal is a double in most parsers.
• Excel/Sheets exports: format the column as Text before the first paste. Formatting a column that already holds broken values does not recover the digits - that information is gone, not hidden.

Validating a column you inherited

Cheapest checks first:

s = df["tracking_number"].astype("string")

s.str.contains("E+", regex=False).sum()   # loud corruption
s.str.len().value_counts()                # a USPS column should be all 22s
s.str[-1].value_counts(normalize=True)    # an implausible pile of "0" = rounding

That last one is the useful one. If a column has the right lengths but an unreasonable share of values ending in 0, you are looking at rounding, not coincidence.

The validation rule that actually helps

Do not validate tracking numbers with a numeric range or a length-only check. Check the shape: a letter-anchored prefix plus a fixed length is both cheap and specific. ^1Z[0-9A-Z]{16}$ identifies UPS exactly. ^[A-Z]{2}\d{9}[A-Z]{2}$ is the S10 international postal form, and the last two letters are the issuing country - not the destination, which is a different and equally common mix-up.

And if the number survives shape validation but returns nothing, check whether the label was merely created before you conclude anything is lost - "we have the order details but not the parcel" is a legitimate state, not an error.

---

Written by the team behind 24hTrack, a free package tracker for 3,200+ carriers - paste any tracking number and the carrier is detected automatically, no sign-up. Shape and format notes come from the carrier formats we maintain at package-tracking-guide (CC BY 4.0).