Difficulty: expert
Content
| Table of Contents |
|---|
...
|
...
|
1
...
Learning Objectives
After reading this article you will be able to:
Import data using default imports.
Have you read: What are imports and exports in Workplace?? The article below repeats some of the information from that article and goes into further depth.
Basics
The import functionality allows for a quick upload of large datasets into Workplace. By using an import connector with import mappings, it is possible to specify in which data table the various data should be stored.
Default imports are available for importing master data when setting up an environment. This allows for filling the environment with for example: contacts, properties, and areas. Files are imported in csv-format.
Where to find imports
The default imports are explained in detail in the articles that describe how to set up a new Workplace environment using the Solution-Based Rollout (SBR) Step by step implementation guides (SBR). If you want to run an import, other than in the context of an SBR, imports can be accessed by:
Pressing the ‘Default imports’/ ‘Custom Imports’ button on the startBoard.
Open the import connector by clicking on the “reference”. After clicking the reference the general tab of the import will open;
If this import you are looking for is not yet available in the list, click: Generate default imports at the bottom of the import list.
Steps to import data (and debug error messages)
To import data follow these steps:
Generate an import template by clicking “Generate import template”.
A file will become available in the include at the bottom with the name “Template import xxx”.
To download the file click the download icon.
This is the Excel file we send to the client and ask them to fill in.
After receiving the file back from the client, please verify the data:
Compare with the template if the client did not delete/ add columns, or change column headers;
Are all columns filled in;
Check if the data is valid, especially for fields that require making a selection (a number of value) or fixed format (dates). Deviating/ no values will NOT result in an error. The result will simply be that the value will not be imported;. For example:
if a column description asks “It it possible to reserve areas from this property (1 = yes)“ > make sure a '1’ is filled in.
if a column description asks “The construction date of the property (dd-mm-yyyy)” > make sure the date format is correct.
if a columns description asks “The status of the property, choose between: in use, negotiation, renovation, construction, sold“ > make sure the input exactly matches one of these value.
Now data save the file as CSV UTF-8.
Tip for converting your .xlsx to CSV: open in import file in Excel > File > Export > Change file type > select 'CSV '> Save as.
The field delimiter used in the CSV file (a comma or semicolon) should correspond with the field delimiter setting of the user that is importing the file.
To change the field delimiter setting of your user, you can navigate to: your profile (top right of your screen) > Settings > Settings tab > field “Field delimiter“.
Import the file by clicking import file.
Select file by clicking Select files.
Importing the files will continue in the background. When we click: Ok, we return to the import page.
In the right top corner the background task icon will show up, when clicking on it we can see the import status.
Tip: if the background task does not appear refresh the page by clicking the Spacewell logo on the top left of the page (of click F5 to refresh your browser).
When the import finishes, you can find the results by clicking the document icon in the top right corner.
On the document page select Advanced Search.
In the field Dates > “Linked from/until”, fill in today’s date or immediately press 'ok to find all documents belonging to this import.
Depending on the connector setting, you might find the following three document
(csv) file that you uploaded e.g. Files[]-Template import properties. The files are only saved when the import connector setting ‘Save documents = yes’;
Processing log - e.g. FMB-F-021-[Manual upload]-import. This shows which new record are created and if existing record have been updated;
Processing errors - e.g. FMB-F-021-[Manual upload]-error. A list of the blocking errors that occurred.
If the import file is filled in correctly and uploading in the correct format no errors will occur. When an error does occur it can be quite hard to find out that the exact problem of the import file is, below some tips and pointer are given on possible causes
...
| title | Tips & trick to debug the import errors/ warnings |
|---|
General info
The error message will always include the row of the import file on which the error occurred.
An error is blocking issue resulting in a record not being created or updated. The whole import might be aborted.
Warnings give information on an action that could not be completed. All other items will be imported as usual.
| Info |
|---|
Note: first read the “How is the import file mapped in Workplace?” chapter of this article. |
Common error message overview
...
Message
...
Possible cause
...
Example of error message
...
Error Field does not exist
...
File imported in wrong file format, for example .xlsx instead of .csv
This can be recognized also because all column of the import file will generate an error line.
The import file was altered: a column header changed. For example the header ‘Street’ was changed to 'Streetname'.
Only the altered column header will generate an error
A column header was deleted from the import file.
The deleted column header will generate an error
...
files[]-Template import properties_test.csv: Error Row 1 - Address with Letter : Field does not exist: Street
...
Warning Cannot find referenced object
...
When we want to link an object field in an import and Workplace can’t find that object, the object can not be linked. A non-blocking warning is shown.
For example: we want to link a contact with externalReference=4343453 in the ownerContactId field of a property. However, no contact with this external reference exists yet. In this case, the The solution would be to create the missing contact or link a contact that does exist.
...
files[]-Template import properties_test.csv: Warning Row 3 - FMB Property : Warning cannot find referenced object: ownerContactId={externalReference=4343453}
...
Warning Creating or looking up an object for this key combination has failed
...
Using key values, Workplace determines if an existing object should be updated or that a new object should be created. However, an import mapping can only create a new instance of the object it concerns. The import mapping ’Properties' can for example only create new properties, not addresses or contacts. Import mappings however, can use fields of other objects as key values. But, if an object containing these key values does not exist, the mapping cannot create the object resulting in this error.
For example, the address fields: street, nr, and city, are key values in the property mapping. If a certain address does not exist, it cannot be created by the property mapping (this should be done by an address mapping or created manually). In the example error message an address with the key values “street=Retail street, nr=18, postalReference=6666 MR, city=Arnhem” resulting in an error. Solution is to make sure the address is created in Workplace (manually or by import).
...
files[]-Template import properties_test.csv: Error Row 3 - FMB Property : Creating or looking up an object for this key combination has failed {externalReference={=X100099998}, addressId={street=Retail street, nr=18, postalReference=6666 MR, city=Arnhem}
Example screenshots
...
| Warning |
|---|
Warning:
|
How is the import file mapped in Workplace?
Opening an import mapping
Press the ‘Default imports’/ ‘Custom Imports’ button on your startBoard;
Open a ftp connector, e.g. FMB-F-021;
Navigate to the ‘Details’ tab (here you’ll find the general import settings);
Scroll down to the ‘Mapping’ include;
Open the mapping you want to view by pressing the magnifying glass behind the column ‘Mapping’ (not the blue magnifying glass at the beginning of the row!!).
When filling in the import files, there are a few things we need to keep in mind.
Every import file has different fields that have to be filled in. What these fields are depends on the configuration of the mappings that are linked to the import connector.
By opening a mapping we can see what object it concerns (field ‘Objects’) and what fields are involved ('Mapping' include).
...
Columns in a mapping
See screenshot above.
Order: determines in what order the mapping is handled.
Name: the name here corresponds with one of the column header in the import file (xlsx/ csv).
Type/ Field/ Lookup: these determine in which field in Workplace the imported value should be stored.
Example: look at line 80 of the screenshot above. With these setting, the value in the column ‘City’ of the import file will be stored in the field address.city (the object is Address ) in Workplace.
Key values
The key values of a mapping are found in the column ‘Key’. The value ‘Yes’ means this field is a key value.
Key values are mandatory fields for an import to work. Meaning, the import will not run properly when Key values are missing.
Key values are used to determine if a new object needs to be created OR that an existing object is updated. To determine this, Workplace checks if an objects already exists that contains ALL the key values.
How does this work? In the Address mapping (see screenshot above), the Street, Nr, and PostalCode are the key values. The values in the import file could for example be: Second Street, 55, 1234AB. The mapping will check if an address with these characteristics exist. One of the following actions will take place:
If the the address exists, the non-key values are updated.
If the address does not yet exist, a new address is created where the key and non-key values are stored.
| Info |
|---|
There are a lot of ways to configure mapping. Hover over the setting to see the help text. The case described above assumes that the setting “Create object = yes”; meaning a new object is created when no exact match is found for the key values. |
Import connectors and mappings background information
Almost all imports connectors that are available consist out of multiple import mappings. For every object that we want to import a separate mapping is needed. However, this is not the only reason we use different mappings. We could also use one specific mapping to only set the hierarchy of an object, so for properties this could be the hierarchy within the imported properties.
Running a mapping instead of the import connector
We are able to run mappings separately from their connector:
Open the import connector;
Navigate to the details tab;
Select a mapping by clicking on the magnifying glass next to the mapping name;
Click import;
Select your file.
The import will then run and display the results on the page.
Connector settings
Only partner users can change the settings of the connectors. Please note that if it concerns a default import, also Partner Users do not have access, because the import definition is inherited from the baseline. We will go over some important settings in the table below. Hover over a setting in Workplace to see the help text of other settings.
...
Setting
...
Description
...
Log
...
When set to ‘yes’, maintains a log of all actions
...
Save documents
...
When set to ‘yes’, stores all import/export documents. Document are usually removed after 1 month. This can be changed on the ‘Documents’ tab of the client settings by altering the “Cleanup documents after“ setting.
...
Save warnings
...
If set to ‘yes’, stores warnings when there are no errors. When set to ‘no’ the error document containing warnings/errors is only generated when there was at least one error.
Automating an import or export
It is also possible to set up imports to be executed periodically from an FTP server.
Automated export can be set up to export date to: FTP servers and email.
Summary
| Rw ui textbox macro |
|---|
|
Search
...
. General
Importing data allows you to bring information from external sources, such as files from other systems, into Workplace Management. This process involves:
Preparing the data to meet application requirements.
Uploading the data.
Validating for errors.
Once imported, the data is consolidated into a single platform and becomes available for various tasks like managing processes, generating reports, or conducting analyses. This ensures your information is organized and ready for use.
2. Technical background information
| Info |
|---|
This chapter goes into some technical details regarding how the default imports map the data to the relevant objects in Workplace Management, this information is only informative and no additional actions are needed when using the default imports, as this is already setup correctly. |
Workplace Management provides default import options for all relevant (master) data objects. These imports, technically referred to as ‘FtpConnector’, allow bulk data to be imported or exported, either manually or periodically. This article focuses specifically on manually importing (master) data using the default import options. Other methods, such as periodic imports from an SFTP location, are not covered here.
An import consists of one or more mappings, technically referred to as ‘FieldMap’. Each mapping defines:
The object to be imported.
The relationship between the column headers in the import file and the corresponding fields in Workplace Management.
Importantly, each mapping is related to only one object.
Example:
The following objects are relevant for the ‘own employees and users’ import:
Object: Person
Personal information such as first name and last name.
Object: Contact
Contact information such as phone number and email address.
Object: SystemUser
System user details such as the username.
Object: SystemGroupContact
Authorization details such as specific access rights assigned to the system user.
In this example, the default import ‘own employees and users’ will consist of 4 mappings, one for each object:
...
Depending on the import, there could be 1 mapping or 10 mappings linked to the import. Each mapping imports a part of the applicable object(s).
More information about the settings of the import and linked mappings will be applicable when customizing, see: /wiki/spaces/KB/pages/958562341.
3. Available default imports
In Workplace Management, the default available imports can be used to import (master) data in bulk. This data can be used in all relevant modules, used by the customer. For example, the building and area data is used in modules like ‘Reservations’ and ‘Requests’.
Module-Based Availability
Default imports are automatically enabled based on the activated modules. When a module is activated, its associated default imports are displayed directly within the module activation interface. All (generated) default imports can also be found via the administrators startboard:
...
From the 'Default imports' menu on the administrators startboard all activated default imports can be found. From this overview, the imports can be deleted and regenerated. When regenerating a default import, it will always fetch the latest version of the import.
| Info |
|---|
Regenerating a default import does only work, if the relevant (old) import is deleted first. Regenerating an import will copy the latest version of the import template and mappings from the baseline to the customers environment. This way the template will always correspond with the import on the customer environment and any changes done on the baseline will not “break“ the import for the customer. Imports can only be deleted by partner users. |
...
Via the 'Email import templates for selected items' the applicable template files can be send to the user. More on the templates explained below.
When opening an import, more information is directly shown on the ‘General’ page. The ‘Details' tab is only visible for level 3 administrators. On this tab, more details of the import and more technical settings are visible. The 'Log’ tab shows when the import has been executed.
...
4. Templates
Via the button 'Download template', the applicable template is directly downloaded to your computer. This file is an Excel file with multiple sheets. The import template includes a sheet with instructions, one or more sheets to fill in the data to be imported and possibly more sheets to enter client specific details e.g. custom area categories.
...
Via the 'Default imports' overview on the administrators startboard, it is possible to directly email the import templates of the selected imports to yourself. This email can then be forwarded to the customer, if desired.
...
More information on how to fil in the template, see: How to use the import templates.
| Info |
|---|
An import template or various mappings can be updated after a Workplace Management release. Make sure to regenerate the imports after a release, before sending it to the customer, to prevent sending an old import template, which might miss new mappings. |
5. Importing data
The import templates are .XLSX files, which are send to the customer to fill in all relevant (master) data. Once the customer is done filling in the import file(s), the customer sends back the files to the implementation consultant to import the data.
Before the data can be imported, the implementation consultant:
Opens the file(s) and confirmed all required data filled in (e.g. name of the building)
Opens the file(s) and confirmed all data filled in is according to the instructions (e.g. selected the correct option from a drop-down list)
Verifies that no relevant columns have been deleted
Saves the sheet with data to be imported as a .CSV file (for all sheets that contain data to be imported)
Verifies the field delimiter in the CSV file corresponds with the field delimiter of the user importing the data
If the file is filled in correctly by the customer, the process can continue. Via the 'Upload import file' button, the import can be executed.
| Note |
|---|
Avoid large imports (e.g. > 10.000 rows) during busy office hours: Large imports can strain system performance. Start such imports after office hours. |
...
More information about the file to be uploaded, is shown on the top of the screen:
...
The .CSV file to be imported, can be dragged and dropped in the dedicated location (grey block), or the file can be searched via the 'Selected files' option.
As soon as the file is dropped, the message is shown that the import is executed in the background. A background task is executed (as it states) in the background, while the user can continue with other things in the meantime.
...
Via the icon next to the username in the top right corner of the screen, any yet to start, ongoing and finished imports (background tasks) can be found:
...
Once the import is finished, the log files are generated and shown in the include 'Generated import documents (last 24 hours)'. This include shows log files which show the correctly imported data and possibly errors that might occurred. More information about these log files in the next chapter.
6. Log files for validation and debugging
By default the imports log files, generated after importing data, are shown via the include 'Generated import documents (last 24 hours)'. As described, the documents shown here are not older than 24 hours.
...
| Info |
|---|
Documents generated in the last 24 hours are shown by default. Older objects can still be found via the document search option found as an icon on the top right corner of the screen.  |
After the import is completed, one or more log files are shown:
The file which was uploaded
A log file which indicates per line what has been imported
A log file with all errors
If only the first 2 files are shown, that means that no errors occurred and the data was imported correctly, or there was nothing to update in already imported data.
...
If the error file is also shown, then the import was (partly) unsuccessful. With this file, the debugging process can begin to see what is the reason the errors occurred. Below an overview of the most common error/warning messages.
6.1 Most common error messages
Message | Possible cause | Solution |
|---|---|---|
Field does not exist |
|
|
Warning cannot find referenced object |
|
|
Creating or looking up an object for this key combination has failed |
|
|
Example error log 1:
...
There is no language with the identifier 'ent', so it could not be linked to the imported organization
There is no address with the combined identifier address: Groningensingel, nr: 11, addressLetter: C, postalReference: 6836 EA, so it could not be linked to the imported organization
There is no organization with the identifier ‘ORGANIZATION-7’ to be linked as the parent organization of the imported department
The above errors are considered warnings. The object itself is imported, but specific fields (e.g. language, address and parent organization) could not be set.
Example error log 2:
...
The area import is executed before the building import. When importing the areas, the corresponding building (which is part of the key to import areas) cannot be found.
This is considered an error, which results in the areas not being imported.