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_emailandis_valid_emailclean and check an address, including the 64-character limit before the @ from RFC 5321.is_role_emailflags info@ and sales@. -
phone_e164formats 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_nameturnso'NEILinto O'Neil andMCDONALDinto McDonald. -
email_match_keyandcompany_match_keybuild keys for deduplication:The Acme Company, Inc.andACME Coboth becomeacme.
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"]);
| 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_emailchecks the format, not whether the mailbox exists.phone_e164doesn't know every country's numbering plan. -
to_centsassumes 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)