Importing Data

Group and membership 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.

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.

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:

1	Open the file in Excel. (This is a good time to scan your data for inconsistencies, blank rows, etc., and fix any problems you find.)

3	Depending 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:

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.

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.

A "Select Text File to Import" browse window will open automatically so you can choose the Import File. Browse to the appropriate text file, select it, and click Open.

2	When the Import Data window returns, the file will be displayed in the Import File field. If you selected the wrong file, click the Browse icon to change it.

3	Choose the delimiter used in the text file from the dropdown, and specify the type of data you are importing. The data type determines the fields available for import:

•	Membership Additional Address Data - enables you to import additional (non-current) addresses to existing membership records using an import file containing the following fields:

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.

Phone Type - The phone type for the phone number on the address. This is required even if no phone number is imported (just use "Normal"). If not provided, the default phone type will be used.

•	Membership Dues Data - enables you to import dues entries to existing membership records using an import file containing the following fields:

Posting Type - Must be a dues 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.

4	Click "Display Sample." The first line of data will be read from your file and displayed on the Map Fields tab. 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.

The Map Fields tab enables you to specify a target field for each field of data you are importing. The rows in the "Sample Import" section exactly match the rows in the "Mapped Target Fields" section. For each row in the Sample, pick the corresponding Target by clicking the dropdown list and scrolling up or down to find the appropriate field. If there is no appropriate field, you can select "[Ignore Field]." Otherwise, there are two ways to map a field:

•	Direct - A direct match, for example, is when you have a field containing the last name and you can simply select the "Last Name" field as the target.

•

Split - A split match is 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, it helps to set up your custom field labels first so they appear in the target field dropdown with the proper names ('eye color' instead of 'Custom 01', for example).

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

Once you have mapped all of your fields, specify the first row of "real" data in the "Begin Import with Row" 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.

7	Click "Verify Defaults." Each value displayed is required by the database. If your data doesn't include one of these values, the default will be used.

The option for treatment of a State/Province code of "WA" may or may not apply to your data. By default, the database includes an entry in the State/Province list for Washington (WA) and Western Australia (WA). If your import file just uses the value "WA" for the state (instead of "Washington") and the record being imported does not have either "United States" or "Australia" as a country, the system will not know whether you mean the state or the territory. Use this option to indicate how to treat the data in this case. Other options to deal with this kind of data include: 1. change your import file to spell out any entries for Washington or Western Australia instead of using the code value; 2) delete one of the WA entries from the State/Province List if you have no use for it; or 3) include a country entry on each record.

8	Click Test Import when you are ready to begin. The test import will read all of your data into a temporary file for your inspection.

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.

View Import Errors - Many types of errors can occur during the import process that will not cause the import to fail. These errors or warnings are written to a text file on your computer. To view these messages, click "Show Errors." The most common kinds of errors are regarding missing data that has been filled in with a default value, and invalid data, such as mapping a phone number field to a birth date field or something similar. The error messages should be self explanatory, and will include the Results row where the problem occurred so you can cross-reference it to the View Results tab listing.

Show Duplicates - Click "Show Dupes" to highlight any row where the name already seems to exist in the database. You are not prevented from importing an existing name. (This option does not apply to additional address or dues data.)

Delete - If you see a row you don't want to import you can remove it. The data will remain in your raw data text file, but will not be imported to the live database.

Change Data - You can also make changes to almost every field in the list by clicking it and either selecting a new value from a dropdown, or by typing in new information. You should scan each row carefully and make any changes necessary to the information displayed before it is imported.

For membership data, the "Type" column indicates whether the system plans to create a new record or update an existing one using the data being imported.

10	When you are satisfied with your data, click "Update Database." Your data will be scanned a final time for verification and then you will be prompted to confirm the import. Click Yes to begin updating the database.