🔢 File Operations · 6 min read

Count Rows in a CSV File: 5 Ways That Get It Right (2026)

To count the rows in a CSV file, parse the file and count the records, not the line breaks. wc -l data.csv is the quickest thing to type and the most common way to get the wrong answer, because a quoted field is allowed to contain a line break and the last record is allowed to have no line break at all. In Python, sum(1 for _ in csv.reader(f)) returns the true record count. In Excel or Google Sheets, open the file and read the last row number. For a file too big to open, DuckDB counts it as a streaming query.

Why the Line Count Is Not the Row Count

RFC 4180, the document that describes the CSV format, makes two allowances that break naive counting.

• A field may contain a line break, as long as the whole field sits inside double quotes. One record can therefore occupy several physical lines.
• The last record may or may not end with a line break.

Put those together and a line count can be wrong in both directions. Anything that reads the file as plain text, which includes wc -l, grep -c and most log-style tools, follows the text rather than the format.

A support export where one column holds the body of a ticket is the usual culprit. A ticket with four paragraphs inside a quoted field adds three lines to the count and not one row. On a file of a few thousand tickets that is a rounding error. On a file where the same column holds pasted addresses, it can be several percent, and percentages in a row count tend to end up in a reconciliation somewhere.

Method 1: The Terminal, and Its Limits

The two commands everyone reaches for both count newlines:

$ wc -l data.csv
4210 data.csv

$ grep -c "" data.csv
4210

Both return 4210, and both are answering the question "how many line breaks are in this file", which is a different question. If your CSV has no quoted multi-line fields and it ends with a newline, the number is right, and that covers most files exported from a spreadsheet. If it has either problem, the number is wrong and nothing warns you.

If csvkit is installed, csvstat --count data.csv parses the file properly and returns the record count. It is a Python tool, so pip install csvkit is the whole setup. Worth having if you work in the terminal often and the numbers matter.

Method 2: Python's csv Module, the Reference Answer

The standard library ships a parser that understands quoting, including the embedded line break case. Counting with it is three lines:

import csv

with open("data.csv", newline="", encoding="utf-8") as f:
    rows = sum(1 for _ in csv.reader(f))

print(rows, "records including the header")

Two details are worth keeping. The newline="" argument hands newline handling to the csv module, which is what the documentation asks for and what keeps quoted line breaks intact on Windows files. And this form streams: a ten-gigabyte file works, because only one record is held at a time.

The count includes the header row, since csv.reader yields it as the first record. Subtract one if you want data rows, and write down which convention you used.

Method 3: pandas, If You Need the Data Anyway

When the file is going to be analysed rather than just measured, pandas gives you the count for free:

import pandas as pd

df = pd.read_csv("data.csv", dtype=str)
print(df.shape[0], "data rows,", df.shape[1], "columns")

shape[0] is the row count and shape[1] is the column count. Because read_csv treats the first line as the header by default, the number excludes it, the opposite convention from the csv module. Passing dtype=str stops pandas from guessing types, which matters here: a column of ZIP codes read as integers loses the leading zeros and you find out later. The leading zeros guide covers that trap in detail.

The trade-off is memory. read_csv builds the whole table, so on a very large file you pay for data you are about to throw away. For a count alone, the csv module or DuckDB is cheaper.

Method 4: Excel and Google Sheets

Both of these tell you the row count the moment the file is open, with no formula needed.

Excel: press Ctrl plus End and the cursor jumps to the last used cell; the row number in the name box is the count. For a number you can reuse, =ROWS(A:A)-1 if A is filled, or =COUNTA(A2:A1048576) to count non-empty entries in one column. Excel's grid holds 1,048,576 rows and 16,384 columns, so a CSV with more rows than that is truncated on open and Excel will happily report the truncated count.

Google Sheets: select the first data cell and press Ctrl plus Down, then read the row number, or drag-select a column and watch the row count in the summary that appears. Sheets is capped by cells rather than rows: 10 million cells per spreadsheet, which is 10 million rows if you only use one column and 1 million rows if you use ten.

Neither is a good way to count a file the tool cannot fully open. If the count matters, take it before the spreadsheet gets a chance to trim anything.

Method 5: DuckDB, the Fast Answer for Big Files

DuckDB reads CSV with quoting rules and counts without loading everything into memory:

SELECT count(*) FROM read_csv_auto('data.csv');

One line, and it is usually faster than anything else here on a file of a few hundred megabytes. If the file is already in SQLite, the same idea applies after import: sqlite3 then SELECT COUNT(*) FROM the_table;. The CSV to SQLite guide covers the import step.

Which Method to Use

Check the Shape, Not Just the Number

A row count on its own rarely answers the real question. The one people actually have is whether the column they need is populated, and that is a different measurement: total rows against non-empty cells in that column. A 40,000-row file with an email column that is two-thirds full will produce 13,000 bounces, and no row count warns you.

The free CSV analyzer reports fill rates per column in the browser, which is the fastest way to see that. If you would rather inspect the file by eye first, the CSV viewer guide covers the options, and if the file is too long for the tools you have, splitting it into workable pieces is the next step. Blank rows are worth a check too, since they inflate the count in a spreadsheet without carrying data.

Frequently Asked Questions

How do I count the rows in a CSV file?

Parse the file and count records. In Python, sum(1 for _ in csv.reader(f)) includes the header; len(pd.read_csv("f.csv")) excludes it. In Excel or Sheets, open the file and read the last row number. Counting newlines works on tidy files and fails on any file with quoted line breaks.

Why does wc -l give a different number than Excel?

Because wc -l counts newline characters. RFC 4180 allows a quoted field to contain a line break, so one record can span several lines, and it also allows the final record to have no trailing newline, which makes wc -l one short. Excel parses the format instead, so it sees records.

Does the header row count as a row?

Decide and write it down. Python's csv.reader includes the header in the record count; pandas excludes it because the header becomes the column names. Off-by-one from a mismatched convention is the most common counting error in a pipeline, and it is invisible until something rejects the file.

How do I count rows in a CSV that has line breaks inside a field?

Use a parser. Python's csv module, pandas and DuckDB all treat a quoted multi-line field as a single value, so the count is right. Line counters overcount, and the error grows with the number of multi-line fields in the file.

How many rows can Excel and Google Sheets hold?

Excel is 1,048,576 rows by 16,384 columns from the 2007 format on. Google Sheets limits a spreadsheet to 10 million cells in total, so the row ceiling depends on how many columns you use. Both truncate a larger CSV on import, which means a count taken after opening the file can be lower than the file's real count.

How do I count rows without loading the whole file into memory?

Iterate. sum(1 for _ in csv.reader(f)) holds one record at a time, and DuckDB's count(*) over read_csv_auto streams as well. pandas builds the full table, which is fine when you need the data and wasteful when you only want a number.

How do I count only the rows that have data in one column?

Count non-empty values in that column. =COUNTA(A2:A1048576) in a spreadsheet, or df["email"].notna().sum() in pandas. Comparing that with the total row count is what tells you whether the column is actually usable.