The purpose of this utility is to allow ChilliDB users to import multiple records retrieved from a list of values stored in an external CSV, XML, XLS, or XLSX file. Currently it is possible to import data into Contact, Organisation, Note, User, or Client Administration.
The use of this utility requires special formatting of files and possible post migration cleanup activities and any attempt to use the utility should be performed carefully ensuring database backups are taken before commencing. For clients who are using Polymorphic Solutions hosted service for their ChilliDB systems will have their database backed up nightly.
The data loader is acessed underneath the System > Data Management > Data Loader menu. You must have the data loader user ticket to see it.
Input Data File Format
If you are importing using CSV files, they must be formatted as follows:
Line Delimiter (Line Separator) - each CSV row should be separated by a delimiter character or string.
By default, a carriage return (new line character) will be used as the line delimiter.
Polymorphic Solutions recommends the user to use "#!!#" (without the double quotes) as delimiter for each CSV row as it is unlikely to be present within the data itself.
The user can however specify other delimiter format as long as it does not match the common used delimiters such as commas ( , ), slashes ( / ), dashes ( - ) or hashes ( # ).
Column Delimiter - each CSV column should also be separated by a delimiter character or string.
By default, a comma ( , ) will be used as the column delimiter. The user can however specify other delimiter format to separate the CSV fields. The use of commas as column delimiter is a standard within CSV file, however there are some cases when the comma is being used as a valid content. In this case, the user may want to specify another character as column delimiter to be able separate the correct values.
Despite the utility considers commas which are contained within the CSV cell values as delimiter, Polymorphic Solutions recommends the user to use pipe character "|" (without the double quotes) as delimiters for each CSV column value as sometimes commas can be used as valid values within the file.
The example below shows how the CSV file content can be formatted. It is permissible for data to span rows provided the row delimiters are present
Firstname | Lastname | Organisation | Phone | Fax | Email #!!# Joe | Blogs | Polymorphic Solutions | 07 3274 2356 | 07 3274 5101 | joeblogs@polymorphicsolutions.com.au #!!# Mary | Jane | ACME Solutions | 07 3274 2345 | 07 3275 5111 | mjane@acme.org.au #!!#
Processing Rules
Entity Dependency
The data import tool does not currently check for dependency within the uploaded data. In order to ensure that the Contact, Organisation and Note data linked properly, the following steps must be followed in order when processing the data using this tool:
1. Upload all organisation data including all Organisations linked to the contact Data
If there are some identifiers linked to the old system, they can be uploaded as external identifier for the organisations and can be used to identify duplicates when updating existing data
The external identifiers can later also be used to link the Organisations with the Note data
2. Upload all contact data including all Contacts linked to the Note data
If there are some identifiers linked to the old system, they can be uploaded as external identifier for the contacts and can be used to identify duplicates when updating existing data
The external identifiers can later also be used to linle the contacts wit hthe note data
3. Upload all note data
By default, the Note data will be able to be linked with existing Organisation records using their name. If the user wants to link the Note against the existing Contact records, then the users will need to keep the external identifiers of the Contacts and have them linked to the proper Note row in the file. The user should ensure that the external identifiers have been uploaded along with the Contact data. The user can also link the Note with the Organisation external identifiers. In doing so, the user should also ensure that the external identifiers have been uploaded along with the Organisation data.
Not following the above steps in order may cause the Contacts not to be associated with the correct Organisations, or Notes not to be associated with the correct Contacts and Organisations.
Reference Data
Hierarchical Data - Type, Category, and Sub Category
Type, Category and Sub Category contains hierarchy linkage which requires them to be inserted appropriately according to their parent-child relationship. The hierarchical order starts from the Type at the highest, then followed by Category and the last bottom one would be the Sub Category.
Whenever the data import utility processes each record row from the input file, the text values defined for those Type, Category and Sub Category will be collected first at application level before checking the existing Type, Category, and Sub Category in the database.
If the Type does not exist in the database, the Type will be automatically created as the new reference data. The same process will also apply to the Category and Sub Category relationship. If a Category does not exist in the database, it will be created as a new reference data.
General Reference Data (Other than Type, Category, and Sub Category)
Whenever the data import utility processes each record row from the input file, the text value will be checked if it exists in the database. If the checked value does not exist in the database, the value will be automatically inserted into the database.
Organisation
Inserting or updating an Organisation requires a set of mandatory parameters to be able to insert/update the data properly. These mandatory parameters are described as follows:
Type: The Type of the Organisation
Organisation Name
Notes:
Organisation Status will only accepted two values i.e. "A" for Active and "I" for Inactive. When assigning a mapping column for Organisation Status, if the status value of the input file is stated as "Active" or "Inactive", the data import utility will pick up the first character of that word and converted to "A" or "I". Everything else will be converted as empty text. The utility will consider any word beginning with letter of "A" or "I" to treat it as "Active" or "Inactive"
Duplicate Handling
The duplicates which may occur during insertion of Organisation records can be prevented by assigning a key field/column in the input file to an external identifier field in ChilliDB database. The key value from the input file will be verified against the existing values in the external identifier field for any record to be inserted and if the value matches, an update function will be executed instead of the insert function.
If external identifier is not defined in the mapping list, the Organisation's name will be used to match for existing Organisation records in the system.
Contact
Inserting or updating a Contact requires a set of mandatory parameters to be able to insert/update the data properly.
These mandatory parameters are described as follows:
Type: The Type of the Contact
Contact First Name
Contact Surname
Organisation Name
Duplicate Handling
The duplicates which may occur during insertion of Contact record can be prevented by assigning a key field/column in the input file to an external identifier field in ChilliDB database. The key value from the input file will be verified against the existing values in the external identifier field for any record to be inserted and if the value matches, an update function will be executed instead of the insert function.
If external identifier is not defined in the mapping list, the combination of Contact's first name, middle name, and surname will be used to match for existing Contact records in the system.
Note
Inserting or updating an Note requires a set of mandatory parameters to be able to insert/update the data properly. These mandatory parameters are described as follows:
Type: The Type of the Note
Organisation Name
Contact External Identifier: This is the original Contact unique identifier in the client external database.
Organisation External Identifier: This is the original Organisation unique identifier in the organisation external database
Notes
Note Status will only accepted two values i.e. "C" for Closed and "O" for Open. The utility will consider any word beginning with letter of "C" or "O" to treat it as "Closed" or "Open". When assigning a mapping column for Note Status, if the status value of the input file is stated as "Closed" or "Open", the data import utility will pick up the first character of that word and converted to "C" or "O". Everything else will be converted as "C" as Closed is the default status for Note item within ChilliDB.
Duplicate Handling:
The duplicates which may occur during insertion of Note records can be prevented by assigning a key field/column in the input file to an external identifier field in ChilliDB database.
The key value from the input file will be verified against the existing values in the external identifier field for any record to be inserted and if the value matches, an update function will be executed instead of the insert function.
Address
The data import utility supports Business Mail Address, Business Physical Address, Home Mail and Home Physical Address to be inserted or updated.
Communication
The data import utility supports Business Phone, Personal Phone, Business Fax, Personal Fax, Business Email and personal email Address to be inserted or updated.
Custom Fields
Currently the data import utility supports a number of types for creating new Custom Field within ChilliDB. The following list describes the available types, and the expected input values:
Text - expected to receive any characters.
Integer - expected to receive absolute numbers only. Any decimal point number will be rounded down to the nearest absolute number.
Decimal - expected to receive floating point numbers only. Any integer number will have additional .00 appended to the number.
Date - expected to receive the following format as text:
- 30-Nov-52
- 30/11/1952
- 30 Nov 1952
- Any time value appended to the date will be dropped when storing
- When using csv files, DD-MMM-YY format must be used
Creating Custom Fields
Whenever the data import utility creates a new Custom Field into ChilliDB database, it is stored in a custom section called “Migrated Data”.
The data loader will check if there is already an existing Custom Field with the same name.