Split huge CSV files – Open in Excel

One of Microsoft Excel’s shortcomings is the limited size of a spreadsheet. Excel has a spreadsheet row limit of 1,048,576. If you need to open in Excel, make your Excel file smaller or split a large CSV file. To get started, you will need to have the CSV file that you want to split into smaller chunks. You can use one of the following methods.

If your file is larger than that and you will try to open this file in Excel, you are going to get the following message.

a) Using a Batch File

Create a programmable batch file to process the CSV into smaller chunks, customizing the file to deliver different chunks.

Open a new text document, then copy and paste the following:

@echo off
setLocal EnableDelayedExpansion

REM Edit this value to change the number of lines per file.
set limit = 15000
REM Edit this value to change the name of the file that needs splitting. Include the ext.
set file = SampleFile.csv
set lineCounter = 1
set filenameCounter = 1

REM Edit this value to change the name of each short file.
set name =
 SampleSplit
REM Do not change beyond this line.
for %%a in (%file%) do (
    set "name=%%~na"
    set "extension=%%~xa"
)

for /f "tokens = *" %%a in (%file%) do (
    set splitFile = !name!-part!filenameCounter!!extension!
    if !lineCounter! gtr !limit! (
        set /a filenameCounter = !filenameCounter! + 1
        set lineCounter = 1
        echo Created !splitFile!.
    )
    echo %%a>> !splitFile!

    set /a lineCounter=!lineCounter! + 1
)

Once you’ve entered your variables, save the file as .bat extension. Then you can run it, so that it’ll give you the splat files output.

b) Using PowerShell Script to Break Up a CSV File

In case you want to try a PowerShell scripts which are faster, follow the following steps.

The following script quickly cuts your large CSV into smaller files. Open Windows PowerShell and run the following script.

# Read parent CSV
$InputFilename = Get-Content 'Enter file path here'
$OutputFilenamePattern = 'output_done_'
$LineLimit = 15000

# Initialize
$line = 0
$i = 0
$file = 0
$start = 0

# Loop all text lines
while ($line -le $InputFilename.Length) {

    # Generate child CSVs
    if ($i -eq $LineLimit -Or $line -eq $InputFilename.Length) {
        $file++
        $Filename = "$OutputFilenamePattern$file.csv"
        $InputFilename[$start..($line - 1)] | Out-File $Filename -Force
        $start = $line;
        $i = 0
        Write-Host "$Filename"
    }

    # Increment counters
    $i++;
    $line++
}

I hope this will solve your problem.