Record Importer

Purpose

The purpose of the Record Importer is to accept a csv from an external data source, such as an excel spreadsheet, database export, or legacy application.

Deprecated

Note, this import processing methodology has been deprecated. It's still functional for now, but new development has been halted.

The new method for importing records provides much more control and power for our Software Developers, and can be found in the File Attachment and Enterprise Services Bus areas of the tools.

Accessing the Record Importer

The Record Importer can be accessed via the tools section of the WorkXpress Handbook.

Adding an Import Profile

When adding a Record Importer profile there is a choice presented for if the profile rolls out with the application. Profiles that roll out with the application can be used in record import actions and or ran again in testing and production environments without having to recreate the profile in the respective environments. If an import profile is only going to be used to load sample data for building purposes the profile should not roll out with the application.

Parts of an Import Profile

CSV Setup

Profile Name

The profile name will be used to reference this profile.

Run Priority

When an import profile is ran by default it is ran as a background process. To keep very large imports from slowing down the system while others use it it is recommended that the run priority of the profile be lowered.

File

The file to be imported must be a CSV file, comma separated values. The file can either be uploaded directly to the import profile or it can be looked up in a remote ftp location. It is also possible to skip the lines at the top of the file if they contain column title.

Column titles

If zero rows are skipped then the system will assign names to the columns starting with “Column A”. If 1 or more rows are skipped then the values in the first row will be used as the column titles.

Commas within a string

If you need to place commas inside a column the proper way to deal with it is to surround the data using double quotes. If you need to place a double quote inside the data that is quoted then precede the double quote with another double quote. For example

“Apple,Banana,Grape”,“This is a ”“Test”“ Column”,“Cat”

Will generate 3 columns the first will contain the data Apple, Banana, Grape. The second column will be This is a “Test” Column. The last column would be just be Cat.

Tables and Relationship Tables

Selecting Tables and Relationship Tables

To either start a query or reset an existing query click on the “Start New Query” button and choose a starting table. After you select the table that this import profile will create or update records the builder can choose if this profile is going to strictly add new records or update existing records with the option to add records.

Add New Records

When this option is selected the import will always create a record for the table. The advantage of always adding new records is the processing will be quicker because the import will not have to check for any existing records to update. The disadvantage is that the import profile is not as easy to run a second time. If it is run a second time with the same csv then the system will create duplicate entries. This means that the builder has to either delete the records before re-importing or it needs to be acceptable that duplicate records exist in the system.

Update Existing Records

When choosing this option the builder must choose a column in the csv and a field on the table that the column corresponds to. The system will then run a search for a record where the value in the field matches the value in the column. If more then one record matches the search only one of the records will be updated. The advantage of first checking for an existing record and then updating it and only adding when a match is not found is that the profile is much easier to reuse even using the same csv file. The disadvantage is the increased overhead of searching for a record on each row of the import. If the builder does not choose to add new records when a match is not found then that row of the provided csv will be skipped.

Multiple Tables with Relationship Tables

It is possible to add or update multiple related tables by including values in one row that correspond to the values needed in all of the tables provided. It is not possible to use one import profile to add multiple unrelated tables in a single profile. When dealing with multiple table it is possible to update one table while adding records to another table. For example you have a csv that matches an Employee record to a Organization record. If the first column is the Organization Name and the second column is the Employee Name the build could choose to update existing Organizations and update existing Employees and always create a new relationship record. In this case the import would search or an Organization and Employee and if it found them both it would create the relationship record between them.

Fields

For every column in the csv it is possible to assign a matching field in the system where the value in the column will be saved. It is not required to save any field values when setting up a record import profile. If a field is selected then when a record is created or found that value will be written into the field selected. Some fields may have additional information needed so that they can save properly. For example the csv may have a column that has “Yes” and “No” in it and the builder wants to set the value into a checkbox field so the system will need to know that “Yes” corresponds to checked.

It is also possible to mark a column in the csv as required. If a column is marked as required and the csv does not contain a value for that column in a row that is being processed the row will be skipped regardless of the preference to add a record or to update a record.

Actions

During the import process by default actions will run as they normally would. This means any actions on the pre and post record save triggers and actions on the pre and post field save triggers will be ran. If the actions are not needed or would be detrimental during the import because for example the actions are used to calculate a value but the row being imported already contains the calculated values then it is possible to turn off the actions. The other advantage of turning off the actions when possible is the import will run faster. It is important to note that these options turn off all the actions for all the fields and records.

Run

After the import profile is completed the run tab can be used to execute the import profile. When the import profile is ran for the first time it will be saved for later reuse. If an import profile is not ran it will not be saved and any configuration will be lost when the window is closed.

Accessing the Record Importer in Testing and Production Applications

Remove the Url

To access and use the Record Importer in an application that is in the Testing or Production role simply remove the text after your application's url address:

Replace the Url

In place of the information removed, paste /im_tools/import_profiles.php

This will launch the Import Profiles created in your application to use in Testing and Production.

record importer.txt · Last modified: 2016/09/14 14:19 (external edit)
Copyright WorkXpress, 2019