Importing Donations or Recurring Donations from Excel, Text or CSV Files



    Importing Donations or Recurring Donations from Excel, Text or CSV Files

The option Database ⇒ Import ⇒ Donations can be used for two different purposes:


1.You can use it to import donations from a previous program when you first start using DONATION. (This would generally be preceded by Importing Donors.)
Note: To import multiple years of donations, you have to split them into one file for each year, then move to successive years with the Database ⇒ Change Year menu option, and import the donations for each year when you are in it.

2.You can use it to import regular or recurring donations, such as monthly donations, in the case where lots of donors have given you either a series of monthly cheques or pre-authorized bank withdrawals or credit card charges.


In both cases the usage is the same.


Note: This option is not available with the free Lite version of DONATION.


Also, you cannot import files that contain both donor and donation information together. To import donor information separately, see Importing Donors from Excel, Text or CSV Files. If you have a file that contains both types of data, you will have to edit it yourself to separate it into two files, one containing donor information, and one containing donation information, and both files must contain a member or envelope number field to be imported, that links the donors to the donations.


There are special menu options for importing donor, donation and category data from certain other programs, which should be used instead of this feature if you are switching from one of those programs. See the following topics if they apply:


Converting Data from Membership Plus

Converting Data from GiftWorks

Converting Data from The Church Assistant

Importing Data from Viansoft Church Contribution System

Converting Data from Geminon


If you want to import your Organization Info, Donors, Donations, donation Categories and possibly Receipts together when you first start using the program, and you are not switching from one of the programs listed above, you may prefer to look at the option Importing all Data from Other Programs. However, that is much more complicated than just importing the donors followed by importing the donations, so very few users attempt it.


If you are using the United Church of Canada's PAR (Pre-Authorized Remittance) service, you can instead use the Database ⇒ Import ⇒ PAR Donations menu option.


The Database ⇒ Import ⇒ Donations routine imports the Donation data from an Excel file, tab separated text file or comma separated value (CSV) file. If you select an Excel file to import, DONATION will have Excel invisibly save it to a tab separated text file, and import the text file.


A tab separated text file is a plain-text file where each donation is on its own line, and each field on the line (even optional fields that have no data for the current donation) is separated by one tab character. Fields may optionally be enclosed in quotation marks, but that is not necessary. The file may optionally have one or two rows of column headers at the top.  If you currently have the data in a spreadsheet program such as Excel, you can easily export it to tab separated text with the File ⇒ Save As menu option, using the Save as type "Text (Tab Delimited)" (or words to that effect in spreadsheet programs other than Excel), or you can just let DONATION do that for you by importing the Excel file itself.


A comma separated value (CSV) file is somewhat similar to a tab separated text file, but a comma rather than a tab separates the fields on each line, and fields containing commas (or double quotes) are enclosed in double quotes.


Other spreadsheet programs (like OpenOffice) may only have options to save files as CSV - that works too, although you will have to do that that manually. DONATION cannot do that conversion for you if you do not have Excel installed.


In whatever file format you use, it is strongly recommended to use a header row with column headings in it, both to remind you of what to put into those columns, and to make sure that the correct number of columns are found (especially if you are importing directly from Excel).


What follows is a description of the tab separated columns of data to be imported from the file of donations, in the order they are required to be in that file.



Max Length



Member/Env. Number



A member or envelope number for the donor giving this donation. This is how the donor is identified.

Date Received



A date in a display format that will be accepted by the program, such as the format you see in the Donation Details area of the program's main window. More technically, the formats that are accepted by the program will be the Short Date Format and Long Date Format from Control Panel's Regional and Language Options tool.




The amount of the donation. Decimal places and cents are optional.




The description of this donation category. If a new category is encountered, it will be added to the list maintained with the Maintenance Donation Categories menu option.

Cheque Number /
Paid By



The cheque number that this donation was paid with, or any text describing how the donation was paid. Standard options include "Cash", "Check" or "Cheque", "VISA", "MasterCard", "Money Order", "Credit Card", "Direct Debit", "Debit Card", "PAC", "PAR", "PayPal", "Non-Receiptable", and "Pre-Receipted". This field may also be blank.


The following two fields can each optionally be imported (or not), depending on whether you check the relevant checkboxes in the importing window:


Eligible Amount



In the case of Split Receipting in Canada, the eligible amount of the donation for tax purposes. (The 3rd field, Amount, is then the Total Amount.) Only enter this if it is less than the Total Amount.




The Description of the donation.


You can create this file in a couple of different ways. Usually, you would create the file in a spreadsheet program such as Microsoft Excel, and have DONATION import from that file. Alternatively, you can use a text editor (such as Notepad) or a word processor (such as Microsoft Word) to create a tab separated text file. If you use the word processor, make sure you use File ⇒ Save As to save the file, and select to save it as type "Text only" or words to that effect.


Each line of the file must always include fields for the first 5 fields listed above, in that order. If the Cheque Number / Paid By field is not filled in and you are using a text file, there must still be a tab on each line, separating where it would go from the preceding field's value. If Cheque Number / Paid By is not filled in and you are using Excel, be sure to include a header row with something in that 5th column, to ensure that it gets exported properly. Then in addition, depending on your selections for including the Eligible Amount and Description fields in the window, each line of the file may need to instead have 6 or 7 fields in it.


Here is a sample file in Excel, with all 7 columns shown:




It is important to understand that this procedure does not ever replace any existing donation data, but rather always adds additional donations into your database. All existing donations will stay there. This procedure also is very careful to identify duplicates. If any donation in the file to be imported has the same Member/Envelope #, Date Received, Amount and Category as an existing donation, you receive a detailed message about it and the whole import is cancelled. The reason it is cancelled in this situation is that you are most likely importing the same file for a 2nd time, which is obviously not desirable! (However, it is OK if the file itself contains such duplicates. This could happen if you had sections of the same donation that needed different Descriptions, so it was split into two or more donations.)


You will notice that you have to supply the donor's Member/Envelope # to identify them. That means that even if your organization does not normally assign Member or Envelope numbers to donors, you must do so for the donors that you want to use this import donations feature for. That means that you must ensure that the field for showing the Member/Env # in the Donor Details tab of Maintenance ⇒ Main Window Options is checked, since that makes the Member/Envelope # field in the Donor Details area visible. For more details on how to enter Member/Envelope numbers, see Entering a New Donor.


If the donors for the donations you are importing were themselves imported via the Import Donors routine, they must have been imported with a Member/Envelope Number included among their fields, or the donation importing will not be able to do the required matching.


Also, if you plan to import Eligible Amounts and/or Descriptions, you must ensure that those fields are displayed on the main window, via the Donation Details tab of Maintenance ⇒ Main Window Options.


Filling in the Import Window


When you select Database ⇒ Import ⇒ Donations, the following dialog box comes up:




In section 1 of the dialog box, the fields that must always be in the import file are shown, and there are checkboxes for determining whether to import the two optional fields (Eligible Amount and Description).


In section 2, you specify the name of the Import File that is to be imported. You can either just type it in (include the full path – drive and directory), or use the Browse button to bring up a standard File Open dialog box to find the file. It can be a tab separated text file (*.txt), either type of Excel file (*.xls or the newer version, *.xlsx), or a comma separated value file (*.csv).


When you use the Browse button, it will start out showing only one type of file, "Microsoft Excel Documents". If you need to import a different type (tab separated text, or comma separated value), you can change that by dropping down the file type control near the bottom of that window.


In section 3, you can specify how many lines of column headers are at the top of the import file. Most commonly this should be set to 0 (if there are no column headers in the file) or 1 (if there is one line of column headers in the file).  It defaults to 1 the first time this dialog box opens, so it will probably be easiest for you if you put one line of headers in the import file.


Section 4 is used if you have recurring monthly donations, and don't want to have to edit the dates in the import file each month. In this section, you can specify that you want all of the dates in the file to be imported to be adjusted, either to a date in the current month, or in the previous month. (This will depend on your processes - do you run the import at the end of the relevant month, or sometime in the following month.) If you leave both checkboxes unchecked, the dates will be imported exactly as they are in the file, and must be within the current year. If you check one of the checkboxes, the dates will be adjusted appropriately. If a date in the import file is at a month-end and the month you are importing into has fewer days in the month, it will be adjusted appropriately. (For instance, if the date in the import file is 01/31/2016, and you are importing for February 2016, it will be imported as 02/28/2016.)


If you do not check the option in Section 4 for adjusting the dates, then all of the dates in the import file must in the current year that the program is working on. That will be a calendar year unless you are in a country like the United Kingdom, Australia or New Zealand and you are Using Fiscal Data Entry and Receipting, as described in that linked topic.


Once you have used this window (and clicked Import) once, the next time you open it all of the settings that you used last time, including the Import File name, will be redisplayed, since it is most likely that each import will use the same settings.


Doing the Import


Once you have entered your desired choices for sections 1 to 4, click the Import button. The first thing that will happen is that it will read the import file, and look for problems in it. If there are any problems that are fixable, it will tell you that fact, and display the list of problems in the Notepad program. Print off that list for later fixing, then exit Notepad. An example of such a problem is a field that is too long for the field lengths in DONATION. If the problem is not fixable in an obvious way (for example an Amount that does not appear to be numeric), it may just give you a message about it and return.


That file displayed to you in Notepad clearly separates out unfixable problems (like missing required fields) from ones that it will try to fix for you (like overly long names). If there were unfixable problems, you will have to go back to the original file being imported, make the appropriate fixes, and then try the import again.


The data to be imported will now be displayed for you. Take a good look at it. Make sure the right data is shown with the right fields, and that any rows of headers from your data are not displayed, and that dates have been adjusted appropriately if you used one of the checkboxes in section 4. (Note: the program will have put in its own headers, so what you are checking is that you don't seem to have two or more rows of headers.) Also check that all of the donations you expected to be displayed are in fact displayed. Finally, you can check the count and dollar total of the donations in the summary at the end of the list. If everything looks fine, click Import in the window displaying the data to be imported, and the import will begin. If there are problems with what you see in the data preview window, click Cancel and the Import will not be done.


As the data is being imported, progress will be showing in the bar at the bottom of the window.


Note that in this preview window, there are columns for the Eligible Amount and Description, whether or not you are importing those fields. If you aren't importing one of those fields, the values in that column will just be empty.


After you click Import in the preview window (or after you click Import in the Import Donations window above if you didn't select to preview the data), it will perform the import. When it is done, it will tell you so, then close the Import window.


If fixable errors were shown in Notepad and you printed them off, you should now go back in the DONATION program's main window to each donor and donation that had an error displayed in that printout, and decide how or whether you wish to fix it in the imported record.


Possible Problems with Complaints about Numbers of Rows or Columns


Sometimes the program will complain that you are only supposed to be importing some number of columns, but some rows have more columns. But when you look at the Excel file, you only see data in the expected number of columns.


Or, it may complain that there are rows with missing data, and the row numbers it mentions appear to be empty and are below the bottom of your data.


In either case, a simple solution is to just copy the rows and columns that do have data in them out of this Excel file, and paste them into another file. Then import that other file instead!