How do I configure the import settings properly?

When importing transaction data from a QIF or CSV file, iBank will first ask you to configure a few settings. (OFX and QFX files do not require any configuration before importing.)

QIF settings:

If you choose a QIF file for import, the following sheet will appear:

qif settings

Next to "The date currently looks like" is a sample date taken from your import file. Look at this date carefully and choose the matching date format from the drop-down menu. Pay attention to the order of the day and month, as well as the number of digits in the year. The format code shown at the bottom will update itself automatically depending on your menu selection.

If the sample date doesn't match any of the drop-down menu selections, you can edit the format code directly. The date format codes below can be used to match the format of the sample date. For example, to match a date format of "12/31/2005" you would enter "%m/%d/%Y", or "31.12.05" would be "%d.%m.%y".

These are the codes available for use:

Two common mistakes are using an incorrect separator (e.g. entering slashes instead of dashes or dots between the month, day, and year codes) or using the wrong code for the year. If the sample date has a four-digit year (e.g. "2006") then be sure your format code includes "%Y" instead of "%y" - the case of the letter 'y' is important.

CSV settings:

Most CSV files are formatted differently depending on the financial institutions or software that generated them. If you choose a CSV file for import, the following sheet will appear:

csv options 1

At the top of the window is a preview of the data inside the CSV file. The bar across the middle of the window lists each of the columns that can be imported. Use the mouse to drag and drop the various columns into the correct order to match the order of the items in the preview pane.

Note that iBank only has one "Amount" column available for the CSV import. iBank cannot import data from CSV files that use separate columns for debit and credit amounts.

For example, the first transaction listed in the preview pane above reads "11/1/07,Billington Life & Home,automatic withdrawal,Insurance:Home,-1200.00." To match this transaction, drag the columns so that they read in this order: Date, Payee, Memo, Amount. You'll notice, however, that there is another item in the preview transaction between the memo and amount (the transaction's category). iBank can't import categories from CSV files, so we'll need to create a placeholder instead.

Use the +/- buttons to add and delete placeholder columns for extraneous information in your CSV file. Following our example, click the + button to add a placeholder column (it will appear as "Blank"), then drag and drop it between the memo and amount columns. This is how things should look after rearranging the columns and adding a placeholder:

csv options 2

At the bottom of the window, you will see a couple of sample transactions formatted according to the settings you entered. They will update automatically as you make changes so that you can use them to help you determine when the importer has been configured properly.

In addition to making sure the columns are arranged correctly, you will also need to choose a date format (see above for more information on this) and specify a separator. Given that CSV stands for "comma-separated values," most CSV files will use a comma to separate items in the file, but there are exceptions. If your file uses a semicolon, tab, or other character to separate the payee, memo, and other information, type that character next to "Items are separated by."

If you are importing from a tab-delimited file (i.e. the values are separated by tab spaces instead of commas), you must enter the tab character into the separator field. Since iBank will not allow you to type the tab character directly, you will need to select one of the tab spaces in the preview pane, choose Edit>Copy, then select the contents of the "Items are separated by" field and choose Edit>Paste.

Sometimes, when working with CSV files, it may be useful to do some preparatory work on the file before importing it into iBank. The following suggestions may help you to avoid potential problems with the import process:

  1. Remove any unneccessary data from the CSV file, such as header rows or blank entries. The first line of the CSV file should be the first transaction record you want to import.
  2. Remove any extra commas that are not used to separate the values in each transaction - for example, an amount of "1,000.00" should be changed to "1000.00" or else iBank will read it as two values ("1" and "000.00").
  3. If you really want to simplify the process, remove all data except for the four values iBank can import: Date, Payee, Memo, and Amount.

To save your import settings:

Once you have finished configuring the import settings, click "Continue Importing" to proceed with the import process. If you are planning to import transactions from the same source on a regular basis, click "Save Settings" and your configuration will be stored for later use. The next time you import data into that account, your saved settings will be used automatically; you will not be asked to configure them again.

Import settings are stored separately for each account. If you configure settings for one account and save them, then import data into another account, you will be asked to configure the import settings again for the new account.

To edit your saved import settings:

If you need to adjust settings after saving them, select the appropriate account from the source list, then choose Account>Edit Import Settings. In the sheet that appears, you can reconfigure the import settings for that account:

edit import settings

Choose the QIF or CSV tab to edit settings for the respective file type. Make any changes necessary, then click "OK." The next time you import data, your new settings will automatically be applied. Check "Fill with memorized info" if you want the categories of imported transactions to be automatically filled in based on your memorized transactions.

To reset your saved import settings:

If you want iBank to forget your saved settings and ask you to configure them manually the next time you import data, open the Import Settings sheet, click "Defaults" and then "OK."