Working With Multi-Million Row Files Without Losing Your Mind
I spent three weeks last year trying to migrate a 1.2-million-row dataset from the old .xls format into something usable. The original file was created by an internal tool that wrote CSV-style output but saved it with a .xls extension, which caused half the entries to get silently mangled on open. That experience taught me more about large file handling than any tutorial ever did. The term refers to datasets approaching or exceeding the one-million-row threshold in spreadsheet contexts, typically stored in either legacy binary Excel format or CSV with semicolon delimiters. The "S" in the middle commonly denotes the semicolon-separated variant, which is standard in European localization where the comma serves as a decimal marker rather than a field separator. When you encounter a file described this way, it usually means someone exported millions of rows using locale-specific formatting that breaks when opened in Western Excel installations. Legacy .xls files cap out at 65,536 rows per sheet. If your source data exceeds that, it either spans multiple sheets or it was never actually a proper .xls file to begin with. The .xlsx format lifts this to 1,048,576 rows, which is why most million-row workflows target that format. But even .xlsx hits real walls when you start adding formulas, conditional formatting, or embedded objects.
The Conversion Process
Start by identifying the actual delimiter and encoding. A file labeled as semicolon-separated will have commas in decimal numbers if it originated from a German or French system. Opening this directly in Excel will split the decimals incorrectly. You can verify the delimiter by opening the raw file in a text editor and scanning a few hundred lines. Look for the most frequent non-newline character that appears between values. Once confirmed, skip the double-click-through-Excel method. Instead, use PowerShell for the import because it handles encoding and delimiter issues without corrupting data mid-flight: Import-Csv "path\to\file.csv" -Delimiter ";" -Encoding UTF8 | Export-Csv "path\to\output.xlsx" -NoTypeInformation
That one pipeline handles millions of rows, preserves numeric types correctly, and writes to a proper .xlsx container. The downside is it creates a flat table with no formatting. If you need formatting, you will need to apply it separately, which means using a library like ImportExcel rather than building it manually.
Get the Full Details
What Nobody Tells You About Million-Row Files
The first thing that goes wrong is memory. PowerSHell's Import-Csv reads the entire file into memory before doing anything with it. On a machine with 8GB of RAM, a 1.2 million row CSV with moderate column count will consume roughly 2 to 3 GB. Add formatting operations and you are looking at a crash before the export finishes. The workaround I settled on was chunking the data into groups of 100,000 rows, exporting each chunk to its own temporary .xlsx, then merging them with a dedicated merge script. This kept peak memory under 500MB. Another issue that surfaces later is worksheet size. Even when you successfully write 1,048,576 rows to .xlsx, opening that file in Excel becomes painful. Every scroll operation recalculates visible ranges. Sorting triggers full-table scans. If your workflow requires interactive exploration, consider switching to Python with pandas and saving to Parquet format instead. Parquet stores the same data in about a third of the space and queries it significantly faster. Excel compatibility is poor, but if your goal is analysis rather than presentation, it pays off.
Common Pitfalls When Exporting Large Files
Date corruption is the most frequent problem. Excel stores dates as serial numbers starting from January 1, 1900, and it incorrectly treats 1900 as a leap year. Files with dates before March 1, 1900 will show off by one day after any round-trip through Excel. If your dataset contains historical records, this will silently corrupt the data. The fix is to store dates as formatted strings rather than true date types, or to shift your date floor to 1904 if you are working in a Mac-native environment. Column count matters more than row count. A million rows with five columns is manageable. A million rows with two hundred columns is a different problem. Each additional column increases parsing time linearly and memory pressure super-linearly. I learned this the hard way when a client sent a 1.1-million-row, 180-column file claiming it was "just a CSV." It took forty minutes to import and nearly doubled my RAM usage compared to a narrower equivalent. The solution was to drop empty columns before processing, which cut the final file size by sixty percent and halved the import time.
When to Walk Away From the Spreadsheet Entirely
If your workflow involves repeated transformations, aggregations, or joins on million-row datasets, spreadsheets are the wrong tool regardless of format. A lightweight SQLite database handles these operations in seconds where Excel would hang for hours or crash outright. I typically move large datasets into SQLite, perform all manipulations there, and export only the final summarized result back to .xlsx for sharing. The transition is straightforward. Use the sqlite3 CLI or the sqldf R package, or write a short Python script with the sqlite3 module. Load the CSV directly with .import, create indexes on frequently filtered columns, and run your queries. A simple GROUP BY on a million-row table with proper indexing takes under two seconds on standard hardware. There is no universal fix for every large-file scenario. The approach depends entirely on what you are trying to do with the data afterward. Importing for one-time review is different from building a repeatable reporting pipeline. The chunking strategy works for most ad-hoc cases, but if you are doing this repeatedly, the SQLite route saves far more time than any formatting tweak ever will.
