CSV column summary and statistics

Reading the summary

• filled — cells that are not empty. Compare with the row count: a column filled at 12% is probably not usable as is.
• distinct_values — number of different values. Equal to the row count: an identifier. Very low: a category you can group by. One: a useless column.
• min_value / max_value — for numbers and year-first dates, the range. A price at -1 or a date in 1900 is an error to fix before analysis.

Go further on one column

Most frequent values:

SELECT status, COUNT(*) AS rows
FROM data
GROUP BY status
ORDER BY rows DESC
LIMIT 20;

Average, total and spread of a numeric column:

SELECT COUNT(amount), AVG(amount), SUM(amount), MIN(amount), MAX(amount)
FROM data;

Rows where a required field is missing:

SELECT * FROM data WHERE customer_id = '' OR customer_id IS NULL;

A note on text columns

For text, minimum and maximum are alphabetical (A before Z). They're still useful: an unexpected value such as a leading space or a number in a name column tends to show up at one end.

Other free CSV tools

Frequently asked questions