How to Count the Rows in a CSV File

Somebody asks how many records are in the export and you get three answers: the text editor says 2,000,001, a colleague’s command says 2,000,000, and the import tool that rejected the file says 1,999,873. None of them is lying. They are counting different things.

A Line Count Is Not a Row Count

Almost every answer you will find online counts lines, and a CSV row is not the same as a line. Three things pull the numbers apart.

The header is a line. If the first line is id,name,city, your data row count is one less than the line count — easy to remember, easy to forget when someone quotes you a number.

A value can contain line breaks. RFC 4180, the closest thing CSV has to a specification, says fields “containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes”. So this is two data rows, not three:

id,note
1,"line one
line two"
2,ok

A line counter reports 4. A real CSV parser reports 3 records, or 2 data rows. Python’s documentation is blunt about the distinction: the csv reader’s line_num is “the number of lines read from the source iterator. This is not the same as the number of records returned, as records can span multiple lines.” Any export with free-text fields — addresses, product descriptions, ticket bodies — is likely to contain these.

The last line may have no line break. RFC 4180 says “the last record in the file may or may not have an ending line break”, and tools disagree about what to do when it does not. That is the source of most off-by-one arguments.

Counting Lines Quickly (and Knowing What You Got)

Line counts are still useful: they are fast, and on a file with no line breaks inside values they are the row count plus one. Just label the number honestly when you pass it on.

macOS and Linux

wc -l file.csv is the standard answer, but read the specification carefully: the POSIX definition of -l is to “write to the standard output the number of <newline> characters in each input file”. It counts line breaks, not lines. A three-line file whose last line has no trailing newline reports 2. So: data rows ≈ wc -l − 1, plus 1 again if there is no final newline.

Windows PowerShell

The usual advice, (Get-Content file.csv | Measure-Object -Line).Lines, is correct but slow, because Get-Content emits every line as a separate object through the pipeline. Reading the file with .NET instead is several times faster and still streams, so memory use stays flat. Measured here on a 112 MB file of 2,000,001 lines in Windows PowerShell 5.1:

Two warnings about that table. The -Raw version is fastest because it reads the file as one enormous string, so the whole file has to fit in memory — fine at 112 MB, not at 4 GB. And the -ReadCount 1000 trick, which circulates as a speed tip, is simply wrong: Get-Content then hands the pipeline batches of 1,000 lines, and you get the number of batches. The seconds are from one Windows 11 laptop and will differ on yours; it is the ranking, and the wrong answer in the last row, that travel.

[System.IO.File]::ReadLines is a .NET call, so give it a full path — it resolves relative paths against the process working directory, not the folder PowerShell is showing you. Unlike wc -l, it does count a final line that has no line break.

Text Editors

Notepad++ shows the count in its status bar, which displays “the length of the file (in bytes, not characters …) and the number of lines in the file”. VS Code’s status bar shows where the cursor is rather than a total, so press Ctrl+End and read the line number instead. Both have a ceiling: past a certain size an editor stops treating a file as editable text and disables syntax highlighting and wrapping, so if the window goes plain, trust the command line rather than the editor.

Getting a True Row Count

When the file has free-text fields, or when the number is going into a reconciliation, count records rather than lines.

Stream It in the Browser

All the tools on this site parse CSV properly — quoted line breaks included — and count as they read. While a job runs, the progress line shows the bytes done and a running row count; when it finishes, the summary gives the total. The cleanest number comes from the duplicate remover with every column matches selected, because its summary gives three figures at once, in the form “Removed 812 duplicate rows and kept 2,499,188 of 2,500,000”. The last number is the data row count, header excluded and completely blank lines skipped rather than counted. Nothing is uploaded; the file is read on your machine.

The honest caveat: these tools exist to produce files, so they write the result into the browser’s private on-disk storage as they go. You need roughly as much free disk space as the file takes, even if all you wanted was the number.

DuckDB

One line, no import step, and a proper CSV parser with delimiter and quote detection:

SELECT count(*) FROM 'orders.csv';

The best option on multi-gigabyte files: nothing has to fit in memory.

Power Query

If the file is already loaded in Excel’s Power Query editor, there is a Count Rows command on the Transform tab that replaces the table with a single number (it applies the documented Table.RowCount function). Do not read the count off the data-profiling pane instead: Power Query “profiles data over the first 1,000 rows” by default, and you have to switch to Column profiling based on entire data set in the bottom-left corner of the editor to get whole-file figures.

Python

Three lines, and it handles embedded line breaks because the csv module does: import csv, then f = open('orders.csv', newline=''), then print(sum(1 for _ in csv.reader(f)) - 1). The - 1 drops the header. Passing newline='' is not optional: the documentation notes that without it, “newlines embedded inside quoted fields will not be interpreted correctly”.

Why Excel’s Row Number Can Mislead You

Pressing Ctrl+End in a worksheet shows how many rows you have — of the rows Excel loaded, which is not the same thing. A worksheet stops at 1,048,576, so a row indicator reading exactly 1048576 is a truncated file rather than a remarkable coincidence. Excel’s row limit explained covers what happens to the rest, and why saving at that moment is the expensive mistake.

Counting Rows per Category, Not Just in Total

Usually the interesting question is not “how many rows” but “how many per country, per store, per status”. Running split by column value answers it as a side effect: the results screen lists one output file per distinct value with its row count, largest first, before you download anything. Pick the column, run it, read the list, close the tab. It refuses columns with more than 10,000 distinct values, which is the point at which you want a database instead.

For duplicate-versus-unique counts, the duplicate remover’s summary gives both, which is usually all you need before an import. In SQL the equivalent is SELECT country, count(*) FROM 'orders.csv' GROUP BY country ORDER BY 2 DESC;

Which Method at Which Size

If the count was only a step towards making the file usable, the next move is splitting it into parts that open — or, if the total came out wrong because several exports were being added up, merging files whose columns differ. And opening a CSV that is too large for Excel covers what to do with the rows once you know how many there are. The same line-versus-row difference decides whether a command-line split is safe: splitting a CSV with PowerShell, Bash or Python shows a split that cuts a row in half, and one that does not.