Page tree
Skip to end of metadata
Go to start of metadata

Importing Record Data

It is possible to import record data into Agiloft from a variety of file types. The following information provides a guide for basic imports. For more about importing and exporting please visit Import Export.

Importing Data

Data is imported from a file, e.g. Excel, csv. Before uploading the document, check the fields and data naming conventions. The labels on the import document should closely match the labels for the fields they will be mapped to. A great way to create an import template is to export a single record from the table to populate a spreadsheet with the appropriate field names.

Uploading an import file may take several minutes, depending on the number of records and overall file size.

Note: If you are using the Data Import Excel template, provided by Agiloft staff, copy and paste your data into a clean spreadsheet before beginning your import.

  1. Navigate to the table into which you would like to import data.
  2. Hover over Actions on the action bar and click Import. This will open the Import wizard.
  1. Data Source tab: select Local hard drive as the source of the data to be imported.
  2. File Name tab: browse for, select, and upload your file.

  3. Data Format tab: select the file type according to the file you uploaded in step two. Your options are Tab-delimited text, Other text file, Excel spreadsheet, XML file, or JSON.
  4. Options tab: choose whether to run rules and workflow options, and whether to encrypt the password.
  5. Table tab: select the relevant options. Available options depend on the file type selected on the Data Format tab, but most contain options for missing choice values, how to treat the first line of text, import errors, etc.

  6. Records tab: choose how the import will act on existing records:
    • Append new records
    • Replace matching records
    • Skip matching records
    • Update imported fields only in matching records
    • Delete all records before importing
  7. On the Fields tab, define how the data in your import file will be mapped to the system fields.
    1. Remember to label your spreadsheet as accurately as possible to make the field mapping process simple, or export a record from the table to create an import template. The system will attempt to match the imported fields based on data type and field name, as in the screenshot below.
  8. Click Test to test your import and discover any potential errors such as conflicting unique values.

  9. Click Finish to complete your import. The system will generate a summary of the import process and number of imported records.

Importing Attached Files

To import attached files in bulk, refer to the following process:

  1. Create a folder named "files" with all the attached files. Note: the folder must be labeled "files".
  2. Create an index spreadsheet with an Excel or standard .csv text file that contains a column for a unique field in the table you importing into and a column for the filenames. You can use the ID, an agreement number, or any other field that will be unique in the table as the unique identifier field. Populate the file with all your file names and IDs.

    OR
  3. Select the folder and the text file. Right click, and use a zip tool to create a .zip file containing the folder and the main file. Zip the folder and file into a single text file, such as "import.zip". Both the spreadsheet and the files folder should be at the root of the archive and not enclosed in some other folder. If your import contains subfolders, please refer to the Importing Files Containing Subfolders section.

  4. Use the process outlined in Importing Data to import your records.
    1. Select your table in the left pane, mouse over the Actions button on the action bar, and click Import.
    2. Choose Local hard drive and click Next, then upload your file.
    3. Click Next to go to the Data Format screen. Choose the format according to the type of file you uploaded, Excel or Other Text file.
    4. On the Table tab, choose your preferred options.
    5. On the Records tab, choose to Update imported fields only in matching records. Select the field you are matching against, either the ID or another field. If there are multiple attachments per ID or unique field, then choose use all records under the Duplicates heading.

      Your import should show the following settings:

    6. On the Fields tab, map the data into the appropriate fields.
      Tip: The Contracts table has a field specifically for this purpose, titled Transitional Contract Files. Any file attached to this field in a contract record will, via automation, be placed in a related entry in the Attachments table. This allows multiple attachments to be tracked for a given contract. Be sure to choose the option 'Run rules on import' when uploading contracts to this file field.

    7. Click Finish to run the import/update. When done, check your data and validate that the attached files were added.

Importing Files Inside Subfolders

If your files have duplicate file names and must therefore be stored in subfolders, your index spreadsheet must define the entire file path. For example:

  • Image A reflects a normal index spreadsheet. All the files are in one folder.
  • Image B reflects an import with subfolders. The whole file path is defined with forward slashes between file names. In this case, each contract in contained in the folders 100 or 200, which are contained in higher level folders with the same name.

If your files contain subfolders, we recommend extracting the files into one directory and running a normal import. If subfolders are required, please contact your Agiloft implementation specialist for more information about your particular import use case.

Importing Integer Data into Multi-Value Enabled Fields

When importing data into multi-value enabled (MVE) fields, the values must be comma-separated. However, when attempting to import text strings into a MVE field, the text may contain a comma. For example, a company name may have commas - for example Minister of Innovation, Science and Economic Development - therefore it becomes necessary to use another unique field for importing purposes. In most cases this would be some type of ID field. However, if an ID integer field is being used for import with an MVE field type, it must be delimited with a comma even if there is only one value. 

Example

Company A has structured their Company table to have multiple Parents per Company to represent their internal divisions. To see detailed information about the parent company, they chose to have a link to selected fields with MVE. At the time of the import, each company only had one parent and therefore only one integer to import. The following values will import correctly when a comma is appended to the Parent Unique ID integer field.

ID

Company Name

Company Acronym

Company Level

Parent Unique ID

4Audit and Evaluation Sector (AEBS)AEBSSector 
5Intellectual Property Office (IPO)CIPOSector 
6Chief Information Office (CIO)CIOSector 
21Audit and Evaluation Branch (AEB)AEBBranch4,
22Business Services Branch (CIPO-BSB)CIPO-BSBBranch5,
23Copyright and Industrial Designs Branch (CIPO-CID)CIPO-CIDBranch5,
24Corporate Strategies & Services (CIPO-CSS)CIPO-CSSBranch5,
29Trademarks Opposition Board (CIPO-TMOB)CIPO-TMOBBranch5,
30Business Services Branch (CIO-BSB)CIO-BSBBranch6,
31CIPO Business Solutions Branch (CIO-CBSB)CIO-CBSBBranch6,


Importing Contract Information

Below are tips and recommendations for importing contract information into our out-of-the-box Contract Management module. Because data, like company and contact names, are linked between tables, it's important to import records in the right order. The Contracts table also includes a special field for attached files that automatically creates related Attachment records when files are added to it.

  • If this is a preliminary import, please import data for each table in the following order: 1) Companies, 2) Locations, 3) People, and 4) Contracts.
  • If this is not a preliminary import, but you are introducing new records for any table prior to Contracts in the order below, please import data for each table with new records in the order defined above.
  • If you are introducing no new records for tables other than the Contracts table, skip directly to importing your Contracts data in Step 4.
  1. Prepare and import your company level information.
  2. Prepare and import your location level information:
    1. Include a separate column for US States within the Unites States and locations outside the United States - State or Province
    2. By default, US States should be listed using a two-character abbreviation.
  3. Navigate to the People table. Prepare and import your contact level information.
    1. Be sure to import First and Last Names separately – the system will join them into Full Name
      1. If you only have a single column for Full Name, and are importing an excel file:
        1. From Excel, select the column containing Full Name.
        2. Select Data > Text to Columns and specify "space" as the delimiter.
        3. For the destination, specify the first cell in the rightmost available column where the next column is also available.
        4. Click Finish.
        5. Title the first column First Name and the second column Last Name.
    2. On the Table tab of the Import wizard, select the External Users subtable.
    3. Import the Company Name as this field links the contact to the company record you created in Step 1.
  4. Import your Contract level information:
    1. Be sure to include the correct Company Name as this field links the contract to the company record you created in Step 1.
  5. Prepare and import any attached files related to the imported contracts:
    1. Please see the Importing Attached Files section. The Contracts table has a field specifically for this purpose, called Transitional Contract Files. Any file attached to this field in a contract record will, via automation, be placed in a related entry in the Attachments table. This allows multiple attachments to be tracked for a given contract. You must select the option to 'Run rules after import' to run this action.


  • No labels