How to Open a 5-Million-Row Property CSV

A property export with a few million rows looks like an ordinary CSV file. You double-click it, a spreadsheet opens, and for a moment everything seems fine. Then you notice the row count stops at a suspiciously round number, ZIP codes have lost their leading zeros, and a parcel number has turned into a date. None of that is a problem with the data. It is the tool reaching its limits.

This guide covers what those limits are, how to check a large file before you open it, and four ways to work with it: splitting it into spreadsheet-sized pieces, loading it into Excel’s Data Model, reading it with Python, or querying it with a database. The examples use the column names from a Bulk Property Records export, but they apply to any large CSV.

Where a 5-million-row file comes from

Exports are delivered one row per property with 46 columns, so file size grows with the area you pull. A single county is often small enough for a spreadsheet. Several counties, a full state, or a few weeks of daily exports combined into one file is not. On a plan with a daily allowance, people commonly append each day’s export to one master file, and that file crosses a million rows sooner than expected.

At 5 million rows and 46 columns you are looking at 230 million cells. That number is what matters for the tools below.

The limits you will hit

Spreadsheet limits are published by the vendors and change occasionally, so check the current documentation if you are close to a boundary. At the time of writing:

For a 5-million-row file, that means Excel would need at least five sheets and Google Sheets is out of the question without heavy filtering first: 230 million cells is 23 times its limit.

What happens if you open it anyway

Excel loads the first 1,048,576 lines and warns that the data set is too large for the grid. The remaining rows are not loaded. If you then save the workbook, those rows are gone from the saved copy. The original CSV is untouched, but it is easy to lose track of which file is complete. Google Sheets refuses the import when it would exceed the cell limit.

Spreadsheet type conversion: the quieter problem

Even when a file fits, spreadsheets guess the type of each value, and property data has several columns that look numeric but are identifiers:

• Zip and Owner Mailing Zip are five-digit codes, and ZIP codes in New England, New Jersey and Puerto Rico start with 0, so 02134 becomes 2134 once a spreadsheet treats it as a number.
• APN formats vary by county. Some are long digit strings that get rounded or shown in scientific notation; some contain dashes that can be read as dates.
• Property ID is an identifier too. Whatever it looks like, it is not a quantity.

Once a value has been converted and the file saved, the original text is lost. If you plan to join the file to anything else later, these columns must stay text. In Excel, import through Data → From Text/CSV and set those columns to Text in the preview instead of double-clicking the file. Recent Microsoft 365 builds also have settings under File → Options → Data that turn off some automatic conversions, such as removing leading zeros.

Step 1: Inspect the file before opening it

You do not need to open a file to learn its size, row count and columns. On macOS or Linux, use the terminal:

# Size on disk
ls -lh records.csv

# Number of lines (one header line plus one line per record)
wc -l records.csv

# The header row, one column name per line
head -n 1 records.csv | tr ',' '\n'

On Windows, the PowerShell equivalents:

# Size on disk, in megabytes
(Get-Item records.csv).Length / 1MB

# The header row, one column name per line
(Get-Content records.csv -TotalCount 1) -split ','

# Number of lines (streams the file, so it is slow but safe on large files)
$n = 0; Get-Content records.csv -ReadCount 10000 | ForEach-Object { $n += $_.Count }; $n

Line count is a close estimate of row count, not an exact one. A quoted field that contains a line break counts as an extra line. That is rare in property records but possible in free-text fields, which is one reason the CSV-aware methods later in this guide are safer than line-based ones.

With the row count in hand, pick a method:

Step 2, option A: Split the file into spreadsheet-sized parts

Splitting is the fastest way to get everything into Excel. The shell version below cuts the file into parts of one million records and copies the header row to the top of each part:

# Split into files of 1,000,000 records, repeating the header in each part.
tail -n +2 records.csv | split -l 1000000 - part_
for f in part_*; do
  { head -n 1 records.csv; cat "$f"; } > "$f.csv" && rm "$f"
done

Splitting by row count is mechanical: records for the same county end up scattered across parts. In practice it is usually more useful to split by columns you will work with anyway, such as State and County. This Python script uses the standard csv module, which handles quoted fields correctly and keeps every value as text:

import csv
import re
from pathlib import Path

SOURCE = Path("records.csv")
OUT = Path("by_county")
OUT.mkdir(exist_ok=True)

def safe(text):
    return re.sub(r"[^A-Za-z0-9]+", "_", text).strip("_").lower()

files, writers = {}, {}
with SOURCE.open(newline="", encoding="utf-8") as src:
    reader = csv.DictReader(src)
    for row in reader:
        # County names repeat across states, so key on both, e.g. "ga_fulton".
        key = f"{safe(row['State'])}_{safe(row['County'])}"
        if key not in writers:
            files[key] = (OUT / f"{key}.csv").open("w", newline="", encoding="utf-8")
            writers[key] = csv.DictWriter(files[key], fieldnames=reader.fieldnames)
            writers[key].writeheader()
        writers[key].writerow(row)

for f in files.values():
    f.close()

You end up with one file per county, named by state and county, each with its own header row. Most will open in a spreadsheet directly. The largest counties may still need a second split.

Step 2, option B: Excel Power Query and the Data Model

If your team lives in Excel, the Data Model lets you summarise the whole file without splitting it. The data is stored in a compressed in-memory engine instead of on a worksheet, so the row limit does not apply.

1. Go to Data → Get Data → From File → From Text/CSV and select the file.
2. In the preview, choose Transform Data. Select Property ID, APN, Zip and Owner Mailing Zip, and set their type to Text. Remove any columns you do not need; fewer columns means less memory.
3. Choose Close & Load To…, select Only Create Connection and tick Add this data to the Data Model.
4. Insert a PivotTable from the Data Model and summarise by county, property type, yes/no columns or value bands.

The limitation is that you cannot scroll or edit individual rows the way you would on a sheet. Use it for counts and summaries, and pull out subsets with one of the other methods when you need row-level work.

Step 2, option C: Python and pandas

pandas reads a few million rows comfortably on a typical laptop, as long as you are selective about columns and types. Two settings make the difference: usecols to load only the columns you need, and dtype to keep identifiers as text.

import pandas as pd

# Identifier columns must stay text, or ZIP "02134" becomes 2134.
TEXT = {"Property ID": "string", "APN": "string",
        "Zip": "string", "Owner Mailing Zip": "string"}

cols = ["Property ID", "Address", "City", "State", "Zip", "County", "APN",
        "Owner 1 First Name", "Owner 1 Last Name",
        "Owner Mailing Address", "Owner Mailing City",
        "Owner Mailing State", "Owner Mailing Zip",
        "Estimated Value", "Equity Percent", "Owner Occupied"]

df = pd.read_csv("records.csv", usecols=cols, dtype=TEXT)
print(len(df), "rows")
print(df.memory_usage(deep=True).sum() / 1e6, "MB in memory")

Check the memory figure the script prints before loading more columns. If the full file does not fit, read it in chunks and keep only what you need from each one:

import pandas as pd

TEXT = {"Property ID": "string", "APN": "string",
        "Zip": "string", "Owner Mailing Zip": "string"}
parts = []

for chunk in pd.read_csv("records.csv", dtype=TEXT, chunksize=250_000):
    # One county: County is a name, so match it together with State.
    keep = chunk[(chunk["State"] == "CA") & (chunk["County"] == "Los Angeles")]
    parts.append(keep)

county = pd.concat(parts, ignore_index=True)
county.to_csv("ca_los_angeles.csv", index=False)

Chunked reading keeps memory flat no matter how large the source file is, because only one chunk is in memory at a time. The same pattern works for any filter: a list of ZIP codes, an equity threshold, or rows where a yes/no column is true.

Reading the yes/no columns

Yes/no columns such as Owner Occupied and Vacant? hold the text true or false. Before filtering on them, check what your file contains with df["Vacant?"].value_counts(dropna=False), and convert them to real booleans once rather than comparing strings throughout your code. Blank values mean the source did not supply one, which is different from “no”.

Step 2, option D: Query it with SQL

DuckDB: SQL on the file itself

DuckDB is a free, single-binary analytical database. It can query a CSV in place, with no import step and no server, which makes it the quickest way to answer questions about a large file. It also writes results back to CSV or to Parquet, a compressed columnar format that is much faster to query repeatedly.

-- Run inside the duckdb shell. No import step: DuckDB reads the CSV directly.
-- Column names keep the header's spaces, so quote them in double quotes.
CREATE VIEW records AS
SELECT * FROM read_csv('records.csv', header = true,
  types = {'Property ID': 'VARCHAR', 'APN': 'VARCHAR',
           'Zip': 'VARCHAR', 'Owner Mailing Zip': 'VARCHAR'});

-- How many records per county?
SELECT "State", "County", count(*) AS n
FROM records
GROUP BY "State", "County"
ORDER BY n DESC;

-- Write one county to a file small enough for a spreadsheet.
COPY (SELECT * FROM records WHERE "State" = 'CA' AND "County" = 'Los Angeles')
TO 'ca_los_angeles.csv' (HEADER, DELIMITER ',');

-- Or convert the whole file to Parquet for faster repeat queries.
COPY (SELECT * FROM records) TO 'records.parquet' (FORMAT parquet);

DuckDB keeps the header names as they are, so a column such as Owner Mailing Zip is written "Owner Mailing Zip" in queries. If you would rather not quote, pass normalize_names = true to read_csv: names become lowercase with underscores and punctuation stripped, so Lot (Square Feet) becomes lot_square_feet and Vacant? becomes vacant.

DuckDB is a good default for analysts: you get grouping, filtering and joins over millions of rows in seconds, and the results come out as small CSV files a spreadsheet can open.

SQLite: a single-file database

SQLite stores a whole database in one file and ships with most operating systems. Its shell can import a CSV into a new table in one command, using the header row for column names and storing every value as text, which conveniently preserves leading zeros:

sqlite3 property.db
sqlite> .import --csv records.csv properties
sqlite> SELECT count(*) FROM properties;
sqlite> CREATE INDEX idx_county ON properties("State", "County");

The column names are the header names, spaces included, so quote them. Add indexes on the columns you filter by most often, usually State and County, and the mailing ZIP. Cast numeric columns in queries when you need arithmetic, for example CAST("Estimated Value" AS INTEGER).

PostgreSQL: when a team shares the data

If several people or applications use the dataset, or you plan to refresh it monthly and keep history, a server database is worth the setup. Create the table with explicit types, then load the file with \copy from psql, which reads the file from your machine rather than the server:

-- Create the table first with text identifiers, then load with \copy from psql.
-- COPY matches columns by position, so list all 46 in CSV header order.
CREATE TABLE properties (
  property_id text, address text, city text, state char(2), zip text,
  county text, apn text, property_type text,
  -- ...one line per remaining column, in the same order as the CSV header
  data_version text
);

\copy properties FROM 'records.csv' WITH (FORMAT csv, HEADER true)

CREATE INDEX ON properties (state, county);

The table can use short lowercase names because COPY matches by position, not by name, so the order must follow the header exactly. Typing zip and apn as text keeps them intact. Load the remaining numeric and date columns as text first if you are not sure every value will parse, then convert them in a second step once you have checked for blanks and odd values.

Getting results back into a spreadsheet

Most work ends with a file someone opens in Excel or Google Sheets: a mailing list for one county, a summary by ZIP, a sample for review. The reliable pattern is to do the heavy filtering in one of the tools above and export only the result:

1. Filter to the area and records you need.
2. Keep only the columns the recipient will use.
3. Check the result against the limits table: under about 1 million rows for Excel, or under 10 million cells for Sheets.
4. Export to CSV, and tell the recipient to import it with identifier columns set to Text.

If the file is going into a CRM or a mail house’s system rather than a spreadsheet, the same advice holds: ask whether they have a row or file-size limit for uploads, and split by county or ZIP rather than by row count so each part stands on its own.

Quick reference

If you are new to the columns themselves, the property data field dictionary explains what each one means and which ones need care when you load them.