How to Count the Rows in a CSV File

For a quick line count use wc -l on macOS or Linux, find /c /v "" in the Windows Command Prompt, or [System.IO.File]::ReadLines in PowerShell; for the true row count outside the browser, use DuckDB or Python’s csv module.

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.

Windows Command Prompt: find /c /v ""

Command Prompt has its own line counter: find /c /v "" file.csv. find treats no line as containing the empty string, so /v, which keeps the lines that do not contain the search text, keeps every line, and /c prints how many there were instead of the lines themselves. Given a file name, it prints the count after a blank line and a row of dashes, with the name in capitals: ---------- FILE.CSV: 2000001. Given the text through a pipe or a redirect — type file.csv | find /c /v "" or find /c /v "" < file.csv — it prints the number alone, which is the form a script wants.

It is a line count, with the same catch as wc -l and ReadLines: the header is one of the lines, and every line break inside a quoted value adds another, so on the four-line example above it reports 4 for 2 data rows. Like ReadLines, it counts a final line that has no line break, and blank lines count too. Type it into Command Prompt, not PowerShell: Windows PowerShell 5.1 does not pass the empty "" on, and find answers FIND: Parameter format not correct.

Measured on Windows 11, on a file with the same size and line count as the one in the PowerShell table — 112 MB, 2,000,001 lines, Windows line endings — taking the median of seven runs:

In the same session, on this file, the ReadLines command from the PowerShell table took more than ten times as long and the -Raw one more than twice as long, so at this size find is the quickest Windows line count on this page. On very large files it breaks, and not always visibly. Given a file name, it counted a file of exactly 3 GB (3,221,225,472 bytes) correctly; one byte longer, it printed Unable to read file and no count. Redirected input got past that limit and then failed worse: on a 4.2 GB file of 75,000,001 lines, find /c /v "" < file.csv printed 3,608,131 with no error — the lines in the first 203 MB, which is the amount by which the file exceeds 4 GB. Piped through type, the same file came back right, 75,000,001, in 133 s. Above 3 GB, use the pipe, or a tool from the next section.

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 RowSlice’s CSV Analyzer, because it reports all three counts at once instead of one of them: physical lines, CSV records, and data rows with the header excluded and completely blank lines skipped rather than counted. Reading them together is what settles the argument — when the line count and the record count agree, nothing in the file has a line break inside a value and wc -l would have been safe after all. Nothing is uploaded; the file is read on your machine.

One thing to know before running it on something enormous: the analyzer counts exact duplicate rows as well, which is the one part of the run that holds anything sizeable in memory, and it stops with a message past about 33 million different rows. Untick Count exact duplicate rows and that ceiling goes with it.

The usual caveat about disk space does not apply here. The analyzer writes only its column report, a few kilobytes however large the input. It is the tools that produce a copy of your rows — Split CSV into Multiple Files, Merge CSV Files, Remove Duplicates from CSV — that write that copy into the browser’s private on-disk storage as they go, and for those you need roughly as much free space as the file takes.

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
f = open('orders.csv', newline='', encoding='latin-1')
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”.

The encoding='latin-1' is what makes the file open on any computer. Without it, Python falls back on a default encoding and can stop with a UnicodeDecodeError: on Windows in Python 3.14 and earlier, for some UTF-8 files (a name with a Polish Ł is enough), and on a Mac or Linux, or on any computer in Python 3.15 and later, for Windows-1252 files with accents. Latin-1 gives every byte a character, and the commas, quotes and line breaks that mark rows are the same bytes in UTF-8 and Windows-1252, so the count is right for either. The exception is UTF-16, and a quiet one: the script prints a wrong number instead of an error, so for a UTF-16 file write encoding='utf-16' instead. If you are not sure which encoding you have, how to tell UTF-8, UTF-16 and Windows-1252 apart walks through it.

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 the large CSV into files that Excel can 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.