The Provider Data Import (PDI) is designed to provide an easy ‘intermediate step’ in the complicated process of importing data from other applications into CAREWare. Standardized CSV files can be easily mapped client-related data and imported. The PDI also employs a value mapping system allowing users to send their own values and map them to existing CAREWare values. Note that values sent which match native CAREWare values are automatically mapped to those values. 

 

* For legacy purposes, CAREWare retains the ability to import Access files. Those file structures are not updated in future builds. The latest build specification for those file types was build 214, published in November 2023.

 

The Files

 

The PDI utilizes a system of CSV files, each of which has a specific column structure to import a specific CAREWare entity. Thus, there is a file structure for client records, services, diagnoses, etc. All CSV files are expected to be in the standard format described in RFC 4180. Users are able to upload single CSV files or zip files containing multiple files, including attachments for custom fields. 

 

See CAREWare CSV File Specifications for more information.

 

The Data

Exporters are recommended to include any record that was added, edited, or deleted since the most recent export. The export files can contain data from any range of dates, though it is important for the central site to be able to know the chronological sequence of the export files.  The import process adds new records and edits or deletes existing records based on rules for each record type. If there is an existing record based on these values, it is updated to match the incoming record. Otherwise, a new record is added. An existing record is deleted if it matches an existing record with the is_delete field is set to ‘yes’.

 

CLIENT records are imported and matched based on the client’s URN (this value is generated by CAREWare based on the client’s first name, last name, gender, and dob). An incoming client record that generates the URN of an existing client is treated as the same client and the existing client record is updated based on options chosen by the user running the import (see Client Matching in Import Settings).

 

 

ADAP ENROLLMENT HISTORY records the ADAP enrollment history of clients. This table is only used for the ADAP provider. This table is matched to client, date, and provider.

 

ANNUAL REVIEW Only Annual Review custom field data are imported using this table. Records are matched on client and year. Any number of annual custom fields can be imported in a record.

 

APPOINTMENT records are matched on: client, provider, service type and date. An appointment is considered a match if the date is within + or – 1 day of the date of the incoming record.

 

ATTACHMENTS records are matched to attachment list, date, and Provider.

 

CASE NOTE records are matched on: client, date and provider. NOTE: If an existing case note is a match, the existing note is overwritten, rather than updated or appended to. 

 

COUNSELING AND TESTING records are matched on: client, test date, and provider. 

 

CUSTOM SUBFORM records are matched on: client, date, and provider.

 

DIAGNOSIS records are matched on: client, date, provider, and diagnosis code. 

 

DRUG PAYMENT records are matched on: client, date, provider, and NDC for drug overlaps. User can set a drug overlap days in the import settings to determine whether a drug is overlapped.

 

ELIGIBILITY records are matched on: client, date, provider, and eligibility code.

 

FORM DESIGN DATA records are matched on: client, date, provider, and form design code.

 

IMMUNIZATION records are matched on: client, date, provider, and immunization code. 

 

INSURANCE ASSESSMENT records are matched on: client, date and provider. If there is an existing record, its values are updated based on the options chosen by the user for Handle Insurance in Import Settings. Records for insurance assessment are imported in this table rather than the deprecated annual review table.

 

MEDICATION records are matched on: client, medication code, start date and strength. Any matching records that are found are treated as the same record and updated accordingly.

 

MESSAGE records are matched on client, date, and provider. 

 

OUT OF POCKET EXPENSE records are matched on client, date, and provider.

 

POVERTY LEVEL ASSESSMENT records are matched on client, date and provider. Records for poverty level assessment are imported in this table rather than the deprecated annual review table.

 

PREGNANCY records are matched on: client, provider, and estimated conception date. If there is an existing record with an Estimated Conception date within 30 days of the incoming record, then this is treated as the same record and updated.

 

PROVIDER records are matched on the provider name and source name in the import settings. Note: This determines the settings used for the entire import process. Example: A user is importing data for a provider called the Central Health Clinic. The prv_pk would be 1. The prv_name would be Central Health Clinic. The prv_source would be Default CSV. This tells CAREWare to use the settings for Default CSV for this imported file.

 

REFERRAL records are matched on: client, date referred, (referred from) provider, service category, referred-to site. 

 

RELATIONS records are matched on: provider, index client, and dependent client. 

 

SERVICE records are matched based on: client, provider, service date, and subservice. Receipt records are brought in with each service record. Note: regarding service contracts - If there is a value in the srv_contract_name field, then this value MUST match to the name of a valid contract for the provider (a valid contract is one where the subservice is active for the contract and the service date falls between the start and end dates of the contract). If no value is entered in the contract name, then there must be exactly one valid contract for the subservice and date of service.

 

SHARING REQUEST records are matched on client, date, and provider.

 

TEST records are matched on: client, date, provider, and test definition.

 

VITAL SIGN records are matched to client, date, and provider. 

 

For additional information about import settings and how those setting affect the import process, refer to the import settings guide here.

 

File Specification Information

 

All CSV files are expected to be in the standard format described in RFC 4180.

 

• The file name should match the name of the table in the CSV template.
• The first row must contain column names, which must match those in the specification document and sample files.
• Columns that are not required may be excluded from files (any assumed default values are noted for each)
• Each CSV file is prepended with the first_name, last_name, gender_cs, gender_cs_def_code, dob, urn_suffix, and match_id as the first 7 columns. The first_name, last_name, gender, and dob (Date of Birth) comprise the URN fields CAREWare uses to match to uniquely identify a client.
  • urn_suffix is used to distinguish different clients with matching URN (not required, blank values are treated as “U”).  To import a record, either all 4 URN fields must all be populated or the match_id field must have a value for matching on Client ID or a custom field.
  • Records without complete URN fields can be processed. They are matched to client via match_id. New clients can only be added when URN fields for the record are complete.
  • Match_id can contain Client ID or a custom field value, and is required if using one of those two client matching options in the import settings. Values for match_id are excluded for the matching process if when matching on the URN and are excluded from updating Client ID or custom client field values. To update Client ID or a custom client value, those records must be included in the exp_client table.
  • The mtd_ files do not need the client fields, as they are not related to clients
• Custom field columns can be appended to the columns

• Custom field columns must start with the prefix ‘cst_’
• Custom field names should conform to ‘safe field names’ (spaces and special characters are removed). The
• CAREWare PDI has functionality to create a sample template file based on selected custom fields
• Any number of custom fields can be added to the file

 

Users are able to upload single CSV files or zip files containing multiple files

 

• If the ZIP file includes file attachments, the attachments are stored in a folder called 'attachments' 
• There are no requirements for the entire list of possible tables (files) to be in the ZIP file, nor a restriction for including CSV tables with zero records. The minimum tables needed for an import are the client data table and the exp_provider table. Example: If services are imported only the exp_service and exp_provider tables are required.
• The import file parser are able to determine which tables (files) are included based upon the file name and handle them appropriately 
• If there are any files in the import file outside of the attachments folder that do not match a PDI table name, the import is rejected

 

 

Coding Systems

 

Clinical data related tables use a Coding System (CS) to allow for automatic value matching during import. Fields that use CS values are paired _cs and _def_code columns for each value. The _cs column specifies the coding system to use to interpret the value, while the _def_code specifies the value code itself. Note that the CS column is not required; if CS is not specified then the standard value mapping system is used during the import process. Remember that for columns that are not required, the values for those columns may be null, or the column may be omitted entirely from the file.

 

For an example of how the coding system can be used for insurance assessment records click here.

 

Some tables allow more than one CS value per record. In these cases, the column names are specified as _cs_1 and

_cs_1_def_code. Additional column pairings in that file would be specified as _cs_2, _cs_2_def_code, _cs_3, _cs_3_def_code…etc. Note for these tables that the parings should be used in order. I.e., if there is no value for _cs_1_def_code, then the import will not look for values in _cs_2_def_code, etc. Any limits to the number of column pairings are specified on the table specification.

 

Coding System Examples:

 

Sample header row for a client record supplying ethnicity and two races per client: cln_pk, cln_last_name, cln_first_name, cln_dob cln_gender_cs, cln_gender_cs_def_code, cln_race_cs_1, cln_race_cs_1_def_code, cln_race_cs_2, cln_race_cs_2_def_code, cln_ethnicity_cs, c ln_ethnicity_cs_def_code

Required fields for each record in this file: cln_pk, cln_last_name, cln_first_name, cln_dob, cln_gender_cs_def_code

 

Sample header row for a service file entering up to 3 services for a client for each row in the file: srv_pk, last_name, first_name, dob, gender_cs, gender_cs_def_code, srv_date, srv_cs_1, srv_cs_1_def_code, srv_cs_2, srv_cs_2_def_code, srv_cs_3 , srv_cs_3_def_code

Required fields for each record in this file: srv_pk, last_name, first_name, dob, gender_cs_def_code, srv_date, srv_cs_1_def_code