How do I import my existing data?

The Prophet Import Export Wizard allows you to import data from Microsoft Excel into Prophet to create Contacts, Companies and Opportunities. It is important to know that you must use Prophet’s template (described in more detail below) as it is pre-mapped to the fields within Prophet.

Template and Field Format

Fixed Fields:

  • Column names for fields 1-54 and order cannot be changed – they are hardcoded.

  • Columns 1-23 apply to contact

  • Columns 24-40 apply to company

  • Columns 41-54 apply to opportunities

Custom Fields:

  • Columns 55+ are customizable and may be deleted.

  • Each field title has an embedded identifier that must remain intact, although the other text around the identifier can change.

Template Rules

Account Reps:

  • Account Rep specified in the template must be an EXACT match of the Account Rep specified in Prophet. If not, the record will fail. Account Rep should be initially input into Prophet “firstname lastname” with no comma and only one space separating first and last name.

  • Multiple reps are assigned to an opportunity contact by inserting a comma between the names of the account reps.

  • The Account Rep specified on the line of the import template will be assigned to the Contact, Company, and/or Opportunity also specified on that line.

  • All items should have an assigned Account Rep. Items without an assigned Account rep will not show up in Mobile Users.

Date Values:

  • Date values in Excel must be converted to text – otherwise they will import in a numeric from (days since 1/1/1900).

  • You can convert to text using the following formula: =IF(ISBLANK(RC[-1]),””,TEXT(RC[-1],”mm/dd/yyyy”))

Drop Down List Values:

  • Spaces before or after a drop down value may be problematic. Fixed in release 6.0.354 and later.

  • Only allowed ONE contact per OPPORTUNITY for import.

  • When importing an OPPORTUNITY, the data assigned to the COMPANY or CONTACT assigned to that OPPORTUNITY will be overwritten with the data in the template (i.e. if the template has BLANK address information, any address information for the COMPANY in Prophet will be LOST).

Notes Fields:

  • xlsx limitation of 2GB per cell

  • xls limitation 64k per cell.

  • Long string length like notes (over 256 characters long) will be truncated unless sorted longest string first. To work around, add a new column to calculate the length, sort largest to smallest, and delete the column. Formula is =LEN(RC[-1])

Prophet Import Duplicate Check

While importing contacts, Prophet will check for duplicates based on the following criteria:

  • First and last names much match

And one of the following must also match:

  • Email1

  • Email2

  • Email3

  • Business Phone

Prophet Database Importing and Manipulation

The data access library for Excel does “sampling” of the first 10 rows to determine how it will format all rows for the import. This means that if the first 10 rows all have number in them, Prophet will get only values that can be converted into numbers and all text values will be lost. Convert entire column to TEXT type before importing.

Data Import Validation Failures:

  • If data does not meet validation criteria, the import will still import the invalid data and report the failed record in an error log and a dialogue pop-up will appear during the import process with a link to that error log.

  • If the overall process fails midstream, records up to the failure point will be imported up to the point of failure. All importing is done on a record-by-record basis.

Direct Prophet database access for OnPremise (locally hosted) customers:

  • This is possible, but administrators should be extremely careful about manipulating any data without consulting first. Any dependence on such updates or reads could break with future versions of Prophet. Care should be taken to isolate 3rd party code from our schema. This can be done using standard approaches such as writing code against newly created SQL views, etc.

Backing out a batch update:

  • The Prophet import tool imports on a row-by-row basis (non-batch). If controlling data on a batch basis is needed, we recommend using one of the user defined fields as a batchID that can be used to identify the records from a single import.

Step I – Prepare the Import File

The Import wizard will pull data into contacts, companies, and/or opportunities from a specific Excel format.

The Import Template can be found in C:\Program Files\Technologies\Prophet and the Excel file is named ‘Prophet Import Data Template’. The Excel file is populated with column headers for Contact Name, Company Name, Description, Opportunity Sales Stage, Opportunity Status, Opportunity Description and many more. Your data must be filled out in this specific format. Do not move, delete, hide, etc. any of the columns.

To import Contacts you must at least fill in the First Name in the template.

Prophet CRM: Template View First Name View

To import Companies you must at least fill in the Company Name in the template.

Prophet CRM: Template View Company View

To import Opportunities you must at least fill in the Opportunity Sales Status in the template.

Prophet CRM: Opportunity Sales Status ViewProphet CRM: Notification of non-obligation View

If you are filling in the Excel Import Template with data that is to import into an Opportunity Drop Down field, the specific field name you are importing must exactly match the data you have in the Opportunity Drop Down, otherwise the field will be blank in the Opportunity.

For example, if your drop down values for Sales Status in the opportunity are Active, Lost, Won, Cancelled and you try to import the value of ‘Import’ into the Sales Status field, the field will be blank because the value you are trying to import doesn’t match the drop down values for Sales Status.

Preparing the Import Template

As you customize fields in Prophet, such as add fields to the user defined fields in Contact Manager, Company Manager, or rename/customize fields within the opportunity, you can rename the corresponding column on the spreadsheet to match.

For example, take the opportunity below. Some of the fields have been changed from the original version – such as the Title (which is called Opportunity Description on the template), and Item Type (which is called Opportunity Sales Type on the template), etc.

Prophet CRM: Company Record - Sales Type View

The original template is shown below:

Prophet CRM: Original Template View

The template that has been renamed to match the current configuration in Prophet is shown here:

Prophet CRM: Template Renamed View

Renaming the Opportunity User Defined Fields

In the spreadsheet, columns CJ through FP are all of the user defined labels found in the three user tabs within the Opportunity. An example is the image below.

Prophet CRM: Opportunity - Sales Follow-up View

As you customize your opportunity template, you should also update the import template as you go since all labels are numbered.

Here is the original import template before renaming the fields:

Prophet CRM: Import Template View

Here is the template that has been renamed to match the current configuration in Prophet:

Prophet CRM: Import Template View

For these fields, you can only rename the “userDefinedlabel20” portion – leave the rest in there:

Prophet CRM: User Definable Label View

Renaming the Contact Manager User Defined Fields

The 20 labels found in Contact Manager User Tab 1 are also available on the spreadsheet – columns BO through CH.

Prophet CRM: Contact Manager User Defined Fields View 1

Here is the original import template before renaming the fields:

Prophet CRM: Original Template before Renaming View 1

Here is the template that has been renamed to match the current configuration in Prophet:

Prophet CRM: Original Template after Matching View

Renaming the Company Manager User Defined Fields

The first 12 labels found in Company Manager User Tab 1 are also available on the spreadsheet – columns BC through BN.

Prophet CRM: Renaming the Company Manager User Defined Fields View

Here is the original import template before renaming the fields:

Prophet CRM: Original Template before Renaming View 2

Here is the template that has been renamed to match the current configuration in Prophet:

Prophet CRM: Original Template after Matching View 2

Step II – Import the Data

  1. Select the arrow next to the Tools button in the Opportunity/Contact/Company Manager Toolbar.

  2. Select Import and Export Wizard.

  3. Select Import Company, Contact And/Or Opportunity Data and Click Next.

    Prophet CRM: Import and Export Wizard View

  4. Browse for the file you are importing, select worksheet 1, and also select your department.

  5. Click the Import Button.

    Prophet CRM: Import Company, Contact And/Or Project Record Data View

  6. The Contact will be created if it didn’t exist. It will be saved in your Working Contact folder in Outlook and also in Contact Manager.

  7. The Company will be created if it didn’t exist.

  8. The Opportunity will be created and listed in Opportunity Manager.

    Prophet CRM: Notification: Small Batches View

Importing Product Data

  1. Open Opportunity Manager.

  2. Click the arrow Next to the Tools button in the Opportunity Manager Toolbar.

  3. Select Import and Export Wizard.

  4. Select Import Product Data, click Next.

    Prophet CRM: Import and Export Wizard - Import Product Data from Excel view

  5. Open the import template by double clicking under The import template can be found at: to open the Prophet Import Product Data Template.

    Prophet CRM: Import Product Data from Excel View

    Prophet CRM: Import Product Data Template View

  6. Prophet Import Product Data Template has six columns that import directly into the Opportunity Product Window.

    Prophet CRM: Import Product Data Template Columns View

  7. Type or paste your product data into the appropriate column header in the Prophet Import Product Data Template. Product Group and Product Name Columns are required for the data to import.

    Prophet CRM: Import Product Data Template - Group and Product Name Columns View

  8. Save and close the Prophet Import Product Data Template.

  9. Open Opportunity Manager.

  10. Click the arrow next to the Tools button.

  11. Select Import and Export Wizard.

  12. Select Import Product Data, click Next.

  13. Click Browse.

  14. Select the Prophet Import Product Data Template.

  15. Click OK.

  16. From the Worksheet to Import drop down field, select the worksheet from the Excel file you prepared containing your product data for import.

Prophet CRM: Import File Name View and Prophet CRM: Nofification Product and Group Name Already Exists View