Why won’t any of my information import?

When importing information into Prophet you can run into certain errors during the validation process that you may not understand or which do not make sense to you. A specific example of this type of message is the error: Info

why-won't-any-of-my-information-import-figure-1

A severity of Info indicates that No Information will not be imported. The reason for this exclusion is listed within the Message area.

In the example above you can see that the Message area states “The maximum limit of warnings and errors to display has been reached. Total warnings/errors area 809”. This means that there are too many errors for the import tool to try to import any information from the spreadsheet.

To resolve this:

  • Correct all errors listed above the Maximum Limit error message and re-import.
  • This may included some items not seen in the validation window as the error messages do become truncated.

What does this Warning in my import validation mean?

When importing information into Prophet you can run into certain errors during the validation process that you may not understand or which do not make sense to you. A specific example of this type of message is the error: Warning

what-does-this-warning-in-my-import-validation-mean-figure-1

A severity of Warning indicates that the specific field within the listed row will not be imported. The reason for this exclusion is listed within the Message area.

In the example above you can see that the Message area states “Unknown user Harry Potter”. This means that the User called Harry Potter is not within the User list as it is spelled.

To resolve this:

  • Verify that the User name within the spreadsheet is spelt exactly the same as the User name within the Prophet database.
    • This can include trailing spaces after the last name if they were added from the Admin area by accident.
  • You may also use a User’s email address instead of their name to assign them as a User.

What does this invocation error mean?

When importing information into Prophet you can run into certain errors during the validation process that you may not understand or which do not make sense to you. A specific example of this type of message is the error: Exception has been thrown by the target of an invocation.

what-does-this-invocation-error-mean-figure-1

This error is seen for two reasons.

  1. The version of Prophet you are using is older than your server level which creates a mismatch of table fields from the client to the server.

    • To resolve this please update your version of Prophet to the newest version.
      1. Download Prophet Update from: www.avidian.com/install/clientupdate
      2. Close Outlook.
      3. Run downloaded update.
      4. Open Outlook and try import again.

    what-does-this-invocation-error-mean-figure-2

  2. A Primary Field Type is being duplicated or does not match the criteria for import.

    • To resolve this issue.
      1. Make sure Primary Keys like Company Name, Prophet ID, and External ID are not duplicated within the spreadsheet or against information already existing within Prophet.

        • If information is duplicated remove duplication and re-import.
      2. Or, confirm that dropdown menu items are spelled within the Import exactly as shown within the Prophet Field.

        • Once dropdown fields are spelt exactly as they appear in Prophet re-import.

    what-does-this-invocation-error-mean-figure-3

What does this Error in my import validation mean?

When importing information into Prophet you can run into certain errors during the validation process that you may not understand or which do not make sense to you. A specific example of this type of message is the error: Error

what-does-this-error-in-my-import-validation-mean-figure-1

A severity of Error indicates that the entire listed row will not be imported. The reason for this exclusion is listed within the Message area.

In the example above you can see that the Message area states “Duplicate row in the input file. The row will not be imported”. This means that the listed row, Row 28, has a duplicate/matching row within the same spreadsheet.

To resolve this:

  • Review the spreadsheet to determine where the duplicate row information is located and remove the duplicate.
    • For Contact this would be duplicated Prophet ID OR External ID OR First Name, Last Name, & Email Address OR First Name, Last Name, & Business Phone OR First Name, Last Name, & Home Phone OR First Name, Last Name, & Cell Phone.
    • what-does-this-error-in-my-import-validation-mean-figure-2
    • For Company this would be duplicated Prophet ID OR External ID OR Company Name.
    • what-does-this-error-in-my-import-validation-mean-figure-3
    • For Opportunity this would be duplicated Prophet ID OR External ID.
    • what-does-this-error-in-my-import-validation-mean-figure-4

What does the data validation tell me about my import?

The Prophet Import Tool has a wonderful feature that allows you to validate your data before it is imported. This gives clients the chance to correct data mistakes before they are imported into the Prophet Database. However, understanding the validation screen can be confusion.

Here is a breakdown of the Validation Screen.

General Information about Import

what-does-the-data-validation-tell-me-about-my-import-figure-1

The Information located at the top of the Validation Screen gives you specific information about your import.

Name Item in Import
Data Type Type of Import being completed
Blank space to the right of Data Type Name of Excel Workbook being imported
Time(hh:mm:ss) Time taken to validate import
Import Rows Scanned: Total number of rows import tool has viewed for validation for that Data Type.

Source Column

what-does-the-data-validation-tell-me-about-my-import-figure-2

The Source Column shows the name of the mapped column within the spreadsheet that is being discussed.

Message

what-does-the-data-validation-tell-me-about-my-import-figure-3

The Message gives detailed information about the validation issue being cited.

Value

what-does-the-data-validation-tell-me-about-my-import-figure-4

The Value gives the field item shown within the spreadsheet which has caused the validation issue.

Severity

what-does-the-data-validation-tell-me-about-my-import-figure-5

The Severity describes the type of validation issue that must be addressed. To see specific types of Severity issues please continue looking in the Frequently Asked Questions for these specific types.

Row

what-does-the-data-validation-tell-me-about-my-import-figure-6

The Row indicates the row number within the spreadsheet on which the validation issue has occurred.

What are the best practices for importing my information?

The Prophet Import Tool is a wonderful feature that allows you to bring in information from any Excel formatted worksheet into Prophet. However, there are a few best practices that can help you to ensure you are bringing in your data in the best possible way.

Excel Workbook

Unlike older versions of the Prophet Import Tool a single worksheet can no longer be used to import contact, company, and opportunity information. Each of these data types must be imported separately. For that reason it is best practices to create each data type to be import as a separate worksheet within a single workbook (though you may have multiple workbooks if you wish).

An Example of this is shown below:

what-are-the-best-practices-for-importing-my-information-figure-1

Import Structure

When importing multiple common data types at a single time – what we refer to as stacking your import – it is best to order them in the following format:

Company

Contact

Opportunity

Unfortunately, you cannot import common data types and uncommon data types at the same time. That means you cannot import both Opportunity and Opportunity_Contact at the same time. This is because the Opportunity must already exist within Prophet before you can link a Contact to it.

A Common Import will look like this:

what-are-the-best-practices-for-importing-my-information-figure-2

An Uncommon Import will look like this:

what-are-the-best-practices-for-importing-my-information-figure-3

Correct Errors Before Importing

To make sure the best possible data is being imported into the Prophet Database it is important to correct all errors before continuing to import after validating the data. If any issues arise during the validation of the data, it is best to save your mapped setting, correct the issues, and validate again to confirm the issues have been resolved.

To see how to correct validation issues please read through the Frequently Asked Questions about the type of Validation Issue you are experiencing.

Can I export my data out of Prophet, edit/update that information and then re-import it back into Prophet?

This is technically possible but is not recommended.

Let’s explore the steps of how to do this and then take a look at the potential problems you might encounter.

First, to export data out of Prophet, you will need to either create a report using the Report Wizard (please refer to that section of this FAQs), or you can also export data from your views in Prophet.

Avidian Prophet CRM: Contact Manager View

Once you have the data exported out of Prophet (in excel format), you can begin editing that data.

Avidian Prophet CRM: Excel Export of Company Information View

Once you have updated the information, in this case the addresses, you will need to then copy and paste each cell into Prophet’s import template and then import that back into Prophet.

Avidian Prophet CRM: Import Template View 3

The problem is this: any cell that is left blank in the import template will then replace/overwrite what was currently in Prophet for each contact/company. In this case since we were only looking to update the addresses, we left blank all the other columns like phone number, email address, notes, etc. So for John Haden, the information that was already in Prophet will be replaced by what is on the import template. If the phone number cell is left blank, his phone number will be erased in Prophet. If the notes cell is left blank, we will lose all existing notes that had been tracked thus far.

To avoid the potential loss of data, you would need to build a report using the Report Wizard that includes all fields from Prophet. Since that is not possible, you would need to build one report for your contact data and one report for you company data.

Another issue is regarding opportunities. When you import opportunity data into Prophet, it won’t update or overwrite the existing data. Instead, Prophet will create new opportunities. So if you export all opportunity data out of Prophet, update that information, transfer that into the import template and then import that into Prophet, you will then have duplicate opportunities all with a new created date and modified date.

So while this is technically possible, it also poses problems and a lot of additional work to avoid losing important data.

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 Avidian 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\Avidian 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.

Avidian Prophet CRM: Template View First Name View

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

Avidian Prophet CRM: Template View Company View

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

Avidian Prophet CRM: Opportunity Sales Status ViewAvidian Prophet 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.

Avidian Prophet CRM: Company Record - Sales Type View

The original template is shown below:

Avidian Prophet CRM: Original Template View

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

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

Avidian 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:

Avidian Prophet CRM: Import Template View

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

Avidian Prophet CRM: Import Template View

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

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

Avidian Prophet CRM: Contact Manager User Defined Fields View 1

Here is the original import template before renaming the fields:

Avidian Prophet CRM: Original Template before Renaming View 1

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

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

Avidian Prophet CRM: Renaming the Company Manager User Defined Fields View

Here is the original import template before renaming the fields:

Avidian Prophet CRM: Original Template before Renaming View 2

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

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

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

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

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

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

    Avidian Prophet CRM: Import Product Data from Excel View

    Avidian Prophet CRM: Import Product Data Template View

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

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

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

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