The data import features allows you to import much of the data that would be manually generated into the system by users. This option will allow Org Admins (must be Org admin) to import large amounts of data via a .xls file.
Types of Data that can be imported
- Cases - Case related information
- Persons - Persons but not Users
- Items - Items that are related to the cases
- Legacy Chain of Custody
- Users - Users that are in the Settings > User Admin section.
- Notes - Notes related to items or cases
- Legacy Task (From Trackers' legacy Fluid system)
- Media (From Trackers' legacy Fluid system)
Template XLS fileYou can download the template XLS files from here. XLS Version or Zip Version
Basics of the Excel File
- .xls or .xlsx file is required.
- Each file should contain only one tab of data (if using the template file above, save in separate sheets), the importer will only recognize the first tab if multiple uploaded).
- The first row should be header information. This will be used for mapping data to fields in the system.
- Each row in the file should be a separate 'thing'. For example, if importing cases, each row should be a single case. If an item, each row is a separate item.
- The file can contain as many columns of data as you have. You will have the opportunity to map each column to a data container within the system.
- You do not need to include every required field in the file. There is an option to create a default value that is applied to everything being imported. For example, if when importing a Case the Offense Date field is required, and you don't have this value in your data, you can default the Offense Date to 1/1/1970 and every imported record will have this date. This can be done with any import field.
Phase 1. Preparing the XLSIn many cases, you will use the XLS template file provided to get your data into a format necessary for import. Follow the rules outlined above for Basics of the Excel file and move / organize your data into the template.
Phase 2. Import XLS File
- Go to Tools > Data Import
- Either drag and drop your file to the upload box OR click Upload Files box and select your .xls for upload.
- The file will begin to process and upload the file.
- If the file errors (red thumbs down button) you can retry or close your browser and try again.
- If you get this pop-up box then the file successfully uploaded.
- Select the Type of import you just completed from the drop down. If this .xls file is a list of your items, then select the Items option.
- Click Ok and you will be directed back to your main menu where you will see your newly imported file.Possible Errors - There are several errors you might receive when importing the .xls file. In many cases, you can retry and it will go. Also, make sure all the minimal requirements have been met (see 'Basics of an Excel file' instructions above). If you can't get your file to import please contact your Tracker representative for help.
Edit or Delete the Import File
- To Edit or Delete the import file, click on the file (which will highlight the row in blue an then go to Actions > Edit or Delete.
Phase 3. Map DataNow we map all the columns in your .xls File to data fields within the Safe system.
- Click on the record set you want to map / import.
- Click Next
- Map all your data (left column) to Fields that are available in the system.
The second column in the Mapping Area allows you to pick which field you want to place the imported value. The drop down will contain options avialble for the type of data you are importing. First will be all the default values and then custom field values. You can pick any field available.
- As you are mapping fields, you may find it helpful to use the Notes field for each row. This could be used to help track work you have done for your import specific to that row.
In this example, I am choosing to ignore one of my xls file and I place a note that it was ignored.
Mapping Required FieldsEvery type of data import has certain fields that must be included. If your excel file does not include these required fields then you have two options. 1. Update the excel data to include the required field or 2. Set a default value for all imported fields.
To map a default value for all imported records, go to the bottom of your Map Data screen and select a value (left column) with a mapped field (right column). You may click the + button to add as many default values as you want or need. In this example, I have mapped out four fields that will be applied to every item. Your data import will vary greatly.
Common Fields you will typically set in this area
- Office ID - This can be found in Settings > Offices
- Active - 1 for yes and 0 for no
- Deceased (Persons) - 1 for yes and 0 for no. If you don't know select 0.
- Juvenile (Persons) - 1 for yes and 0 for no. If you don't know select 0.
- Race (Persons) - Typically set to 'Unknown'
- Gender (Persons) - Typically set to 'Unknown'
Mapping Custom FieldsIf you are importing data into custom fields (Settings > Custom Forms), the drop-down field for mappings will not only show all the Safe basic fields but also custom forms. If you find, during this process, that you need more fields added to the custom form, you can create and apply those values from this screen.
Go to the Add new Custom Field option at the bottom of the screen.
- Select the custom form from the Select User Form
- Select the type of custom field in the Select field type field
- Name the field in the 3rd box.
- Click Add Field and the new custom field will be generated in the custom form
- Go back to your field mappings and map the row to the new custom field value
Mapping Single Excel value to Multiple FieldsIf you have a single XLS column that should apply to multiple Safe fields, just click the + button (far right of import row) and you can map that single column of data to multiple fields.
In this example, I am mapping the single excel column (offensedate) to both the Recovery Date field and Date Created field.
Click the 'show Unique Values' button (eye icon) to show a list of all unique values in the column with options to translate.
If you do translate a value (take source value and change to another value) make sure you keep the necessary format in place (make sure you do not change or remove the quote marks or colon).
Validating Unique and Date Fields
Phase 4. Import Validation / Fixing ErrorsOnce you have completed mapping all your fields, added any default mappings & translated any values, you are ready to proceed to the import phase. Most likely, and especially if this is your first import, you are going to get errors.
If the import validation does pick up error, you will need to fix these before you can proceed to the final state of import. The errors should be very specific as to the issue and each error should indicate which row of the XLS doc is a problem. You might have to proceed through this effort a couple of times to get your import to meet all requirements.