How to Split Large Excel CSV File into Smaller Files ?

Your CSV file has 500,000 rows and Excel just crashed trying to open it. Or you need to split it into batches for upload to a system that only accepts 10,000 rows at a time. Either way, you need to divide that large file into smaller ones without losing data or headers.

There are 3 reliable methods. I’ll walk through each one so you can pick the approach that fits your situation.

Why Split Large CSV Files?

CSV files don’t have an inherent size limit, but the tools that work with them do. Here are the most common reasons to split:

• Excel’s row limit is 1,048,576 rows. Files larger than that simply won’t open in Excel
• Many web applications cap CSV imports at 10,000 to 100,000 rows per file
• Email systems often reject attachments over 10 MB, and large CSVs blow past that quickly
• Database bulk imports run faster and are easier to monitor when done in smaller batches
• Sharing a 200 MB CSV file is impractical. Splitting it makes distribution manageable

Method 1: Split CSV Using Excel Macro

This method works well for files you can at least partially open in Excel. It uses a VBA macro to split the file into chunks of however many rows you specify.

Step 1: Open the VBA Editor

Open the CSV file in Excel. Press Alt + F11 to open the Visual Basic Editor. Click Insert in the menu and choose Module.

Step 2: Paste This Macro

Sub SplitCSV()
    Dim rowsPerFile As Long
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim newWb As Workbook
    Dim newWs As Worksheet
    Dim totalRows As Long
    Dim currentRow As Long
    Dim fileNum As Integer
    
    rowsPerFile = 10000
    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1)
    totalRows = ws.UsedRange.Rows.Count
    fileNum = 1
    currentRow = 2
    
    Do While currentRow <= totalRows
        Set newWb = Workbooks.Add
        Set newWs = newWb.Sheets(1)
        ws.Rows(1).Copy newWs.Rows(1)
        Dim endRow As Long
        endRow = Application.Min(currentRow + rowsPerFile - 1, totalRows)
        ws.Rows(currentRow & ":" & endRow).Copy newWs.Rows(2)
        newWb.SaveAs Filename:=ThisWorkbook.Path & "split_" & fileNum & ".csv", _
            FileFormat:=xlCSV
        newWb.Close False
        currentRow = endRow + 1
        fileNum = fileNum + 1
    Loop
    MsgBox "Done! " & (fileNum - 1) & " files created."
End Sub

Change rowsPerFile = 10000 to whatever chunk size you need. Press F5 to run the macro. The split files will appear in the same folder as the original CSV, named split_1.csv, split_2.csv and so on.

Each output file includes the header row from the original, so you don’t need to add headers manually.

Method 2: Split CSV Using a Dedicated Splitter Tool

For very large files (over 500,000 rows) or if you can’t open the file in Excel at all, a dedicated CSV splitter application is the right tool.

Dedicated tools read and write CSV data directly without loading the whole file into memory. This is how they handle files that would crash Excel.

Most CSV splitter applications follow the same basic workflow:

Step 1: Open the tool and browse to your CSV file.

Step 2: Specify whether the file has a header row and what delimiter it uses (comma, semicolon or tab).

Step 3: Enter the number of rows per output file.

Step 4: Set the destination folder for the output files.

Step 5: Click Split and wait for the process to finish.

The tool generates your split files in the destination folder, each with the original headers preserved.

Method 3: Split CSV Using PowerShell

If you’re comfortable with the command line, PowerShell can split CSV files without installing any extra software. This works on Windows 7 and above.

Open PowerShell and run this script (replacing the path and row count with your values):

$inputFile = "C:path	oyourile.csv"
$rowsPerFile = 10000
$header = Get-Content $inputFile -First 1
$data = Get-Content $inputFile | Select-Object -Skip 1
$fileNum = 1
for ($i = 0; $i -lt $data.Count; $i += $rowsPerFile) {
    $chunk = $data[$i..([Math]::Min($i + $rowsPerFile - 1, $data.Count - 1))]
    $outFile = "C:path	ooutputsplit_$fileNum.csv"
    $header | Out-File $outFile -Encoding UTF8
    $chunk | Out-File $outFile -Append -Encoding UTF8
    $fileNum++
}
Write-Host "Done! $($fileNum - 1) files created."

This script adds the header row to each output file automatically. It runs in memory-friendly chunks, so it handles large files without crashing. A 1 million row CSV typically processes in under 2 minutes on a modern PC.

Limitations to Know

Frequently Asked Questions

What’s the fastest way to split a CSV file with 1 million rows?

PowerShell or a dedicated CSV splitter application. Both process the file directly without loading it into Excel, which is the bottleneck for large files. A 1 million row CSV with no complex data typically splits in 1 to 3 minutes using either method.

Will the split files include the header row from the original CSV?

Yes, if you use the Excel macro or PowerShell script provided above, the header row is copied to each output file automatically. Most dedicated CSV splitter applications also have an option to include headers in every file. Always verify the first output file before processing the rest.

Can I split a CSV file by file size instead of row count?

Some dedicated CSV splitter tools support splitting by file size (e.g., split into 10 MB chunks). The Excel macro and PowerShell script in this guide split by row count only. If you need size-based splitting, use a tool with that specific option.

Will splitting the CSV mess up any rows that contain commas inside quoted fields?

No, as long as the splitter correctly handles quoted fields. A properly quoted field like “Smith, John” is treated as a single value even though it contains a comma. The methods above handle standard RFC 4180 CSV formatting. Problems arise only if the original CSV has malformed quoting.

I split the CSV but now the row counts in the output files don’t add up to the original. What happened?

Check whether the original CSV had blank rows in it. Many tools skip blank lines during processing. Also check if any data rows spanned multiple lines due to embedded newlines in quoted fields. If that’s the case, a simple line-count split may have divided those multi-line rows incorrectly.

Can I merge the split CSV files back into one later?

Yes. In PowerShell you can use Get-Content split_*.csv | Out-File merged.csv to combine them. Note that this command combines the headers too, so you’d need to clean up the extra header rows. A simpler approach is to import each file into the same spreadsheet or database table, which handles duplicates naturally.