How to split a huge CSV file into smaller files on Windows 11 / 10

Microsoft Excel is a spreadsheet program and a great tool used for analyzing data, locating information, creating charts, and presenting them to decision-makers. Excel works effectively at all levels, from a simple calculator to the most technical computations. But, every now and then, you run into a Spreadsheet size problem when dealing with large data. In this article let us learn how to split a huge CSV file into multiple smaller files.

Table of Contents

Why split the Huge Excel Spreadsheet?

Excel Spreadsheets will open only the first 1,048,576 rows and 16,384 columns of data. At times you come upon a .csv file that contains far more data than that.

Let us suppose you need to store data of the patients in a hospital, you will need to import a CSV file containing that many numbers of records, say over a million. But, the question here is how will you import and manage a Spreadsheet with over a million records?

The solution is to split the huge CSV file of more than a million patients into smaller multiple files so that it becomes easy for Excel to work with.

What happens when you open huge CSV files in Excel?

Excel will find it difficult to open. The large file will take a few minutes to open and you can see only the data present in the top 1048576 rows. After that, you will get an error saying “File Not loaded Completely“. You can work only in the rows that are displayed. This does not help when you need to analyze and study the entire data. So it’s better to split the file.

In this article, I am using two random CSV Excel spreadsheets to explain how to split huge CSV files into smaller files.

Ways to split Huge CSV Files

1. Use a Program to Split CSV files

We can simply use the CSV Splitter programs to split the huge CSV files. These splitters are widely used in spite of their memory issues. Use any one of the below-mentioned programs to split the files. They are available freely on the internet to download.

1. Huge CSV Splitter

This is a straightforward and simple to use CSV splitter. Let’s see how we can split the files using this below,

1. After opening the splitter, Simply input the CSV file you want to split and mention the Line Count and click on the Split file.

2. Once, the split is completed, you will get the multiple split files as shown

2. CSV Splitter

This also does the same function as the above splitter. Let us see how to split the files in this program.

1. Open the CSV splitter and enter the CSV file you want to split. Enter the number of rows and finally click on Execute.

2. After the split is done, the files will be saved in the location where your original CSV file is present.

There are many more CSV Splitters available in the market, one example is BitRecover CSV Splitter. You can use this if you want, but you need to pay to get the full version.

2. Online Source Split CSV

You can also break up the CSV files into smaller files using a free online source called Split CSV.

1. Open the Split CSV in your browser.

2. Upload the file you want to split.

3. Mention the line count you want the file to have.

4. Follow the on-screen steps and finally click on Split.

NOTE: Split CSV has some premium options also. To use these options you need to pay some subscription fees.

3. USe a Batch File

We can also use a batch file to split the huge CSV file into multiple files. Let us see how to create a batch file and then customize it to do the needy.

1. Open a text file ( Notepad). Write the batch code to split the CSV file (copy the code given below).

@echo off
setlocal ENABLEDELAYEDEXPANSION
REM Edit this value to change the name of the file that needs splitting. Include the extension.
SET BFN=SampleSpreadsheet.csv
REM Edit this value to change the number of lines per file.
SET LPF=2500
REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
SET SFN=ResultSplitFile
REM Do not change beyond this line.
SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
For /F "delims==" %%l in (%BFN%) Do (
SET /A LineNum+=1
echo %%l >> %SFN%!FileNum!.%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A FileNum+=1
)
)
endlocal
Pause

2. Now, let us look into the code and what changes do you need to make so that you can split your particular CSV file.

You will need to change the below values for the code to work for you accordingly,

SET BFN = This should be pointing to the CSV file that needs to be split. Just replace with your own CSV file path .

SET LPF = This is the number of rows you wish to open in your new file.

SET SFN = This is the new name you give to your split files.

3. Now, go to File > Save As > batch.bat file >Save.

4. Now, the file gets saved on the desktop as shown below. Double-click on the file to run in the command prompt.

NOTE: If you want to edit the code, Right-click on the batch icon and click on Edit.

5. Once, the batch program process the code, the Huge CSV file will be split into multiple smaller files based on your input.

After changing these values, please save your code as a .bat file with a new name and follow the above steps to split the CSV file.

4. Use Powershell

Powershell coding is easier and faster when compared to batch file processing. Hence, we can go with Powershell for this processing. Before going into the process, please copy the code given below into a notepad.

1. Press ” Windows+X” to open Powershell as admin.

2. Modify the code copied in the notepad according to your CSV file requirements. The modifications to be made are

$InputFilename = Get-Content ‘C:\file\location’ :- Fill in the location of your CSV file in the system. My files are found at “C:\Users\msdsh_000\Downloads\ Sample-Spreadsheet-500000-rows”.

$OutputFilenamePattern = ‘output_done_’ :- You can change the output name of your file in this line. My output file name is changed to ” Output-sample”.
$LineLimit = 50000:- Here, you can limit the line count. I am limiting it to 10000.

After the changes are made, copy the entire code and paste it into the Powershell window.

3. The code is run in the Powershell window and the output is saved as .csv files.

5. Use Power Pivot

1. Open a blank Excel Spreadsheet. Go to Data > New Query > From file > From CSV.

2. A window pops up asking you to import a CSV file.

3. Preview of the file is shown. Now, click on Load > Load to

4. Make sure you check the box against ” Add this data to the data model” and also click on the radio button ” Only create connection”. Finally, click Load.

NOTE: This step tells that we are not loading the data to an Excel sheet.

6. The entire large file gets loaded. Now, click on Manage Data.

7. The Power Pivot table will now appear on the screen, where you can add and change the columns as you need.

This method does not divide the CSV file into smaller files. You can simply manipulate the CSV in Excel, which is really useful.

That’s all.

Hope this article has been useful and informative.

Comment and let us know which method you used.