How to Open a Large CSV File: 6 Ways When It Is Too Big for Excel or Google Sheets

If you are wondering how to open a large CSV file, start by checking the row count. Excel shows at most 1,048,576 rows per worksheet, so anything bigger gets cut off. The fastest fix is to cut the file into Excel-sized parts with our free CSV splitter, which repeats the header row in every part and never uploads your data. If you need every row in one place, load it through Excel’s Power Query, a database tool or Python instead.

We open oversized CSV exports every week: CRM dumps, lead lists, email platform exports and enrichment results that run into millions of rows. This guide covers the methods we actually use, the limits that cause the problem, and the errors you are likely to hit along the way.

Why won’t my CSV open properly?

A CSV is just plain text, so the file itself has no size limit. The limits come from the program you open it with.

• Spreadsheet row caps: Excel stops at 1,048,576 rows and 16,384 columns. Everything past that is not loaded.
• Cell caps: Google Sheets limits a spreadsheet by total cells, so a wide file hits the wall long before a narrow one.
• Memory: spreadsheets hold the whole file in RAM, with formatting on top. A file of a few hundred MB can make a laptop freeze even when the row count fits.
• Plain text editors: some open big files fine but show raw commas, not columns, which makes them useless for real work.

When Excel cuts a file short, it says so: “This data set is too large for the Excel grid. If you save this workbook, you’ll lose data that wasn’t loaded.” Microsoft’s own guidance is clear: do not save over the original file. If you must save, use File > Save a Copy and name it as a truncated version.

How big is too big? CSV limits by program

These are the limits we check before choosing a method. Figures come from official documentation; where a product changes its limits over time, we say so.

Excel’s numbers are in Microsoft’s Excel specifications and limits page. Google raised the Sheets cap to 10 million cells in its 2022 Workspace update. A quick rule for Sheets: multiply rows by columns. A 30-column file tops out around 333,000 rows.

How to open a large CSV file without losing data

The right method depends on whether you need to edit the rows, summarise them, or just look. Here are six methods, fastest first.

Method 1: Split the CSV into Excel-sized files (fastest)

Splitting is what we do for most client files, because each part then opens in Excel, Google Sheets or Numbers like any small file. Our browser-based CSV splitter handles files up to 1 GB.

1. Open the free CSV splitter and drop in your file. It accepts .csv, .tsv and .txt.
2. Choose Split by: rows per file, number of files or file size in MB.
3. Leave First row is a header ticked so every part gets the column names.
4. Click Split CSV. The file is read in 4 MB chunks, with a progress bar.
5. Download each part, or all of them in one ZIP. Parts are named like leads_part1.csv, leads_part2.csv.

Two details matter for real-world exports. The splitter follows CSV quoting rules, so a quoted value with a line break inside stays in one row instead of breaking the file. It also keeps a UTF-8 byte order mark if your file has one, which helps Excel show accented names correctly.

Privacy matters here too. Lead lists, customer exports and payroll files contain personal data. The splitter runs inside your browser using your own device, so the file is never uploaded to LeadCanal or anyone else, and nothing is stored once you close the tab.

Which split setting should you use?

Method 2: Load it with Power Query in Excel for Windows

If you need one total across all rows, such as revenue by state or leads by industry, Power Query can read the full file without putting it in the grid. These steps follow Microsoft’s instructions for Excel for PC:

1. Open a blank workbook.
2. Go to Data > From Text/CSV (in the Get & Transform Data group) and select your file.
3. In the preview window, click the arrow next to Load and choose Load To.
4. Pick PivotTable Report, or choose Only Create Connection and tick Add this data to the Data Model.
5. Build your PivotTable from the field list. It summarises every row, even past the grid limit.

The catch: you cannot scroll or edit rows past 1,048,576 in the sheet. Recent Microsoft 365 builds for Mac also have Get Data for text files, but the menus differ, so check your version.

Method 3: Open it in Google Sheets (if it fits)

For files under the cell cap, go to File > Import > Upload in a blank sheet. This does upload the file to Google Drive, so think twice with sensitive data. A file near the limit often fails, so splitting first is usually quicker.

Method 4: Peek at it from the command line

Sometimes you only need the headers and a few sample rows. These built-in commands read the start of the file without loading the rest:

• Mac or Linux: head -n 20 leads.csv shows the first 20 lines. wc -l leads.csv counts lines.
• Windows PowerShell: Get-Content leads.csv -TotalCount 20 shows the first 20 lines.

Line counts are approximate. A quoted field that contains a line break counts as two lines, so wc -l can overcount rows in exports with notes or address fields.

Method 5: Query it with Python or DuckDB

For filtering, deduplicating or joining millions of rows, code beats any spreadsheet. With pandas you can read a file in pieces using the chunksize option of read_csv, and load only the columns you need with usecols:

for chunk in pd.read_csv("leads.csv", chunksize=100000, usecols=["email", "company"]): ...

DuckDB runs SQL straight on the file, for example SELECT country, COUNT(*) FROM 'leads.csv' GROUP BY country;. Both are free, run locally and handle files far bigger than Excel can.

Method 6: Use a dedicated large CSV viewer

Desktop CSV viewers and editors are built to page through huge files without loading everything at once. Check whether a tool is a desktop app or a web service, because some online viewers upload your file to their servers.

Which method should you use? Side-by-side comparison

Worked example: a 2.3 million row CRM export

Here is how to open a large CSV file the way we do it on a typical job. A client sends a CRM export of about 2.3 million contacts, around 600 MB, and wants it cleaned before a cold email campaign. Double-clicking it in Excel shows the grid warning and only the first 1,048,576 rows.

1. We count rows first. In PowerShell or with wc -l, we get a rough figure to plan the split.
2. We open the LeadCanal CSV splitter, set Rows per file to 1,000,000 and keep the header box ticked.
3. The tool returns three files: two with 1,000,000 rows and one with the remaining 300,000 or so, each with the original header.
4. Each part opens in Excel in seconds. We fix formats, remove obvious junk and then run the emails through our email verifier before anything is sent.

The original file is never edited, so we always have a clean source to go back to. When a project needs deduplication, enrichment and validation at that scale, our data cleansing team handles it end to end.

Why is my CSV file too large for Excel? Common errors and fixes

Got a text export that is not a real CSV yet? Turn it into one with our TXT to CSV converter, or read our guide on converting TXT to CSV in Excel, Sheets and Python. Once the parts are clean, you can turn a contact list into phone contacts with CSV to VCF, or pull addresses out of messy notes with the email extractor.

Frequently asked questions

How do I open a CSV file with more than 1 million rows?

Split it into files of up to 1,000,000 rows and open each part, or load it with Excel’s From Text/CSV to a PivotTable or the Data Model.

Can Excel open a 2 GB CSV file?

Only partly. Excel loads up to 1,048,576 rows and drops the rest. Use Power Query, Python, DuckDB or split the file first.

How do I split a large CSV file without opening it?

Use a tool that reads the file in chunks, such as the LeadCanal CSV splitter, or the split command on Mac and Linux, which does not repeat the header.

What is the maximum CSV size for Google Sheets?

Google limits a spreadsheet by cells, not file size. Its last announced cap is 10 million cells, so rows times columns must stay under that.

Why does my large CSV lose leading zeros in Excel?

Excel converts values like 01234 to numbers. Import the file through From Text/CSV and set those columns to Text.

Is it safe to open a large CSV in an online tool?

Only if the tool processes files locally. Many online viewers upload your file. Our splitter runs in your browser, so the data stays on your device.

How do I open a large CSV file on a Mac?

Split it first for Numbers or Excel, use head in Terminal for a quick look, or query it with Python or DuckDB.

Conclusion: key takeaways

Knowing how to open a large CSV file mostly comes down to matching the method to the job. Split it when you need to work in a spreadsheet, and query it when you need answers from every row.

• Excel shows at most 1,048,576 rows, so bigger files lose data in the grid.
• Never save over the original after Excel’s too-large-for-the-grid warning.
• Split the file into parts with the header repeated to work in Excel or Sheets.
• Use Power Query, Python or DuckDB when you need totals across every row.
• Pick a tool that runs locally when the CSV holds personal or client data.

Ready to try it? Open the free CSV splitter, or browse all our free browser tools.

Editorial policy

LeadCanal publishes practical guides for people who work with files and data every day. We write from hands-on use of the tools we build, check product limits against official documentation from Microsoft, Google and open-source projects, and update articles when those limits change.

Content transparency

This article was researched with keyword and search results data and checked against official documentation, including Microsoft’s Excel limits pages, Google’s Workspace announcements and the pandas documentation. LeadCanal offers the free tools mentioned here, including the CSV splitter, TXT to CSV, CSV to VCF and email extractor. We have no affiliation with the other products named.

About the author

This guide was written by the LeadCanal team. We build verified B2B contact lists and run cold email infrastructure for clients, which means opening, splitting and cleaning large CSV lead lists, VCF contacts and PDF reports daily. We built our browser-based converters to make that work faster and more private.