Normalize expense CSV rows with Make

Outcome and scope

Transform expense CSV rows into a consistent review table without overwriting the source export. Keep rejected rows with their original values and a reason. This is a preparation workflow for accounts review, not an automatic decision about ledger categories or tax treatment. The conversion rules should be explicit enough that another person can reproduce a result.

Use a versioned transformation profile for each source format. Record headers, date format, negative notation and currency origin so a later rerun can reproduce the same interpretation.

Prepare the fields and destination

Prepare these fields: Import file ID, original row number, transaction ID, raw date, normalized date, currency, raw amount and transformation status. Keep source values separate from a reviewer’s confirmed values, and do not reuse an existing column for a different meaning. Create the first record with practice identifiers in a destination whose read and edit access matches the intended staff roles. Inspect the saved result from that role before using live information.

Include quoted commas and multiline descriptions in the fixture. Validate the parsed column count, and reject unsupported amount formats rather than extracting digits and accidentally dropping a negative sign.

Build the workflow in order

Inspect encoding, delimiter and header placement before parsing. Map columns by their meaning, not only their name: transaction date and settlement date are different. Preserve the import file identifier and original row number throughout processing so an exception can always be traced to the untouched export.

Parse dates with a defined source format and validate amount separately from currency. Address thousands separators, decimal symbols and any parenthesized negatives that the source format uses. Route ambiguous rows to review. Never substitute today for an unknown date or zero for an unreadable amount, because those defaults conceal reconciliation errors.

Write valid rows and exceptions to distinct outputs, then reconcile their combined count with the input. If the source has no transaction identifier, document the limits of deduplication. Identical-looking rows may be separate legitimate purchases. Keep a reason for each exclusion instead of simply deleting rows that share a date and amount.

Work through a small example

Use four practice rows containing an ISO-like date, an ambiguous day/month date, a blank date and a negative refund. The first two must not be treated as equivalent without a declared source format. The blank belongs in review. Keep foreign-currency totals separate unless a separately governed exchange-rate process is part of the specification.

Handle changes during operation

A partial valid-row total is not a confirmed whole-file total. Keep manual corrections separate from source values and decide explicitly whether a later import may replace those corrections.

Estimate cost and usage

Rows and transformation steps often matter more than the number of CSV files. Estimate normal and largest files, monthly import frequency, exception writes and corrected reruns separately. Check limits at both Make and the destination. Include the time accounts staff spend on held rows rather than assuming every imported record becomes fully automated.

Investigate failures

If fields shift, inspect changed headers, quoted commas and multiline descriptions. A plain split on commas is not a CSV parser. After correcting a transformation rule, rerun it under a new import version and reconcile existing output rather than appending the corrected rows on top of the earlier results.

Review before release

Before release, test a normal record, one missing a required value, a repeated identifier and an edited record. Use import file id to trace the saved result back to the source. The acceptance condition is: Account for every input row as valid, held or explicitly excluded; preserve uncertainty. Record the expected result before testing, compare it with the actual saved state, and resolve unexplained differences before increasing the scope.

• Automate CSV imports
• Parse JSON in Make
• Dates and timezones in Make