We have a list of new items in our own app. Turn that into a CSV we can
import straight into the accounting.
Making an import file for Exact Online looks like the easiest part of an integration. In practice it is the part you start over on most often, because there is no error message telling you what is wrong. Your import goes through, and afterwards there are amounts in it that are a hundred times too high, or names with strange characters.
We make these files in an app for a drinks wholesaler with an off licence: a list of new items and a list of supplier items, as a CSV for the user. Nothing goes straight into the accounting; somebody checks it and imports it themselves. The client stays anonymous and there are no amounts, prices or margins here.
These are the rules we fixed for those exports.
The three things that really break it
A semicolon as the separator. Not a comma. A Dutch installation of Excel and the Exact import both assume the semicolon. Use a comma and every row lands in a single column.
A decimal comma in amounts. So 12,50 and not 12.50. And no thousands separator: 1234,56, not 1.234,56. A dot in an amount is quietly read as a thousands separator, and then your amount is a thousand times too big.
A BOM at the start of the file. That is the invisible marker saying: this is UTF-8. Without it Excel reads the file as an old character set, and letters with an accent or an umlaut break. A name with an ë turns into something unreadable, and you only notice when the client sees it.
Two more things that get mentioned less often:
CRLF as the line ending. A Windows line ending, not a Linux or macOS one. This is the least critical of the five, but it costs nothing.
As few quotes as possible. Only put a field in quotes if it contains a semicolon, a quote or a line ending, and in that case double the quote inside the field. That is how most programs write it and how every importer expects it.
The trap nobody mentions: fields that start with =
Here it moves from formatting to safety. A CSV field starting with =, +, -, @, a tab or a carriage return is read by Excel as a formula. That is called CSV injection, and it is not a theoretical problem: item descriptions from a supplier file start with a hyphen more often than you would think.
Our fix is one rule: if a text field starts with one of those characters, we put an apostrophe in front of it. Excel then shows the text as intended and executes nothing.
Amounts: two decimals, sometimes four
An amount with two decimals is the rule, because that is what your accounting stores. But a purchase price per unit is sometimes a case price divided by six, and then a cent disappears at two decimals.
What we do: two decimals if the amount fits exactly, otherwise four. Then the total still adds up, and you can see from the number of decimals in the file that something was converted.
Watch the rounding as well. We round these text fields the way most programming languages do by default, with a half going to the even digit. That means 0,005 becomes 0,00 and not 0,01. That is not a bug, but it explains why a total in your export can be a cent off a total you rounded differently somewhere else. Pick one way and use it everywhere.
Not every package wants the same thing
This is where people reuse an existing export and get into trouble. We also make a cash statement for a different accounting package, and that one wants no BOM and no UTF-8 but the old Windows character set, with the same semicolon, decimal comma and CRLF.
One package, one export. Write down per destination which five things apply, put that in a comment next to the code, and check it on the first import with one row instead of two thousand.
How to check it in a minute
- Open the file in a plain text editor, not in Excel. If you see something like
at the start, the BOM is there and your editor is showing it as characters. If you see nothing, your editor is opening it correctly. - Look at one row with an amount: semicolons between the fields, a comma inside the amount, no dot.
- Search for a name with an accent or an umlaut and see whether it is right.
- Import one row in a test environment before you throw two thousand at it.
Common mistakes
A comma as the separator. The name CSV says comma, Dutch practice says semicolon.
Leaving a dot in amounts. It is read as a thousands separator and your amount no longer adds up.
Forgetting the BOM. Everything looks fine until there is an umlaut or an accent in a name.
Quoting every field. Not wrong, just unnecessary, and some importers read a quoted number as text.
Writing supplier text out untouched. A description starting with a hyphen or an equals sign is a formula in Excel.
Using the same export for two packages. The differences are in the character set and the BOM, and you cannot see those.
What an integration on Exact Online can do is on connecting Exact Online. What else we learned about that API is in what the docs do not say.