The process to import data into a custom table is very similar to the import of data from an external file, except that the data is not imported into a project but into a separate table, that is associated to a project.

PrerequisiteTo be able to import data into a custom table, the current project must have at least one custom table. Otherwise, no import target option will be offered (either in a custom table or in a project).
The file to import must be an Excel format (.xls or .xlsx) or in a CSV format (comma separated values (.csv)). The size of the file must not exceed 50 Mb. This file can be compressed to the ZIP format if needed (non locked with a password and containing only one file).
Access Path > Contacts > Import

How To

Define the import
  1. In the New importwindow, define your import:
    1. In the Source section, click on Browse to select the file that includes the contacts you want to import (.xls, .csv or .zip file).
    2. In the Destinationsection, specify where you want to import the content of the file:
      • In the contact list
      • in a custom table, that you will need to select.
        If the project has at least one custom table, a choice of table will be listed. If none, this option will simply not show.
    3. In the Import type section, how you want the content of the source file to be integrated in the custom table.
    4. If needed, other import options are available if you display the advanced import options, shown when you click on this option in the upper right corner of the window.
      • In the Source section, you will also have the possibility to import contacts from another project.
      • In the Import type section, the option to import by replacing the existing contacts by the ones in the source file is offered.
        WARNING - This type of import deletes all the contacts existing in your database by the ones found in the file to import.
  2. Click on Continue.
Assign project fields to external file columns

Mapping fields consists in matching the data in the source file to the data in the target file, that is the fields in a custom table.

There are two different mapping modes offered, but by default, you are always presented with the simple mode. If the simple mode options do not meet your needs, or are more complex, you can simply toggle to the advanced mode using the mode selector.

Note that if you switch to the advanced mode and save your field mapping, this import will always display in advanced mode further on.

Mapping in simple mode

The header of the mapping section shows the import type as well as the destination project for the import.

  1. If you have already imported contacts from the same source file, want to import the same data in the same order and have saved the format of the previous import, you can select this format under Apply the following file format so that fields are already mapped.
  2. Then comes the mapping of the data to import in the custom table.
    HeaderShows the first line of the external file to import, which most often is the header row.
    SamplesShows a value from the external file for the selected column. To show other values for this column, use the before and next arrows.
    Import intoShows drop-down lists from which you must select the custom table field in which you want to import the external file value.
  3. For each external file data you wish to import, select the custom table field in which you want to import the information.
    Note that you do not have to map all the columns in the external file, but it is mandatory that you map a column to all the required fields in the custom table.
  4. Check the Keep this file format so that it can be used for future imports option if you want to save the mapping you just completed in order to use it later. If saved, you will need to provide a name for that mapping format.
  5. Click on Continue.
Mapping in advanced mode

The top part of the window indicates the source (external file name) and the target (project name) of the import, as well as the type of import applied.

  1. If you have already imported contacts from the same source file, want to import the same data in the same order and have saved the format of the previous import, you can select this format under Apply the following file format so that fields are already mapped.
  2. You can also enter a description of the import under Describe the file you are importing. Note that this description is provided for your information only and never shows in publications intended for contacts.
  3. Under the Assignmenttab, map the data to import to the fields in the custom table.
    1. In the left side of the assignment box, which corresponds to the contacts found in the source file, select one column to map.
    2. In the right side of the assignment box, which corresponds to the fields available in the custom table, select the field into which you want to import the column selected on the left side.
    3. Apply the mapping by double-clicking on the desired available field (in the right side) or click on .
      The field will then display in the left side, in the 2nd column, under the column header Import in the field.
    4. Repeat the 3 previous steps for each column to import.

  4. For the mapping to be complete, values MUST be assigned to the fields in the custom table that compose the primary key () and those that are required (). All the other values in the source file are not mandatory.
    However, you must assign a field to each column you wish to import in the custom table. If a value to import cannot be associated to a field in the custom table, as it does not show in the list of available fields, you can add a new field by clicking on Add a field. More info...Defining Project Fields

  5. To display the value found in a specific line in the source file, click on the - or + sign preceding the Show the content of this row label, until you see the number of the desired line. Since the source file often contains a header, you might not want the header info to be imported. If this is the case, displaying the value of a specific line will help you validate the imported data. For example, when displaying line 3, you will see all the values contained on line 3 of the source file, for all the columns. For example, if the source file has 4 columns, you would see something similar to the following:
idContact123456
f_PostalCodeG1L 2G5
f_FirstNameMicheal
f_LastNameSimms
  1. If the source file contains a header, it is recommended to exclude that line from the import by specifying the line number under Number of header rows that will not be imported.
  2. Check the (missing or bad snippet) option if you want to save the mapping you just completed in order to use it later. If saved, you will need to provide a name for that mapping format.
  3. Once the field mapping is completed, you can assign static values to some fields or preview the result of the import. It is important to know that you MUST perform either of these tasks BEFORE processing the import and clicking on Continue. Once the mapping is processed, it is not possible to go back to perform those two optional steps.
  4. When all the import parameters are defined, click on Continue.
Assign a static value - Optional - Advanced mode only

Assigning a static value to a field provides the ability to always have content in a given field in the custom table, even if the source file does not contain a value for a column. For example, if the project has a field to hold the language but the source file does not contain a language value, you could decide to assign the value English to the project field Language if you know that the imported contacts speak English.

The assignment of static values MUST be done BEFORE the field mapping is completed, since once the field mapping is done, the system validates and processes the import.

  1. Select the Static values tab.
  2. The Assign a static value for the following fieldfield will display a list of all the fields in the custom table that were not assigned.
    1. Select the field to which you want to assign a fixed value.
    2. Click on Add.
    3. Type in the fixed value to apply to that field for ALL the imported contacts.
  3. Once you have assigned a value to the desired fields, you must go back to the Assignment tab to complete the mapping process.
Preview the content of the source file - Optional - Advanced mode only

Previewing the content of the source file lets you see a sample of the source file, with all its columns. It might be useful to perform a preview to make sure you are mapping the proper column with the proper field in the custom table.

  1. Go to the Preview tab.
  2. Once you have previewed the source file, you can go back to the Assignment tab to complete the mapping process.
Validate the mapping

Once the mapping has been processed, an analysis is displayed in the approval window. You can see in this analysis the number of lines read in the file, the number of contacts added, updated and in duplicates, as well as the total number of lines not included in the import.

Depending of the analysis results, three options are possible:

  • If the analysis is clear of errors and the number of lines read is the same as the number of lines in the source file, you will be able to process the import by clicking on Approve.
  • If however the analysis shows errors, it will probably by caused by a bad mapping; you will want to correct your mapping errors or the static values assigned by selecting Reassign columns. The window display the type of error in the area called Error belong field and error type. To see the actual lines in error, click on Errors.
    • If you decide to reassign columns, you will need to start from scratch, unless you have saved the import format and reuse it.
    • If you display the errors, you have the option to export the lines in error in an Excel file to later perform the necessary changes.
    • Following a new mapping of the source file columns to the fields in the custom table, if the analysis is still not correct, it is recommended to reject the import and to start again.
  • When the import analysis contains errors, duplicates or no new or updated contacts, or if you have done a new mapping without success, it is possible that you have selected the wrong source file or import type. If this is the case, click on Reject import to cancel the current import and start from the start a new import process.
Review the import

Once the import is completed and approved, the review window displays various information about the processed import, such as the type and date of the import, the results, as well as the processing sequence. Following is a list of available functions:

  • View sample - This function lets you display a list of the data that were added or updated. This function is available only for a few days following the import, as it shows information from the imported file, which cannot be maintained too long on servers for its size is often substantial. 
  • Errors - This button displays a list of the import errors, if there are any, and lets you export them to an Excel file to correct them.