Importing Consumer Data using CSV Files

Modified on Mon, 20 May 2024 at 05:45 PM

Overview

The TWP Integration supports importing of data using CSV files. This document describes the format and required field names of the CSV file to importing consumer data and display information on the consumer portal..


Watch a brief video on how to import consumer data.


For a full list of possible fields please refer to our Standard Data Fields article for more information.

Basic Format

  • The CSV file must be an ASCII text file. 
  • Unicode characters are not supported. Examples: $ % ( ) @
  • Each line of the CSV file must end with a carriage return line feed combination (ASCI 13, and 10).
  • The first row in the file must be a header row consisting of the fields you are including.
  • Header titles should have no spaces, i.e. PrimaryFirstName
  • Carriage returns and line feeds cannot exist in any data fields.
  • A carriage return/line feed is not required on the last row of data (but is allowed)
  • Any blank line in the file is ignored.
  • The following fields are required in every file. Other fields are optional.
    1. UniqueID
    2. PrimaryAccountName
    3. PrimaryFirstName
    4. PrimaryLastName
  • Any field not included is treated as if you set it to its default value.
    1. boolean: false
    2. string: blank
    3. date: blank
    4. number: zero or blank

Naming Conventions

  • Every file must have a unique name. We recommend using a date or time stamp.

Data Formats

  • Dates must be in the format YYYY-MM-DD.
  • Boolean fields can be either “true” or “false”. A value of blank (“”) will be treated as “false”.
  • Custom fields are treated as text fields so you may use any format you want to indicate dates (i.e. “Monday, May 5th, 2020”).

Header Row

The header row (the first row in the file) may contain any number of field names from the list of supported fields (see below). There is no specific order required for the fields. The field names are case sensitive. Any field name that is not recognized will cause an error. You may also include any number of custom fields. (See Custom Fields section below.)

Custom Fields

In addition to the supported fields, you may have any number of custom fields. All custom fields are treated as text and will show up exactly how you supply them. Custom fields must be prefixed with and asterisk (*). This lets the import know that it is not a standard field. Custom fields should not reuse any existing field names. They should contain only letters and numbers. Examples are show below:

  • *LoanType
  • *LegalDisclosure
  • *Address2
**To be able to view the Custom Fields, an account included in the import file needs to be accessed through the Consumer Menu.

Payment Fields

Each payment consists of 3 fields which are all combined to make up a payment record. The 3 fields are:

  • PaymentAmt
  • PaymentDate
  • PaymentDesc

Each consumer record may have up to 100 payments. When specifying payments, start with the suffix of ‘1’ for each field name and increment it for each payment. You do not need to put the payments in any specific order. The system will sort them by date regardless the order in the data file. The Amount and Date fields are required for each payment. The Description field is optional.

Fields Required for Settlements

If you wish to send consumers an email and display a settlement offer on the consumer portal with all the details, ensure the following seven (7) mandatory fields are included in the csv file. This will import the settlement offer on the consumer's account and display the offer when they log into the portal.  


  • SettlementOffered  -  True if a settlement was offered on this account, false otherwise
  • SettlementAmount  - The amount of the settlement that was offered on this account.
  • MaxSettlementPayments -  The maximum number of payments allowed for a settlement on this account.
  • MaxSettlementDate - The last date a payment can be schedule to complete a settlement arrangement.
  • SettlementACHAllowed - Allow Credit Cards for Settlements. Only applicable when there is a settlement offer. This field may be null. This field is unlike standard boolean fields as it will not default to false if left null. Instead, it will use the system default setting.
  • SettlementCreditCardAllowed - Allow Credit Cards for Settlements. Only applicable when there is a settlement offer. This field may be null. This field is unlike standard boolean fields as it will not default to false if left null. Instead, it will use the system default setting.
  • SettlementDebitCardAllowed - Allow Debit Cards for Settlements. Only applicable when there is a settlement offer. This field may be null. This field is unlike standard boolean fields as it will not default to false if left null. Instead, it will use the system default setting.

NOTE: If you are an SCollect Customer, these fields are not required as the settlements are initiated from SCollect. If you wish to send a settlement offer, please refer to the SCollect documentation or contact SCollect Support for additional information on how to initiate a settlement from within their system. 

Examples

Below is an example of the payment fields in a record:

================
PaymentAmt1,PaymentDate1,PaymentAmt1,PaymentntDate2,PaymentDesc1,PaymentDesc2
 
100.00,2020-01-15,150.00,2020-02-15,Check Payment,Online Payment
================

Post Date Fields

Post Dates use the same logic as Payments except their fieldnames are:

  • PostDateAmt
  • PostDateDate
  • PostDateDesc

Example Files

Below are multiple examples of file that contain only one record (for simplicity). Your file can contain any number of fields and records. Because of word wrap, some records appear to extend onto multiple lines. That would not be the case when saving in a CSV file.

Example 2 – Simple File:

================
Unique, UniqueID,PrimaryAccountName,PrimaryFirstName,PrimaryLastName
12345,John Doe,John,Doe
 ================

Example 2 – More Fields:

================
UniqueID,PrimaryAccountName,PrimaryFirstName,PrimaryLastName,CurrentBalance,AllowConsumerToAccess,AcceptCheckingPayment,AcceptCreditCardPayment,OriginalCreditor
12345,John Doe,John,Doe,2000.55,true,true,false,ACME Credit
 ================

Example 3 – With Custom Fields:

================
UniqueID,PrimaryAccountName,PrimaryFirstName,PrimaryLastName,CurrentBalance,AllowConsumerToAccess,AcceptCheckingPayment, AcceptCreditCardPayment,OriginalCreditor,*CoBorrowerName,*AccountType
12345,John Doe,John,Doe,2000.55,true,true,false,ACME Credit,Jane Doe,Loan
 ================


For a list of standard data fields view here:

https://support.concepts2code.com/support/solutions/articles/44002503591-standard-data-fields


 

Additional Fields

The fields below are not data fields for the account. Instead, they are action fields that can be included with an account. They will cause an action to occur after the import of the file.

Name

Type

Description

EmailTemplateName

String

This field may contain the internal template name of one of your email templates. When this field contains a value, an email batch will be created after the file is imported. This field and any values in it are optional. You can add a value on some records and not on others. Any value that is included must be a valid email template name.

 

Common Questions

What is the format of the data file?

The format details of the CSV file are listed below this section. The format has standard fields and allows for unlimited custom fields.


How often should I upload the data file?

We recommend uploading a new data file once per day. This ensures that the consumer information is no more than one (1) day old, provided up-to-date information to Consumers.


Do you support real-time/live data access instead of having day old data?

Yes – advanced setups are available that allow your data to be live on the website. These setups require integration software and setup by your Agency. This setup has advantages but requires that you have the IT personnel to install/manage/troubleshoot. There is also an additional fee for this setup.


How do I upload the file?

Your agency will have access to an SFTP site. You may use any tool that supports SFTP to upload your file, or you may automate a process that uploads the file on a regular basis.


How long does it take to import my data?

Our systems run 24/7 and look for any new data files. Most data files are processed within one (1) hour of uploading; larger files may take more time. These processes seamlessly run behind the scenes, making the solution full accessible to all users and Consumers while the data is imported.


Can we import other types of files?

If you are unable to produce the standard data import files, our team can work with you to create a custom import that will read the format you are able to produce and import it. Additional technical programming costs apply.


Does a COMPLETE data file of all accounts need to be sent daily?

No, a COMPLETE data file does not need to be sent everyday depending on your upload strategy. In the onboarding stage, we will work with you to determine what upload strategy works best for your Agency. The simplest option is to upload your full customer list every day. When you do this, any consumer that has been removed from your list will automatically be removed from the portal. The more complex but more efficient method is to upload only accounts that have changed since the previous day. The system will be configured to update only the accounts that it receives and not modify any other accounts. When you use this method, you should utilize the AllowConsumerToAccess field to ensure that you can disable accounts that you no longer want to use the portal. You do not ever need to delete or remove accounts, simply disable them using your own data or a custom field, this allows for a full reporting and audit trails to exist. 

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article