LARGE CSV · PRACTICAL GUIDE
How to Split a Large CSV File
A CSV that will not open in Excel is easier to handle when split by rows, target size, or a column value. This guide helps you choose a method and avoid delimiter or encoding problems.
The short answer: choose the method that matches the split
Use Excel for a small one-off file, Power Query when the same Excel steps will be repeated, and LocaCSV when you want to split a CSV of up to 50 MiB in your browser. Avoid copying and pasting large files by hand because rows are easy to miss or duplicate.
Try a CSV split
Switch methods to see how the same six-row sample CSV becomes different output files.
Before: orders.csv
6 data rows128 B
| Order ID | Store | Amount |
|---|---|---|
| A001 | London | 1200 |
| A002 | Madrid | 800 |
| A003 | London | 1500 |
| A004 | Madrid | 600 |
| A005 | London | 900 |
| A006 | Madrid | 1100 |
After
Split into 3 files
orders_001.csv
2 data rows58 B
| Order ID | Store | Amount |
|---|---|---|
| A001 | London | 1200 |
| A002 | Madrid | 800 |
orders_001.csv2 data rows58 B
| Order ID | Store | Amount |
|---|---|---|
| A001 | London | 1200 |
| A002 | Madrid | 800 |
orders_002.csv
2 data rows58 B
| Order ID | Store | Amount |
|---|---|---|
| A003 | London | 1500 |
| A004 | Madrid | 600 |
orders_002.csv2 data rows58 B
| Order ID | Store | Amount |
|---|---|---|
| A003 | London | 1500 |
| A004 | Madrid | 600 |
orders_003.csv
2 data rows58 B
| Order ID | Store | Amount |
|---|---|---|
| A005 | London | 900 |
| A006 | Madrid | 1100 |
orders_003.csv2 data rows58 B
| Order ID | Store | Amount |
|---|---|---|
| A005 | London | 900 |
| A006 | Madrid | 1100 |
Why a large CSV may not open
CSV is plain text, but the program opening it still has limits. Excel cannot place more than 1,048,576 rows on one worksheet, and smaller files can still be slow when rows contain many or long cells or memory is limited.
- The file has more rows than an Excel worksheet can hold.
- The computer runs short of memory while loading the file.
- The wrong delimiter or encoding makes the preview unreadable.
- A simple comma or line split breaks quoted commas and line breaks inside cells.
Excel vs. Power Query vs. Python vs. LocaCSV
Choose by file size, how often you repeat the task, and whether you can maintain code.
| Method | Best for | Advantages | Limitations |
|---|---|---|---|
| Excel | A small, one-off file | Familiar visual interface | Row limit and manual errors |
| Power Query | Reusable Excel imports | Recorded steps can be refreshed | Multiple CSV exports need extra setup |
| Python | Large or recurring jobs | Easy to automate | Ongoing setup and upkeep |
| LocaCSV | Quickly splitting up to 50 MiB | Works in the browser | Up to 100 outputs; one column in value mode |
Excel
- Best for
- A small, one-off file
- Advantages
- Familiar visual interface
- Limitations
- Row limit and manual errors
Power Query
- Best for
- Reusable Excel imports
- Advantages
- Recorded steps can be refreshed
- Limitations
- Multiple CSV exports need extra setup
Python
- Best for
- Large or recurring jobs
- Advantages
- Easy to automate
- Limitations
- Ongoing setup and upkeep
LocaCSV
- Best for
- Quickly splitting up to 50 MiB
- Advantages
- Works in the browser
- Limitations
- Up to 100 outputs; one column in value mode
How to split a CSV with LocaCSV
If the preview columns do not line up, correct the delimiter or encoding before continuing.
- Open CSV Split for row or size chunks, or Split by value for column groups, then choose one CSV.
- Check the header, encoding, delimiter, and preview.
- Enter the data rows per file or target size. On the value page, choose one column.
- Run the split, check the counts, and download the ZIP.
How splitting by a column value works
Value mode creates one CSV for each value in a selected column. For example, a Store column containing London, Madrid, and Seoul produces three files.
- Rows with the same value stay in the same output file.
- Blank values can be excluded or written to a dedicated output.
- You can select one column and create up to 100 outputs per run.
Row-count and file-size cautions
LocaCSV accepts one CSV of up to 50 MiB. Actual performance also depends on the device, row length, and number of outputs.
- Row-count mode excludes the header from the number of data rows per file.
- Target-size mode includes the output encoding, BOM, and repeated header. A single data row is never cut in half.
- Keep the original file and check the row counts before using the outputs.
Character encoding and garbled text
UTF-8 is common, while older Windows files may use Windows-1252. Choose the source encoding, check the preview, and use UTF-8 with BOM when the destination Excel setup recognizes it more reliably.
- Check names, punctuation, currency symbols, and accented letters.
- Shifted columns usually mean the delimiter is wrong.
- If the source encoding is unknown, check the export settings of the system that created the file.