Troubleshooting CSV Validation Errors

Previous Topic  Next Topic 

After running an HMIS export or validation and upload process, you will receive an export results message via the AWARDS Messages module.  This message may contain up to three file attachments: - The full CSV file set.  This attachment is always included in the export results message.

  Validation_Error.txt - This attachment is only received when relevant.  It tells you when there's a problem with your AWARDS data; for example, missing or incorrect data in a client's records.

  Export_Error.txt - This attachment is only received when relevant.  It tells you when there was a problem with the creation of the CSV file.  Typically receipt of this error file means that you'll need to contact the Help Desk for assistance; however, the content of the error file will provide additional direction, and in some cases you may need to contact the administrator of the destination database instead.

If you receive the Validation_Errors.txt file in your export results, you will need to use it - along with the file - to complete data troubleshooting and clean-up.  The information below tells you how, and provides you with detail on commonly received errors that will be helpful as you work through the process.

How to Troubleshoot

Understanding Common Validation Errors

How to Troubleshoot

To troubleshoot your AWARDS data using the Validation_Error.txt file and your AWARDS-generated CSV file set, complete the following steps:

    1. From the AWARDS Messages module, open the export results message.
    2. Open the following message attachments as directed here:

  Validation_Error.txt - Open using a word processing program such as Microsoft Word.  (Do not use a basic .txt file reader.  Using a word processor preserves the special formatting of the content and allows it to be displayed in an easy-to-read way.) - Unzip the file to access the full CSV fileset, and then open the individual CSV files using Microsoft Excel or a similar spreadsheet application.

    1. Review the content of the validation error file.  Each error will start with the name of the CSV record, followed by the row within the file where the problem can be found, the column in that row, and the specific issue with the data.  For example, the following sample validation error indicates that there is an issue with the "NameDataQuality" field for the client whose record is in row #2 of the Client.csv file; specifically, that field is blank for the client and should not be.


               Row #2


                               Field's value is empty while it should not be

    1. Within the relevant CSV file navigate to the location referenced in the error.  For the example mentioned in step 3, you'd want to open the Client.csv file and look in row #2 to find the client whose Name Data Quality information is missing.

  TIP: Most of the export CSV files contain a "PersonalID" column which contains the client's UUID.  If the error is client-specific (as opposed to a project or site error), use that ID to search for the individual using the AWARDS Client Search tool, find his/her name, and then navigate to his/her records as directed in step 5 below.  Alternately, you can search for the Client UUID number in the Client.CSV flie to find which client is associated with that ID, and then use AWARDS to search for the client name directly.

Additionally, many of the .CSV files have a column called "DataCollectionStage" which lets you know whether the row refers to data collected at Intake/Admission, Discharge, or at an HMIS Info update.  Which value you see for DataCollectionState points you to where specifically in AWARDS you would go to fill out additional needed fields for that row, as detailed here:

1 - project entry (Intake/Admission)
2 - updates other than those completed annually *
3 - project exit (discharge)
5 - annual updates *

Annual updates happen around the anniversary of admission, give or take 30 days.  Both annual updates and those updates that are not annual are completed under Profile > Face Sheet > Update HMIS Info.  To adjust or add to the information there, click the update that corresponds to the "InformationDate" on the .CSV.

    1. In AWARDS, open the relevant client's record (if the error is client-specific) or project/site configuration record (if the error is not client-related).  If you're not sure exactly where in AWARDS the problematic data is located, the list of Common Validation Errors below can help point you in the right direction.  In the case of our sample validation error, we'll want to open the client's admission record.
    2. Enter or correct the data in question, being sure to save your changes.
    3. Repeat steps 3 through 6 until all items from the validation errors file have been addressed.
    4. Run the HMIS export again to verify that you've fixed everything correctly.

The troubleshooting process is now complete!

Understanding Common Validation Errors

Errors in the .csv files generally fall into one of two categories, either relating to the setup of the Program being exported/imported/uploaded, or relating to the specifics of a Client in that program.  Occasionally errors may relate to both of these categories.

  Client errors - Corrected by going to the client's intake/admission form or face sheet, as specified.

  Program errors - Corrected using the AWARDS System Setup module.

The most common errors are as follows.

Client Location Errors

These errors indicate that the client location is wrong or missing.  For example:

EntitlementCoC.csv - Row #2 - CoCCode - Field's value is empty while it should not be.


Client # ____ has an invalid Client Location (CoCCode) related to ProjectEntryID record #____ in Enrollment.csv file.  No entry will be added to EnrollmentCoC.csv for this enrollment.  CoCCode must be in the format XX-999.

TRANSLATION:  In programs that are part of a Continuum of Care, every program is set up with a CoC code, and that code is applied to each client as they are admitted.  If the field's value is empty, that means it is blank; if it is invalid, that means that there is a problem with the format.

TO FIX:  Make sure that in System Setup > Agency Program Information > Add/Edit Entire Program > HMIS Settings, there is at least one CoC selected and that it is in the form XX-999 (where xx is the state abbreviation).  Any clients who were admitted before the CoC was added in this location need to have the Client Location (CoC) updated on their Intake/Admission form.  Contact the Help Desk if the CoC you need is not in the drop-down list.

Inventory.csv Errors

Errors relating to Inventory.csv indicate incorrect data under System Setup > Residence Units.  For example:

HouseholdType - Null values are not allowed for this field.  This data will not be uploaded.

TRANSLATION:  Residence Unit Household type is blank and it can't be.

TO FIX:  A value must be entered in System Setup > Residence Units for the Household Type.  Keep in mind that there is a chance that the error relates to an archived unit.  If you don't see the Household Type option on the Residence Units Data Entry page at all, contact the Help Desk for assistance.

Make sure that the HMIS fields ("Household Type" through "Client Location") have been turned on for this project and that the data has been updated within the Residence Units feature.

Household Errors from the Enrollment.csv file

Errors relating to missing Household IDs indicate that a client has not been joined to a household as of the client's admission date.  For example:

Household ID - Null values are not allowed for this field.  This data will not be uploaded.

TRANSLATION:  This client wasn't in a Household on his/her admission date.

TO FIX:  See below.


RelationshipToHoH - Null values are not allowed for this field.  This data will not be uploaded.

TRANSLATION:  This client is missing his/her relationship to Head of Household.

TO FIX:  See below.

To correct such errors, visit the client's face sheet and click Household Composition to join them to a household.  If the client is already in a household, please click his/her name on the Household Composition tab to edit the Household Start Date so that it is on or before the admission date.

Another possible Enrollment.csv error is as follows:

Household ID - The [ ] Household ID has no Personal ID for whom relationship to HoH is 1.

TRANSLATION:  This household has no one designated as "Self."

TO FIX:  Run the HMIS ReportBuilder including, and sorting by, Household ID to see who is in this household.  Next, go to the face sheet of the client that should be HoH to designate that person as "Self."

  TIP: Run an HMIS Data Quality Report for the program and use the Q3 Relationship to Head of Household row to find problematic clients before an export.

Missing Data Errors

If data is missing for a Universal Data Element, it usually means the client's admission form wasn't updated after the release of the 2014 Data Standards.  For example:

Row 7 - NameDataQuality - Field's value is empty while it should not be.

TRANSLATION:  This is a required field that is blank.

TO FIX:  Go to the client's Intake/Admission form and update it by filling in the missing data.

For errors of this type, open the Client.csv file and navigate to the row number referenced in the error to find out which client needs to have their admission form updated.

Project.csv or Site.csv Errors

These errors are related to the Agency Program Information page in System Setup.  For example:

Geocode - Null values are not allowed for this field.  This data will not be uploaded.

TRANSLATION:  This is a required field that is blank.

TO FIX:  Go to System Setup > Agency Program Information > Add/Edit Entire Program > HMIS Settings, and fill in the missing value.  Keep in mind that Continuum of Care must be in the format XX-999.  GeoCode must be in the format 999999.

Note that if you're uploading data to another database, the Continuum of Care and GeoCodes must exist in that destination database. 

Invalid Data Errors

Data must conform to specific CSV standards.  If invalid data is found, you'll receive an error.  For example:

Field's value '13' is not a valid 'LastGradeCompleted' value.

This type of error will always tell you which file to look in (i.e. it will say which .csv to open), which field to look at (i.e. which column in the .csv), and which row # to investigate.  From there it's up to you to use the PersonalID to figure out which client's records need to be updated.  This type of error might be relating to information added on the Intake/Admission form, the HMIS Update, or the Discharge form.  If there is a column in the .csv that says "DataCollectionStage," see step 4 above so you know which location to go to for data editing/updating.  Complete the update in AWARDS and try the export again.

Information Date Errors

Information date errors indicate that data was recorded either before a client was admitted or after a client was discharged.  For example:

InformationDate - The InformationDate for this record does not fall within the client's Entry Date and Exit Date.  This record will not be uploaded.

Services.csv Errors

Errors in the Services.csv file relate to the Contacts Log / Supportive Services Checklist in the Services - Individual module.  For example, the following error indicates that a "cost" was not entered in a financial assistance record:

FAAmount - Field's value is empty while it should not be.

TRANSLATION:  This "cost" field should not be blank.

TO FIX:  Use the Personal ID on the specified row to identify the client, and the date and other data in the row to identify which record to edit, then make the necessary changes.