Sooner or later a spreadsheet of real customer data has to leave the building: to a vendor, to a contractor, into a demo environment, or into a model-training pipeline. Deleting the personal columns answers the compliance question and destroys the file's usefulness. Replacing them with random values keeps the shape and breaks everything downstream.

Here is what actually goes wrong, and what a usable substitute looks like.

Three properties a masked file needs

Format-preserving. An 11-digit phone stays 11 digits. An 18-character ID stays 18 characters with a plausible birth date inside it. If the output format differs, every downstream parser breaks — and people re-run the job on the real data to make the error go away, which defeats the point.

Checksum-valid. Bank cards must still pass Luhn. Chinese ID numbers must still pass the ISO 7064 MOD 11-2 check digit. This is not cosmetic: form validation, importers and test suites reject numbers that fail the checksum, so a masked file that fails it cannot be used for testing at all.

Consistent and reversible. The same real value must map to the same fake value everywhere, in every file, on every run. Otherwise a JOIN on customer id silently returns nothing and the "anonymized" dataset is useless for the analysis it was created for.

Why random digits fail review 

import random, string fake = "".join(random.choice(string.digits) for _ in range(18)) # -> 483920174638291047

Three separate defects in that one line:

  • it fails the checksum, so any form or importer rejects it;
  • its embedded birth date (month 92) is impossible, so anyone who glances at it knows it is fake — and can no longer tell whether the rest of the file is real;
  • it is random, so the same person gets a different id in the next file, and nothing joins.

The fix: a keyed deterministic map

Derive the fake from HMAC(secret, real_value), then repair the check digit. Same input plus same secret gives the same output forever, and without the secret you cannot walk it back to a person.

import hmac, hashlib def det_digits(key: bytes, value: str, length: int) -> str: """Deterministic digit string of the requested length.""" out = b"" counter = 0 while len(out) < length: out += hmac.new(key, f"{value}|{counter}".encode(), hashlib.sha256).digest() counter += 1 return "".join(str(b % 10) for b in out[:length])

Counter mode matters: one HMAC gives you 32 bytes, and an 18-digit ID plus a date worth of entropy needs more than one block. Incrementing a counter keeps it deterministic while giving you as many bytes as you want.

Now the two check digits.

ID_WEIGHTS = [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2] ID_CHECK = "10X98765432" def id_check_digit(first17: str) -> str: total = sum(int(d) * w for d, w in zip(first17, ID_WEIGHTS)) return ID_CHECK[total % 11] def luhn_check_digit(payload: str) -> str: total = 0 for i, ch in enumerate(reversed(payload)): d = int(ch) if i % 2 == 0: # this position gets doubled once the check digit is appended  d *= 2 if d > 9: d -= 9 total += d return str((10 - total % 10) % 10)

The Luhn parity is the part people get wrong. The check digit itself sits at position 0 from the right and is never doubled, so the rightmost digit of the payload is at an odd position and must be doubled. Iterating the reversed payload from index 0 and doubling on even indices does exactly that.

Assembling a Chinese ID: build 17 digits, repair the embedded date so it stays plausible, then append the check digit.

def fake_id(key: bytes, value: str) -> str: d = det_digits(key, value, 17) year = 1950 + int(d[6:10]) % 60 # 1950..2009  month = 1 + int(d[10:12]) % 12 # 01..12  day = 1 + int(d[12:14]) % 28 # 01..28, safe in every month  body = d[:6] + f"{year:04d}{month:02d}{day:02d}" + d[14:17] return body + id_check_digit(body)

Clamping the day to 28 avoids the leap-year and 30-vs-31 branches entirely. That is a deliberate trade: a smaller output space, but zero chance of emitting an impossible date.

The false-positive problem is the real hard part

The naive approach is a regex for "18 digits = Chinese ID". Real spreadsheets also contain 18-digit order numbers, millisecond timestamps, and concatenated fields. Mask all of them and a colleague opens the file, sees the order numbers destroyed, and stops trusting the tool — which is the worst outcome, because next time they hand over the real data instead.

So validate before masking:

  • Chinese ID — checksum passes and the embedded birth date is a real date and the province code is one that exists
  • bank card — Luhn passes and length is 13–19 and the BIN prefix is plausible
  • phone — the prefix is an allocated mobile range, not merely 11 digits

In a typical mixed spreadsheet this takes the finding count from roughly 400 down to the 88 that are actually personal data. That ratio is the difference between a tool people keep using and one they disable after the first false alarm.

What masking does not cover

Masked columns are not automatically safe. Three things leak identity on their own and are usually forgotten:

  1. Free-text notes. "Called him back on his mobile, he said the account is under his wife's name" — no regex finds that.
  2. Rare combinations. Postcode plus job title plus birth year identifies most individuals even after names are gone. For a small file, dropping a column beats masking it.
  3. File metadata. Author name, company and the original path survive inside the .xlsx container after every cell has been rewritten.

Wrapping up

The recipe is: validate strictly, derive fakes with HMAC in counter mode, repair format and check digit, and emit a mapping file so the operation is reversible by whoever holds the key.

I put this together as a small CLI that handles csv, xlsx, txt, log, json, markdown and docx, and reports what it validated versus skipped so false positives are visible rather than silent. If you want to see the approach on your own files, there's a longer write-up here: Removing personal data from an Excel file so it is still usable.

Happy to hear where this breaks — especially the validation heuristics, which are always more local than they look.