By Ashley Preen
July 16, 2020
If you have a foreign bank account which doesn’t support bank feeds to QuickBooks, FreeAgent, or any other accounting software you’re currently using, you’ll know that trying to upload CSV files generated from these foreign banks can be a nightmare.
That’s because many non-English European languages use the comma (,) instead of the period (.) as a decimal separator.
So, for example, in German, the number €100.05 would be formatted as €100,05. (Actually, in Germany, the Euro symbol would come after the number, like so: 100,05 €).
Some Canadians still use a comma as a decimal separator, although this is no longer taught in schools.
U.S. and U.K. English are in the minority in using commas as decimal separators, but the fact that “CSV” stands for “Comma-Separated Values” means that a CSV file will separate each unique value using a comma.
For example, a CSV file formatted for upload to FreeAgent could look like this:
10/04/2020, 100.23, Some Expense
When a computer reads that file it sees three distinct fields: A date, a value of 100.23, and a description.
If we were using commas as decimal separators, the above line would look like this:
10/04/2020, 100,23, Some Expense
Now when a computer system reads the file, it sees four fields: A date, a value of 100, a value of 23, and some text. That would throw an error if you tried uploading it to FreeAgent.
To solve this problem, languages which use commas as decimal separators save CSV files with semicolons as a separator. So, even though it is still called a “CSV” file, the separator is now a semicolon and not a comma.
Confused yet? Yes, computer programmers feel your pain.
When downloading a CSV statement from a German bank you would receive a copy which might look like this:
10/04/2020; 100,23; Some Expense
But it gets even more complicated. Languages vary widely in how they write down dates.
Germans separate date parts using a period (.), so the above file would actually look like this:
10.04.2020; 100,23; Some Expense
The Swiss format dates the same way.
Even if you replaced the commas with periods and the semicolons with commas, FreeAgent and QuickBooks would still reject the file because they aren’t able to determine the value of a date formatted with periods (.) instead of slashes (/).
If the file was from a Swedish bank, it might look like this:
2020-04-10; 100,23; Some Expense
Okay, there are many ways to do it “wrong”, but we need a quick and easy way to format the file and get it “correct” so that you can upload it to QuickBooks, FreeAgent, Xero, etc. And the solution is to do a trick with Microsoft Excel.
The steps are:
Easy, yeah? That works for any file in any foreign format. Now go upload those transactions and get your bookkeeping up to date!