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 to analyze data, locate information, create charts, and present them to decision makers. Excel works effectively on all levels, from a simple calculator to the most technical calculations. But every now and then you run into a spreadsheet size problem when dealing with big data. In this article, let’s learn how to split a huge CSV file into several smaller files.

Why split up the huge Excel spreadsheet?

Excel spreadsheets will only open the first 1,048,576 rows and 16,384 columns of data. Sometimes you come across a .csv file that contains a lot more data than that.

Suppose you need to store patient data in a hospital, you will need to import a CSV file that contains that many records, let’s say more than a million. But, the question here is how will you import and manage a spreadsheet with more than a million records?

The solution is to divide the huge CSV file of more than a million patients into several smaller files so that Excel can work with it easily.

What happens when you open huge CSV files in Excel?

Excel will have a hard time opening. The large file will take a few minutes to open and you will only be able to see the data present in the top 1048576 rows. After that, you will get an error saying «File not fully loaded“. You can work only on the rows that are displayed. This does not help when you need to analyze and study all the data. Then it is 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 dividers are widely used despite their memory problems. Use any of the programs mentioned below to divide the files. They are freely available on the Internet for download.

1. Divisor CSV enorme

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

2. Once the division is complete, you will get the multiple files divided as shown.

2. Divisor CSV

This also does the same function as the divisor above. Let’s see how to split 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 Run.

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

There are many more CSV splitters available on the market, an example is Divisor de CSV BitRecover. You can use this if you want, but you have to pay to get the full version.

2. CSV split from online source

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

1. Open Split CSV in your browser.

2. Load the file you want to divide.

3. Mention the number of lines you want the file to have.

4. Follow the steps on the screen and finally click on Break apart.

NOTE: Split CSV also has some premium options. To use these options, you have 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’s see how to create a batch file and then customize it so you need it.

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

2. Now go to File> Save As> batch.bat proceedings > Save.

3. Now the file is saved to the desktop as shown below. Double click in the file to run at the command prompt.

NOTE: If you want to edit the code, The right button of the mouse on the batch icon and click Edit.

4. Once the code is processed by the batch program, the huge CSV file will be divided into several smaller files based on your input.

5. Now, let’s look at the code and what changes you need to make in order to split your particular CSV file.

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

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

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

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

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

4. Utilice Powershell

Powershell encoding is easier and faster compared to batch file processing. Therefore, we can opt for Powershell for this processing. Before starting the process, copy the code provided below into a notepad.

1. Press «Windows + X» to open Powershell as an administrator.

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

• $ InputFilename = Get-Content ‘C: file location’: – Complete the location from your CSV file on the system. My files are at “C: Users msdsh_000 Downloads Sample-Spreadsheet-500000-rows”.

• $ OutputFilenamePattern = ‘output_done_’: – You can change the production name of your file on this line. The name of my output file is changed to “Output-sample”.
• $ LineLimit = 50000: – Here you can limit the line count. I am limiting it to 10,000.

Once the changes are made, copy all the code and paste it into the Powershell window.

3. The code runs in the Powershell window and the output is saved as .csv records.

5. Use Power Pivot

1. Open a blank Excel spreadsheet. Go to Data> New query> From file> From CSV.

2. A window will appear asking you to import a CSV file.

3. A preview of the file is displayed. Now click Upload> Upload to

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

NOTE: This step indicates that we are not loading the data into an Excel sheet.

6. The entire large file is loaded. Now click Manage data.

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

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

That is all.

I hope this article has been helpful and informative.

Comment and let us know what method you used.