Clean a large contact export for a compliance report

A contact export can look ready for a compliance report and still contain blank fields, inconsistent dates, or duplicate rows. Cleaning it without clear rules can also remove records that should remain, or change an identifier that needs to match another system.

Work on a copy, check the fields against your reporting requirements, and record what you changed. The CSV Editor can open large CSV files in your browser, with tools to find, edit, sort, and remove duplicate rows.

Clean the contact export

1. Open the CSV Editor and load a copy of the export, not your only original. The editor can handle files with up to 10 million rows. Keep the untouched source file so you can check or restore a value later.
2. Review the column names before changing data. Compare them with the report requirements: for example, a report may need a contact identifier, a status, a date, and a source field. Do not assume two columns with similar names mean the same thing.
3. Use Find to check important fields for blank values and inconsistent entries. Look for variations such as Active, active, and ACTIVE, or dates written in more than one format. Check several matches in context before editing.
4. Sort by fields that help you review records, such as status, date, or email address. Sorting changes the order for inspection, not the meaning of a record. Compare nearby entries when you suspect duplicates.
5. Remove duplicate rows only when the report's rules say those rows are duplicates. Before using the duplicate-removal function, confirm whether exact repeated rows are what you need to remove. Save the cleaned result as a separate file, then compare its row count with the original.

For a large file, check a small set of rows first and confirm the changes are right before applying the same rule more widely. Keep notes on the fields checked, the duplicate rule used, and the number of rows changed or removed.

What the results mean

A required field is blank

A blank does not always mean the contact is invalid. It may mean the source did not collect that information, the value was not supplied, or the export omitted it. Follow the report's rule for missing values. Do not fill blanks with guesses or copy a value from a nearby row.

Values differ only in capitalization or spacing

These entries may refer to the same status or category, but they may also reflect distinct source values. Check the approved list of values for the report before standardizing them. Keep the original export if you need to explain how a value was changed.

Two rows look like the same person

A matching name is not enough to prove that two records are duplicates. People can share names, and one person can have multiple valid contact records. Check the identifier or matching rule specified for the report. If the rule depends on a combination of fields, verify that combination rather than deleting rows based on one similar value.

The cleaned file has fewer rows

The decrease may be expected if you removed duplicate rows, but check the difference before submitting the report. Compare the number of rows removed with your notes and inspect the cleaned file for records that should have remained. A smaller file is not, by itself, proof that the data is correct.

The rows appear in a different order

Sorting can make a file easier to review without correcting or removing any data. If a receiving system expects a particular order, check its instructions and sort the final file accordingly. Do not treat a new order as a change to the underlying contact information.

How to fix common problems

1. Confirm the report rules. Get the required fields, accepted values, date format, and duplicate definition from the person responsible for the report. A compliance export should not be cleaned according to assumptions about what looks tidy.
2. Correct only clear, documented errors. Fix a typo or inconsistent value only when you can verify the intended value from an approved source. If you cannot confirm it, leave it unchanged and flag it for review rather than inventing a correction.
3. Protect identifiers and dates. Preserve IDs exactly, including leading zeros. Keep dates in the required format and verify that the month and day have not been swapped. If an identifier or date looks different after editing, compare it with the original export.
4. Review suspected duplicates before removing them. Use the precise matching rule for the report. If two records match only on a name or email address, check whether the rules allow merging or removing them. When the right action is unclear, retain both and ask the report owner.
5. Compare the finished file with the source. Check the row count, column names, required fields, and a sample of records from the beginning, middle, and end. Confirm that the cleaned copy opens as expected and that your notes explain each material change.

What a clean file can and cannot prove

A spreadsheet editor can help you find and correct visible data problems, but it cannot decide whether a contact has valid consent, whether a record meets a legal retention rule, or whether the report is complete for your organization. Those decisions depend on your policies and the source systems, not on how neat the CSV looks.

Keep the original export, the cleaned copy, and a short change log according to your organization's retention and access rules. The CSV Editor runs in your browser, and files you open stay on your device.