How to divide a csv file according to the number of rows？

How to divide a csv file according to the number of rows？

We often need to divide a file into small files in our daily work. For example, I have a file with 1 million lines, and I want to divide it into a file with every 100,000 lines. This is not a simple matter, and sometimes you need to program it.

There are several ways to split a large CSV or Excel file into smaller files:

• Using Excel: Open the large file in Excel, sort the data on the column that you want to use to split the data, then select and copy the data for each smaller file and paste it into a new workbook. Save each new workbook as a separate file.
• Using a scripting language such as Python: Use the pandas library to read in the large file, then use pandas methods to split the data into smaller dataframes, and finally write each dataframe to a separate CSV file.
• Using a data management tool such as SQL: Import the large file into a database, then use SQL queries to split the data into smaller tables and export each table as a separate CSV file.
• Using a command-line tool such as split: If the large file is a plain text file (CSV), you can split it into smaller files using the "split" command in a terminal. For example, "split -l 20000 largefile.csv splitfile_" will split the largefile.csv into multiple files, each containing 20000 lines, with names like splitfile_aa, splitfile_ab, etc.

You can use Microsoft Excel to split a large CSV or Excel file into smaller files. Here's how:

• Open the large file in Microsoft Excel.
• Sort the data based on the column that you want to use to split the data into smaller files.
• Select and copy the data for the first smaller file.
• Create a new workbook in Microsoft Excel.
• Paste the copied data into the new workbook.
• Save the new workbook as a separate file.
• Repeat steps 3-6 for each additional smaller file.

Note: The number of rows in each smaller file will depend on the number of rows you choose to copy and paste into each new workbook.

Here's an example of how to split a large CSV file into smaller files based on the number of rows using Python and pandas:

import pandas as pd

# Read the large csv file into a pandas dataframe
df = pd.read_csv("largefile.csv")

# Set the number of rows per file
rows_per_file = 5000

# Get the total number of rows
total_rows = df.shape[0]

# Calculate the number of files needed
num_files = total_rows // rows_per_file + (total_rows % rows_per_file > 0)

# Split the data into smaller dataframes
for i in range(num_files):
    start = i * rows_per_file
    end = (i + 1) * rows_per_file
    filename = f"split_file_{i}.csv"
    df.iloc[start:end].to_csv(filename, index=False)

The best way ：Use QuickTran function

• Visit our website and find the online tool.
• Open your csv file by clicking the "Choose File" button and selecting the file you want to change.
• Once the file has been uploaded, you can input "the number of rows" you want to split into. In seconds, you'll split the big csv into small files.
• You can then download the files and save them to your computer.