This session will show how to quickly enter data into eMaint X5 using the Import Data tool. You will also learn how to export data from the system.
1 - Preparing Spreadsheet
The data import tool allows you to use data from a spreadsheet to create multiple records in the system. You can also use this tool to make mass updates to existing records. This can save you hours of data entry time.
We have created an import template that will make this process much easier for you. This template can be provided by the eMaint team and it has simple instructions, across the top of the columns, to help you when entering your data.
When preparing a spreadsheet with the purpose of importing data into the X5 system, it’s important to understand the following rules:
- The default file must be saved in either CSV (Comma Delimited) or CSV UTF-8 (Comma delimited) formats. We recommend to use the latter for every import, so that if your file contains any special language characters, the system can accommodate them upon import.
- While not required, it’s good practice to have the spreadsheet header match the field name, so that the system pre-selects the correct mapping for you. If the names don’t match, you will need to pick the column to match the header yourself.
- When you are importing new data, the system will automatically create unique IDs for each record. However, when you are making an import to update existing records, then your file must contain the key field for each record, which is a 36 character ID created by the system.
- The text in the columns cannot exceed the maximum character length of the corresponding fields.
- Required fields must be present in the file. For example, the “Calendar” field needs to be in the file, when importing Assets and Contacts.
- The Form ID must be present in all files.
- The CSV file cannot contain commas (,) or quotation marks (“) anywhere, so you would need to perform a “Find and Replace” on your spreadsheet before saving the file for import.
With these rules in mind, let’s create a simple spreadsheet to practice importing data into an account. Create a new Excel spreadsheet then follow the steps below:
- A - Create 9 headers (Contact ID, Email, First Name, Last Name, Full Name, Contact Type, Company, Calendar Id and Form Id). Each column will represent an existing field on your Contact form. Now populate the next four rows down with information about these Contacts.
- B - On the excel file, click the “File” tab, then hit “Save As”.
- C - Make sure to select CSV UTF-8 (Comma delimited) into the “Save as Type” area (save it in a folder where you can easily locate it in your computer).
You may have noticed that the highlighted columns above have numbers on them. These numbers are the IDs for options within lookups (references), Forms and Calendars. In this example, the number “2” in the “Contact Type” column corresponds to “Electricians”, but how would you know what number corresponds to which value?
To make sure that you have the right numbers to put in the import file for the Contact Type lookup field, for example, you will first need to navigate into the Account Settings, then follow the steps below:
- A - Click on References.
- B - Search for Contact Types, then click on it.
- C - On this list, you will see all the contact types in your account and will be able to see the number that corresponds to each option.
To make sure that you have the right numbers to put in the import file for the “Calendars” and “Forms”, you will first need to navigate into the Account Settings, then go into each one of these screens and see what numbers correspond to them.
2 - Importing New Data
Now that your file has been created properly, you are ready to import it into the Contact table in your X5 account:
- A - Go into Account Settings.
- B - Click on “Data Import” on the left menu.
- C - Click on the “New” button, then select “Create”, since you are sending brand new data into the system.
- A - Select “Contacts” into the Entity field.
- B - Select the Site that you want to send this data to.
- C - Click the “Choose File” button and select your CSV file.
- D - On this next screen, you will need to match the “Entity fields” with the “CSV columns” to assure that the information on your spreadsheet will be sent to the right fields in the Contact entity. In this case, since a header was provided in the spreadsheet, the system matched all of them automatically.
- E - When all fields are in order, click the "Validate relationships for import" button to continue with the upload.
The next screen shows a summary of the data to be imported. If there are any issues with your data, the system will fail those records and will provide the option to download an excel file with all the records where items need to be corrected.
- F - When you are ready to import the data, click on the “Import” button.
Now, navigate to the Contact list for that site, filter for those records and you will see that they are now in the system.
Removing Imports
If for any reason you need to undo the import, go into the Account Settings ,then follow the steps below:
- A - Click on “Data Import” option on the left menu.
- B - Click on “Show completed imports”.
- C - Select the import file that you would like to remove.
- D - Hit the “Remove Imports” button.
3 - Exporting Data & Updating Existing Records
In X5, you will also be able to make imports to update existing records. If say you want to provide a phone number for each contact in the system, since you didn't do it when you first imported these contacts.
The easiest way to start this process is to Export data from the Contact list view.
When you export the data, the CSV file will contain the data for all fields that are currently on the listview and, in addition, it will give you the 36 character unique ID for each record. These IDs are very important as they are required to have when doing an import with the purpose of updating existing records.
In the spreadsheet, remove all the columns that don’t need to be updated. In this example, only keep the “Work Phone” and the “ID” fields.
Then provide the phone numbers for each record and save your file as CSV UTF-8 (Comma delimited).
Navigate to Account settings, click on “Data Import”, click on “New”, then select “Update”.
- A - Select “Contacts” into the Entity field.
- B - Select the Site that you want to send this data to.
- C - Click the “Choose File” button and select your CSV file.
- D - On this next screen, you will need to match the “Entity fields” with the “CSV columns” to assure that the information on your spreadsheet will be sent to the right fields in the Contact entity. In this case, since a header was provided in the spreadsheet, the system matched all of them automatically.
- E - When all fields are in order, click the "Update records" button to continue with the upload.
Now, navigate to the Contact list for that site, filter for those records and you will see that the phone number has been added to your records.
