How to split a large CSV file into multiple files?

CSV file is an Excel spreadsheet file. This is exactly the program that is able to cope simply with a huge number of tasks that people face every day. At first glance, it may seem that the Excel table is infinite, but in reality it is not, and it will be quite difficult for a simple user to verify it. But if you are here and are reading this article, then you understand exactly what could be the matter.

What is it for

Why is the skill of dividing a large CSV file into several smaller ones useful? Excel has a little over a million rows and columns in its arsenal, and if you just look at the sheet, it may seem very difficult to fill it out entirely, but in fact it is easier than you think.

Let's imagine a situation: you own a fairly large business that is associated with sales or the provision of any services. You work a decent amount of time, so you have a large customer base of email addresses and names at your disposal. You have successfully imported the CSV file and you realize that the entire table is full. What's next?

Then comes the understanding that managing such a huge table is very difficult, even if you know all sorts of tricky Excel tricks, know how to use filters and formulas. Or there may be such a situation that someone sent you such a file, sometimes it may even exceed the limit if it was created in some other program.

The first way is to split using the program

There are a sufficient number of programs on the Internet that are able to cope with splitting a large CSV file into several small ones, but not all of them perfectly cope with the task, so we will consider only the best programs from this category in this article.

1. Free Huge CSV Splitter.

This program is considered to be the basic tool for splitting CSV files. It works according to a very simple principle: you need to select the file that you want to split, and also specify the number of lines that you want to use, and then click the Split file button.

2. CSV Splitter.

This program works on the same principle as the first, with the only difference that the Splitter has a slightly smoother design, although to some the simplicity of the first program may seem more appropriate when performing such a task.

The second way is using a batch file

This option is a little more complicated than the first, so it is very important to follow clear instructions here.

After using the batch file, you need to create a programmable batch file. To do this, you need to create and open a new text document, write the following:

@echo off
setlocal ENABLEDELAYEDEXPANSION
REM Edit this value to change the name of the file that needs splitting. Include the extension.
SET BFN=HCAHPSHospital.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. If will be followed by a number indicating where it is in the list.
SET SFN=HosptialSplitFile
REM Do not change beyond this line.
SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
For /F “delims==” %%1 in (%BFN%) Do (
SET /A LeneNum+=1
echo %%1 >> %SFN%!FileNum! .%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A LineNum+=1
)
)
endlocal
Pause

But take your time, because you need to configure the batch file before starting. To understand how this works, you need to understand why and how each command that was used above works.

SET BFN is a very important command because it specifies the CSV file to be split.

The SET LPF command will indicate how many lines will be in the new file.

SET SFN is responsible for how the new split files will be named.

Now let's move on to the fun part - using the resulting file. To do this, click File> Save As> select a file name. After that, select the same file and press the F2 button, this will make it possible to change the format of this file from .txt to .bat. A warning will appear, you need to agree and click OK.

It is after following these steps that you can successfully split the CSV file into smaller files.

The third way is using a PowerShell script

Of course, you can use the previous method using batch files to split large CSV files, but it's worth knowing that PowerShall scripts are much faster and more convenient, and also better suited for modern assemblies.

First, the user needs to get into a special Windows Power menu. To do this, he must press the key combination CTRL + R. The menu that opens will contain PowerShall. If for some reason this method did not work, then you can enter powershell directly in the search bar in Start and select Best match. After that, you need to write the following script:

$InputFilename = Get-Content ‘C:filelocation’
$0outputFilenamePattern = ‘output_done_’
$LineLimit = 50000
$line = 0
$i = 0
$file = 0
$start = 0
while ($line -le $InputFilename . Length) {
if ($i -eq $LineLimit -Or $line -eq $InputFilename.Lenght) {
$file++
$Filename = “$OutputFilenamePattern$file.csv”
$InputFilename[$start..($line-1)] | Out-File $Filename -Force
$start = $line;
£i = 0
Write-Host “$Filename”
}
$i++;
$line++
}

Before working with this script, you need to understand that instead of filelocation should be written the location of your CSV file, which needs to be split.

The fourth way is to use Power Pivot

In fact, Power Pivot does not split CSV files into multiple small files; the way it works is slightly different.

To use it, you need to upload a CSV file to Excel, and then use this program to open it. It turns out that, in fact, the file is not divided, it remains a single whole, but at the same time, using the program, it is possible to bypass the limit of a million lines. Believe it or not, some people who have taken advantage of this option have posted a screenshot on the forum, which displays two million lines in Excel.

The fifth way is to use online services to split CSV files

If you don't want to deal with scripts that may not seem like an easy solution to everyone, you can use the free online CSV separators.

One of these services is Split CSV. It works the same way as the first two programs, which were discussed above, with the only difference that all the action takes place on the Internet, and not in the computer itself.

The program is totally free to use, and by using HTML5, all calculations are done on your own computer. Your data is not uploaded on the internet, and your CSV file will be safely cut into smaller chunks on your own computer.

• The free online split CSV service has the following functionalities:
• Selecting the number of header lines;
• Choosing the number of lines per page after the CSV split.

To sum up: what are the various ways to split a CSV file?

If you have several large CSV files that cannot be opened or fully loaded due to reaching the maximum number of columns and rows in Microsoft Excel or some CSV viewer, then you need to split up the CSV file.

There are many different CSV file splitter software and online tools available that can be used to split files.

Thus, we got acquainted with five not tricky ways with which you can easily and easily split a large CSV file, and even learn how to open it in Excel using the program.

Frequently Asked Questions

Is it possible to use a PowerShell script to split CSV files?

Yes, you can use PowerShall scripts to separate files. This method will be much faster and more convenient, and also better suited for modern builds.