Data Import Manual

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.


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).
• 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.

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 technical support.



NOTE: The tables in the list are not in alphabetical order.

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 format. The headers of the columns in your 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 format, proceed as follows:
                        With the Excel spreadsheet open, click on "Save As":
                        In the Save As screen, choose the file type "CSV (Comma delimited)".



                        After selecting, click "Save"
                     
  IMPORTANT! No field in the file may contain a comma (,) if you are using the comma 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 file appear 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 ready to import the data, click on the blue "Import Data Now!" link.

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. This will be discussed later on.

NOTE: As you can see in the example below, the header is being rejected because we do not want to import that.



7. 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 see a screen like this:



You can download a spreadsheet of the rejected records, by clicking the link.

NOTE: 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.

8. 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.

Just go back to the first screen of the import tool, select the table, and click the Proceed button.


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). Just select the one you want 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.