Building a Resilient Number & String Sanitizer for Financial CSV Ingestion

In fintech and accounting software, numeric sanitization is deceptively tricky. You cannot simply apply Number(input) or parseFloat() across raw user values and hope for the best.

In financial ingestion pipelines (such as generating files for Xero or QuickBooks), slight formatting variations—like accounting parentheses, localized decimal points, currency symbols, and non-breaking spaces—can silently corrupt numbers or crash the importer entirely.

In this post, let's explore how we architected a robust financial sanitization pipeline for Sync2Zero to handle real-world accounting inputs without losing precision or mutating data.

The Common Failure Modes of Raw Financial Inputs

When processing invoices, receipts, or user spreadsheets, the incoming payload usually contains human-facing conventions that break strict accounting schemas:

• Accounting Negatives: (150.00) should evaluate to -150.00, but standard parsers often strip parentheses or return positive floats.
• Currency Affixes & Whitespace: Strings like £ 1,450.50 or non-breaking spaces (\u00A0) cause parseFloat to choke or drop fractional values.
• Comma Decimals vs. Thousand Separators: In European locales, 1.250,50 means 1250.50. Blindly stripping commas turns a €12.50 expense into €1250.00.

Building the Financial Number Sanitizer

Here is an architectural pattern in TypeScript designed to defensively sanitize monetary inputs:

export function sanitizeFinancialAmount(rawInput: string | number): number {
    if (typeof rawInput === 'number') {
        if (isNaN(rawInput)) throw new Error('Invalid numeric input');
        return rawInput;
    }
    let cleaned = rawInput.trim().replace(/\u00A0/g, ' '); // Strip non-breaking spaces
    // Check for accounting parentheses: (123.45) => -123.45
    const isNegative = /^\(.*\)$/.test(cleaned) || cleaned.includes('-');
    // Remove currency symbols and non-numeric artifacts (except period, comma, and minus)
    cleaned = cleaned.replace(/[^0-9.,]/g, '');
    // Detect and resolve European comma decimal separator (e.g., 1.250,50 vs 1,250.50)
    const lastCommaIndex = cleaned.lastIndexOf(',');
    const lastDotIndex = cleaned.lastIndexOf('.');
    if (lastCommaIndex > lastDotIndex) {
        // Comma is the decimal separator: replace periods (thousands) and swap comma to dot
        cleaned = cleaned.replace(/\./g, '').replace(',', '.');
    } else {
        // Dot is decimal: strip thousand-separator commas
        cleaned = cleaned.replace(/,/g, '');
    }
    const parsed = parseFloat(cleaned);
    if (isNaN(parsed)) {
        throw new Error(`Failed to parse financial value: "${rawInput}"`);
    }
    const result = isNegative ? -Math.abs(parsed) : Math.abs(parsed);
    return Math.round((result + Number.EPSILON) * 100) / 100; // Round to 2 decimal places safely
}

Key Considerations for Production Pipelines

1. Explicit Currency Stripping: Always strip currency glyphs ($, €, £, ¥) before downstream schema validation. Accounting platforms expect pure numeric strings (1250.50).
2. Deterministic Rounding: JavaScript floating-point arithmetic can introduce fractional artifacts (e.g., 0.1 + 0.2 = 0.30000000000000004). Ensure you round to two decimal places using explicit epsilon compensation or fixed-point decimal libraries.
3. Hard Validation Over Fallbacks: As raised in previous technical discussions, never fall back silently to 0.00 if parsing fails. Throw an explicit validation exception so the user can verify the corrupted cell in the web preview grid before final export.

Wrapping Up

Building ingestion pipelines that handle real-world financial documents requires thinking defensively about human conventions and internationalization.

If you are dealing with invoice extraction or CSV mapping challenges, take a look at Sync2Zero. How does your backend handle currency and decimal normalization across different locales? Let's discuss in the comments below!