How do I check a CSV file before I use it?

Opening a file, scrolling for a minute and deciding it looks fine is how bad data gets into reports. The problems are rarely on the first screen. A date format changes at row 400. A column that is mostly numbers has a few words in it. A code loses its leading zero.

A profile is a short, boring report on what is actually in the file: each column, its type, how many blanks, the range of values. It is the thing you wish someone had handed you before you started.

What you need

• The header row and 20 to 100 sample rows. Include a few from the end of the file as well as the start, if you can.
• The total number of rows in the file.
• One line on what the file should contain and what you will use it for. If you do not know, say "unknown".

The prompt

Paste this into a new chat, then fill in the fields and paste your rows.

You describe a data file honestly before anyone builds on it. You never edit the file. Report only what you can see in the rows I paste. Do not invent counts for the rest of the file.

What the file should contain: [one line, or "unknown"]
What it will be used for: [one line, or "unknown"]
Total rows in the file: [n]
Header and sample rows:
[paste]

Do this:
1. Say it is a profile of a sample of N rows and may miss problems elsewhere.
2. List each column with: what type it looks like (number, date, flag, text), how many blanks in the sample, a few distinct or common values, and the numeric range where it applies.
3. Flag, quoting examples: mixed types in one column; dates in more than one format (could day and month be swapped?); values with leading or trailing spaces; the same value in different cases; constant columns; very odd numbers; numbers that look like codes with leading zeros; duplicate rows; ragged rows.
4. For each flag, say what it could mean and one quick way to check it in a spreadsheet.
5. Write five to eight questions to ask whoever owns the data.
6. End with "Not checked": whether values are true, whether rows are missing, where the data came from.

Do not suggest you changed anything. Do not guess what an unclear column means: list it as a question. Plain short sentences, no em dashes.

How to check the result

First, notice that the report starts by saying it covers a sample. Believe it. Anything it says about blanks or ranges applies to the rows you pasted, not to the whole file.

Go through each flag. For every one, the prompt gives you what it could mean and a quick way to check it in a spreadsheet. A good habit is to run that check on the whole file and write down the real number. A flag such as "dates in two formats" is worth acting on only once you know how many rows are affected.

Take the questions for the data's owner to that person. If there is no owner, they are your own checklist. Questions such as "Is this price per unit or per order?" are the ones that cost money when nobody asks them.

The last section lists what was not checked. It will say that whether values are true, whether rows are missing and where the data came from are all outside the report. Keep that in mind when you share the results.

Limits

• It profiles only what you paste. It does not see the rest of the file.
• It never edits your file and it does not clean anything. Cleaning is a separate job, with a backup first.
• It will not guess what an unclear column means. It lists it as a question.
• It cannot tell you whether the data is true.

Browse all skills in the catalog

Want this as a ready-made skill? CSV Profile and Sanity Check (Free) does it in your own assistant.