Importing Donors and Donations from One File

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

 

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:

 

ImportBothWindow

 

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.

 

Field

Max Length

Description

Business Name

60

If the donor is a business or organization, the name of that business

Last Name

30

The donor's last name, or if the donor is an organization, the last name of a contact at the organization

First Name

30

The donor's first name, or if the donor is an organization, the first name of a contact at the organization

Name: First Last

61

The first and last names of the donor, combined into one field (for example "John Smith"). If multiple words are found in this field, the last word is considered to be the last name, and whatever precedes it is considered to be the first name.

Name: Last, First

62

The last and first names of a donor, combined into one field, separated by a comma (for example "Smith, John")

Title

15

A title (such as "Mr." or "Ms.") to come before the donor's First Name

Middle Initial

8

A middle initial to go after the donor's First Name

Addr1

40

The first line of the donor's address (required for Canadian users)

Addr2

40

The second line of the donor's address

Addr3

40

The third line of the donor's address (may contain the postal code if the Postal Code field is not used)

Addr2 (Merge 2 Fields)

40

Merges two fields from the input file (generally City and State/Province) into the Addr2 field in DONATION. You can use this or the regular Addr2 field, never both.

Addr3 (Merge 2 Fields)

40

Merges two fields from the input file (generally City and State/Province) into the Addr3 field in DONATION. You can use this or the regular Addr3 field, never both.

Postal Code

10

The Postal Code or Zip Code of the donor

Phone

18

The donor's phone number. Import values like 1234567 will be reformatted and saved as 123-4567, and values like 1234567890 will be saved as (123) 456-7890.

Charitable Number

20

If the donor is another charity, their charitable number

Member/Env. #

9

A member or envelope number for the donor

Other1

50

Can be used for any consistent information, for example you might use this one for a fax number

Other2

50

Can be used for any consistent information, for example you might use this one for a 2nd email address

Other3

50

Can be used for any consistent information, for example you might use this one for children's names

Other4

50

Same, but only visible if you check a checkbox to show it in the Maintenance Main Window Options window.

Other5

50

Same as Other4

Other6

50

Same as Other4.

Comments

Any length

Any desired free-form text about this donor

Pledge Amount

Dollar amount

An annual pledge amount that the donor has pledged to donate. May contain commas at the thousands, but may not include a dollar sign.

Email

60

Email address

Donor Category 1

20

Donor Category 1 value. It will be added if it is not already present in the pull-down list for that field, which is specified with the Maintenance Donor Category 1 menu option. (The match can be case-insensitive, so for instance "MEMBER" would match an existing value of "Member".)

Donor Category 2

20

Donor Category 2 value. It will be added if it is not already present in the pull-down list for that field, which is specified with the Maintenance ⇒ Donor Category 2 menu option. (The match can be case-insensitive, so for instance "MEMBER" would match an existing value of "Member".)

Date Received

n/a

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.

Total Amount

n/a

The total amount of the donation given by the donor. Decimal places and cents are optional.

Donation Category

128

The description of this donation category. If a new category is encountered, you will be prompted with a window where you can choose whether to add it to the list maintained with the Maintenance ⇒ Donation Categories menu option, or translate it to one of those existing categories.

The actual maximum category length is 20, but they can be truncated from longer imported values.

See New Categories below for more details.

Cheque Number /
Paid By

20

The cheque number that this donation was paid with, or any text describing how the donation was paid. Common 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, or automatically overwritten with "Pre-Receipted" or another value, depending on the options you pick in section 5 of the window.

Eligible Amount

n/a

In the case of Split Receipting in Canada, the eligible amount of the donation for tax purposes. Only enter this if it is less than the Total Amount.

Description

50

The Description of the donation.

Advantage Description

50

Only needed for Gifts in Kind in Canada when there is also an Eligible Amount less than the Total Amount. See also Split Receipting.

Skip Column(s)

n/a

See below for details about skipping columns in the input file.

 

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:

 

ImportBothSection4

 

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 be completely ignored for import purposes, as if they were not there. 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.

 

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 the Notepad program. You should print that list off 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 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 donation category descriptions that you already have in DONATION, a window such as the following 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.

 

ImportingCategoriesWindow

 

For each displayed row, the Imported Category Name is what was found in the import file, the New Category Name is the same (or the same cut down to at most 20 characters), and the Existing Category starts out as <Add New>.

 

You can leave <Add New> alone, and edit the New Category Name to be the way you want it.

 

Or, if you think the imported name for one that starts out with <Add New> is really just another name for an existing category in your database (perhaps "Library" for the first row in the sample above), you can pull down the Existing Category drop-down and pick the existing category to match it to. Then all categories in the import file with the displayed Imported Category Name will instead be set to have the existing category.

 

Click OK to save any changes you have made, or to accept what is displayed even if you have not made any changes. The import will then proceed.

 

If you feel you have made any mistakes in your associations of imported category names to new or existing categories in the program, you can later come back and fix those associations with the Maintenance Imported Category Conversions menu option.

 

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 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, 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 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!

 

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 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 Sep 22, 2020