How to create anonymized test data from excel production exports for staging
- Step 1Pull the production export — Generate the
.xlsxor.csvfrom production. Put the data your tests need onto the first worksheet — only that sheet is scanned. - Step 2Open the redactor in-browser — The Excel PII Redactor launches the scrubber. Drag the file in; SheetJS parses it locally, so production data never leaves the machine.
- Step 3Run the detectors — Email, IBAN, card, US SSN, UK NI, and phone patterns run in order, with Luhn/mod-97 checksums gating the card and IBAN matches.
- Step 4Verify nothing real survived — Check the per-category counts and skim the output for any unredacted addresses or numbers before promoting the file to a fixture.
- Step 5Add realistic fakes if needed — Placeholders are labels, not valid data. If tests need plausible emails/phones, post-process the scrubbed file with a Faker-style generator.
- Step 6Load into staging or commit as a fixture — Import the
<name>-scrubbed.<ext>file into staging, or check it into the repo as a PII-free test fixture.
What the scanner actually detects (and how)
The six built-in detectors, in the exact order they run. Order matters: specific number patterns (IBAN, card, SSN, NI) run before the generic phone matcher, because each is a subset of 'a run of digits with optional spaces'. Tags are fixed literal strings — there is no mask-format option in this tool.
| Detector | What matches | Validation | Replacement tag |
|---|---|---|---|
local@domain.tld — letters/digits/._%+- before @, a dotted domain, 2+ letter TLD | Pattern only (no MX/syntax-strict RFC check) | [REDACTED_EMAIL] | |
| IBAN | 2 letters + 2 check digits + 4–7 groups of alphanumerics, spaces allowed | ISO 13616 mod-97-10 checksum — fails the check, left untouched | [REDACTED_IBAN] |
| Credit card | 13–19 digits with optional spaces or dashes between them | Luhn checksum — fails Luhn, left untouched | [REDACTED_CARD] |
| US SSN | NNN-NN-NNNN with SSA invalid-block exclusions (no 000/666/9xx area, no 00 group, no 0000 serial) | Structural rules baked into the pattern | [REDACTED_SSN] |
| UK NI number | Two prefix letters + 6 digits + a final A–D letter, e.g. QQ 12 34 56 C | Excludes the disallowed prefixes (BG, GB, NK, KN, TN, NT, ZZ) | [REDACTED_NI] |
| Phone | +CC optional, then 2–4 digit groups separated by space/dot/dash — broad international shape | Pattern only; runs last so card/SSN/IBAN claim their digits first | [REDACTED_PHONE] |
Input, output, and tier limits
The Excel PII Redactor entry routes to the Email & Phone Scrubber, which reads the spreadsheet, flattens the first worksheet to text, scrubs it, and hands back a text file. Tier numbers shown are the Excel family caps from the pricing model.
| Property | Behaviour |
|---|---|
| Accepted inputs | .xlsx, .xls, .ods, .csv, plus pasted/dropped JSON / Markdown / TXT |
| What gets read | The first worksheet only — it is converted to a JSON array of row objects, then scanned |
| Output | A text file named <yourfile>-scrubbed.<ext> (the scrubbed JSON/text), not a rebuilt .xlsx workbook |
| Findings | Per-category counts (email, iban, credit_card, ssn_us, ni_uk, phone) plus a total itemsRedacted — no per-cell address log |
| Configurable options | None. There is no options panel, no custom-regex field, no mask-format picker — the six detectors and their tags are fixed |
| Free tier (Excel family) | 5 MB file, 10,000 rows, 1 file at a time |
| Pro / Pro-media / Developer | 50 MB · 100,000 rows · 5 files / 200 MB · 500,000 rows · 20 files / 500 MB · unlimited rows |
| Where it runs | 100% in your browser — the spreadsheet is never uploaded to a server |
Cookbook
Production-to-staging rows showing what the scrub guarantees (no real PII) and what it does not (realistic-looking values).
Production row to safe fixture
Every identifier becomes a label. The result is safe to commit, but the email is no longer a usable test address.
Input (production export, sheet 1):
[
{ "id": 9001, "email": "real.user@acme.io",
"phone": "+1 312-555-0173", "ssn": "412-55-1234" }
]
Output (-scrubbed, commit-safe):
[
{ "id": 9001, "email": "[REDACTED_EMAIL]",
"phone": "[REDACTED_PHONE]", "ssn": "[REDACTED_SSN]" }
]
Counts: { email: 1, phone: 1, ssn_us: 1 }Why placeholders break some tests
If a test asserts email-format validity or uses the value as a unique key, the label will fail. Post-process with fakes.
Scrubbed fixture: email = "[REDACTED_EMAIL]" // not a valid email Test expectation: assert isEmail(row.email) // FAILS on the label Fix: pipe scrubbed rows through a faker step: row.email = faker.internet.email() // valid, fake, safe
Synthetic IDs survive (good for fixtures)
A test order number that fails Luhn is not treated as a card, so your seeded reference values stay stable across runs.
Input:
order_ref = 1000000000000001 // not Luhn-valid
Output:
order_ref = 1000000000000001 // preserved
Counts: { credit_card: 0 } (nothing to redact here)Second sheet of seed data is ignored
Only sheet 1 is scrubbed; a 'reference' tab with real emails would ship unredacted. Consolidate first.
Tabs: [ Customers (1) | Reference (2, has emails) ] Scanned: Customers -> scrubbed NOT scanned: Reference -> emails LEFT IN Fix: merge needed reference rows into sheet 1 before running.
Counts as a CI gate
Asserting itemsRedacted > 0 (or that key categories were hit) catches the case where someone exported the wrong, already-empty file.
Findings: { counts: { email: 500, phone: 480 }, itemsRedacted: 980 }
CI check:
if (findings.itemsRedacted === 0) fail('export had no PII to scrub?!')
# guards against committing an unscrubbed or wrong fileEdge cases and what actually happens
Card or SSN-shaped number that fails its checksum / structure
PreservedCredit-card detection only fires when the digit run passes the Luhn checksum, and IBAN only when the mod-97-10 check passes. A 16-digit order number or a typo'd card that fails Luhn is left exactly as-is. An SSN-shaped value in a banned block (e.g. 000-12-3456 or 666-...) is likewise skipped by design — those are never valid SSNs.
Only the first worksheet is scanned
By designThe reader flattens wb.SheetNames[0] to text and scrubs that. PII sitting on a second tab (a hidden 'Contacts' sheet, an 'Audit' tab) is not touched. Split multi-tab workbooks first, or scrub each sheet's data separately, and remember to clear residual sheets with the hidden-sheet destroyer.
Output comes back as text, not a workbook
ExpectedFeeding an .xlsx in does not return an .xlsx out. The first sheet becomes a JSON array of row objects, the scrub runs, and you download <name>-scrubbed.xlsx whose contents are scrubbed text/JSON. Treat it as a redacted data dump, not a styled, multi-sheet workbook — formulas, formatting, and extra tabs are gone.
Phone numbers in an unusual local format
May missThe phone matcher targets a broad international shape (+CC then 2–4 digit groups split by space/dot/dash). Tightly packed or unusually punctuated locals (e.g. parenthesised area codes glued to text, vanity numbers like 1-800-FLOWERS) can slip through. Check the per-category count after a run and spot-check the output.
A full name or street address in a cell
Not detectedThere is no name/address NLP here — the six detectors are all pattern-and-checksum based. John Smith, 14 Mill Lane stays in the clear. For free-text identifiers you must redact those columns by other means (drop the column, or generalise it) before sharing.
Email-like token inside a URL or file path
RedactedThe email pattern matches anything shaped like local@domain.tld regardless of context, so mailto:sam@acme.io and //user@host.example/path both get the address portion replaced with [REDACTED_EMAIL]. This is usually what you want, but it can rewrite a host string you meant to keep.
Numbers run last — phone can swallow a near-miss
By designBecause the generic phone pattern runs after IBAN/card/SSN/NI, any long digit group those earlier detectors rejected (failed Luhn, wrong block) is still eligible to match as a phone number if it fits the loose phone shape. Review counts if a value you expected to keep was tagged [REDACTED_PHONE].
File over the tier row/byte cap
RejectedA Free-tier scan stops at 5 MB / 10,000 rows / 1 file. Larger exports return a limit error before any scrubbing runs. Split the export, trim to the needed columns, or move to a higher tier (Pro 50 MB / 100k rows, Developer 500 MB / unlimited).
Placeholders are not valid test values
By designRedaction replaces a value with a label like [REDACTED_EMAIL], not a realistic fake. Tests that validate format, parse the value, or use it as a unique key will break. Generate substitutes with a Faker-style step after scrubbing if your suite needs valid-looking data.
Frequently asked questions
Does this produce realistic fake data for tests?
No. It replaces matches with fixed labels such as [REDACTED_EMAIL], which proves there is no real PII but is not a valid email or phone. For plausible synthetic values, post-process the scrubbed file with a Faker-style generator.
Can I commit the scrubbed file as a repo fixture?
Yes — once you've confirmed the counts and skimmed the output, the labelled file contains no real personal data and is safe to check in. Keep the original out of the repo.
What exactly gets replaced, and with what?
Six categories: emails, IBANs, credit-card numbers, US SSNs, UK National Insurance numbers, and phone numbers. Each is swapped for a fixed label — [REDACTED_EMAIL], [REDACTED_IBAN], [REDACTED_CARD], [REDACTED_SSN], [REDACTED_NI], [REDACTED_PHONE]. The tags are not configurable.
Can I add my own regex or change the mask string?
No. This tool has no options panel — the detectors and their replacement tags are fixed in code. If you need custom patterns or realistic fake values, generate substitutes downstream (e.g. with a Faker-based scrambler) rather than expecting per-pattern masks here.
Does it produce a redacted .xlsx workbook?
No. It reads the first worksheet, flattens it to a JSON array of rows, scrubs that text, and gives you a <name>-scrubbed.<ext> text download. It is a redacted data export, not a rebuilt styled workbook with all sheets and formulas intact.
Are credit cards and IBANs really validated, not just pattern-matched?
Yes. Card numbers must pass the Luhn checksum and IBANs must pass the ISO 13616 mod-97-10 check before they are redacted. Values that fail are left untouched, which keeps order numbers and SKUs from being mangled — but also means a malformed card slips through.
Does my spreadsheet get uploaded anywhere?
No. Detection and replacement happen entirely in your browser. The file is parsed locally with SheetJS and scrubbed in memory — nothing is sent to a server, which is the whole point for sensitive exports.
Why did a number I wanted to keep get redacted as a phone?
The phone pattern is deliberately broad and runs last, so any long digit group that earlier detectors rejected can still match it. Check the per-category counts; if phone is higher than expected, your data has digit runs that fit the loose phone shape.
Why did a real card / SSN slip through?
Cards must pass Luhn and SSNs must fall outside the SSA-invalid blocks to be redacted. A card with a typo'd digit fails Luhn; an SSN-shaped value in a banned area/group/serial is treated as not-an-SSN. The validation that avoids false positives also means malformed values are kept.
What are the size and row limits?
Excel-family caps apply: Free 5 MB / 10,000 rows / 1 file; Pro 50 MB / 100,000 rows / 5 files; Pro-media 200 MB / 500,000 rows / 20 files; Developer 500 MB / unlimited rows. Oversized files are rejected before scrubbing.
What other privacy tools should I run alongside this?
Strip document metadata with the app-metadata wiper, remove cell comments via the comment purger, and check for data-leaking links with the external-link auditor. Together they cover content, metadata, and references.
Is this the same engine as the Email & Phone Scrubber?
Yes. The Excel PII Redactor entry points at the Email & Phone Scrubber; it is the same detection pipeline, just fed by a spreadsheet that is flattened to text first.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.