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 including client-related data can be easily mapped 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: Values sent which match native CAREWare values are automatically mapped to those values.
Note: 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. As of June 2025, the MDB format for importing and exporting is deprecated and no longer an option in CAREWare.
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 upload zip files containing multiple files, including attachments for custom fields. The minimum files required for an import, using the CSV format, is to have at least one client level data file and the import settings file, exp_provider, which contains the provider name and import settings source name.
Example: If a user is importing services, they need the exp_service.csv and exp_proivder.csv files zipped together for the import. The exp_service.csv file contains all of the client matching field necessary to match the client to the service during the import.
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).
Note: If the imported client URN is similar to an existing client, however slightly different, CAREWare using a Client Match Scoring system to identify the clients as a potential match as long as users have the Manual Client Matching setting selected in the Import Settings, otherwise the client is added as a new client or discarded using the other 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.
Note: 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.
Note: 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.
Note: 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 import 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.
Note: If the imported value for srv_contract_name is slightly different from the name of the contract in CAREWare, a user may get the error that there is no valid contract for ther subservice when importing. If the subservice imported is active for more than one contract on that date and the column srv_contract_name is blank, a user may get an error that the subservice is active for 2 or more contracts.
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 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 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 contain setup information rather than client level data, therefore the client fields are excluded from those tables.
- 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 are converted to underscores and special characters are removed.
-
The CAREWare PDE has functionality to create a sample template file based on selected custom fields. Here are instructions for exporting a custom CSV template.
- Any number of custom fields can be added to the file
The minimum files required for an import, using the CSV format, is to have at least one client level data file and the import settings file, exp_provider, which contains the provider name and import settings source name.
Example: If a user is importing services, they need the exp_service.csv and exp_proivder.csv files zipped together for the import. The exp_service.csv file contains all of the client matching field necessary to match the client to the service during the import.
- 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
Example: If services are imported, the client level data file needs to be called exp_service.csv.
- 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: 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: 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, cln_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