All Collections
Importing
Other
How do I format CSV file before importing?
How do I format CSV file before importing?

Help MoneyWiz understand your CSV file by introducing some modifications.

Iliya Yordanov avatar
Written by Iliya Yordanov
Updated over a week ago

There is no official format for CSV files so every bank, institution and application makes a decision on how to format their syntax. With MoneyWiz, we have adopted the most popular and universal format. You can read more about it in this article. It is meant for users who would like to format their CSV files without our involvement (for example, because the file contains sensitive information). If you need help, we are always happy to answer your questions – please contact us.

Recognised fields

Bare minimum

At minimum, your CSV file needs to contain:

  • Date

  • Description

  • Amount

Maximum

Here is the list of fields that are available during “Field mapping” stage in MoneyWiz, along with a brief description:

  • Date – numeric date (for example 13-06-2018).

  • Amount – should be used if your CSV contains transaction amounts for both expenses and incomes in one column.

  • Amount (Expenses) or Amount (Incomes) should be used if your CSV files contains transaction amounts for expenses and incomes in separate columns.

  • Description – single-line description of the transaction.

  • Payee – single-line information about the payee/merchant/transaction participant.

  • Category – information about what (sub)category the transaction is assigned to.

  • Account – in case your CSV file contains transactions from various accounts, not just one, you should include a column that would link transactions to their respective accounts.

  • Transfers – if particular transaction is a transfer to another account, its cell in this column should include name of the account that the transfer is aimed at.

  • Check # (check number) – if particular transaction has an assigned check number, it should be stored here.

  • Memo – multi-line comment associated with the transaction.

  • Tags – tags that can be attached to transactions (including transfers). Transactions can have multiple tags – you can read more about those here.

Remember that the order of columns in the file is not important.

Linking fields during import

When you start importing a file in MoneyWiz, it’ll present you with a screen where you can “link” one field to another. Basically you are instructing MoneyWiz what it should expect in each column. MoneyWiz will try to match the columns automatically by name so if your column in the file is labelled “Date”, it would be linked to MoneyWiz automatically. This applies not only to English language but to all languages supported by MoneyWiz (Datum -> Date).

If MoneyWiz cannot guess what the column contains, it’ll default to “Don’t import”. You can click on that button to instruct MoneyWiz what the column is.

Please note that Amount, Amount (Expenses) and Amount (Incomes) are special. You should either use only Amount or use both Amount (Expenses)and Amount (Incomes). MoneyWiz will not allow you to use both Amount and Amount (Expenses) for example. For more information please refer to “Formatting the Amounts column”.

Details

File syntax

Generally MoneyWiz can handle most configurations you throw at it, as long as it is consistent. The safest way to format the file is to wrap every column in double quotes then separate the columns with commas:

"Date","Description","Amount"
"13-06-2018","ABC DEF, and 123","1.23"

What MoneyWiz might not like is additional information stored like this:

ACCOUNT BALANCE: 3312.50"Date","Description","Amount"
"13-06-2018","ABC DEF, and 123","1.23"Thank you for using our services.
Modern Bank Limited
Export generated on Wednesday, 13 June 2018, 15:52:05

Notice the Account balance information stored above the headers and the additional information stored at the end of the file.

Most of the time, MoneyWiz won’t mind this data and import your file just fine. However, there is a chance that it would confuse the importing algorithm, while it does not provide any additional value to the import process. If possible, avoid including those in your file.

Formatting Date column

Highlights

  • don’t include time in the same column as date

  • the date format should be consistent throughout the entire file

  • avoid using letters to describe months and days of the week

  • use Gregorian calendar whenever possible

MoneyWiz is very robust in recognising the date format and including all possible variants here would take too much space. If you’d like to be sure, the safest way is to format it as fully numeric date (for example 13-06-2018).

The date format throughout the file needs to be consistent. This not only means that the file should not shift from DD/MM/YYYY to MM/DD/YYY but the separators should also remain consistent. For example, we’ve seen cases where an application would export dates like this for transactions up until end of 1999: DD/MM/YY and from year 2000 onwards: DD/MM’YY. Notice how the Month-Year separator changed.

An important rule is not to include time in the same column as the date – it’s the single most common reason why date format is not recognised. MoneyWiz cannot parse the date if time is stored in the same column. At the moment, MoneyWiz does not import time of transactions for CSV files and will import all transactions as 12:00. If importing time of transactions is important for you, please use OFX/QFX format instead. If your CSV file contains time in the same column as date, you can use Excel/Numbers or regular expressions feature in your text editor to modify the file. If you need help, please contact us.

MoneyWiz will recognise what do you mean by “June” in “13 June 2018” but the month should be written in the same language as your MoneyWiz language. Overall, however, we recommend that you avoid using words to describe months and days of the week altogether and stick to numbers.

MoneyWiz works best if your file includes dates of the Gregorian calendar. Japanese, Buddhist, Persian and Islamic calendar are not supported.

Formatting the Amounts column

Highlights

  • it’s best to use a comma or a dot as a decimal separator

  • the decimal separator in the amounts column should be consistent throughout the file

  • your file should not include “transaction code” in a separate column

  • we recommend that amounts column does not include any different characters than: 0123456789,.-+ (that is: digits from 0 to 9, comma, dot, minus, plus)

  • due to the above, we recommend against including currency symbols/codes in the amounts column

If MoneyWiz seems not to recognise your amounts properly, check if the thousands and decimal separators from the file are the same as in your device’s regional settings.

When to use Amounts field and when Amounts (Expenses) and Amounts (Incomes)? Here’s an example of a file where you should link the Amounts column to Amounts field in MoneyWiz:

"Date","Description","Amount"
"13-06-2018","ABC DEF, and 123","1.23"
"13-06-2018","Alpha Beta and Zeta","-5.89"

And here’s an example when you should link your amount columns separately:

"Date","Description","Credits","Debits"
"13-06-2018","ABC DEF, and 123","1.23","0.00"
"13-06-2018","Alpha Beta and Zeta","0.00","5.89"

We’ve also noticed that small number of banks export transaction amounts like this:

"Date","Description","Transaction","Amount"
"13-06-2018","ABC DEF, and 123","CR","1.23"
"13-06-2018","Alpha Beta and Zeta","DB","5.89"

Notice how the Amount column contains absolute values – there are no plus or minus signs. The only way to distinguish between incomes and expenses is to look at the Transaction column and know what bank considers Credit and what they consider Debit. MoneyWiz does not support this syntax. How do I reformat my CSV file to remove Transaction code?

Formatting the Categories column

Highlights

  • for main level categories, just include their name

  • for 2nd level categories onward, denote their hierarchy with > sign (Main > 2nd Level)

For main level categories, simply include the name of the category:

"Date","Description","Category","Amount"
"13-06-2018","ABC DEF, and 123","Groceries","1.23"

You can preserve the hierarchy of your categories and assign a transaction to a child category using syntax like this:

"Date","Description","Category","Amount"
"13-06-2018","ABC DEF, and 123","Everyday Expenses > Groceries","1.23"

“Everyday Expenses” is the main level category, while “Groceries” is the 2nd level (in other words a sub-category).

Formatting the Account column

Highlights

  • an Account column should be included when you need your transactions to be imported in more than 1 accounts

  • you can use the account name in that column or any other unique identifier

The Account column should simply contain the name of the account the transaction belongs to:

"Account","Date","Description","Amount"
"Checking","13-06-2018","ABC DEF, and 123","1.23"
"Savings","13-06-2018","Alpha, Beta and Zeta","5.89"

In this example, those are two transactions made from two different accounts that have no relation to one another. The account name listed in Account column does not necessarily need to be the real account name – it just needs to be unique and preferably something that allows you to identify that account. During import process, MoneyWiz will list all accounts it found in the file and ask you to link it to account you’ve created in MoneyWiz before import.

If a transaction is a transfer from one account to another, see the “Formatting Transfers column” section.

Formatting Transfers column

Highlights

  • add a new column where you’ll indicate the name of the account the transfer is aimed at

  • without a transfers column MoneyWiz cannot distinguish transfers from other types of transactions

When importing your file, you might want MoneyWiz to recognise that a transaction is not an expense or an income but a transfer between two account you own. At the most basic level, you need to include new column that will contain the name of the account the transfer is aimed at:

"Date","Description","Amount","Transfers"
"13-06-2018","ABC DEF, and 123","1.23","Account 2"

As you can see, it is not required to include both sides of the transfer – MoneyWiz will import this transaction as a transfer from Account 2 to the main account and automatically create the other side of the transfer in the main account. However, if your file contains both sides of the transfer, MoneyWiz will recognize them as such too. The direction of the transfer depends on the amount sign.

For transactions that are not transfers, the cell in this column should simply be empty:

"Date","Description","Amount","Transfers"
"24-06-2015","Alpha, Beta and Zeta","5.89",""
"24-06-2015","ABC DEF, and 123","1.23","Account 2"

Furthermore, transferring money from account A to account A makes no sense, therefore the following structure makes no sense:

"Date","Description","Amount”,”Account”,"Transfers"
"24-06-2015","ABC DEF, and 123","1.23”,”Checking”,“Checking"

If this was meant to be a transfer between two Checking accounts, they should have unique identifiers (for example Checking1 and Checking2).

Formatting the Memo column

Highlights

  • remember that Memo field is not enabled by default, even if you import CSV file containing Memo information. Enable it in MoneyWiz Settings -> Transaction layout.

  • Memo field is different from Description field in such a way that Memo can hold multiple lines of text while Description is always stored as single line.

If you wish to make the Memo in your file multi-line, please open the quotation mark for Memo column, enter your multi-line text, then close the column by inserting a quotation mark, like so:

"Alpha","","Other","Empty Description Ltd.","Other","13/06/2018","12:00","First line of memo
Second line of memo
Third line of memo","","-1,00","USD",""

Formatting the Tags column

Highlights

  • remember that Tags field is not enabled by default, even if you import CSV file containing Tag information. Enable it in MoneyWiz Settings -> Transaction layout.

  • Tags in your file need to be separated using the separator set in Settings -> Transaction layout. By default it’s a semicolon (;) but you can select a comma too (,).

If you remember to use the correct tag separator, even importing transactions that contain multiple tags is a breeze:

"Alpha","","Check transaction","Modern Bank","Bills > Other","13/06/2018","12:00","","House Maintenance;Regular Bills","-3,00","USD","1"

As you can see, we’ve applied here two tags: “House Maintenance” and “Regular Bills”. The tags were separated using the separator that MoneyWiz uses by default – a semicolon.

Did this answer your question?