Clean up a data file you received

Turn an unfamiliar CSV into a clean file you can send back with confidence.

Situation. Someone sends you a CSV. It comes from a spreadsheet, an application export or a system outside your control, and must go back to a script, repository or another person.

What you get at the end. A corrected file in a dialect its destination understands, with the original still intact beside it.

1. Open and check how it is read

Drag the file onto Balise or choose File > Open… (⌘O).

Look at the grid first: are the columns where you expect them? If everything is in one column or accented characters are damaged, the reading is wrong, rather than the file.

Correct the delimiter or encoding in the inspector. See When a data file causes problems for each symptom.

2. Read diagnostics before changing anything

The inspector separates errors, warnings and observations.

Start with errors: an unclosed field, varying column counts between rows, or an empty or duplicate header. These can break downstream processing.

Observations describe your file without treating it as faulty. A nearly empty column is not a problem if that is intentional.

3. Inspect columns one by one

For each column, the left navigation shows its probable type, empty value count and distinct value count.

This helps you spot a supposedly numeric column with three text values or an identifier that should be unique but is not.

Types are an interpretation rather than a decision: nothing is converted.

4. Correct

Transformations in the Data menu change the source, and all can be undone with ⌘Z.

Useful operations for an incoming file include trimming spaces around a column’s values, normalizing Unicode to NFC and deduplicating rows by a column.

For individual corrections, edit directly in the grid. The value bar shows the exact cell content without reformatting it.

5. Use a repair instead of improvised fixes

If Balise proposes a repair, such as closing an unclosed field, unifying line endings or padding short rows, read the preview before accepting.

It shows the before and after formats, every difference, its reason and its severity.

The repair targets a copy. Your original file stays untouched.

6. Deliver the right dialect

Convert a copy produces the file for its intended destination.

Choose the profile based on who will read it:

  • a script or Git repository: Unix UTF-8 with LF line endings;
  • a spreadsheet configured in French: regional semicolon;
  • a tool requiring a BOM: Excel UTF-8;
  • a strict tool: RFC 4180.

Here too, a preview shows the changes before writing.

7. Keep a record

Export an inspection report as Markdown and keep it beside the file.

It contains dimensions, dialect, encoding, diagnostics and column profiles. The next time this supplier sends a file, you can see whether the same problems return.

If something goes wrong