Importing Donors from Other Programs

<< <%SKIN-STRTRANS-SYNTOC%> >>

Navigation:  INSTALLATION INSTRUCTIONS >

    Importing Donors from Other Programs

It is possible to import donors exported from other programs into DONATION using the Database ⇒ Import Donors menu option.

 

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

 

There are special menu options for importing 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 (See also below for further details.)

Converting Data from GiftWorks

Converting Data from The Church Assistant

Importing Data from Viansoft Church Contribution System

Converting Data from Geminon

 

With the Database ⇒ Import Donors option, you can import Excel files, tab-separated text files, or comma-separated value (CSV) files. They can contain any combination of the fields in DONATION's Donor table, in any order. The only fields that must be filled in are a field or fields for the donor's name, and Addr1.

 

Note: You cannot import text, CSV or Excel files that contain both donor and donation information together. To import donation information separately, see Importing Donations from Other Programs or Regular Donations. 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.

 

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 donor is on its own line, and each field on the line (even empty ones) is separated by one tab character. Fields may optionally be enclosed in quotation marks, but that is not necessary. It may have a row of column headers at the top if you wish. 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 of course just let DONATION do that for you.

 

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.

 

Starting in version 3.80 of DONATION, this procedure can replace or update any existing data, as long as you check the "Allow updating existing donors ..." checkbox. (Prior to that it could only always add donors.) The updating is based on matching the Member/Envelope Number in the input file to that field in the existing donors. If you don't check that checkbox and you try to import donors with a Member/Envelope Number that matches an existing donor, the import will not be allowed.

 

All existing donors will always stay there when you use this procedure. In fact, you can end up with two copies of each donor if you import the same file twice by accident, and it doesn't have Member/Envelope Numbers that allow the program to notice the duplication! This process will not allow duplicate Member/Envelope Numbers, unless you choose to do updating, and will also warn you if there are names matching existing donors, that might create duplicates.

 

The importing only affects the current year's donor list. (To understand about the fact that each year's donor list is distinct, please see Changing Years in the Database.)

 

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

 

ImportDonorsWindow

 

The values you select in this dialog box are saved when you click Import, and restored on any future visit to this dialog box, so if you have used it before, you may see different settings than the ones above when you open it again.

 

Filling in the Fields on this Window

 

In section 1 of the dialog box, 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, and Addr3, in that order, the first time you use it. The Donor List 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 text file from which you will import the data.

 

To import more or fewer fields you can use the Add and Remove buttons, or double-clicking. The Add button adds a selected field from the Donor Fields list to 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 Fields list. Alternatively, double-clicking fields in either list will move them to the other list. 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. The Donor Fields list on the left is always in alphabetical order, for ease of 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 will be canceled. (The one exception is that 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.) You will then 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")

Addr1

40

The first line of the donor's address

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

Charitable Number

20

If the donor is another charity, their charitable number

Member Number

9

A member or envelope number for the donor (required if you are also planning to import donations)

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

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

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

 

Note: You must import either a business name or both the last and first name for each donor (or all three). Therefore 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 Addr1 field must always be among the selected fields for importing. For Canadian users, at least one address 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 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.

 

If you intend to also import donations, you must import the Member Number field, because that field is also included in the donations import file. It is how the donations are matched up to their donors.

 

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 text file (*.txt), either type of Excel file (*.xls or the newer version, *.xlsx), or a comma-separated value file (*.csv).

 

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

 

In section 4, there is a checkbox to specify whether to allow updating existing donors, by matching on the Member/Envelope Number field. If this is not checked, your import will be rejected if the Member/Envelope Number field is being imported and any of those numbers in the import file match existing donors in the current year's donor list. If it is checked, inputs in your file can update fields in existing donors that are matched by Member/Envelope Number.

 

Starting 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, 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 missing Last Name, a missing address, data in a field that is too long for the field lengths in DONATION, or a duplicate member/envelope number if you aren't updating.

 

If problems were found that are fixable, a fixed file will be created, with overly long names shortened, missing fields that are required in DONATION filled in with fake values, etc. Its name will be re-entered into the Import File entry box for you. To try importing the fixed file, click the Import button again.

 

If you selected to allow updating existing donors, and the program detects that some donors will in fact be updated, you will be asked to confirm that you want to do that.

 

If duplicate names are detected, matching existing names, that aren't being updated based on a matching member number, you will be told about that, and asked to review it carefully before confirming the import.

 

Reviewing your Data

 

Next, the data to be imported will be displayed for you. Take a good look at it. Make sure the right data is shown with the right fields, and that the row of headers from your data is 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 you expected to be displayed are in fact displayed.

 

The first column (Status) shows "New" if this row will be imported as a new donor. It can also show "Duplicate?" if this row will be imported as a new donor, but it has the exact same name (except possibly for different capitalization) as an existing donor. That could be an error, or of course you could be adding a second person with a common name like "John Smith"!

 

If you have selected to allow updating existing donors, the Status column can show "Updated" if it will update an existing donor that was matched by Member/Envelope Number, or "Unchanged" if it is a match to an existing donor but no changes were observed.

 

If everything looks fine, click Import 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.

 

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

 

If errors were shown in Notepad and you printed them off, you should now go back to each donor that had an error displayed in that printout, and decide how you wish to fix it in the original input file.

 

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:

 

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

A field that contains exactly the value "empty" 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.

 

Possible Problems with Newlines in Cells in Excel Files

 

We have noticed that in some Excel files invisible characters such as newlines have been introduced in the column data. They can be introduced by the use of Alt+Enter when you are entering the data in Excel. This adds an extra line within one cell, and thus in its row. That does not cause any particular problem in Excel itself. But when this data is exported to Tab-delimited text, for importing into DONATION, the introduction of the extra line causes the file not to import successfully.

If you do run the import with the Excel file, instead of a Tab-delimited text file, DONATION will be able to detect the existence of the newline character in your data and will give you a message box saying which cell contains the the first instance of a newline, and not proceed with the import. You will then have to find all such occurrences and fix them in Excel before you can do the import successfully, by doing the following

1.First, open the Excel file. If you see wrapping in any fields in any columns drag the right edge of that column wider so you don’t see any wrapping.

2.Next, highlight all of the rows and columns in your file with the mouse, or by simply pressing Ctrl+A. Then on the Home ribbon, in the area for Cells, click the Format drop-down, and pick Auto-Fit Row Height from the drop-down menu.

3.By next clicking on Wrap Text in the Home ribbon, you can see all the rows with newlines introduced in columns in the data. At this point, you can move to all those individual cells to delete the still invisible newline characters to fix the problem. (Press F2 to edit the cell in place to fix it, being sure to get rid of any newlines within, before, or at the end of the text.)

 

Importing from Membership Plus or other programs whose fields don't match DONATION's organization

 

First, please note that from all versions of Membership Plus that we have encountered, you can use the Database ⇒ Import ⇒ From Membership Plus menu option to import more than just the donors - it imports your organization info, donation categories (funds), donors (members) and donations (contributions).

 

If you do want to import only your donors from Membership Plus, or you have a version of Membership Plus that the menu option mentioned above doesn't work for, you can use the Database Import Donors menu option described above.

 

First, use Membership Plus' Export Wizard to export the donor fields to an Excel file. You may want to open that file to confirm that the fields are organized in a way that can be imported into DONATION, based on the list of columns above.

 

The only complication is that in Membership Plus, there are separate City and State fields. You handle that in DONATION by importing them using the "Addr2 (Merge 2 Fields)" field, or if you have two lines of address preceding those fields, the "Addr3 (Merge 2 Fields)" field.

 

Other than that, the import should be straightforward.

 

Other programs (or simple custom Excel donor and donation-tracking spreadsheets) may have their donor names merged into one field, instead of separated into First Name and Last Name fields as in DONATION. Use the "Name: First Last" or "Name: Last, First" import fields in this window to handle that situation, depending on whether the donor names were stored as "John Smith" or "Smith, John" respectively.