Import Items From Microsoft Excel

Content:

Import from Microsoft Excel might be very useful feature especially if you formerly used some other customer management system than eWay-CRM or if you purchased a commercial contact database and now you want to move these contacts to CRM and track communication with them. The import from Microsoft Excel can be also used for updates of items that are already in the system.

Be aware, the user who executes data import from Microsoft Excel should be granted sufficient permissions to create and edit items from the Companies, Contacts, Deals, Projects, Journal and Products modules, otherwise the import or update may be done only partially or not at all. Visit the chapter Set Module Permissions to learn more about permissions.

Ways of Creating Source File for Import

Note
The Excel file that will be imported cannot have more than 255 columns.

Import Existing Data Sets

In most cases, you will have the data already prepared in an Excel sheet. You may have gotten that file as a commercial database, or it may be an export from your former software as well. Anyway, such data set needs to be customized to be suitable for the import to eWay-CRM:

  1. First row in the file must be column names. Each column should be named so you can easily recognized what data are contained in it and you can easily mapped them with columns in eWay-CRM.
  2. Prepare files to comply with Source File Conventions.
  3. Consider the risk of duplicates and take the steps to prevent its creation if needed.
  4. Use one of the Import Methods

Create New Set of Data to Be Imported

Follow these steps if you don't have any file with data to import. You can use this template to quickly create your data and load them into the system at once.

  1. Save Template for Import - the file will contain the exact subset of columns as the view you are using to display Companies, Contacts, Deals, Projects, Journal, or Products list.

  1. In case you want to have imported all the columns belonging to the module, please use the All Columns option as shown in the picture below.

  1. Insert data to be imported.
  2. Consider the risk of duplicates and take the steps to prevent its creation if needed.
  3. Use one of the Import Methods.

Update Existing Items

You may also use the import items from Excel feature when processing mass changes among data.  For example, when you want to assign all your companies to another user (change its owner), or relate to some category and such. It has proven to be way faster than opening every single item and changing it manually.

  1. Export data to Excel - Go to the list of module you want to import data to. Right-click any blank space, select Export List and then Items to Microsoft Excel. The exported file will contain only items and columns that are currently displayed.

    In case you want to have exported all the columns belonging to the module, please use the All Columns option as shown in the picture below.

    If it is on, you need to turn a data filter off to export all the items that the selected module contains.
  2. Update the values in the Excel file as needed. You need to ensure that existing items will be correctly paired with items in the Excel file for a successful update. Items are paired on the basis of these fields due to the module:
    Company Matching on the basis of ID or Identification number
    Contact Matching on the basis of Email 1, but the prerequisite is that duplicity check is turned on - see Check for Duplicate Contacts.
    Deal Matching on the basis of ID
    Project Matching on the basis of ID or Project name
    Journal Matching on the basis of GUID (unique database entry)
    Products Matching on the basis of Code
    You can also use other fields to pair items - you need to use Unique permission. The table shows only default pairing fields, but when you set a field as Unique, it will be used for pairing too. For more information, go to Set Unique Permission.
    If you need to remove existing values, leave cells in the file empty.
  3. Use one of the Import Methods to commit the changes.

Import Methods

Important: Before the import, be sure you save changes in Excel file and you close this file. When the file remains open, it is possible that data import will not be correctly done.

If your file complies with Source File Conventions, it is possible to import your data to eWay-CRM. There are several ways to initiate the import.

Simply, you may drag and drop the Excel file to the appropriate list.

Or there is an option Import From Excel in context menu of modules main list.

And finally, when you display one of supported modules, the import features will be enabled in the Outlook ribbon or within the eWay-CRM menu in Microsoft Outlook 2003 or higher.

 

Mapping of Columns Before Import

Availability: The function of mapping is available since eWay-CRM 6.0.1.

You can select any method described above. In all cases, before import, you will be asked to pair columns in imported file with columns in eWay-CRM. If you are importing file that was previously exported from eWay-CRM, there is no need to map anything. eWay-CRM automatically recognizes column names.

If you are importing file with your own column names, you need to specify to what eWay-CRM columns they will be imported. You will see table similar to this one:

Control Description
Sheet selection

Your Excel file can contain more than one set of data, so here you can pick the one you really want to import. The first one is selected as the default.

Excel Name of the column in Excel file.
Sample Data The first value in the Excel column to have a small preview.
eWay-CRM Name of the column in the eWay-CRM list. Here, you can select to what column import or click X to not import the column from Excel.
Check Box

The check box in the bottom part of the dialog is different for import to specific modules:

  • Create new companies while importing contact - only for importing contacts and it is enabled only when you are mapping the Company field. Whether the check box is marked or unmarked in default, is determined by the Create New Company While Importing Contact setting.
  • Ignore workflow actions - importing of deals and projects. (The option is available only for the administrators.)

Columns will be correctly mapped when you click the value in the eWay-CRM column and select a column from the eWay-CRM where the Excel column will be imported. You can search the column in the mapping field:

If the columns are correctly paired, just click Import. When everything is correct, import proceeds. But when there are some errors in data, you will see an error dialog with the list of these errors where you can fix them:

Column Description
Field Name The column where the error is.
Excel Value

The value that is written in this field. If you click the magnifying glass, you can see the whole row with the error value:

The value from the field can be copied.

Error Description Specification of the error.
Skip

If you skip the value, it will not be imported.

The field that is mandatory cannot be skipped.

New Value

Here you can fix the error - select the correct value, insert a new one, etc. It depends on the field type.

The value can be pasted into the field, but it must fit the correct format - e.g. when the field should be only numeric, you cannot paste text value.

 

If you fix errors or skip them, import proceeds successfully. Successful import will be detected by the screen in the picture below. When the imported data are not correct, the table with errors is displayed - see Source File Conventions, Additional Settings.

Source File Conventions, Additional Settings

Check Box Values

If the part of the imported XLS file is a check box column, it is possible to have there only these values: 0 or 1, respectively FALSE or TRUE.

Drop Down Menu Values

The system also cannot import items that contain values that are not listed in the existing drop-down menus. You can fix them easily in the error dialog.

Import Companies and Contacts Linked Together

To gain the most from the import, execute the import of companies first and contacts afterward. If you have filled the "Company" or "Companies" column in the Contacts file, the imported contacts will be automatically related to those companies. As you can see in the picture below, it is possible to fill either the name of the company, or its ID, or both in the form "Company Name [ID]". All these 3 entries are equal. In the case, you want to relate a contact to several companies, use a comma to separate the company info (as shown in row 6).

Availability: For the "Company" column, the function is available since eWay-CRM 5.4.4.

Categorize Imported Items

"Categories"  is the only exception where you are not tied down by what values eWay-CRM offers. You are enabled to fill in whatever value you want, and a category with such name will be created while importing, of course only if it doesn't already exist. Use commas to separate the entries in order to relate an item with several categories.

Duplicates in the Source File

It is possible that there are duplicates in your files. As long as the golden item is the heart of a CRM system, to prevent duplicates we recommend having Check for Duplicate Companies settings and Check for Duplicate Contacts activated, both settings are to be found in the Global Settings section in Administration Settings.

Import Statuses (During the Import of Deals and Projects)

Important: The admin user is able to import statuses even without executing workflow actions. The other users always import statuses with workflow actions being executed. If the condition of the action is fulfilled, the import proceeds; if the condition is not met, import cannot be finished and the user is informed about it.

Workflow statuses are very specific values that can be linked with actions. So in the case that the imported Microsoft Excel file is containing a Status column, you will import the status change as well with the workflow actions that are defined for that status change (unless you mark Ignore workflow actions option). Status import has requirements:

  • Importing existing items with status change - the flow between current status and imported status must exist.
  • Importing new item with set status - the flow between <initial> and imported status must exist.

If the flow between statuses exists and you do not mark Ignore workflow actions, the change is correctly imported, and all actions related to the flow proceed.