File Validation for New Data

Learn how to review data files for format, structure, and consistency to prevent loading failures.

To prevent data loading failures and ensure accurate application results, it's crucial to prepare your data files by following the expected format, recommended file structure and naming conventions as outlined in the Data File Guidelines and Visier Data Dictionaries. In this article, you'll learn key checks to help you review your data files before and during the data loading process.

File format checks

Before loading your data, ensure that your data files are in the correct format.

File encoding

Visier recommends using UTF-8 encoding, which supports a wide range of characters and prevents display issues.

How to check:

1. Open the file in a text editor like Notepad++.
2. In the menu, select Encoding.
3. If the file is not encoded in UTF-8, select Convert to UTF-8.

File extension types

Visier supports data in the following formats:

• Pipe delimited text (.txt)

• Comma-separated values (.csv)

  • Make sure to use double quotation marks to enclose each field. This is to avoid issues when a comma is part of any of the field values. For more information, see Source Settings.

• Tab-separated values (.tsv)

• Excel 2017 and later (.xlsx) and Excel 97-2004 (.xls)

  • Ensure that the Excel files do not contain any formulas.

How to check:

1. Verify the file extension by looking at the end of the file name.
2. If your file does not meet the format requirements, convert your file to a supported format.

File structure checks

For the platform to understand your data, it must be in a structured format.

File delimiter consistency

A delimiter is a character used to separate values in a data text file. Inconsistent use can cause loading failures. For more information, see Field delimiter.

How to check:

1. Open the file in a text editor like Notepad++.
2. Verify that every row has the same number of columns and the correct values are showing.
3. Missing delimiters: Add the missing delimiters to ensure each column is properly separated.
4. Commas in data: If a comma is part of a value, for example, "Director, PMO", enclose the value in double quotes to prevent misinterpretation.

Column Count

Every row in the data file must have the same number of columns. An inconsistent count is often caused by missing or extra delimiters. Correcting the delimiters, as described above, will fix the column count issue.

Column Headers

Column headers must be present and consistent for the system to identify the data correctly. Headers are case-sensitive, for example the column name First Name is different from first name.

How to check:

1. Open the file in a text editor like Notepad++ and examine the header row names.
2. Missing or duplicate headers: Ensure the file includes a header row with the correct unique column names.
3. Incorrect casing: Match the casing to the required format.
4. Extra rows: If there are rows before the header, configure the source settings to skip these lines once the file is uploaded. For more information, see Skip lines.

Data Dictionary review

The Visier Data Dictionaries document the datasets and data elements included in each application. Each Visier application has its own data dictionary outlining details such as data format, data type, and required fields.

Reviewing your source data against the application’s data dictionary is essential to ensure data integrity. To download an application’s data dictionary, see Visier Data Dictionaries.

How to conduct a data dictionary review

1. Download the data dictionary for the application you are loading data for.
2. Compare your data file’s columns against the Field Name column in the data dictionary.
3. Ensure all required fields are present.

4. Verify that your source data follows the specified data format, string or date, and data type such as YYYY-MM-DD in the data dictionary.

   Note: Incorrect or inconsistent data formats across rows can cause validation errors during processing, which prevents a new data version from being generated.

Shared dimensions

If the data dictionary includes a shared dimension, such as Gender, you must ensure your data matches the dimension members already populated by other subjects in Visier. For example, your data file lists Female, Male, and Not Disclosed, but the dimension members in Visier have F, M, and U.

To check the dimension members, do the following:

1. In a project, on the navigation bar, click Model > Dimensions.
2. Select the dimension.
3. In the Customize tab, under Analytic objects, confirm the dimension has been populated by another subject.
4. In the Configure tab, confirm the dimension members listed match the data in your source file.

If there's a mismatch between your source data values, for example Female, Male, and Not Disclosed, and the corresponding dimension members in Visier, F, M, and U, you have options for transforming the data in Visier. The best approach depends on the scale of the change required.

• If you need to update 5 or less members, the transformation can be done using an extraction formula to replace the value "Female" with "F". For more information, see Extraction Formula Examples.
• If you need to update more than 5 members, a lookup mapping can be used to replace existing property values with defined values in the lookup, for example "Female" to "F". For more information, see Create a Lookup Mapping.

Post-upload data quality checks

After uploading your file to Visier and creating a source, you may need to perform an additional data quality check if there are validation errors, such as incorrect data formats or missing event date columns. For more information, see Validate a Source.

To identify data quality issues within the uploaded file, do the following:

1. On the global navigation bar, click Data > Data Transfers.
2. Select the uploaded file.
3. Verify the following:
   • An Event Date or Snapshot Date column exists, and there are no null values.
   • Date formats are consistent across all rows, for example YYYY-MM-DD.
   • All required fields contain values. You can download an application’s data dictionary to determine which fields are required. For more information, see Visier Data Dictionaries.
   • Records are unique, and no duplicate records exist.
   • IDs match across corresponding datasets, for example, the Employee ID listed in the Skills data file matches the Employee ID in the Employee data file.
4. Correct any issues found during the data quality check and upload the data file again.