Support Portal

Data Schema

• The standard Customer Assisted Conversion data schema includes the data entities and fields defined below. This is the data schema you must provide to successfully convert to Pulse.
• The files must be returned precisely as defined or the data will be returned for correction.
  • File and column names cannot be changed.
  • Columns that do not have corresponding PIMS data may be deleted or left blank.
  • PIMS data may be combined into one column but will appear in Pulse as it was received.
    • For example, Spouse Full Names may be submitted in field Client.SpouseLastName, but will appear only in the Spouse Last Name field in Pulse.
• The file format to be returned is CSV (Comma-Separated Value), with double quotes at the start and end of all fields.
• Sample CSV files will be included in the entity documentation with the required double quotes. Open the files with a text editor, such as Notepad, to preserve formatting.

Formatting with double quotes is the last step in the process because Excel does not readily handle double quote formatting.

• Entity documentation includes the expected Excel field types in double quotes.
• Field type format assumptions are noted in the entity documentation and must be corrected as needed for the specified data set.

Data Details and Notes

• The file format to be returned is CSV (comma-separated value) file with double quotes at the start and end of each field. This will require custom formatting based on the Excel assumed field type.

Sample CSV files assume ClientAccountIDs are integers with column formatting set to \"0"\. If the ClientAccountIDs are alphanumeric (including GUIDs), the column formatting will need to be changed to \"@\"

• Embedded double quotes must be preceded by the "quoting character," typically a backslash.
• Cells that do not contain data are denoted by two double quotes.
• Required fields are noted in the entity documentation.
• Conditional field dependencies are noted in the entity documentation.
• CSV files will generate multiple lines due to embedded CRLFs. PIMS notes and comment data often contain multiple CRLFs. To eliminate, use the Excel Clean function on text columns. Note the Clean function needs to be run 2 times for text columns to catch all of the embedded CRLFs in the text fields.   
• To further determine file validity, it is recommended that the last column in the CSV file be a checksum representing the number of columns in the file. Set a filter for all values other than the checksum value to identify invalid rows. 
• Datetime fields are formatted as YYYY-MM-DD HH:MM(e.g. 2018-04-16 13:00 for 1:00PM). 
• Date fields must contain a valid date or null indicated by 2 double quotes.

Once the data is populated, check each column by setting a filter and reviewing each entry for the required double quotes at the start and end of the field. You must select specific cells and use custom formatting on those cells, as described above, to correct them. An example is the Clients.Address2 field that may contain "Apt 202" or just "202." The default formatting will work for the former but not the latter, as Excel will treat it as an integer.