How to clean a messy CSV before you analyse it

Most analysis problems that look like maths problems are data problems. A column that should be numbers is text, a date is read the wrong way round, a totals row is counted as a customer. Ten minutes of checking saves a wrong conclusion.

Look at the shape first

• Is the real header on row 1, or are there title rows and notes above it?
• Are there footnotes or a totals row at the bottom?
• Do any rows repeat the header in the middle (common when files are stitched together)?

Numbers that are not numbers

Currency symbols, thousands separators and percent signs turn a number into text. Parentheses often mean a negative: (45) is -45. In many countries the decimal mark is a comma and the thousands separator is a dot, so "1.234,56" means one thousand two hundred thirty-four and fifty-six hundredths. Indian digit grouping such as 12,34,567 has a different rhythm again. Decide which convention a file uses by looking at several values, not one.

Dates: never guess

04/05/2026 is the fourth of May in most of the world and the fifth of April in the United States. If every day value in the column is 12 or less, you cannot tell which is which from the data alone. Look for a value above 12 in either position, check the source’s documentation, or ask whoever made the file. A silent guess puts half your rows in the wrong month.

Wide tables and long tables

A table with one column per year (2018, 2019, 2020 and so on) is easy to read and hard to chart or filter. Reshape it to one row per entity per year. Do the reverse only when you need to read it.

Blank, zero and missing

They are three different things. Blank often means "not recorded", zero means "none", and special codes such as -999 or "n/a" mean "unknown". Convert them deliberately; averaging a -999 is a classic way to wreck a result.

Duplicates and keys

If a column should be unique (an order id), check that it is. Repeated rows from a double export inflate every total. Also look for near-duplicates, such as the same customer spelled two ways.

Encoding and delimiters

Accented characters that turn into symbols mean the file was saved in a different encoding. A file that opens as one long column may use semicolons or tabs instead of commas.

Keep the original, and write down what you changed

Work on a copy. Note each change (for example, "removed the totals row", "read dates as day/month") so the result can be checked by someone else, including you in six months.

What AnalyzeIt does for you

When AnalyzeIt downloads a table from the internet, it detects the header, reads numbers in these formats, converts dates (and refuses to guess an ambiguous day/month order), reshapes year-per-column tables, sets aside total rows and tells you what it changed. It also reports when a table was too messy to use, instead of analysing it anyway.

Read more: How online data works, or try the live demo with no account.