Tools (Import)

Import

Import is designed to bulk load data into the site; this can be on an ad-hoc (One off) or scheduled basis. The import has been designed in a way to allow a user to upload data that will populate drop-downs, UDF’s and classifications. Imports are heavily security controlled as not only can they create new records, they can also update existing ones. There is a built in audit log so a user can see a historic list of what has been uploaded. There is also the ability to schedule an import to happen on a daily or weekly basis at a time that suits you; this is usually done out of office hours so as not to disturb the users of the site.

Upload

Create an import template

In order to perform an ad-hoc import, you must first download a fixed template containing all of the fields required for the import. To do this navigate to the record type that you wish to create an import for.  At the top of that record you will see icon for Export presented on the screenshot below. Only Excel, CSV and XML files are allowed to be imported to the system.

Selecting one of the options above will export the records to the chosen file format; the export will display all of the fields corresponding with the selected module including those not in general view. Clear the data but leave the fields headers and you will receive the template. Save the template to your personal drive so you can use it to create your import file any time in the future.

You can also create an import template by exporting a single record, for example – Assets – General Lighting. From there at the top of the page you will see the same symbols listed above. By selecting these, you will see an export populated with all fields displayed on the record page, including the UDF’s. This can be used to form the base of your template.

Import file is valid only if all the mandatory fields (columns) are present and populated on the file. The columns for fields that are not mandatory can be removed from the import file if changes to those fields are not required.

There is ability to import the generic links and related records in bulk. This option has been implemented for all the records in the system.

Generic link that have been already set up from the record will be exported in the export file. The Generic links set up from other record will not be exported on the file. To add generic links just add Import record ID number against field RecordLinks.Generic.

Import Validations

There are number of import validations used in the system. Please refer to table below:

Import Validation	Description	Import Error Message
Column Header	Import will fail if the column header is incorrectly populated. In the import message invalid column will be addressed. If any mandatory column will be missing then the error message will indicate which column was missing.	The column was not mapped.
Invalid drop-down data	Import will be Partially Successful if new data for fields that are drop-downs will not match with the drop-down items in the system	Cannot find drop down element for drop down [Drop-down name] with value [Invalid Value]
Invalid classification	Import will be Partially Successful if populated value will not match with classification set in Admin section	There is no classification with name: [Field Name] and parentID [Parent ID number] or Cannot field [Field Name] with name [value]
Invalid Import Record ID	Import will fail if the Import Record ID is incorrectly populated	Cannot find [Field Name] with importRecordId [Import Record ID number]
Mandatory fields	Import will fail if mandatory field is blank in the file.	The [Field Name] is mandatory.
Postcode Format	Postcode Format other than YYXX XXYY	Invalid postcode.
Invalid file format		File could not be deserialized. Probably structure of the file is incorrect.
Unequal number of column headers and data items	The number of column headers does not match to the number of data items	The number of values does not match to the number of columns at [Line with missing data].
Date Format	Date format other than dd & mm & yyyy	Invalid date.
File deleted from FTP or file name changed.	File deleted from FTP or file name changed.	File 'XXX' cannot be found.
Existing link between two records	Generic link is already set up from different record to the record that import file is placed for.	An unexpected error has occurred: Link between two records already exists.
Obsoleted record in the file	If any related record or linked record has been obsoleted the entire import file will fail.	Cannot find 'Record' with importRecordID:  [Import Record ID number]

Creating import for different purposes

Using the import to create a new record

If you would like to import a large quantity of data as a one off, follow the steps above to create a template. Populate the template with the new data and save the file to your personal drive. From the main menu select Import > Upload, this will open the Upload form:

The import upload form will open:

Once Record Type is selected additional fields will be presented Column Mapping and File Source Type with options to select from: Local, DMS and FTP.

Select Import frequency form the drop-down to state if you require file to be imported now, once, daily, weekly or monthly. Select the record type from the drop-down to direct your data file to the right location. Once Record Type is selected additional field will be presented called File Source Type with options to select from: Local, DMS and FTP. Select one of the sources and click on Upload File.

To select file from the DMS you need to place the file in one of the DMS folders that you have an access to. Populate first letters of the file name to search for it. Files that are saved in the Import folder or any folder that you have no access to will not be available for selection.

You can upload more than one document at one time from different sources and for different record types.

To improve the import performance select option Disable AutoAudit . After selecting this option audit of changes made via the import file will not be generated, and there will be no track of updates being made.

It is recommended to keep this option off but in case of huge amount of records being imported this feature can be used to complete the import quicker.

Once you have selected required files press Import  button to update the site with the new data.

If there was any import scheduled then it will display in the table called: Ongoing Import List

Once the file is processed you will get a message in the top right corner of the screen:

Green: Import has been successful

Amber: Import has been partially successful

Red: Import has failed.

After any import you will also get an import summary:

More information can be viewed in the Import History page.

After each import, the import file is stored within the DMS. To find an imported file select Documents in the menu and then navigate to the Import folder. Once you expand that Import library you will find all of the folders: Proceeded, Failed and Scheduled. Depending on the status of the imported file stated in History folder, you should find it in corresponding Document folder.

Using the import to amend an existing record

If you would like to amend details on one or a number of records for the same module, you can do this by exporting the required records from a Search page. To amend record details, the records must be exported to Excel & CSV & XML as mentioned above.

When you save the imported file to your personal drive you can then add the required updates. It is fundamental to keep the ImportRecordID field unchanged as this is the referencing field; it corresponds with a record Unique Key and import will use it as the reference code to find and overwrite the old data with the new.

For example; if there is an office move and you need to amend the details of a number of assets from one building to another, go to Assets – Search for assets relating to that building. Click on the export – in the column “Property” put in the new details and save – then import the new document. All related assets will be updated on the site.

Scheduled Import

Within the Upload page you can set a file to be imported as per your requirements: Now, Daily, Weekly and Monthly. You can also select a specific date and time for when you need your file be imported in the future. Once the file is uploaded to the system the details of the scheduled import will show up in the table called Scheduled Import List;  the file will be saved within the DMS. Scheduled imports can be stopped at any time by pressing the Cancel button.

Import via FTP with wildcard feature

When uploading a file using the FTP method there is ability to use wildcard function. A wildcard is a symbol which is used in order to represent any character or range of characters:

• asterisk (*) - represents one or more characters. For example importing files from FTP using command *.xml will import only xml files or using the command Co* will import files which are started with 'Co'
• question mark (?) - which represents a single character. For example importing files from FTP using command T?sk will import files with file name: Task, T sk

There are some rules to remember when using the wildcard option:

• Cannot use the same wildcard twice.
• Wildcards are case sensitive (FTP directory, files)
• The FTP's import will fail if there are files which are not supported by import without error message on History table (only Excel, CSV and XML are allowed)

To use wildcard when importing file from FTP:

1. Please files on FTP server.
2. In the Upload page select File Source Type: FTP.
3. From FTP's File Name field populate the name starting with the ' & ', for example: & Con*
4. Press import button and all the files which file name starts with 'Con' will be imported to the system.

Complex Import

System allows to import external files that are not matching our import template and columns naming convention. There is an option in the system to create mapping table for such a files. From menu select Tools > Import > Mapping > Create Mapping

Create Import Mapping

To Create Import Mapping select Create Mapping from menu and the Create Import Mapping form will display. Populate all the mandatory fields on the form:

Name - provide the name of the mapping that will display on the Import upload drop-down

Record Type - select record type for which the mapping is required

Click on  to add Input Field and Record Column. Input Field should match the column name from external file and Record column is the desired field in the system.

Once the mapping is completed click on save. Import Mapping record will be created also selected name of Mapping will display on the Upload form on Column Mapping drop-down.

At the point of importing external file populate Upload form as usual and from Column Mapping field select required Mapping for your Import.

Search Mapping

All created Import Mappings can be found against Search Mapping menu presented in the table.

History

To get to the import history page from main menu navigate to the Import and select History.

This page will show an audit trail of all imports no matter their success status. A user with the correct security will be able to see a table which will display the name of the file being uploaded, the author (Who uploaded it), the date, the record type the import was uploaded against and the status of the import (success, partial, failure).

These columns are all filterable by clicking on  which will give you a number of options to assist in finding what you are looking for; alternatively you can just click on the column header and it will sort alphabetically or chronologically.

To examine a partially successful or failed import click on the arrow on the left hand side next to the import reference and the details of failure will display. This will open up a new table to display specific information on the import to show where it had an issue. This information can be used to edit the import and try again.

Import Record ID: This is a specific identification mark for this record which can be used in SQL to locate it.

Name: This is the name of the record that had an issue.

Column Name: This shows which column had the error in.

Value: This shows the invalid value.

Data Type: This shows the type of the data.

Error Message: This offers insight into what caused the import to fail.

A partially successful import will still create a record using the information that it has managed to get from the import, if you then make an alteration to the import and based on feedback and run the import again it will update the record.

A failed import will not create a record; however will offer suggestion as to why it failed.