Import Prospects

The Import Prospects utility lets you create new prospect files using data saved in an Excel or comma delimited file.

Note: This utility is not designed to update existing prospect files in TAM.

The Import Prospects workflow is divided into the following sections:

• Before You Begin
• List Setup
• Define Fields
• Define Constant Fields
• Import Prospect List

Before You Begin

1. Make note of the order and content of the columns in the file you will use for import, and ensure that it is formatted appropriately. See Prepare a Spreadsheet for Import for detailed instructions.
2. When you import the prospect file, you will be required to define a marketing plan code as a constant field. Make sure that a marketing plan that suits your needs is entered in your system before beginning the import. See Define Marketing Plans for more information.
3. Before running the utility, it is recommended that you save backup copies of PROSP.DBF and any other databases that will be affected (see Import Prospects- Available Fields to determine which databases you will be using). These files are located in your TAM folder.

List Setup

1. Once all the steps in the Before You Begin section are complete, exit TAM and click Start > Programs > Applied Systems > Utility Manager on your computer's desktop. Enter your login information.
   Note: TAMCloud clients should instead click Start > Programs > Applied Utilities > Utility Manager.
2. On the Utility Manager screen, expand the General folder.
3. Expand the Prospect Maintenance folder, select Import and Export Prospects, and click the arrow to continue.
4. If you are using a comma delimited file, continue to step 6. If using an Excel file, do the following:
   1. Click Options, Convert Excel Spreadsheet to Prospect List.
   2. Browse to the appropriate file and click Open.
   3. On the Convert Excel Spreadsheet to Prospect List window, enter the full path and filename you wish to use to save the converted file. Click OK.
   4. You are prompted, "Spreadsheet converted successfully." Click OK.
5. Navigate to the location where your import file is saved (use the location of the converted file if you followed the instructions in step 4). Open the file in Notepad or WordPad. Before using the file for import, you will need to do the following:
   1. From the open file, click Edit, Replace.
   2. In the Find What field, enter two double quotation marks with no spaces ("").
   3. In the Replace With field, enter two double quotation marks separated by a space (" ").
   4. Click Replace All, then click Cancel to close the Replace window.
   5. Click File, Save and close the file.
6. On the Import and Export Prospects screen, click New to set up a new list.
7. You are prompted, "Is this Prospect List in comma delimited format?" Click Yes or No.
8. The Data Definitions screen displays. In the Prospect field, type the name of the new list.
9. Enter a Description for the list.
10. Click Save.

Define Fields

Once you click Save on the Data Definitions screen, the Add button is enabled, allowing you to define the fields you will be importing. It is important that the structure you define here exactly matches the structure of the spreadsheet you are importing.

1. Click Add.
2. For each column in your import file, you must enter the following:
   1. Title: Name of the field.
   2. Length: This field only displays in some cases. Enter the number of characters that you wish to import from this column. If the number you enter is greater than the character limit for the field in TAM, any characters exceeding the limit will be truncated.
   3. Database: Select NONE, PROSP, CINFO, PINFO, NOTE, or CLIENT. See Import Prospects- Available Fields to determine which database to select for which field. It's important to note the following:
      • You must define all columns. If you do not want to include a column in the import, you still must define it, in the proper order, and select NONE for the database. This enables TAM to skip this column and still match up the rest of the columns correctly.
      • You may only import one note per prospect. If you select NOTE as the database and Note as the field multiple times, the last Note data field in the import file will overwrite all previous Note data.
      • In order to import a prospect email address, you must choose CLIENT as the database.
   4. Field to Load Into: The options available from this dropdown list vary based on the Database you selected.
3. At any time, you may highlight a listed data definition and click one of the following buttons:
   • Insert: Add a new definition above the selected field.
   • Revise: Modify the information entered for the selected field.
   • Delete: Remove the selected field from the Data Definitions list.
   • Print: Print the list of data definitions.
4. When you have finished defining fields, click OK and continue to the next section.

Define Constant Fields

Use this option to define a field to be filled in uniformly for every prospect imported. It is required that you define Marketing Plan as a constant field. You also might want to define other values, such as Producer or CSR, as constant fields.

Note: The fields you define here should not be included in the import spreadsheet.

1. With the appropriate Prospect List selected, click Options, Define Constant Fields. The Define Constant Fields window displays.
2. Click Add.
3. On the Constant Fields window, select the Database and Field.
4. In the Constant Value to be Loaded field, enter the value that should be entered in this field for every prospect.
5. Click OK.
6. Repeat steps 2-5 for each constant field you wish to define. You may also use the buttons on this screen to Revise or Delete the selected constant field or to Print a list of constant fields.
7. When you have finished revising constant fields, click OK and continue to the next section.

Import Prospect List

1. With the appropriate Prospect List selected, click the Browse button to the left of the Prospect list path field.
2. Navigate to the location where your file is saved. (If you converted an Excel file via step 4 under List Setup, ensure that you use the converted file.)
   
   Note: In order for your file to display on the Open screen, you will need to change the file type to search for from Data Files (*.dat) to All Files (*.*).
3. Select your file and click Open.
4. In the Translate Case at Load Time frame, select Upper and Lower Case to preserve mixed case, or All Upper Case to import the data in all upper case. You are not able to translate a file in all upper case letters to mixed case.
5. In the Import Data as frame, select Personal or Commercial.
6. If you wish to import all records in the file, continue to step 7. If you wish to filter the records to be imported before continuing, click Options, Define Filter.
   1. On the Define Filter screen, double click a field to establish a filter.
   2. The Only load records where [Field] = field displays below. Enter the appropriate value and click Ok.
   3. Click Save to save all filters you set before closing the Define Filter screen.
7. Click Import.
8. You are notified when the import has completed successfully. Click OK.
9. From the Options menu on the Import and Export Prospects screen, you can select Duplicate Reject List or Corrupted Reject List to view/print any records that did not import successfully.
10. To exit the Import and Export Prospects utility, click Back.