<< Click to Display Table of Contents >> Navigation:  INSTALLATION INSTRUCTIONS > Importing Donors and Donations from One File

Sometimes you will have an import file that contains both donor and donation information on each line of the file, and you want to import it in one step. The most likely situation where you would want to do this is if your organization accepts online donations from a 3rd-party service, such as CanadaHelps.org, EasyTithe or PayPal, that have exporting options that export both donor and donation information across one line of a file.

 

You may be able to do that importing with the Database ⇒ Import ⇒ Donors and Donations menu option described here.

 

If you are using the Abundant online donation system, there are separate features for configuring it and importing from it described in the Abundant Help topics. They do not use the Database ⇒ Import ⇒ Donors and Donations menu option.

 

It would likely also be possible to import the same file with separate uses of the Database ⇒ Import ⇒ Donors menu option and the Database ⇒ Import ⇒ Donations menu option on the same file, with appropriate choices of options in the windows that come up. Before trying that, you would need to thoroughly understand both of those features! But it will usually be simpler to use Database ⇒ Import ⇒ Donors and Donations.

 

You will have to carefully examine the columns in such a file to figure out how to correctly set up the options on this window.

 

The initial setup for these imports will often be quite complicated. So, unless this is the only importing of donors and donations from one file that you do, you will want to save the settings in the import window with the Save Settings button once you have them working right. (The import window remembers the last settings you used, including the lists of Fields to Import, the filename of the import file and the choices you made for the other sections of the window. It also redisplays them the next time you use the window.) When you come back to this window, if the displayed settings aren't the ones you want for this type of file, you can load those settings with the Load Settings button.

 

If you are doing this sort of importing, but you also have recurring donations for existing donors in your program that would otherwise want to import, we recommend doing them with the separate Recurring Donations feature.

 

The Window and its Options

 

Here is a sample filled-in window from Database ⇒ Import ⇒ Donors and Donations:

 

 

If you have used either or both of the Database ⇒ Import ⇒ Donors menu option and the Database ⇒ Import ⇒ Donations menu option before, you will see that the options on this window are basically a combination of the options from those two windows. The list of available Donor and Donations Fields that you can import is a combination of the fields available in those to windows, with the following minor changes for clarity:

 

•Category, from the list of Donation fields, is renamed to Donation Category.

•Category 1 and Category2, from the list Donor fields, are renamed to Donor Category 1 and Donor Category 2.

 

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 Last Name, First Name, Addr1, Addr2, Addr3, Member/Env. # (or if you are choosing not to display that field, Email), Date Received, Total Amount, Donation Category, and Cheque # / Paid By, in that order, the first time you use it. The Donor and 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 Donor and 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 Donor and 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.

 

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 Donor and Donation Fields list on the left is always in alphabetical order, to simplify finding fields in it.

 

The following 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.

 

 

The Donor fields are shown first in the table above, then the Donation fields, and finally the Skip Column(s) field (details below). As mentioned above though, you can import them in any order, and in the Donor and Donations Fields box, they are shown in alphabetical order.

 

Note: You must import either a business name or both the last and first name for each donor (or all three). So those fields (either a business name, or both the first and last names) must be filled in on every row, in the input file. The first and last name can be input as separate fields, or via the combined fields "Name: First Last" (which will import things like "John Smith") or "Name: Last, First" (which will import things like "Smith, John").

 

For Canadian users, at least the Addr1 field must be filled in on every row in the input file, because the Canada Revenue Agency requires donors' addresses to be printed on charitable receipts.

 

If multiple address fields are being imported, and earlier ones (like Addr1) are empty in the input data while later ones (like Addr2) are filled in, they will be moved up, for instance moving the Addr2 value into the empty Addr1 field, etc.

 

To match the donors to existing donors in your database, and to keep the donors together with their donations, you must import at least one of the Member/Env. # field and the Email Address field. You specify which of those two fields is used to match donors being imported to existing donors in your database, with an option in Section 4 of the window, which is described below.

 

You also must import at least the Date Received, Total Amount and Donation Category fields, which are the minimum required fields for donations.

 

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.

 

Figuring out which Columns of your File to Import into which Donor and 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 Donor and Donation Fields list is of course not really a donor or 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.

 

Sections 2 and 3

 

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).

 

If you select an Excel file to import, DONATION will have Excel invisibly save it to a comma-separated value (CSV) file (described below), and then actually import that CSV file.

 

A tab-separated text file is a plain-text file where each donor and donation is on its own line, and each field on the line (even optional fields that have no data for the current donor or 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.

 

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 right 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).

 

Section 4 Options

 

This section looks as follows, as a reminder:

 

 

The example above is the initial default, which is to use the Email Address as the donor identifier, and updated existing donors from data in the import file. That is likely to be the right choice for many import files that contain both donor and donation information on each line.

 

If you know that your input file contains a number field matching Member / Envelope Numbers of existing donors in your program, rather than an email address field matching them, you can  select the radio button for that field on the first row here.

 

If you are using the Email Address as the unique ID, you will need to make sure that any email addresses in the import file, that are for donors already in your Donor List in DONATION, are actually recorded as that donor's email address in the program. (Otherwise, they will be added as new donors, causing duplication!) Of course, the same applies to Member / Envelope Numbers, if you use that as the unique ID.

 

The next two radio buttons determine what happens if an input line contains a value of that unique ID matching an existing donor in the current year's Donor List. Here is what each of those options means:

 

•Update existing donors from data in matching rows from the file: With this option, for rows to be imported that match existing donors in the Donor List on your selected unique ID field (Member/Envelope # or Email Address), filled-in field data in the import file will update (overwrite) your existing donor data. Use this option if you believe that the data in the import file is likely to be more correct (or at least as correct!) as your existing data in the Donor List. Entirely empty fields in the input will not overwrite existing data. See also under Special Processing for Empty Fields when Updating below, though, for a way to force input values to clear existing donor field data.
 

•Ignore rows from the file matching existing donors: With this option, rows in the import file that match existing donors in the Donor List on your selected unique ID field (Member/Envelope # or Email Address) will not update the existing donor data. Use this option if you believe that your existing data in the Donor List is more likely to be correct than whatever data you are importing. The donor information on such rows will still import!

 

With either of those radio button options, any donors in the import file that do not match existing donors in your Donor List on the unique ID will be added to the Donor List.

 

Note: If you use Database ⇒ Import ⇒ Donors menu option separately, it has another radio button option for this matching, Add all rows from the file as new donors. That seems very unlikely to be the right action for imports of donors and donations together, so it is not an option on this window.

 

One combination to be aware of is if you pick the Email Address as the Unique ID field, and select Update existing donors from data in matching rows from the file. In that case, if you have two or more existing donors with the same email address as a row in the input file, that will be an error and the import will not be able to proceed. That's because the program cannot know which of the existing donors it should be updating, or recording donations for! Most commonly this would be a problem if you have spouses recorded as separate donors, and they share an email address. One solution to this would be to merge them into one donor (since at least in Canada and the U.S.A. they can share each other's tax receipts anyways!), with the Database ⇒ Merge Duplicate Donors menu option.

 

The same donor (as identified by the unique ID) may of course appear on multiple rows of the input file, if they have made multiple donations.  In that case, you would not want to attempt to import or update that donor more than once! So, only the first row in the input file with a given value of your selected unique ID field will be imported or updated. The donor information on all further rows with the same unique ID field will be ignored for import purposes, as if they were not there, but the donation information on those rows will still be imported.

 

Section 5 Options

 

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.

 

Special Processing for Empty Fields when Updating

 

There's a problem if you are updating existing donors, in distinguishing whether an empty field means you just aren't updating whatever is already in the Donor Details for that field, or whether it means you want to clear the contents of that field.

 

To allow you to distinguish between those two cases, we came up with a trick:

 

•An input donor field that is actually empty will never replace an existing value in a matched donor being updated. The existing value will still be there after the import.

•An input donor field that contains exactly the value "empty", without the quotes, will cause that field in the matched donor to be cleared.

 

For donors being added, rather than updated, either an empty field or the value "empty" in the field will cause the resulting imported field to be empty.

 

Note: We considered using the more commonly used value "n/a" instead of "empty" for this trick, but the problem with that would be that you would then have no way to import the actual value "n/a" into a field, which you might want! We can't really imagine that anyone would want to import the actual value "empty" into a field.

 

Since donations in the import file are always added (imports cannot update existing donations!) these issues do not apply to donation fields, and there is no special processing as there is for donor fields.

 

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.

 

This is actually done twice, once for the donors to be imported, and once for the donations to be imported. So if there are fixable problems that have been displayed to you for the donors, and you agree to continue with the import, you may have another file displayed to you of any problems with the donations.

 

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

 

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 Donation Categories

 

If there are Donation 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 donation categories, or create your desired shorter forms (maximum 20 characters) for new categories.

 

See Importing New Categories for full details.

 

Note: If you are importing the Donor Category 1 or 2 fields, this type of approach is not taken - any new values are automatically added to the list of available options for those Donor Categories in the program.

 

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. It is displayed in a two-part window, with the donor details to be imported on the top, and the donation details on the bottom.

 

Take a good look at the displayed data in both sections. Make sure the right data is shown with the right fields, and that any rows of headers from your data are not displayed. (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 donors and 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 both parts of the preview window, there are columns for the all importable donor and donation columns, 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, except for donors if you are updating existing donors and the existing donor in the database had values in that field.

 

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 donors and 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.

 

Examples

 

There are separate Help pages with worked-out examples for the following:

 

•Importing Donors and Donations from CanadaHelps

•Importing Donors and Donations from PayPal

•Importing Donors and Donations from Easy Tithe

•Importing Donors and Donations from QuickBooks (These ones actually have to be imported as donors and donations separately.)

 

Please note that those pages do not repeat the full instructions from this page. They only tell you the specifics of the features from this window that you use in those cases.

 

---

This topic was last edited on Feb 17, 2023