wc -l says 135,083 lines in red; a CSV parser reads 50,000 rows in green; below, the five checks: types, nulls, keys, dates, delimiters

A CSV that opens fine is not a CSV that imports fine. Before you point an importer at a file, five quick checks on its columns tell you what will break, and most of them take seconds. To show them on something real, we ran all five on one public file: 50,000 rows of New York City crash records, 29 columns, 13.8 MB.

The headline: wc -l says the file has 135,083 lines. It has 50,000 rows. 42,541 of them carry a line break inside one cell. Everything else in the file is surprisingly tidy, which is the point: the checks tell you which problems you have and which you don't, so you don't clean what isn't broken.

The file

It is the NYC Open Data table "Motor Vehicle Collisions - Crashes" (resource h9gi-nx95), the first 50,000 rows by crash date. Anyone can fetch the same file:

bash
curl -o crashes.csv 'https://data.cityofnewyork.us/resource/h9gi-nx95.csv?$limit=50000&$order=crash_date,collision_id'

We fetched it on 2026-11-22: 13,816,408 bytes, crashes from 2012-07-01 to 2012-09-29, 29 columns. The city updates this dataset, so a later download can differ a little; the SHA-256 of ours starts eded590d9a51. No database is involved anywhere below: every number comes from reading the file. The 13.8 MB is over the guest size of csvProfiler (5 MB), so open it signed in with the free account, or cut the file first (a warning on that in check 5).

We ran the checks with a short script that follows the same rules csvProfiler uses, and confirmed the row count with papaparse, the parser csvProfiler reads files with. What csvProfiler shows for each column is what the first four checks use: the guessed type, filled and empty counts, distinct count and the five most common values.

Check 1: what type does each column look like?

Types come from the values, not from what the column means. Run the guess and compare it with what you know about the column.

text
29 columns, guessed from the values

integer   9   zip_code and the eight number_of_* counts
decimal   2   latitude, longitude
date      1   crash_date
text     17   everything else, including crash_time, location and collision_id

Three guesses are worth a second look:

zip_code is an integer. All 38,616 filled values are five digits, so nothing is lost here. That is luck of geography: New York ZIP codes start with 1. A ZIP is a label, not a number, and the same file from New Jersey would lose its leading zero the day someone stores it as an integer. Store it as text. The leading-zero article has the long version.

crash_date is a date, but every one of the 50,000 values ends in T00:00:00.000. It is a date wearing a timestamp. The time of day lives in a separate crash_time column, guessed as text because 15:53 is not a date. You will join the two.

collision_id is text. 32 hex characters on every row, so a number would be wrong here. Good guess.

One more thing about the guess itself. csvProfiler decides a column's type from its first 5,000 rows. We checked the guess on the first 5,000 rows and on all 50,000: none of the 29 columns changed. If a column is clean for 5,000 rows and has an N/A at row 30,000, the guess can be wrong, so for columns that matter, look at the filled and distinct counts, and the minimum and maximum length, which cover every row.

Check 2: how empty is each column?

Count the empty cells per column, and then count the placeholders that look filled but say nothing.

text
column                          empty     share
contributing_factor_vehicle_5  49,828    99.7%
contributing_factor_vehicle_4  49,277    98.6%
off_street_name                48,572    97.1%
vehicle_type_code_3            46,534    93.1%
contributing_factor_vehicle_3  46,533    93.1%
borough                        11,380    22.8%
zip_code                       11,384    22.8%
latitude, longitude, location   7,459    14.9%
on_street_name                  7,245    14.5%
cross_street_name               7,249    14.5%
contributing_factor_vehicle_2   4,827     9.7%
vehicle_type_code2              4,830     9.7%
vehicle_type_code1                  1     0.0%

What this tells you:

Columns 3, 4 and 5 of each repeating group are almost always empty (93 to 99.7%). That is the shape of a wide table where most crashes involve one or two vehicles. It is a hint to ask whether a vehicles table, one row per vehicle, would suit you better than five pairs of columns.

Empties come in pairs. latitude and longitude are empty on exactly the same 7,459 rows, and no row has one without the other. borough and zip_code are empty on 11,380 and 11,384 rows: four rows have a borough and no ZIP, none the other way round.

Empty does not mean unknowable. 3,921 rows with no borough do have coordinates, so a lookup could fill them in.

Placeholders hide in the filled cells. contributing_factor_vehicle_1 is Unspecified on 29,778 rows (59.6%), and contributing_factor_vehicle_2 is Unspecified on 40,694 (81.4%) on top of its 4,827 empties, so 91.0% of that column says nothing. vehicle_type_code1 says UNKNOWN on 1,482 rows. A null count alone would call these columns full.

A rule that works: for each column, add the empty count and the count of the top value if it is a placeholder word. If the total is over half the rows, decide on purpose whether the column belongs in the table.

Check 3: is the key really a key?

Take the column you will make the primary key and count: rows, filled, distinct.

text
collision_id   50,000 rows   50,000 filled   50,000 distinct   all 32 characters
rows identical in every column: 0

The key is clean: a PRIMARY KEY on collision_id will load. Now the check people skip: a unique key does not mean unique rows. Compare the rows with the key column left out:

text
33 pairs of rows are identical in all 28 other columns and differ only in collision_id

2012-07-02  19:40  QUEENS  11373  QUEENS BOULEVARD / 57 AVENUE
LIVERY VEHICLE and SPORT UTILITY / STATION WAGON, 0 injured, 0 killed
  321c819e4a198dea67c8625a83873ec0
  e5296485553c5dffec9cdc905d489029

Thirty-three extra rows out of 50,000 (0.07%). None of the pairs sit next to each other in the file, and six of them have injuries, which would be counted twice in a total. They may be one crash reported twice or two real crashes at one spot and minute, and the data cannot say which. So the check's output is a list to look at, not a delete. If they are duplicates, removing duplicate rows in SQL shows how to keep one; if the same person appears with small spelling differences, near-duplicate matching is the next step.

Also test the key for case and spaces (ABC and abc as two keys). Here, 50,000 distinct before and after trimming and lower-casing.

Check 4: do the dates have one shape?

Replace every digit with a 9 and count the shapes. One shape means one format. Two or more means mixed formats, and the order of day and month is the first thing to confirm.

text
crash_date   9999-99-99T99:99:99.999   50,000 of 50,000   (2012-07-01 to 2012-09-29, 91 days)
crash_time   99:99                     50,000 of 50,000   (all between 00:00 and 23:59)

Both columns have one shape, and the date is year first, so there is no 03/04 puzzle here (that one has its own article). Check the range too: the minimum and maximum should fit the story. Ours do, and no value is outside it.

What the shape check does not catch is how the values were made. Look at the minutes of crash_time:

text
minute :00   8,958 rows   17.9%
minute :30   6,889 rows   13.8%
minute :15   3,296 rows    6.6%
minute :20   3,181 rows    6.4%

if minutes were evenly spread, :00 would be about 1.7% (1 in 60)

Almost one crash in five is logged at an exact hour, ten times what chance would give. These are times people estimated or rounded, so do not build "which minute is worst" on them; hours are safe. And the file has no time zone, so treat the times as New York local time and say so in your column name or notes.

Check 5: do the lines match the rows?

This is the check that found the real problem. Count physical lines, then count rows with a proper CSV parser, and compare.

bash
wc -l crashes.csv
135083 crashes.csv

# a CSV parser (Python's csv module, papaparse, COPY ... CSV):
50,000 rows + 1 header, every one with exactly 29 fields

The file is valid CSV. The location column holds a value like this, in quotes, on 42,541 rows:

text
"\n,  \n(40.5754516, -73.9953816)"

that is: line break, comma, two spaces, line break, the coordinates

Each of those rows takes three lines in the file. Anything that splits on line breaks sees something else:

text
awk -F, '{n[NF]++}' on the raw lines:
  29 fields   7,449 lines
  30 fields      11 lines   (a comma inside a quoted street name)
  24 fields  42,541 lines
   7 fields  42,541 lines
   2 fields  42,541 lines

42,541 rows x 3 lines + 7,459 rows x 1 line + 1 header = 135,083

So a line-based tool would call 42,541 of the 50,000 rows (85.1%) broken. Two more consequences:

Do not cut this file with head -n or split -l. head -n 5000 gives you 1,849 rows, and a cut can land in the middle of a record. Open it in bigCsv and query the part you need with SQL instead of cutting the file.

The cell is also untidy. Even trimmed, location starts with , and holds a line break. It is a copy of latitude and longitude (all 50,000 rows match), so the easy fix is to drop the column and keep the two numbers.

While you are looking at delimiters, check three more things we did: no byte-order mark at the start, no Windows \r\n endings (the file uses \n), and no semicolons or tabs hiding in cells. Eleven cells have a comma in a street name and are properly quoted. If your file starts with , or shows é where an accent should be, read the mojibake article.

What the five checks found, side by side

text
check          result on 50,000 rows                                   action
1 types        zip as integer, date wearing a timestamp                zip as text; date + time to one timestamp
2 nulls        5 columns over 93% empty; 91% placeholder in one        decide per column; map Unspecified to NULL
3 keys         key unique; 33 pairs repeat in all other columns        look at them, do not auto-delete
4 dates        one shape each; 17.9% of times on the hour              trust hours, not minutes
5 delimiters   135,083 lines for 50,000 rows; 42,541 line breaks       parse as CSV; drop location

A few minutes of looking, no import, and you know what the table should be before you build it. Compare that with the other order: load first, then wonder why the row count is off. The file is a good reminder that clean-looking data still has surprises, and that most of them are findable by counting.

A plain order to do it in

1. Count lines and count rows with a parser. If they differ, you have line breaks inside cells.

2. Open the file in csvProfiler and read the type, empty and distinct columns.

3. Add up empties and placeholders per column.

4. Count distinct on the key; compare rows with the key left out.

5. Reduce every date and time to a shape and count the shapes.

6. Write down your decisions (types, key, what is NULL) before building the table.

We did not load this file into a database for this article. When you do, a messy CSV into Postgres walks through the clean and load steps that follow.

Profile your own CSV in the browser: types, empties, distinct counts and top values for every column. The file stays on your machine.

Open csvProfiler

Once the table is built, Pilotbase is a database client for Windows, macOS and Linux, or Docker on a server: load the file, query it and look at the rows.

Get Pilotbase