<< Click to Display Table of Contents >> Navigation:  INSTALLATION INSTRUCTIONS > 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 Maintenance ⇒ 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. (There is also a different way to memorize and set up automatic weekly or monthly recurring donations, that you may prefer to use.)

 

In both cases the usage is the same.

 

If your file to be imported contains both donor and donation information on the same line, it will probably be preferable to use the Database ⇒ Import ⇒ Donors and Donations menu option, which does all of the importing in one step. For this to work, each line would also have to contain a field that you can use as a unique identifier (also called unique ID) matching a field that you will import into DONATION, which will be either the Member/Envelope # field, or the Email Address.

 

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 PowerChurch Plus

•Converting Data from Roll Call

•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 CSV file (described below), and then actually import that CSV 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 "Unicode Text" if your data might include accented characters, or characters that are not part of the English alphabet.

 

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. If you currently have the data in a spreadsheet program such as Excel, you can easily export it to CSV with the File ⇒ Save As menu option, using the Save as type "CSV (comma delimited)" or "CSV UTF8 (comma delimited)" if your data might include accented characters, or characters that are not part of the English alphabet. If you specify to import an Excel file, DONATION will actually do that save to CSV for you, and then import the CSV file.

 

Other spreadsheet programs (like OpenOffice Calc) may only have options to save files as CSV - that works too, although you will have to do that saving 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 that can be imported from the file of donations. They can occur in your input file in any order, as explained further below.

 

The table lists the maximum number of characters in each possible column that can be imported, and describes each column. If your data has either the wrong number of columns, or data that has too many characters in some columns, you will get a detailed error message and the import may be cancelled. For some columns where the data to be imported has too many characters, you will be shown how they will be cut down to fit, and allowed to accept that if you wish. If all rows of data have a consistent number of columns that is more than the number indicated by your selected fields to import, you will be given an option to just ignore the extra columns at the end and continue the import. If there is a problem, you will have to fix up your data and try again.

 

 

 

DONATION can actually store several additional fields about Gifts in Kind for Canadian users , particularly appraiser information. Since gifts in kind are generally one-off situations (particularly ones that require appraisal) we do not see a reason that you would want to import them rather than entering them normally in the program, so those appraiser fields cannot be imported.

 

You can create the import 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 at least four fields: one of the first two fields listed above (Member/Env. # or Email), and the Date Received, Total Amount, and Category, though they can be in any order. For any fields you specify that are not filled in, if 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. Then in addition, depending on your selections for including the Cheque # / Paid By, Eligible Amount, Description and Advantage Description fields in the window, each line of the file may need to instead have between 5 and 8 fields in it, or more if you use the Skip Column(s) option.

 

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

 

 

It is important to understand that this procedure does not ever replace or edit any existing donation data. Rather, it always 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 donor (identified by the Member/Envelope # or Email), Date Received, Amount and Category as an existing donation, you receive a detailed message about it and the whole import will be 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 may choose to use the Member/Envelope # to identify the donor that each donation is for. If you do 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 need to 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.

 

Alternatively, you may choose to use the Email to identify the donor that each donation is for. If you do that, obviously the donors for whom you are importing donations must have those email addresses recorded. And you need to ensure that the field for showing the Email in the Donor Details tab of Maintenance ⇒ Main Window Options is checked, since that makes the Email field in the Donor Details area visible.

 

One potential problem with using Email to identify the donor is that it will not work if you are importing donations with an email address that occurs in two or more donors in the current year. If those donors are spouses, one solution may be to merge them with the Database ⇒ Merge Duplicate Donors menu option. That will generally be OK since at least in Canada and the U.S.A., spouses can claim each other's charitable donations on their taxes.

 

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 or Email 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, Descriptions and/or Advantage Descriptions, you must ensure that those fields are displayed on the main window, via the Donation Details tab of Maintenance ⇒ Main Window Options. Otherwise those fields will not be displayed as options on this window.

 

Filling in the Import Window

 

When you select Database ⇒ Import ⇒ Donations, the following window comes up. The options shown are the defaults the first time you use the window.

 

 

In section 1 of the window, you select which fields will be imported from the file, and in which order. It defaults to importing the fields shown in the image above, in that order, the first time you use it. The Donation Fields at the left are all fields that can be imported, that have not yet been added to the Fields to Import list at the right. The list of fields at the right shows all fields that you have selected to import, in the order they are expected to be found in the file from which you will import the data.

 

To import more or fewer fields you can use the Add and Remove buttons, or double-click on a field in either list. The Add button adds a selected field from the Donation Fields list to the Fields to Import list, just below the currently selected field in the Fields to Import List. The Remove button removes a selected field from the Fields to Import list and puts it back into the Donation Fields list. Alternatively, double-clicking fields in either list will move them to the other list, exactly as if you had clicked the Add or Remove button. Certain combinations of columns are impossible, and some columns are required - the program will tell you if you are trying to make a field selection that is not allowed. Please read any messages that come up about this carefully.

 

One definite rule you may encounter is that only one of the Member/Env. # and Email fields can be specified as being part of your import. That field will be what is used to identify the donor that the donation is for. If you try to Add or Remove one of those two fields to or from the Fields to Import list, the other one will replace it automatically, after you confirm that that is OK.

 

If the Fields to Import on the right are in the wrong order, so that they don't correspond to the order in the input file, single-click on one you want to move and use the Move Up or Move Down buttons to re-arrange them. You can alternatively click and drag fields up and down in that list. (Click on a field, keep holding the mouse button down and move it to the desired position, then release the mouse button.) The Donation Fields list on the left is always in alphabetical order, for ease of finding fields in it.

 

Figuring out which Columns of your File to Import into which Donation Fields

 

Ultimately it is your responsibility to analyze the data in the file you want to import, and correctly associate it with the desired Fields to Import, in the correct order. Most errors you make as you try to get this to work will be identified by error messages, or visible when you are shown the data to be imported later in the process.

 

If you absolutely cannot figure this out yourself and need assistance, we can help, but potentially complex work like that would not be part of our normal paid support expectations. It would be charged, after getting your agreement of course, at our normal hourly rate.

 

Skipping Columns

 

The "Skip Column(s)" field from the Donation Fields list is not a real donation field. Adding it will prompt you for a number of columns in the input file to skip. An entry such as "Skip 1 Column" or "Skip N Columns" (where N is a number) will then appear in Fields to Import list at the right, based on what you entered. Where you position it in that list with the Move Up or Move Down buttons (or drag and drop) will determine where extra columns are expected to occur, that should be ignored when importing.

 

If there are extra columns at the end of the lines in the import file that you do not need to import, you don't actually have to add a "Skip Column(s)" for that. Instead, when you do the import, you will be informed that there are more columns in the import file than you have specified to be imported, and asked to confirm that you are OK with that. If you confirm that, the import will proceed, ignoring those columns at the end. However, if you want to avoid that question, you certainly can add an appropriate "Skip Column(s)" at the end of your Fields to Import list to account for all of the fields to be ignored at the end of each line.

 

Other Sections of the Window

 

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 window 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. (However, see also Recurring Donations for another way to handle such donations!) 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 - whether 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/2020, and you are importing for February 2020 - a leap year - it will be imported as 02/29/2020.)

 

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.

 

The options in section 5 determine what is imported into the Cheque # / Paid By field. If you have selected that as one of the Fields to Import in section 1, the From Imported Field radio button in this section will be selected, and no other radio button options here will be allowed. If you do not select that field as one of the Fields to Import, you have three other fairly self-explanatory options:

 

•Empty: don't put anything into that field

•"Pre-Receipted": put exactly that value into the field, which will cause the imported donations to never be included in official receipts. (See Non Receiptable Donors and Donations for details.) This would be used for importing from any 3rd party service that sends out the official receipts itself, such as CanadaHelps.org.

•Specified Value: If you check this, you have to enter your desired value in the entry field following it, and that value will be put into the Cheque # / Paid By field for all donations you import.

 

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 list of Fields to Import and 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 5, 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 a window. You should print that list off for later fixing, then close that window. 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 stop the importing process.

 

Entirely blank lines in the input file will be skipped. They are not treated as errors.

 

The The Fields to Import, Import File and the choices you made in the other sections of this window are saved when you click Import, and restored on any future visit to this window, so if you have used it with different options before, you may see different settings than the ones above when you open it again.

 

There is also an option to load and save named sets of settings, which is useful if you have to regularly import files from different sources, with different sets of fields in them. See below for details.

 

New Categories

 

If there are Category values in the import file that have not been imported before, and do not match any category descriptions that you already have in DONATION, a window for dealing with that will be displayed. It allows you to associate the new ones with existing categories, or create your desired shorter forms (maximum 20 characters) for new categories.

 

See Importing New Categories for full details.

 

Handling Errors and Warnings and Continuing

 

If there are unfixable problems on the first data line (after any column header lines) they will be shown to you immediately, rather than analyzing the rest of the input file first, because it probably indicates that your settings in this window are incorrect - wrong number of columns, columns in the wrong order, etc.

 

However, if there are more columns on the first data line of the input file than you have specified in the Fields to Import, you will be given an option to import the file anyways, ignoring the extra columns. All rows of the file must have the same number of columns as every other row!

 

That file displayed to you in the window 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, if there were no unfixable problems. Take a good look at it. Make sure the right data is shown with the right fields, 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 optional columns Cheque # / Paid By, Eligible Amount, Description and Advantage Description, whether or not you are importing those fields or displaying them on the program's main window. 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, 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 the window 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!

 

Loading and Saving Settings

 

As mentioned above, when you click Import, your settings in this window are automatically saved, and automatically restored the next time this window is opened.

 

However, some users may need to regularly import donations from more than one external source, where the files have different fields in them, in different orders. The Load Settings and Save Settings allow you to save and re-load your settings for multiple import files.

 

Once you have your settings set up as desired for one import file, click Save Settings to save them. You will be prompted for a name to save them with. If you were for instance using this for importing donations exported from PayPal, you might use "PayPal" as that name.

 

Then when you return to this window in the future, if you most recently used other settings, those settings will be shown. In that case, you can click Load Settings to re-load any settings you saved and named before. If there is more than one saved set of named settings, you will be prompted with a list, so you can choose which one to load. That list also has a Delete button that allows you to delete a previously saved set of named settings.

 

If you have either loaded or saved settings during one use of this window, and then you do an Import, your current settings will, as always, be automatically saved, and then automatically loaded the next time this window is opened. In addition, if you have changed the settings at all since the load or save, you will be prompted as to whether you also wish to re-save them to the last named settings that you loaded or saved.

 

---

This topic was last edited on Sep 17, 2024