DEV Community

Cover image for Data-cleaning edge cases that break integrations: Excel serial dates, ERP negatives and match keys
Adan Potyondy
Adan Potyondy

Posted on Originally published at neblex.com AI-assisted

Data-cleaning edge cases that break integrations: Excel serial dates, ERP negatives and match keys

Originally published on the Neblex Blog by Maria Soria.

Most integration bugs aren't connection problems. The data arrives fine, and then a value in a format nobody planned for loads as a blank or, worse, as a wrong value that looks right. Spreadsheets store dates as numbers such as 46115, ERP exports write negatives as 1,234- or (500.00), and company names need a match key before you can deduplicate them.

Below are the edge cases we keep running into, how to handle each one, and a four-row before/after you can check your own pipeline against. The functions are open source (MIT) with their test cases.

Where do standard transform steps stop?

Most integration and ETL tools handle a value with one known format or one operation. These cases fall outside that.

Built-in step What it does Where it stops
Date/time Parses or formats a date with one format Mixed formats, Excel serial numbers
Text Upper, lower, trim, replace Rules with more than one step, such as "is this a real email?"
Deduplicate Removes duplicates by a key, exact or fuzzy Building a good key from messy names and emails

Where a rule depends on the source, such as day-month order or the decimal separator, make it a setting instead of a guess. The same function then serves a US CRM and a European ERP, and returns empty instead of guessing when a value doesn't fit.

Dates: mixed formats and Excel serial numbers

to_iso_date turns 04/03/2026, 3 Apr 2026, April 3rd, 2026, 20260403 and 2026-04-03T10:20:00Z into YYYY-MM-DD, and returns empty for anything that isn't a real date. You pass "mdy" or "dmy" to say how to read all-number dates.

excel_serial_to_iso converts Excel's date numbers. Excel counts a February 29, 1900 that never existed, kept for compatibility with Lotus 1-2-3 (Microsoft Learn), and the function allows for it: 46115 becomes 2026-04-03. days_between handles aging fields once the dates are clean.

Numbers and money

parse_number reads amounts written the US or European way: $1,234.56, EUR 1.234,56 and CHF 1'234.50 all give 1234.56 or 1234.5. It reads (500.00) and 1,234-, the accounting and ERP-export ways of writing negatives, as -500 and -1234, and returns empty for 12a34.

You pass the decimal separator, or "auto", which reads a single separator followed by three digits as thousands. currency_code finds EUR, £ or R$ in the same text, and to_cents returns whole cents.

Contacts and match keys

  • normalize_email and is_valid_email clean and check an address, including the 64-character limit before the @ from RFC 5321. is_role_email flags info@ and sales@.
  • phone_e164 formats numbers as E.164, a plus sign, country code, and at most 15 digits (Twilio), and returns empty for numbers that can't be complete.
  • title_case_name turns o'NEIL into O'Neil and MCDONALD into McDonald.
  • email_match_key and company_match_key build keys for deduplication: The Acme Company, Inc. and ACME Co both become acme.

Worked example: four rows, one script

We ran four test rows through one script that imports the three modules:

item.email = normalize_email(item.email);
item.email_ok = is_valid_email(item.email) && !is_role_email(item.email);
item.phone = phone_e164(item.phone, "1");
item.contact = title_case_name(item.contact);
item.company_key = company_match_key(item.company);
item.invoice_date = if type_of(item.invoice_date) == "string" { to_iso_date(item.invoice_date, "mdy") } else { excel_serial_to_iso(item.invoice_date) };
item.currency = currency_code(item.amount, "USD");
item.amount = parse_number(item.amount, "auto");
item.missing = missing_fields(item, ["email", "phone", "invoice_date", "amount"]);
Enter fullscreen mode Exit fullscreen mode
Input Output
DANA o'reilly, Dana.OReilly@Northwind.com, (415) 555-0123, 04/03/2026, $1,234.56 Dana O'Reilly, dana.oreilly@northwind.com, +14155550123, 2026-04-03, 1234.56 USD
jürgen MÜLLER, Müller GmbH & Co. KG, +49 30 901820, 46115, EUR 1.234,56 Jürgen Müller, key "müller", +4930901820, 2026-04-03, 1234.56 EUR
sam okafor, info@contoso.com, 555-0199, 3 Apr 2026, (500.00) Sam Okafor, role address flagged, phone empty, 2026-04-03, -500
lee park, lee.park@fabrikam, no phone, 2026-02-30, 1,234- Lee Park, invalid email flagged, date empty, -1234

We put rows from four kinds of sources in one file to show them together. In a real pipeline, each source keeps its own format, and you set its convention once.

Three dates came out as 2026-04-03. The impossible one is empty and listed in missing, so a filter can route it to review instead of loading it.

What can go wrong

  • All-number dates need "mdy" or "dmy"; without it, they return empty. Two-digit years 00 to 69 become 2000 to 2069.
  • Month names are English only, and time zones aren't converted.
  • is_valid_email checks the format, not whether the mailbox exists. phone_e164 doesn't know every country's numbering plan.
  • to_cents assumes two decimal places, so it doesn't suit currencies such as JPY.
  • Empty results keep the load going. Route them to review, or they'll arrive as blanks.

How we run them

The functions are written in Rhai, a small sandboxed scripting language for embedding in applications, with no file, network or system access. They live in the neblex-rhai-modules repository under the MIT license, each with its test cases. We run them as shared modules in Neblex flows, so every flow that imports one gets the same tested rule and a fix reaches all of them. The rules themselves port to any language: copy a function and change it.

The takeaway

Built-in steps handle values with one known format. Excel serial dates, ERP-style negatives and match keys for deduplication need rules you write once and reuse everywhere. Set each source's convention, and route anything a function can't read to review instead of loading a blank.

The full post, with the setup steps and FAQ, is on the Neblex Blog.

Top comments (0)