Reconciling a CSV export against a Google Sheet™
A CSV from another system — a bank, an accounting package, a supplier portal — carries no number formats and no locale. Every value in it is text until something decides otherwise, and the moment that decision goes wrong is usually right at import into Google Sheets™, before the reconciliation has even started.
What goes wrong on the way in
Google Sheets™' File › Import dialog has a Convert text to numbers, dates, and formulas setting, and both positions cost you something:
-
Left on (the default), a reference stored as
0012becomes the number12. The leading zero is gone, silently, and nothing in the sheet marks that it happened. A reference now reads differently from the source file it came from. -
Left on, a date written
06/03/2026is read in whatever locale your Sheets settings use. If the file was exported by a system that writes day/month and your spreadsheet's locale reads month/day,06/03becomes the 6th of March in one reading and the 3rd of June in the other, and the import does not ask you which was meant. -
Turned off, every column lands as text, including the amounts, so a
straightforward
=A2-B2between an imported amount and a sheet formula fails until something coerces it back to a number.
If you do import it
Decide column by column rather than trusting the single all-or-nothing checkbox. A reference column should stay text: format it as Plain text before or immediately after import so Sheets stops trying to be helpful with it. An amount column should end up numeric. A date column needs checking against the source rather than assumed: pick a row you can verify and confirm the date landed on the day you expect, not just a plausible one.
Loading the CSV without importing it
The alternative is not to bring the CSV into a sheet at all. Two-File Reconciliation's sidebar has a Load a CSV option for a second file that is not already a tab in the spreadsheet: it is read directly in your browser, never uploaded, and never passes through Sheets' own import settings. A field that looks like a leading-zero reference is kept as text; only what still reads as a genuine number is turned into one. The add-on asks which locale that file's dates were written in — day/month or month/day — once, for that source, rather than guessing or inheriting the spreadsheet's own locale.
The shortcut for the reconciliation itself
Whichever way the CSV gets in, matching it against the sheet is still a two-table reconciliation on a key: clean the references the same way on both sides, look each row up on the other, and check what is left over. Two-File Reconciliation does the whole thing: pick the sheet tab and load the CSV, it proposes which columns line up, matches on the key, and writes what agrees, what differs, and what is on one side only as new tabs. Your source tab is never touched, and the CSV is never written anywhere. Free up to 1,000 rows a side.
Install: in Google Sheets™, Extensions › Add-ons › Get add-ons, search for “Two-File Reconciliation”. The guide walks through a first run. Using Excel? The Excel add-in does the same.
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Microsoft and Excel are trademarks of the Microsoft group of companies. Two-File Reconciliation is not affiliated with or endorsed by Google or Microsoft.