Data Import Tool
The Data Import tool allows you to use data from a spreadsheet to automatically create 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.
eMaint Data Import Template
The process of importing data to the X3 system is based on the following rules:
- The default file must be in CSV format (CSV Comma Delimitted) or TXT format (TXT Tab Delimited.
- The fields of the X3 system must be in the header.
- The file must contain the key field for the table.
- The file cannot contain spaces or special characters in the key field.
- The text in the columns cannot exceed the maximum character length of the corresponding fields.
- The file cannot contain commas (,) or quotation marks (“) in any field when using CSV. Tab Delimited will allow commas and/or quotation marks, but cannot contain any Tab spaces.
The instructions to properly import data into the system are as follows:
1. Accessing the Data Import tool.
You can access the data import tool by clicking the Data Import menu option, on the Navigation tab. It can be found under the Administration heading.
2. Selecting a Table
The first step is to choose the correct table. Choose the table to which you’re importing records. If the table is not shown in the list, please call eMaint support.
3. Selecting your file.
After selecting the table, choose the file to import. Click the Browse... button. A window will open on your screen where you can choose your file. The file must be in CSV (Comma Delimited) or TXT (Tab Delimited) format.
The headers of the columns in your excel file should match the field names of the fields to which they correspond. For example, the field name of the Asset Description field is comp_desc so the header of the column that holds the asset descriptions in your spreadsheet should also say comp_desc. The key field cannot contain spaces or special characters (eg @ # $% / “ , õ ç among others) and is limited to a certain number of characters (Asset ID: 21, Item No: 15, Contact ID: 10, etc). Key fields can contain letters, numbers, hyphens (-), and underscores (_).
NOTE: You can increase the character length of ordinary fields, but not key fields.
To create a file in CSV or TXT format, proceed as follows:
With the Excel spreadsheet open, click on Save As. Under the Save as type, you can choose between the CSV and TXT file types that can be used for importing.
IMPORTANT! No field in the file may contain a comma (,) if you are using the CSV as your delimiter. The CSV file is created for only one sheet (if you have more than one tab in the Excel document), so you need to save a separate CSV file for each sheet in the excel workbook.
Once you’ve created the file, you can select it in the import tool. When you've selected your file, use the checkboxes below to select the different options, before clicking Upload.
4. Selecting appropriate options
On this page, you will find a number of checkboxes that represent different options.
NOTE: The options available here will vary, depending on the table to which you're importing.
- Import File Delimiter: This allows you to select which type of separator you’re using in your CSV file; comma (default), “|” symbol, or TAB. (make sure the delimiter you choose doesn’t appear in any cell if your file)
- Overwrite existing records: Checking this box indicates that you want to update records that are already in the system, rather than importing only new records. If this is unchecked, duplicates will be rejected. When updating existing records, the spreadsheet should only include columns for the key field and the fields you want to change.
- Strip out quotation marks: Quotation marks are not allowed in the import process. If they haven’t been removed from the file, checking this box will take them out.
- Strip Header Row on Final Import: This will cause the header of the spreadsheet to be rejected, so the system will not mistake it for a record.
- Execute Workflows: If you have workflows configured to be processed when a record in this table is saved (In this example: Assets), checking this box will allow those workflows to be executed for each record being imported.
- Auto Generate Counters \ Unique ID's: When using certain tables (PM’s, Work Orders, etc), you’ll have this option. This will cause the system to automatically generate the values for the key field, based on the WO No counter (work orders) or a randomly generated value (PM’s).
Once you have selected the file and the appropriate options, click the Upload button.
5. Mapping import columns to fields
After you click Upload, a screen will appear that shows you the columns on your spreadsheet and the corresponding fields in the system. The headers of the CSV or TXT file appears in the left column. The right column shows the corresponding fields that are in the account. This allows you to select which columns of data (from the spreadsheet) should be imported into which fields in the system.
Clicking the checkbox Preview Exceptions Only will return only a list of records that are going to be rejected, along with the explanations. Leaving this box blank will display the entire list of records in the process.
When all fields are in order, click the Proceed to Preview button to continue with the upload.
NOTE: The key field must be included here (example: Asset ID).
6. Confirmation List
The screen below shows a summary of the data to be imported. Review the list of exceptions and identify any problems with the data before finalizing the import. You can edit the data on the spreadsheet to correct those lines and then start over. Keep in mind that there may be multiple pages of data, so check all pages for errors. The six lines in the list at the bottom of the screen represent the details of error codes, which correspond to the numbers in the left column.
When you click Import Data Now!, the system will create a backup of the table before importing the records. In case of emergency, you can restore from a backup within a 2 hour period. This will be discussed later on.
NOTE: As you can see in the example below, the header is included on the import. This field will be ignored once the import process begins and will not be stored into the system.
Importing
The import process will start. Do not try to leave or refresh the page during this. When it’s finished, it will say Process Complete. If there are any rejected records, you will get a screen that allows you to download an excel spreadsheet with the data that was rejected.
NOTES:
- If records were rejected due to a value exceeding the maximum length of a field, you will have an option on this screen to increase the length of that field (as long as it’s not the key field). After clicking the link, you will see a screen that confirms that the field size was increased. then, you’ll be able to import the rejected records.
- It may be necessary to adjust the length of the fields in your system to accommodate the data you wish to import. The number of characters in a field in eMaint is finite and any adjustments to it require you to run a process in the system called Verified Structures. To run Verified Structures, please refer to this “LINK” to go over the process.
Restoring a table
Whenever you do an data import, the system will automatically create a backup of the entire table to which you’re importing (work orders, assets, contacts, etc). If, for any reason, the change you’ve just made causes a problem, you can revert the table back to the way it was right before the import.
WARNING: The Parts table or Inventory is the one table that cannot be restored because of the many tables that use part information within the system. Before importing into this table, make absolutely sure that no errors show up and that the data you want to import as new information or edited data is correct.
To restore a table, go back into the Import Data option on the Navigation page and select the table that you wish to restore.
Click the Restore button. You will be taken to a screen with a dropdown box that lists the backup files that have been made.
You can see the time and date of the backup (times are in Eastern time). Select the time you want to roll back to and click Proceed. After a moment, you will receive a message that tells you the table has been restored. All records in the table will go back to the way they were.