Skip to content
Json Works

How to Open and Explore Large CSV Files

Updated

A CSV looks simple, but opening one that has hundreds of thousands of rows can freeze Excel, and a file from another system can show up as a mess of broken characters or a single crowded column. This guide covers how to open big or awkward CSV files reliably, and what to do with them afterwards.

The quick way

  1. Open the CSV Viewer.
  2. Drop your .csv or .tsv file onto the page, or paste the text.
  3. The delimiter and header row are detected automatically. You can override them if the guess is wrong.
  4. Search, sort, filter, resize columns, and export to Excel when you’re done.

Rows are drawn on demand as you scroll, so a large file stays responsive. The file is read in your browser; nothing is uploaded, which matters for customer exports and database dumps.

Why large CSV files are a problem

Row limits. Excel allows 1,048,576 rows per sheet. Open a file with more and Excel silently loads the first million and discards the rest, often with only a brief warning. Check the row count before you trust a total.

Memory. Excel and Google Sheets hold every cell in memory with formatting. A 500 MB CSV may need several gigabytes of RAM.

Speed. Opening, sorting and filtering on millions of rows is slow in a general-purpose spreadsheet.

Guessed types. Excel decides what each column is as it opens the file. That is where leading zeros vanish, long IDs are rounded and strings like 1-2 or MAR1 become dates. In genetics research it has been serious enough that gene names were officially renamed to avoid it.

Fixing common display problems

Everything is in one column

The delimiter isn’t what the program expects. CSV does not stand for only commas: files may use semicolons (common in countries where the decimal separator is a comma), tabs (TSV) or pipes. In a viewer, set the delimiter manually. In Excel, use Data > From Text/CSV and choose it in the dialog instead of double-clicking.

Characters like é or ü look garbled

That is an encoding mismatch, usually UTF-8 text read as a legacy Windows code page. Excel opens a UTF-8 CSV correctly only if it starts with a byte order mark (BOM). Import through Data > From Text/CSV and set the file origin to UTF-8, or open the file in a tool that detects it. When you create CSVs for Excel users, include the BOM. Json Works does so by default.

The first row is data, not headers

Some exports have no header. Toggle the header option so the first row is treated as data and columns get generic names.

Lines break in the middle of a cell

A quoted field can legally contain line breaks, so a cell with an address spread over three lines is still one record. Naive tools that split on newlines break these into several rows. A real CSV parser handles quotes correctly, including doubled quotes "" for a literal quote.

Extra blank rows or columns

Trailing delimiters or empty lines at the end of the file create phantom columns. Remove the empty column or trim blank lines before using the data.

Working with big files

  • Search first. Type a value, such as an order number, to filter rows instantly instead of scrolling.
  • Sort by clicking headers. Click again to reverse. Sorting on a numeric column should sort as numbers, not text, so check that “10” comes after “9”.
  • Hide columns you don’t need to focus on what matters, and so exports stay small.
  • Resize or fit columns to read long values. Dragging a column edge changes the width; double-clicking auto-fits it.
  • Spot-check the end of the file. The last rows are where truncation and trailing junk show up.

Cleaning tips

  • Whitespace. Leading and trailing spaces cause lookups to fail silently. "Ada " is not "Ada".
  • Inconsistent values. Sort a column and scan for variants like NY, N.Y. and New York.
  • Duplicates. Sort by the key column and look for repeats before joining with other data.
  • Mixed formats. Dates as 10/17/2026 and 17/10/2026 are ambiguous. Normalise to ISO 2026-10-17.
  • Keep an untouched original. Clean a copy.

Convert to something more useful

To Excel. Export to .xlsx for typed columns, a frozen header and filters; see JSON to Excel for why this beats saving as CSV. Remember the million-row limit.

To JSON. Use CSV to JSON when an API, front-end or database import wants JSON. Decide whether numbers like "42" should become 42, and whether empty cells become null, empty strings, or are left out. See JSON vs CSV: when to use which.

To SQL. Convert to JSON first, then generate INSERT statements. For very large loads, the database’s native CSV importer is faster.

When a file is too big

Files with tens of millions of rows or several gigabytes are beyond any browser or spreadsheet. Use tools that stream:

# first lines
head -n 5 big.csv

# row count
wc -l big.csv

# split into 500,000-line parts
split -l 500000 big.csv part_

DuckDB can query a CSV directly with SQL, and xsv or qsv provide fast command-line slicing. Both are far more capable than a spreadsheet at that scale.

If your data starts as JSON, see how to view large JSON files and how to convert Postman JSON to CSV.

More guides

All guides · FAQ