How to Split a Large CSV File into Multiple Smaller Files Online?

Do you have a large-size CSV file on a system that you want to split into multiple smaller files? Wondering how to split a large CSV file into multiple smaller files? Looking for the best option to implement the required task? If yes, then continue reading the article because here we have explained an approach that will help in performing the necessary process in minimum time.

Microsoft Excel is an excellent program when it comes to data management. But, at times, the encounter of unexpected errors makes the file corrupt or inaccessible which leads to a lot of frustration. This happens when the file size is extended. Due to this, its wonderful benefits get overshadowed by its shortcomings.

Why to Split a Large CSV File into Multiple Smaller Files?

At first, the Excel sheet will open the first 1,048,576 rows and 16,384 columns of data. Sometimes, you came across of .csv file that carries more data than that.

For e.g., if you need to store the data of patients admitted to a hospital, then you will need to import a CSV file containing a number of records, say more than a million. But, the question is how to import and manage a Spreadsheet with millions of records.

The solution is to split a large CSV file holding millions of records into multiple smaller files so that it will become manageable and easy to work with.

What Happens When a user opens Large-Size CSV Files?

It is very challenging for Excel to open large-size CSV files. It will take a few minutes to open the file and then you can see that the data is only present in the top 1048576 rows. After that, you will see the error message showing “File not Loaded Completely”. So, you need to use only the rows for work. This will not help when you need to analyze and go through the entire data. So, splitting the CSV file will be better.

Ways to Split Large CSV Files into Multiple Smaller Files on Mac & Windows

Method 1. Using MacMister CSV Splitter Program

With the help of MacMister CSV Splitter for Mac Program, users can successfully split large CSV files into smaller multiple files. The software is easy, reliable, and cost-effective. It has a fast processing as well that will immediately divide large CSV files into multiple smaller files. The GUI is also straight and non-typical. Anyone can operate it effortlessly and perform the process without wasting time and effort. Its features are also wonderful that will help in delivering the required results within seconds.

Working Steps to Split Large CSV into Multiple Smaller Files on Mac

Step 1. Download, install, and open Split CSV File Tool on a macOS.

Step 2. Click Add File(s)/Add Folder buttons to https://solutionsformac.quora.com/PST-Converter-for-Mac-Application-for-Mac-Users as per your need on the tool and click Next.

Step 3. The application will analyze the selected files and start listing them on the application with checkboxes.

Step 4. Click the Browse button and add the necessary destination path where you want to save split CSV files.

Step 5. Now, you will see options i.e. does the source CSV file contain headers & include headers in each CSV split file. With that, you will get other properties like no. of rows per split file and CSV file encoding. You can set each and every option as per your requirements and click the Split button.

Step 6. The process will start and the tool will show the live splitting process to users from which users can keep track of the split progress.

The entire procedure will end within seconds and the data will not get affected during the process.

Method 2. Break Large CSV Files using Terminal

In order to divide CSV files on a Mac, you can use the Terminal and the in-built Split command. Follow the steps:

1. Open the Terminal app on the Mac system. For this, go to Applications. Choose Utilities folder or use Spotlight to search the same.
2. Go to the directory where the CSV file is stored using the cd command. For e.g. if the CSV file is on the Desktop, then type cd ~/Desktop and click Enter.
3. Run the split command using the following syntax: split -1 <number of lines> <input file> <output file prefix>. Replace <number of lines> with the maximum number of lines you require for split, <input file> with the name of the CSV file, and <output file prefix> with the prefix you need to use for file split. For e.g. if you need to divide large CSV files into files with a maximum of 1000 lines each and name the split files “output_”, the command would look like this: split -1 1000 input.csv output_.
4. Click Enter to run the command. The split files will be created in the same directory as your input file with the prefix you specified followed by the sequential number and the extension “.aa”. For e.g., if you used “output_” as the prefix, the split files would be named “output_aa”, “output_ab”, “output_ac”, and so on.

Method 3. Break CSV File using Power Pivot

Step 1. Open a new blank Excel Spreadsheet.

Step 2. Go to Data. Select New Query. Choose From the File and Click From CSV.

Step 3. A window will pop up through which you need to go to the location where a large CSV file is stored. Click on it and proceed by tapping the Import button.

Step 4. A file preview will be shown to you. Click Load and then select Load too.

Step 5. Now, make sure that the option of Add this data to the Data Model is selected.

Step 6. Click the Load button.

Note. This step will be letting us know that we not loading the data to Excel Spreadsheet.

Step 7. The large files are loaded and now click Manage Data.

Step 8. The Power Pivot table will be displayed on the screen. You can add as well as change the columns as per the requirements.

The method will not divide CSV file but can help you to manipulate CSV file into Excel somehow which is really helpful.

Method 4. Divide Large CSV Files using Batch File

You can also use batch file methodology to divide large CSV files into multiple smaller files. Let’s see how we can create a batch file and do the customization accordingly.

Step 1. Open Notepad. Write batch code as shown below to break the CSV file (copy the code displayed 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

Step 2. Now, let’s get into the code and see what changes are needed to make to divide the CSV file.

However, you need to modify the below values for the code to work for you accordingly.

SET BFN = It will highlight the CSV file that needs to be split. Replace it with the necessary CSV file path.

SET LPF = It represents the number of rows you want to open in a new file.

SET SFN = This is the new name you provide to files that are split.

Step 3. Navigate to File, click Save as, specify file name i.e. batch.bat, and click Save.

Step 4. The file is now saved on the desktop. Double-click on it to run the same in Command Prompt.

Note. In order to edit the code, Right-click on the batch file icon and tap the Edit option.

Step 5. After the batch program finishes, a large CSV file will be divided into multiple smaller files.

However, once you have changed the values, save the code as .bat with a new name and then follow the above steps to divide large CSV files.

Epilogue

In the blog, we have discussed different ways to divide large CSV into multiple smaller files. Users can choose the suitable method and perform the process as per their requirements. However, it is recommended to go with a solution that is trustworthy, reliable, and safe. In case you face any issues, contact MacMister Support.