Contents

Using the Data Import Tool

Mohammed Jamal Updated by Mohammed Jamal

Preparing the Data Import File

The application includes data import functionality, which is designed to enable you to import relatively small data files into the system. The basis for this functionality is largely re-used for the data migration process; however, there are additional tools in place to aid bulk migration. This article is focuses instead on the bulk migration process.

In order to import data into the system, the file format needs to be in a particular format, following the conventions specified below.

The file format is: Double quote and Comma delimited.

However, the load will be slightly more efficient if you omit the leading double quote. This is because the double quote and comma delimited format is not strictly supported by the SQL Server bulk insert functionality. The initial load imports the first double quote in the left-most field as if it is part of the field text, which then must be removed. If the leading double quote is not present in the first column, this step can be skipped.

Below is an example of a file format with the leading double quote included:

Below is an example of a file format with the leading double quote omitted:

The first row of the file is assumed to be a file header and as such, is omitted from the initial bulk insert. However, the file header isn’t used by the data migration process and thus can take on any format that you wish. Its purpose is solely to make the file header information available to humans viewing the data file directly.

Field Delimiter

To simulate support for double-quoted text, the field delimiter is three characters long. It is a comma encapsulated by two double quotes: “,”

The fields can therefore contain any text whatsoever, including double quotes, unless that text includes segments that match the exact pattern of the three-character field delimiter.

Row Terminator

The row terminator is a double quote followed by a carriage return. In other words, every row is on a separate line in the file and terminated with a double quote.

Below is an example of terminated rows:

NULL Values

Empty field values are interpreted by the bulk insert as NULL values and are imported that way. The double quotes themselves are not counted as field data as they are part of the delimiters. Therefore, a field entry such as this: “”,””, will be interpreted as two NULL value fields.

The word NULL appearing in a field will be interpreted as a string and the system will attempt to import the word “NULL”, possibly resulting in an error if the field in question does not support string-based data.

Configuring the Data Import Tool

The Data Import Tool is a console application designed to move the import files to the staging folder on the database server and generate the data import process records using the CareDirector.DataImportService.

System Settings

The following System Settings (accessed via Setting > Configuration > System Management) are relevant to the Data Import process:

  • DataImport.QueryExecutionTimeout: Maximum amount of time in seconds that we will allow for a single entity to be imported.
  • SQLBasedImportLocalStagingFolder: Location of the database staging folder from the perspective of the database server.
  • SQLBasedImportNetworkStagingFolder: Location of the database staging folder from the perspective of other machines on the network.

Configuring the app.config File

Update the following values in the app.config file:

  • PlatFormServiceBaseUri: The URL for your system
  • FileEncoding: Format of the import files (e.g. 'Default' or 'UTF-8').
  • ExtractFolder: Location of the folder containing the import files and ImportConfig.xml file.
  • UserName: User name that you normally use to access the system.

This task only needs to be carried out once if none of the above values change.

An example is shown below:

Running the Data Import Tool

The Data Import files and the ImportConfig.xml file should be located in a folder that is accessible to the CareDirector.DataImportTool.

To run the data import tool, do the following:

  1. Run the tool and a console-based screen will be displayed:
  1. Enter the password associated with the username provided in the app.config file and press Return.
  2. The system does the following:
    1. Import files are copied over to the database staging folder and Data Import Process records are generated in the system.
    2. Data Import Process records are processed by the CareDirector.DataImportService.

The service works by checking for any Data Import Process records with a Status of 'Not Started' or 'In Progress'. If any exist, it processes them synchronously in the order that they were created. For more information, see Monitoring the Data Import Process.

There is a key in the app.config file by the name of ExecuteEveryXSeconds. This key controls the number of seconds the service will wait before seeking out more unprocessed Data Import Process records (set to 180 seconds by default but can be changed in advance of a Data Migration process).

Was this article useful?

Data Import Processes

Contact