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.
The problem with commas
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
Differences in date formatting
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
How to quickly and easily format your foreign bank CSV file
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:
- Close all Excel windows.
- Change your computer’s regional settings to the language and country of your downloaded CSV file. Instructions for changing regional settings on Windows can be found (Change the Windows regional settings) and for Mac can be found (Changing Regional Setting in a MAC).
- Open the CSV file using Excel and then save it as an Excel file i.e. with a “.xls” or “.xlsx” or “.xlsb”, etc., file extension. (Here are instructions on how to do that if you’re not sure.)
- Move the columns around to make them match the required format for FreeAgent, QuickBooks, Xero, etc.
- Save the file (still as an Excel file).
- Close the file.
- Close all instances of Excel.
- Change the regional settings of your computer to English (UK) (see step #2)
- Open the Excel file you just created.
- Verify that the dates and numbers are in the British format.
- Now save as a CSV file.
- There you go, you’re done. The file can now be uploaded to FreeAgent, QuickBooks, Xero, etc.
Easy, yeah? That works for any file in any foreign format. Now go upload those transactions and get your bookkeeping up to date!
