How to split a csv file according to unique items in a column?

How to split a csv file according to unique items in a column?

We often need to divide a file into small files in our daily work. For example, we have a csv file that stores order data, and one column contains the name of the supplier. We need to cut this file into different files according to the name of the supplier. This is not a simple matter, and sometimes you need to program it. The best way to do it is of course, by using QuickTran powered by QuickTable to save both time and to be efficient as well!

Method A : Python

If you want to split a large CSV file based on unique items in a certain column, you can use Python and the pandas library. Here's an example:

import pandas as pd

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

# Get the unique values of the column you want to split the data by
column_values = df[column_name].unique()

# Loop over the unique values of the column
for value in column_values:
    # Create a dataframe for each unique value
    value_df = df[df[column_name] == value]

    # Write the dataframe to a new csv file
    value_df.to_csv(f"{column_name}_{value}.csv", index=False)

Method B：Excel

You can use Microsoft Excel to split a large CSV or Excel file into smaller files based on unique values in a certain column. Here's how:

• Open the large file in Microsoft Excel.
• Sort the data based on the column that you want to split the data into smaller files.
• Create a new workbook in Microsoft Excel for each unique value in the column.
• For each unique value, filter the data to show only the rows with that value in the column.
• Copy the filtered data for the current unique value and paste it into the corresponding new workbook.
• Save the new workbook as a separate file, using the unique value as part of the file name.
• Repeat steps 4-6 for each additional unique value in the column.

Note: The number of rows in each smaller file will depend on the number of rows with that unique value in the column.

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 select a column you want to split. In seconds, you'll split the big csv into small files.
• You can then download the files and save them to your computer.