Thred
All posts

Getting messy exports into your books: how to clean CSVs from banks, webshops and payroll reliably

Bank, webshop and payroll exports rarely fit your ledger as they are. A reliable routine for cleaning them, control totals that catch errors, and where agents take the work over.

David van Duijn6 min read

Working on this yourself? Talk to Mark, our co-founder. We’re here to solve it with you.

Book a demo
On this page

Cleaning an export means turning a file made for some other system into rows your ledger can accept without changing what they mean: the right dates, amounts, signs, accounts, counterparties and VAT, with nothing counted twice and nothing left out. The reliable way to do it is a fixed routine that ends in a control total you can check against an independent source, usually the bank.

Every finance team has a few of these files. The payout report from the payment provider. The order export from the webshop. The payroll journal from the payroll provider. The transaction list from a bank or card that has no direct feed. For an accounting firm, multiply that by every client.

This post is for finance teams and bookkeepers in accounting firms who import these files every month. It covers why they break, a routine that works, a worked example, and how agents change who does the cleaning.

Why are exports so messy?

Because nobody designed them for your ledger. An export is shaped by the system that produced it, by the person who configured it, and often by the spreadsheet program that opened it on the way.

The problems repeat. In a Dutch or wider European context these are the usual suspects:

  • Decimal commas. "1.234,56" means one thousand two hundred and thirty-four euros in the Netherlands and something very different to software that expects "1,234.56".
  • Semicolons instead of commas. Spreadsheets set to Dutch regional settings separate columns with semicolons, because the comma is already taken by the decimals.
  • Ambiguous dates. Is 03-04-2026 the 3rd of April or the 4th of March? Within one file you can usually tell from the other rows. Across files from different systems you cannot assume.
  • Signs and columns. One bank puts debits and credits in separate columns, another uses a single signed amount, a third writes "Af" and "Bij" in a column next to a positive number.
  • Rows that are not transactions. Opening balances, subtotals, a "Total" line at the bottom, blank lines between days.
  • Net amounts that hide gross ones. A payment provider payout is sales minus refunds minus fees. Book the payout as revenue and both revenue and costs are wrong.
  • Spreadsheet damage. Leading zeros dropped from invoice numbers, long references turned into scientific notation, accented supplier names mangled by the wrong character encoding.

None of these is hard to fix once you see it. The trouble is that a file can look fine and still contain two of them.

What is a reliable routine for importing a CSV?

Use the same steps every time, in the same order. The routine matters more than the tool.

1. Keep the original untouched

Save the raw file exactly as you received it, with the date and source in the name. Every cleaned row should be traceable back to a row in that file. If anything goes wrong later, this is your evidence.

2. Profile the file before changing it

Count the rows, note the date range, and add up the amount column. Write those three numbers down. They are your first control totals, and they will tell you immediately if a later step dropped or duplicated rows.

3. Normalise the format

Convert dates to one unambiguous format, amounts to a single decimal convention, and debits and credits to one signed amount. Strip totals and blank rows. Do this without changing any value.

4. Map columns and counterparties

Match each column to a field in your ledger and each counterparty to an existing customer, supplier or employee. Anything you cannot match should become a question, not a new supplier record. Duplicate master data is how a clean ledger slowly becomes a messy one.

5. Validate against something independent

This is the step most teams skip. Check that the file agrees with itself (the components add up to the total) and with something outside the file, such as the bank line the payout landed on, the payroll provider's summary, or last month's closing balance. Check VAT as well: does the VAT amount match the rate times the base?

6. Check for duplicates

Before posting, compare against what is already in the ledger. A combination of date, amount and the source's own reference is usually enough to catch a file that was imported twice or overlaps with last week's.

7. Post as one batch, and save the mapping

Post everything from one file as one batch with a reference to the source, so it can be reviewed and, if necessary, reversed as a whole. Then save the mapping and the decisions you made, so next month's file goes faster.

A worked example: a webshop payout

Here is an illustrative example. A Dutch webshop receives a weekly payout from its payment provider. The export lists every payment, refund and fee, with semicolons, decimal commas and a total row at the bottom.

After cleaning, the week adds up to:

  • Gross sales €4,840.00, including 21% BTW
  • Refunds −€121.00, including 21% BTW
  • Payment fees −€72.60
  • Payout €4,646.40

The control total checks out: €4,840.00 − €121.00 − €72.60 = €4,646.40, and that is exactly the amount on the bank line for the payout. If the two had differed, the import stops there.

The journal entry then splits the payout into what actually happened:

  • Debit bank €4,646.40
  • Debit payment fees €72.60
  • Debit sales returns €100.00
  • Debit VAT payable €21.00
  • Credit sales €4,000.00
  • Credit VAT payable €840.00

Debits total €4,840.00 and credits total €4,840.00. Revenue is booked gross, the refund reduces VAT, and the fees appear as a cost instead of disappearing into a smaller revenue figure. How payment fees are treated for VAT depends on the provider and the service, so check that with your accountant.

When should you stop using CSVs at all?

When a direct connection exists. A bank feed, a payment provider integration or an API to your payroll provider removes most of the problems above at the source, because the data arrives structured and on time instead of when someone remembers to download it.

Files will not disappear entirely. Smaller banks, older systems, a marketplace without an API, a one-off historical import. The routine above is for those. But if you import the same file every week from a system that offers a connection, the connection is almost always the better use of an afternoon.

How do agents change CSV imports?

Cleaning a file has two halves. Understanding what the file means takes judgement, and checking that the numbers are right takes discipline. Agents are useful for both, in different ways.

  • Reading the layout. An agent can recognise that a file uses decimal commas, that the last row is a total, and that "Af/Bij" is a sign column, without a template built in advance for that exact export.
  • Mapping against your own data. It matches counterparties to the suppliers and customers already in your ledger, and asks when it is unsure, instead of creating a near-duplicate.
  • Running the checks every time. Control totals, VAT arithmetic and the duplicate check are tedious for people and trivial for software. An agent does them on every file, including the fortieth one of the month.
  • Remembering the decisions. Once you have told it how a payroll provider's journal maps to your accounts, it applies the same mapping next month and only asks about what changed.

For an accounting firm the gain compounds. The same kind of export from twenty clients, each with its own quirks, stops being twenty small puzzles for a junior and becomes a review of the exceptions.

What stays with people is deciding what the numbers mean when the file is ambiguous, and approving the batch before it reaches the ledger.

Where Thred fits

Thred Layer runs agents on top of the ledger you already use. It connects to your banks, payment providers, email and other systems, so much of the data arrives without a file in between. Where files remain, the agents do the work described above: they book the same transaction the same way every time, surface the exceptions rather than burying them, and apply the rules you have stored for your business. Finished entries flow back to your accounting software through a two-way sync.

For accounting firms, the same layer works across all your clients' administrations, so your team reviews instead of keying in. See how that works on the accounting firms page.

If your month still starts with a folder of exports, book a demo.

Frequently asked questions

Why does my CSV import show wrong amounts?

Usually because of decimal conventions. Dutch and many European exports write 1.234,56 with a decimal comma, which software expecting a decimal point reads as a different number. Spreadsheets set to Dutch regional settings also use semicolons as column separators.

What is a control total in bookkeeping?

A control total is a sum you calculate independently to check that a batch of data is complete and correct. For a payout export, the sales minus refunds minus fees should equal the amount that arrived on the bank account.

Should a payment provider payout be booked as revenue?

No. A payout is a net amount after refunds and fees. Book gross sales as revenue, refunds separately, and fees as a cost, so that revenue, VAT and costs are all correct. Check the VAT treatment of the fees with your accountant.

How do you avoid importing the same transactions twice?

Compare every row against the ledger before posting, using the date, amount and the source system's own reference. Posting each file as one batch with a reference to the source also makes it easy to reverse a duplicate import as a whole.

Can AI clean CSV files for bookkeeping?

Agents can recognise a file's layout, map counterparties to existing records, and run control totals and duplicate checks on every file. A person should still approve the batch and decide on anything the file leaves ambiguous.

Sources

  1. RFC 4180, Common Format and MIME Type for CSV Files (IETF)
  2. Payout reconciliation report (Stripe Docs)
  3. Keeping business records (business.gov.nl)
  4. VAT record keeping (GOV.UK)

Read next

Where Thred comes in

Thred Layer

Finance agents running on top of your existing ledger. You set the rules, approve the work and handle the exceptions. No migration.

Keep reading

Finance, handled.

Book a demo, or request a callback and we will call you.

Book a demo