The Ragged CSV
A vendor sends a pipe-delimited text file of ~500,000 invoice lines. Most rows have 9 fields. Roughly 3% have 10 fields because a free-text 'notes' field contains an unescaped pipe. Roughly 1% have 8 fields because trailing empty fields were trimmed. Field 9 is a numeric amount. A Text to Columns tool splitting on '|' currently produces misaligned amounts and the downstream total is wrong by about 4%. The team cannot change the vendor's export.
1. Explain why Text to Columns misaligns the amount and propose a parsing approach that recovers the correct amount for the majority of rows.
Read the worked answer
Text to Columns splits on every delimiter, so a row with an embedded pipe yields 10 columns and the amount lands in the wrong column; a trimmed row yields 8 and leaves the amount column null. Because the split is positional, one extra delimiter shifts every field to its right.
Recovery approach: parse from the right rather than the left. Since the amount is field 9 of 9, use a RegEx tool in Parse mode anchored to the end of the string, e.g. capture the final pipe-delimited token as the amount and the preceding tokens for the other fields. Alternatively, split to columns with Text to Columns but set 'Split to rows' off and then use a Formula with a right-anchored regex to extract the trailing amount. Rows where the regex fails should be routed to a separate error output rather than silently nulled.
2. How should the 3% embedded-pipe rows and 1% trimmed rows be handled so the workflow is auditable?
Read the worked answer
Do not discard them silently. Add a Record ID before parsing, then split the parsed output with a Filter on whether the extracted amount is non-null and numeric. Valid rows continue; invalid rows go to an error branch that writes to a .yxdb with the Record ID and raw line for review.
For the trimmed rows, distinguish 'amount missing because fields were trimmed' from 'amount genuinely blank' by checking the raw line's trailing delimiter count. Report counts per category (valid, embedded-pipe, trimmed) in a summary output so the 4% discrepancy is explained rather than hidden. The auditable error branch is what makes the fix defensible.