Your payout CSV was already broken before validation ran

A developer detailed how spreadsheet software silently corrupts financial CSV data, causing payment failures such as invalid routing numbers and altered amounts. The developer's team built an importer that rejects entire files rather than rows, preserving raw string values and enforcing strict formats to catch data loss before validation.

A partner sent their monthly contractor file on the first, the way they had for two years. 1,847 rows. Our importer validated every one of them and reported no errors. We funded the batch and submitted it. 63 payments came back as R13, invalid routing number. The numbers were correct when their finance lead typed them, and correct in the system they were exported from. Every one of the 63 had the same thing wrong with it: a missing leading zero. Open a CSV in Excel or Sheets, save it, and the file you get back is not the file you opened. Type inference runs on every cell, and finance data is exactly what it gets wrong. 021000021 is a valid routing number. Nine digits, leading zero, checksum passes. A spreadsheet sees a number, drops the zero, and writes back 21000021 . Eight digits, still parses as an integer, still sitting in the column looking like data. A 17-digit account number becomes 1.23457E+16 and loses its last digits permanently. There is no repair for information that is gone. An amount typed as 1.234,56 in a German locale and reopened under a US one becomes either 1.23456 or 1234.56 depending on the path, and no checksum will tell you which. That is the one that frightens us. A wrong routing number fails loudly at the bank. A wrong amount succeeds. None of it is visible in the spreadsheet's own display. Our importer's first mistake was letting a parser be helpful. Read the file as bytes, strip the byte order mark if it is there, and keep every value a string. No integer coercion, no date parsing. In pandas, dtype=str and keep default na=False , or a payee who wrote NA as a country code arrives as NaN . The second mistake was validating the parsed value instead of the raw one. By the time int "21000021" has succeeded, the evidence is gone. Length is evidence. Leading characters are evidence. Routing numbers carry their own checksum, so a mangled one can be caught without asking a bank. php def aba valid rtn: str - bool: if len rtn = 9 or not rtn.isdigit : return False w = 3, 7, 1, 3, 7, 1, 3, 7, 1 return sum int d k for d, k in zip rtn, w % 10 == 0 The line that catches the stripped zero is not the arithmetic. It is the length test on the first line, and it is the one people leave out because it feels redundant next to a checksum. Amounts have no checksum to lean on. Require one unambiguous format and reject the rest. AMOUNT = re.compile r"^\d{1,12} \.\d{2} ?$" no separators, cents explicit Refusing 1.234,56 and 1,234.56 alike is not pedantry. A human reads both. A program cannot, without knowing which country produced the file, and you do not know that. You can see the zero is missing, padding produces a valid checksum, and the run is due. We stopped doing that. The spreadsheet operated on the whole file, not one column. If the routing numbers were mutated, the amounts met the same locale handling. You can repair the damage you have a checksum for. You cannot detect the damage you do not, and repairing the visible half produces a file that looks clean and is not. So the importer rejects the file, not the row. It returns the failing line numbers, the column, the value received, and what was wrong with it. A rejection with 63 line numbers gets fixed in an afternoon. A padded batch gets found at month end, if at all. We are less sure about this than about the rest. With thousands of small sellers uploading their own files, a hard reject may just mean nobody ever finishes an upload, and a per-row quarantine might serve them better. We have only run this on partner files, where a named person can produce a new export. The files that arrive intact never touch a spreadsheet: a direct pull, or an upload that validates on the spot and shows the sender their own errors while they still have the original in front of them. The person who can fix a wrong account number is the person who typed it, and they stop being reachable the moment the file leaves their hands. When you design how a file actually gets into the system https://gruv.ai/integrations , that upload moment is where the time goes. Where a spreadsheet is unavoidable, ask for text-formatted columns in the template; a column formatted as text before the paste survives a save. Better still, take the data over a connection: the tradeoff of file and connector intake https://gruv.ai/payouts/universal-connectors is setup work against a class of silent corruption that never happens. An earlier post on this blog has the other half of this. A designer in Lisbon lost forty-one days of cash flow to an invoice rejected for formatting nobody could see https://dev.to/gruvai/a-rejected-invoice-in-lisbon-what-i-learned-the-expensive-way-4pna . Same failure, opposite end of the wire. Disclosure: we build payout infrastructure at Gruv. The routing number above is a valid ABA number; every other figure is synthetic. Payout Engineering at Gruv