Chapter 5

Checking files

Two tools tell you what is in a file before you rely on it: Validate File checks a file against a template, and Data Diff shows exactly what changed between two versions.

Validate File

Validation checks every row of a file against a template's columns, their types and their required flags (chapter 2).

Open Validate File. Under 1 · Template, choose the template to check against. If you have none yet, create one first.
Under 2 · File, choose the file. It is read in your browser; up to 10 MB and 100,000 rows.
Click Validate File.

What is wrong lists each problem it found, such as a value that is not a valid number or date for its column's type, or an empty value in a required column. Fix the source file, or adjust the template if the file is right and the template is not.

Data Diff

Data Diff compares an original file with a modified one. Each file can be up to 10 MB and 100,000 rows, and the first row of each is treated as a header.

Choose the Original file and the Modified file.
Optionally choose a Key column under Matching, such as an order number or customer id. Rows are then matched on that column's value instead of their position, so an inserted row does not make everything after it look changed.
Leave Trim whitespace ticked (the default) so leading and trailing spaces do not count as changes, or untick it to treat them as changes. Tick Case sensitive if a difference in capitals should count as a change; it is off by default.
Click Compare Files.

The results come in three parts:

Changed cells: rows present in both files where at least one value differs.
Rows only in the modified file: rows that were added.
Rows only in the original: rows that were removed.

With a Key column, a key that appears more than once in a file is not compared. It is listed under Duplicate keys were not compared with the key, the file, how many times it appears and whether those rows differ, and it is left out of the counts above. Fix the duplicates or choose a column that is unique, then compare again.

TipWithout a matching column, rows are compared by position. That works for files that only had values edited; for files where rows were added or sorted differently, pick a matching column.