Importing Transactions

<< Click to Display Table of Contents >>

Navigation:  ENTERING AND MAINTAINING TRANSACTIONS >

Importing Transactions

The ACCOUNTS program can import transactions from files that are in the same format that QuickBooks Desktop can import transactions in. QuickBooks import files must have the file extension (ending) ".IIF". ACCOUNTS import files can have that file extension, or the custom ACCOUNTS import file extension ".S4AIF".

 

Uses of Importing

 

The most common use of importing will be to import a summary bank deposit transaction exported from the Software4Nonprofits DONATION program, which is used to track your donors and donations and issue charitable receipts. Please read the Help in DONATION on "Creating Bank Deposits" to see how to associate donation categories in DONATION with ACCOUNTS accounts, and then determine which donations are included in your bank deposit, and export it to a file. (If two different people use those two programs, DONATION can even email the file containing the transaction to another person.)

 

The exported file name from DONATION will be BankDeposit-YYYY-MM-DD.DB.S4AIF, where YYYY-MM-DD is the bank deposit date you entered when creating the bank deposit. (The ".DB" part will change if this is for an additional database created with DONATION's Database Switch Databases menu option.)

 

The other common use of importing will be importing from 3rd-party payroll programs, because ACCOUNTS does not include its own payroll calculations.

 

For users in the U.S.A., we recommend Payroll Central USA (details are on the ACCOUNTS website at https://www.software4nonprofits.com/accounts/RecommendedPayrollUSA).

For users in Canada, we recommend PaymentEvolution (details are on the ACCOUNTS website at https://www.software4nonprofits.com/accounts/RecommendedPayrollCanada).

 

Both of these services can create export files that can be imported into ACCOUNTS. (With PaymentEvolution, there is a free version, good for up to 5 employees, but it does not include the exporting. You can still manually enter the payroll transactions from it into ACCOUNTS. The paid version includes the exporting.)

 

One thing you cannot normally do with importing is import all of your prior transactions from an existing accounting program. This is explained in more detail in the sections on importing charts of accounts from QuickBooks Desktop, QuickBooks Online, Quicken or Simply Accounting.

 

In brief, the concepts and organization in most other conventional accounting programs are so different from those in ACCOUNTS (particularly because of the fund accounting features in ACCOUNTS) that it would be almost impossible to match up and convert the transactions properly into ACCOUNTS. Instead, what is recommended is to enter an Opening Balances transaction in ACCOUNTS, based on a Trial Balance report (with some appropriate adjustments) from your existing accounting system. See the Help on Entering Opening Balances for more details.

 

How to Import

 

There are two ways to import files containing transactions into ACCOUNTS:

 

Simplest Option:

 

If the filename ends in ".S4AIF", you can right-click on the file in Computer or My Computer, and pick "Open" or "Import into ACCOUNTS" from the popup menu to automatically import it. If the file is in an email attachment, you may be able to double-click it to automatically import it.

 

For this to work, the current database / organization that you last used in ACCOUNTS (if you have more than one) must be the one you want to import the file into. (Prior to release 2.20 of ACCOUNTS, the program also had to not already be running. Now that is not a problem.)

 

More Flexible Option:

 

If you have the file stored in a known place on your hard drive (or a USB memory key etc.), run ACCOUNTS, make sure you are in the right database, and use the Database Import Transactions menu option. That brings up the following window:

 

ImportTransactionsWindow

 

You can type in the name of the file to Import (which may have the extension ".IIF" or ".S4AIF"), or click the Browse button and navigate to where it is stored and select it. The initial default directory for the Browse button is the Import subdirectory of the program's data directory. After you have used the Browse button once, though, it will remember what drive and directory you did the previous import from, and show you that as the default directory the next time.

 

Once you have the desired filename in the File to Import field, click Import to do the import.

 

If you use the "Simplest Option" described above (right-clicking on a ".S4AIF" file), ACCOUNTS will open and display the window above, with the filename already filled in, and just ask you to confirm that you want to do the import, then start the import if you answer Yes.

 

Possible Import Errors

 

There are a number of things that will cause detailed error messages, and cause the import not to succeed. The most likely is that there are account names in the import file that aren't in your Chart of Accounts. Also, if the accounts being used are of the wrong types, or have sub-accounts, that will also prevent the import from succeeding. (You can only post transactions in ACCOUNTS to accounts that do not have sub-accounts.)

 

Another possible problem is importing the same file twice. Some import files (including bank deposit transactions exported from DONATION) have a unique ID associated with each transaction, that allows ACCOUNTS to notice that it is importing the same transaction(s) a second time. If this occurs, you will be given options to replace the previously imported transaction, import it again (which will probably be a duplicate!) or cancel the import.

 

If there is an error message, read it carefully, and then make appropriate adjustments. That may involve changing the names of the accounts used in the program that you are exporting that transaction from, for importing into ACCOUNTS. Or it may involve adjustments in ACCOUNTS, such as creating any missing accounts that you do want to use, with the New button on the Maintenance Chart of Accounts window.

 

If the import is successful, you will receive a message telling you how many transactions were imported from the file, and as long as they all went into the same account (like a bank account), it will tell you which account they went into. You can then review that transaction in the register window for that account. (In most cases you will want to open the splits window on the imported transaction(s), to see all of the details.)

 

In unusual cases you may need to make changes to the imported transaction(s). You can do that in the register window and/or splits window, as desired.

 

Importing Transactions with Multiple Occurrences of the Same Account

 

The ACCOUNTS program actually prevents you from entering a transaction on the register (or anywhere else) that has two split lines with the same account. The reason is that the running balance in the register for that account (if it is an Asset, Liability or Equity/Fund account for which you can display a register) would then be incorrect. Why is that? Because the running balance is based on only the primary amount field shown on the register. If there are further lines for the same account within the splits, their affect on the balance will not show up, and thus the balance would be wrong.

 

This is also true when importing transactions. If there are multiple split lines in a transaction to be imported for the same account, their amounts will be added up and they will be put together in one split line. If there are memo field values on those lines with duplicate accounts in the file being imported, they will be strung together into one memo, separated by commas (to the extent that they fit).

 

Creating your own Import Files

 

It is quite complex and difficult to create your own IIF import files, because it is a complex file format, and the ACCOUNTS program's import routine is very specific about what it will accept. (It was really only designed for importing bank deposit transactions from DONATION, and payroll transactions from the online payroll systems that we work with.)

 

You can currently get details from Intuit (the makers of QuickBooks) about the IIF format at http://support.quickbooks.intuit.com/support/articles/HOW12778. In particular, that page has a link to download and install an IIF Import Kit, that we recommend that you use for reference instructions. If you have QuickBooks, you can find most of the same details in its Help.

 

If you do decide to create your own import files, and need any significant amount of help with getting them to work, that will not be considered to be part of our standard support, and we will ask you to pay an hourly fee if you want our assistance.

 

Here are a few things that will help if you do try to create your own IIF files:

 

Some Intuit resources state that IIF files should be tab separated text, while others mention that they can be CSV (comma separated value). Either is accepted by ACCOUNTS.

You must have !ACCNT and ACCNT lines at the top, that list all accounts from your chart of accounts that will be used in any transaction included in the file. If accounts are used in transactions but do not have a preceding ACCNT line, the import will be rejected. Accounts will not be created based on these lines - they must already exist in your chart of accounts.

The required fields in !ACCNT header lines (and required contents in ACCNT data lines) are the NAME and ACCNTTYPE. Any other fields are ignored.

ACCNT lines and accounts in transactions cannot be for fund / equity accounts. Very few transactions in ACCOUNTS should directly affect fund accounts.

The ACCNTTYPE values on ACCNT lines must match the class of account (Asset, Liability, Income or Expense) that the existing account in the program's chart of accounts has.

The required fields in !TRNS header lines (and required contents in TRNS data lines) are TRNSTYPE, DATE, ACCNT, AMOUNT and NAME. Other fields that will be imported are DOCNUM, MEMO, and TRNSID. Use of the CLASS field, with any contents in it, will prevent the import from working, because classes are not supported (or needed) in ACCOUNTS. Use of the TRNSID to supply a unique ID to each transaction will prevent double importing of the same transactions.

Dates must be exactly 10 characters long, and be in the format MM/DD/YYYY or YYYY-MM-DD. Any other format will cause the import to fail.

Amounts that cannot be converted to decimal amounts will cause the import to fail.

The required fields in !SPL header lines (and required contents in SPL data lines) are the ACCNT and AMOUNT. The other field that can be imported is MEMO. Use of the CLASS field, with any contents in it, will prevent the import from working, as explained above.

SPL lines containing an AMOUNT field that is exactly "0.00" will be skipped.

Other than the first field of each !ACCNT, !TRNS and !SPL line, which defines the type of line, the order of the remaining fields is not important. Of course, the fields in the ACCNT, TRNS and SPL lines must be in the same order as they were defined in the corresponding !ACCNT, !TRNS and !SPL lines.


This topic was last edited on Apr 12, 2023