How to Audit a Messy CSV Before You Clean It

Messy CSV files lead to messy decisions. Before you fix anything, you need to see what you are actually working with. This guide walks you through a quick, practical audit so you know what is broken before you start cleaning.

1. Check the Column Structure

Start by looking at the shape of the file, not the details. Every column should have a clear header in the first row, with no merged cells and no hidden surprise columns. Scan down each column to see if it holds the same type of information all the way through. If the structure is inconsistent, your cleaning work will be slow and error-prone.

Example: A small sales CSV might start like this:

Order ID | Order Date | Customer Name | Total
1001 | 2024-01-05 | Alex Stone | 120.50
1002 | 05/01/2024 | Alex Stone | 75.00
1003 | 2024-01-07 | <blank header above this column> | 62.00

Here, the fourth column has a header, but the third column header is missing on row one. In an audit, you would flag the missing header, check for any merged cells at the top, and confirm that every row uses the same column layout before changing anything.

2. Find Missing Values

Missing data is not always a blank cell. People type N/A, none, or a single dash when they do not know a value. During the audit, you are not fixing these yet—you are just counting and locating them. This helps you decide later whether to fill them, drop rows, or leave them as-is.

Example: Imagine a tiny customer table:

Row 2 has N/A, row 3 has a dash, and row 4 has a blank for City. Your audit notes that City is often missing and that missing emails may be stored as a dash. Later, in the Clean step, you will decide how to handle each pattern.

3. Spot Duplicate Rows

Duplicates can quietly distort any totals or counts you run. Some rows are full copies; others are almost the same except for one column. In the audit, you scan for repeated IDs, names, or combinations like email plus date. The goal is to see where duplicates might exist and how risky they are before you delete anything.

Example:

Here, the row for Order ID 2002 appears twice with the same customer and total. Your audit would mark this as a likely duplicate and note how many similar cases appear. In the Clean step, you might compare timestamps or source files before deciding which row to keep.

4. Check Date Formats

Date columns often hide the worst problems. The same day can appear as 01/05/2024, 2024-05-01, or May 1 2024 in one column. When formats mix, sorting by date stops working properly and any time-based analysis becomes unreliable. During the audit, you scan each date column and write down every format you see.

Example:

Order ID | Order Date
3001 | 01/05/2024
3002 | 2024-05-01
3003 | May 1 2024

All three rows might mean the same calendar date, but your spreadsheet tool may treat them as different types of values. The audit result is a clear note: “Order Date uses three formats—must standardise before any sorting or monthly totals.” This fits the Diagnose step in AleFrontier’s Diagnose → Clean → Deliver approach.

5. Review Number and Currency Formats

Number columns should behave like numbers, not like text. If some totals include currency symbols and others do not, or if some use commas and others use dots, basic sums and averages can go wrong. In the audit, you scan for mixed symbols (like $ or £), different decimal separators, and numbers stored as text. You are mapping out the mess so the cleaning work is intentional, not random.

Example:

Invoice ID | Amount
5001 | $1,200.50
5002 | 800.00
5003 | £950
5004 | 1200,50

These cells mix currencies, commas, and decimal styles. Your audit note might say: “Amount column mixes $, £, comma and dot separators; some values are text.” Only after you see this clearly do you decide how to standardise it so that later calculations support clear decisions.

Quick Audit Checklist

[ ] Column headers exist, no merged or hidden columns, structure is consistent down the sheet.
[ ] Missing values are identified, including blanks, “N/A”, “none”, and dashes.
[ ] Potential duplicate rows are flagged by ID or key fields (no deletions yet).
[ ] Date columns are reviewed and all different date formats are listed.
[ ] Number and currency columns are checked for mixed symbols, separators, and text numbers.

Document Your Decisions

An audit is only useful if someone can see what you did next. As you move from Diagnose to Clean to Deliver, write down every decision: what you removed, what you changed, what you standardised, and what you chose to leave alone. You can keep these notes in a simple log file or in a dedicated notes column in the spreadsheet. This makes your work reproducible, reviewable, and easier to build on the next time the data lands messy in your inbox. Clean data, clear decisions.