PLEASE NOTE: READ ALL INSTRUCTIONS AND MAKE A BACKUP OF YOUR DATABASE BEFORE ATTEMPTING AN IMPORT!
The complexity or ease of your import will rest mainly on the integrity of the data file which contains the information used to perform the import.
Before importing into Fundraising 50, we suggest backing up your database. Fundraising 50 does have a utility to remove (Undo) imported data; however, any manual changes made to imported records will negate the ability to utilize the Undo Import utility
For specific information regarding running the backup utility, see KB article 3916.
For specific information regarding restoring from a backup, see KB article 9166.
The Import (data) File:
The Fundraising 50 Import Wizard will only accept a delimited file. A delimited file may contain a comma, space, tab or vertical bar separating the data.
The most common type of delimited files are text or spreadsheets files; text files will have an extension of .txt while a spreadsheet will have an extension of .CSV (comma separated value). We suggest the use of a file in the .csv format for ease of reading the data for troubleshooting as well as initial reviewing purposes.
If the data you wish to import is in an Excel format (file extension is .xls rather than .csv), open the Excel file and from the toolbar select File>Save As. Retain the current file name but select CSV (Comma delimited) (*.CSV) from the Save as file type drop down, then select Save.
Once the file is saved in a delimited format, make certain your file contains the fields indicated below which the system will utilize to determine if the data is to be imported into an existing record or a new record needs to be created.
It is highly recommended to create a ‘mini-file’ of the data you are going to import. If you have 500 rows of data to import, copy the first 10 rows or so. Use that file to setup your mapping, duplicate checks, etc. The import validation process reviews each and every row of data, so if you have a single item incorrect and have 500 rows of data to validate, the system will go through all 500 rows only to return a validation report that has a single error message repeated 500 times. Much easier and quicker to find such issues using only 10 rows of data. Once your validation passes, you can save your Import setup and then import the 500 row spreadsheet.
Fundraising 50 requires the following minimum information to determine whether a constituent currently exists in the database:
Record Type *
Last Name **
ID Number or Alt ID
*Required only if you are importing data to both Individual and organization type records at the same time.
**For organization type records, the Last Name field is the equivalent of the Organization Name. If the data file includes a separate column listing the Organization name in addition to a column for the Constituent’s last name, you will map the Organization name column to the Organization name field on the Organization type Basic card. You may also choose to import Organization data separately.
The above fields will be used by the system to determine which specific record the data will be added to or whether a new record should be created. These fields are the minimum in order for the system to make that determination.
Most likely you will have additional basic card field data to be imported for new records, but those may or may not be used to assist the system during the duplicate check validation process.
In the following example, we will use a spreadsheet with a CSV file extension.
The CSV file contains both Individual and Organization records so a Record Type value is included on the spreadsheet. You can populate this column with an O for Organization and I for Individual or use the actual word.
Lines of data that have embedded carriage returns as well as illegal characters such as tildes (~), commas (,), dollar signs ($), apostrophes (‘)…etc, will cause problems during the import process. We recommend deleting any extra-embedded carriage returns and cleaning up illegal characters.
If you are in a network environment, performing the import at the server will provide local access to the database rather than over the network which will result in a quicker import. If importing at a workstation location, consider closing all applications at the local workstation except for Fundraising 50.
Once the data file has been reviewed for accuracy, also ensure your data does not have any major issues. Check that there isn’t a phone number in the gift amount column or mistyped values for the Solicitation, Campaign or Fund codes and is indeed ready for import. Have all users log out of Fundraising 50 at any other location before starting the import process.
Navigate to: Administration>Import Wizard.
The Import Wizard will launch.
Under Step 1 portion of screen:
Select Open to locate the file to be imported. By default, the Import Wizard opens within the Fundraising 50 directory on your computer. If your file is not located in this directory, use the “Look in” drop down to locate the file. The “Files of type” dropdown is defaulted to ACII Comma Delimited Files (*CSV)
Once the file is located and the Open button selected, the window above will close and the file path will display next to the Open button in Fundraising 50.
Under Step 2 portion of screen:
If you have a previously created and saved Import Definition for importing this particular type of data, then use the drop down menu and select the appropriate definition; otherwise accept the default of <NEW> which indicates you will be mapping your import from scratch.
If the import file does not contain a header row then uncheck “Import file has header row” otherwise leave the default checked.
Dependent upon the import type, under the “Pledges” heading you may need to uncheck “Create Matching Pledges” if the file does not contain pledge data. Select OK to proceed to the next screen in the Import wizard.
Mapping the Basic Card
By default, the Import Wizard displays the Basic card and has the radio button selected for an Individual type constituent. The top of the screen displays the full range of card types found in a constituent’s record for which an import can be performed.
The import mapping process consists of simply dragging one of the columns contained in your import file (displayed in the far right column) to the appropriate card and field.
In order for the system to ‘tie’ or link the data to be imported to the correct constituent record, you must at a minimum map either the Constituent ID or Alt ID Number to the appropriate field on the Basic card as well as the Last Name field.
When importing data for both Individual and Organization type constituents, you would also need to map the Type field (referring to Record Type, i.e. Individual or Organization) on the Basic card.
If your import consists of new constituents, you do not need to map the city and state fields, mapping the zip code field alone will allow the system to automatically populate the city, state associated with that zip code based on postal records.
If you map the Address, City, State, and Zip Code fields (in addition to the minimum required fields) the system may either treat the import file as new records or not import the records as you expect. If you are including the Constituent ID or Alt ID as the ‘linking’ mechanism to tie the import data to the proper constituent records.
If your import file does not contain the Constituent ID or Alt ID values, you can link using First Name, Address, or Phone Number. However, accuracy decreases when using one of these alternatives. The likelihood of record mismatches will increase.
The Phone Number field on your import file must appear exactly as it is in your database, however, the First Name and Address can be designated as Similar.
For example, if you are linking via First and Last Name and your import file contains Robert Smith but your database contains the name Bob Smith there are several scenarios that may take place dependent upon how your Duplicate Check options are set:
Data will not import (Duplicate check: first/last names exact)
There are multiple constituent records for Robert Smith
Data imports to incorrect record; (Duplicate check: first/last names exact)
There is both a Bob Smith and Robert Smith in your database and the data relates to Bob Smith but your import file listed Robert Smith as the value
System creates new constituent record: (Duplicate check: first/last names exact)
Data relates to Bob Smith but database only contains a Constituent listing for Robert Smith
If your file contains data for both Individual and Organization type constituents, after you have mapped the Basic card fields for your Individual type constituents, change the radio button to the Organization designation.
If your data file is segregated as to Record type, then skip to the section titled Mapping the specific card.
On the Basic card for an Organization type constituent, map ID, Type, and Last Name; even though these fields appear grayed out they are still available to map.
Mapping the remaining Data to specific cards
You are now ready to map your remaining data.
Select the appropriate card and map, at a minimum, all required fields. The asterisks denote which fields are required on each card type. You simple move from one card to the next until all your data has been mapped to the appropriate card.
Once a card type is mapped, you can use the VCR buttons to have the system display how the actual data will be displayed on this particular card. This allows a visual review to catch any obvious incorrect mapping.
Setting the Duplicate Check options
Once you have mapped your data to the appropriate card(s), before you validate your import, you need to review your Duplicate Check options.
Make certain the Duplicate check is set to “Exact” on Last Name and Alt ID or any other field you may to using to locate existing constituents - all other fields should be set to Ignore.
If you are substituting Phone Number for Alt ID then that field should be set to Exact along with the Last Name field; all other fields are set to Ignore.
If you are substituting First Name, Address, then set those to Similar and Last Name set to Exact. If you do not select the “Ignore” option for all other duplicate check fields, Fundraising 50 will interpret the import as if you intend to only add new Constituent records instead of adding gifts to existing Constituent records when they are found.
Validating the Import
Select Validate once the Duplicate Check options are established. The system will display the message below during the validation process signifying that it is comparing your import file to your existing database Constituent records.
Dependent upon your import file size, number of Constituents in your database and the capacity of your network, the Validation process may take a few minutes to several hours. In rare instances, the system might display (Not Responding). Do not stop the import process or close the program; the system is still in the process of validating your import file against the database.
It is highly suggested that you test a sample of your import data first, which allows for a quicker validation process, that way if you have a single item mapped incorrectly, the system doesn’t return an identical error message for all 500 rows of data in your file. Quicker and easier to validate 10 rows and discover any error that way. You can then make any necessary correction, save your import mapping and then import the actual data.
During the course of Validation, you may receive the following window.
The system is informing you there are Codes in your data file that do not exist in your database.
Select the blue hyper-link to view the specific codes the system flagged as not existing in the database and will be added if you select the Add option.
If you discover the codes do exist in your database in a slightly different format or spelling, please cancel the import. Adjust your import file by matching the values in your data file to those in your database. If you instead decide to proceed with the import and add these codes, you will establish new values that are similar to existing values and manual clean up will be required.
When the Validation process is complete, the following message will display.
In this window, the line “Number of duplicates found” means that the constituent records indicated in your data file (verified using the Alt ID and Last Name fields) directly match constituent records in the database. When you are importing data into existing records you want the duplicates found number to match the valid rows number. In this example the system is indicating it found 2467 records that matched the constituents indicated in the import data file.
If this file had consisted of 2000 gifts for existing constituents and 467 gifts for new constituents, the Number of duplicates found would indicate 2000. The number of valid rows would remain the same.
If the View Errors button is active, you can view the records that the system has rejected.
The View Warnings button is a listing of records that will be imported but have minor issues which will need correction afterwards.
Importing the data
At this time, you can select “Import”.
Once the import is complete, the system indicates the number of imported rows as well as the number and type of cards added. Referring to the previous scenario, if this data file had contained gifts for new constituent records, the Number of New Constituents added field would be populated.
Once you select “Close”, the system will alert you to save the file definition or Cancel; Cancel does not save the file definition. You may want to save the definition if you import data frequently into the system and your data file will be in the same format as the data just imported.
Reviewing the imported data
We suggest you check your database to ensure the data imported as expected. To do this, navigate to a Constituent query screen and run the following query based on the appropriate card:
In this example, we are checking for imported gift cards. From the Browse list created, select a record, navigate to the Gift tab and open one of the gift records created by the import. Verify the fields contain ‘logical’ information. If you discover that information was imported incorrectly, you can use the Undo Import utility. You would run the same type query for each card type imported.
Again, please note if any manual changes are made to any card created from this import, you will not be able to utilize the Undo Import utility. Your only option would be to restore to the backup you made before importing into the database.
Completing table maintenance
If new codes were added to your tables during the Import process, dependent upon the field (table), you may need to complete the table entry for those codes. Any value which was created during the import that is for a field that is ‘tied’ to another field (Solicitation and Campaign or Fund and Purpose) will display the value “<Created by Import>”.
The corresponding newly created code will exist in its table but the ‘link’ between the two values will need to be established.
Please check the following tables in Table Maintenance: Campaign, Solicitation, Method, Purpose, and Fund. Each will have the <Created by Import> value.
These can then be substituted for the correct value from the corresponding table.
For example, the import contained a new Solicitation value titled 09 Fall Mailing, which is linked to the new Campaign value titled 09 Annual Campaign. Your Solicitation table will list the new value it created (09 Fall Mailing) but the campaign column and solicitation method column in this table will both have values of <Created by Import>.
Merely navigate to the row listing the 09 Fall Mailing solicitation code and select the correct Campaign and Solicitation Method from the drop down.
This same scenario exists between the Fund and Purpose table. Once you have ‘re-linked’ your new codes to the proper corresponding codes, you can then delete the <Created by Import> values from all tables.