Importing Data


Table of Contents   Previous Page  Next Page    

Several types of data can be imported into the database from a delimited ASCII text file. "Delimited" means that each 'chunk' or field of data is separated from the previous chunk by a character of some kind. The best formats are called "tab-delimited" and "comma-delimited." You can usually look at the data to tell how it has been saved. "ASCII" means the file should be plain ASCII text (if you see funny black or white squares in the text -- remove them).

Tab-delimited data is better, because you may have legitimate commas within the text of a field, and these commas could confuse the import engine into thinking a new field has been started.

For example, tab-delimited data might look like this:

 John      Smith      Anytown      PA       75074       United States

But comma-delimited data would look like this:

 John,Smith,Anytown,PA,75074,United States

The first rule to remember about importing data is that if the data you are importing is not consistent, you will probably have trouble getting a clean import. The better the data coming in, the less cleanup you'll have to do afterward. Sometimes it is a good idea to open your entire data file in something like Microsoft Excel, so you can view all the rows and columns of data at once.  Scan through it and look for inconsistencies, stray characters, missing data, etc.  Clean it up as best you can, then save the data to a tab-delimited text file again.

It is also important to remember that the system assumes the record being imported is the way you want it to be.  The only data that is automatically added if not found is for fields that are required in the system. A good example is the Start Date. There is a preference to default the start date to the current date or some specific date, but it does not apply to imports because the Start Date is not required.

Importing from an Excel File

Before you can import data from an Excel file, you must first convert it to a plain text file.  This is done from within Excel itself, as follows:

1Open the file in Excel. (This is a good time to scan your data for inconsistencies, blank rows, etc., and fix any problems you find.)
2Choose File - Save As.
3Depending on your version of Excel, there should be an option at the bottom of the Save As window to set the File Type. Choose "Text (Tab Delimited)" as shown below:
 
4Now the data is in a format that can be imported.

Blank Rows

Make sure your import file doesn't have any blank lines in it.  The import will stop when it finds the blank row, thinking it has reached the last record.

Updates vs New Records (Membership Data Only)

When you import data, the system will make a decision as to whether the data is new, or an update to an existing record.  This is done by looking for the Member ID field in your import map file.  If you are importing a Member ID, the system will check the database for a matching Member ID on an existing record.  If found, data will be used to update the existing record rather than create a new record.

If the data for a field being imported is blank, the system ignores it and does not change the membership record being updated. However, if you really want to import blank data to clear existing data in a field, import the text =CLEAR (that's an equal sign followed by the word 'clear' with no spaces).  The system will respond by clearing any existing data in the target field.

Note: It is a good idea to verify the available State/Province, Country, Phone Type, and Status codes before attempting an import if your data needs to match these values. Each of these code lists are available under the Tools > Dropdown Lists menu.  This is important, because if you import a status called, "Family" and do not have a status in the system by that name, the default status will be used -- possibly generating incorrect data.

Importing Data

1Choose Tools > Database Tools > Import Data.

 A "Choose File" window will open automatically so you can choose the File that contains the data to be imported.  Browse to the appropriate text file, select it, and click Open. (If you receive an error that the file can't be opened, or is already in use, make sure you don't still have it open in Excel, or some other program that was used to create the file.)

 

2When the Import window returns, the file will be displayed in the File field. If you selected the wrong file, click the Browse icon to change it.
3Choose the delimiter used in the text file from the dropdown.
4Specify the type of data you are importing. The data type you specify will determine the fields available for import and also which, if any, fields are required to be part of your import file.
Membership Data - enables you to import new, or update an existing, membership record, including fields on the General, Status, and Custom tabs of the membership record.
Associated Member Data - enables you to import member-to-member relationships, including the Association Type, date, and comment. The following fields are required in the import file:

         Member ID - Must be an ID that matches an existing membership record.
 
 Associated Member ID - Must be an ID that matches an another membership record.
 
Note that the other importable fields with the word "Associated" apply to the Associated Member ID. The ones without the word "Associated" apply to the primary Member ID record.

Friends & Family Data - enables you to import friend/family records to existing membership records. The following fields are required in the import file:

Member ID - Must be an ID that matches an existing membership record.

First Name - The first name of the person being added

Last Name - The last name of the person being added

Assigned Item Data - enables you to import assigned item data. The following fields are required in the import file:
 
 Member ID - Must be an ID that matches an existing membership record.
 
 Assigned Category Name - Must be the name of an existing Group item category.
 
 Assigned Item Name - Must be the name of an existing item under the specified category.
Additional Address Data - (Pro Version only) enables you to import additional (non-current) addresses to existing membership records. The following fields are required in the import file:

Member ID - Must be an ID that matches an existing membership record.

Address Type - The address type to use for the new address record.  Be sure the address type you import actually exists.

Country - The country on the address. This can be a country name or code, i.e., "United States" or "USA" that exists in the list of current country codes.

Dues Data - (Pro Version only) enables you to import dues entries to existing membership records. The following fields are required in the import file:

Member ID - Must be an ID that matches an existing membership record.

Date Posted - The date for the dues entry

Amount Posted - This will be set to 0.00 if not provided

Posting Type - Must be a posting type in the format,  "Dues Charge (D)", i.e., the posting type name, followed by a D or C (debit or credit) in parenthesis, just like it appears on the list of Donations on a membership record.

Donation Data - (Pro Version only) enables you to import donation entries to existing membership records. The following fields are required in the import file:

 
Member ID - Must be an ID that matches an existing membership record.

Date Posted - The date for the dues entry

Amount Posted - This will be set to 0.00 if not provided

Posting Type - Must be a posting type in the format,  "Donation (C)", i.e., the posting type name, followed by a D or C (debit or credit) in parenthesis, just like it appears on the list of Donations on a membership record.

Group Data - enables you to import a new Group record, including any field on the General and Custom tabs of the group record.
Associated Group Data - enables you to import new member-to-group relationships. The following fields are required in the import file:
 
 Member ID - Must be an ID that matches an existing membership record.
 
 Group Name - Must be a name that matches an existing group record.
Assigned Item Data - enables you to import new assigned item data. The following fields are required in the import file:
 
 Group Name - Must be a name that matches an existing group record.
 
 Assigned Category Name - Must be the name of an existing Group item category.
 
 Assigned Item Name - Must be the name of an existing item under the specified category.
Activity Data - enables you to import a new activity. The following fields are required in the import file:

Activity Name - The name for the activity

Activity Location - The name of an existing activity location.

Attendance Data - (Pro Version only) enables you to import attendance (and associated postings) for an existing activity. The following fields are required in the import file:

Activity Name - Must be the name of an existing Activity.

Member ID - Must be an ID that matches an existing membership record.

If posting data is imported as well, the following additional fields are required:

Posting Type - Must be a posting type in the format,  "Activity Income (C)", i.e., the posting type name, followed by a D or C (debit or credit) in parenthesis, just like it appears on the list of postings on an activity record.

Note: If the import results in an activity now having more attendees than the specified activity capacity, a notice will be displayed in the results area. The system will always import all records regardless of capacity.

5Click "Next"
 
The first 50 lines of data will be read from your file held for display as sample data in the "Sample Import Fields" section.  You can page through this data one record at a time using the left/right arrows below the list. Depending on whether your data has a header row or not (i.e., the first row contains the field names), you will either see headers or your first line of data.

 Note: If you see a blank line and have to click the right-arrow a couple of times to get to the data, you need to fix your import file.  Blank lines in an import file can cause the import to stop prematurely, so you should remove them.

 

6The Map Fields tab enables you to specify a target field for each piece of data you are importing. The rows in the "Sample Import Fields" section exactly match the rows in the "Mapped Target Fields" section. For each row in the Sample list do one of the following in the Target list:
Ignore it - If you don't want to import data for this field for some reason, either leave the target blank, or choose "[Ignore Field]"
Map to a Specific Field - If the field contains data that belongs in a specific field, such as "last name", you can simply select the specific target field (i.e., "Last Name")
Split Data to Two Fields - A split is needed when your field contains combined data like, "Smith, John". In this case, you don't want to move the contents to either the Last Name field or the First Name field; you want to divide it between them. In this case, you would select "[Split First-Last Name]" and then specify which part of the split comes first. There are several splits defined for you (when importing standard membership and group data). If you have a combination that is not available, you will have to manually edit your data.

 Note: If you are importing data to custom fields, you must set up your custom fields first so they are defined for the proper kind of data, and the names appear in the target field list. Also remember that only custom fields marked as usable for imports will be available.

 Custom fields defined as a Checkbox will only recognize an import value of Y or N, where Y = selected, and N = unselected.

 Creating a correct map can be time-consuming. You can save your work at any time by choosing "Save Map." Once saved, you can reload it at any time. (This can really help if your first import map doesn't work and you need to adjust it.)

7Once you have mapped all of your fields, specify the first row of "real" data in the "Begin with Record" field. This tells the import engine where to start reading data. For example, if your import file begins with a row of column headings (which is very common), you would want to start the import with row 2, not 1.

 Note: Some things are required by the database, and have to be imported in order to have a valid record to save.  For example, every record has a Country, every phone number has a Phone Type, etc.  If your import file does not include this data, the default set on your database will be used.  All of the default values can be verified via Tools > Dropdown Lists, and the specific type of field.

8When you're ready to import, click "Next."
 
The system begin the process of moving all of your data to the database.

 Note: If the import detects a problem, you may be presented with a dialog box for an on-the-spot correction. The most common cause of this is setting a Split for a field that doesn't always have a space in it. For example, if you split the last name and first name, but have a field of data that reads "SMITHJOHN", the engine won't known where to divide the word. Follow the instructions in any dialogs that appear.

 Important: The import process can be very resource-intensive, and the system will ignore any attempts to click on windows, etc.  If you try to click around while the import is running, you may see the window title change to "Not Responding".  That simply means MemberTies is ignoring you because it is busy right now.  If you're importing a hundred thousand records, you may want to go do something else while it is working. Do not shut off the computer or kill the program in some other way.

9When the import is finished, the Results tab will show the results of the process, and any errors that occurred.

 

 If you see errors listed, you'll have to examine each one to decide if any action is necessary.  Common errors that may be reported include:

A member ID was not found when importing a secondary address, so the data couldn't be imported.
A matching value was not found for some field. i.e., a state of TT was imported by mistake instead of TX, so the system was unable to look up a state called "TT".  The system will use the default state/province value in this case.
Invalid data, such as mapping a phone number to a birth date field, or something similar.

 The error messages should be fairly self explanatory, and will include the row in record in your import file where the problem occurred so you can find the original data. The location of the error file is included in the error log.

10When you're finished, click Close and then go verify the data within the system.  If the import went wrong, you can delete all the imported data, modify your map or data, and try again.