How to remove and merge CSV duplicates online

To remove duplicate CSV rows safely, match the column that identifies one record, review the proposed groups, then remove redundant rows or merge the values you need to preserve. This works for one CSV file or several files imported into the same Datablist collection.

Use email, phone, company website, SKU, or another stable identifier for the first pass. When the values contain spelling or formatting differences, use Smart, fuzzy, or phonetic matching and keep uncertain groups in review.

Merge duplicates from CSV files

Datablist is great to perform data operations that are not possible with spreadsheets. Use it when you are looking for a great online CSV editor.

In this guide, we will work with 2 CSV files that contains thousands of entries. We will load them into a single collection and de-duplicate entries based on 1 of the 4 columns. Deduplication also works on a single CSV file.

To download the tutorial CSV files: CSV File 1 and CSV File 2

The CSV file contains 4 columns: First Name, Last Name, Email, Job Title. We want to merge entries that share the same email address.

If your email field contains several addresses in one cell, split multi-value CSV cells into rows before running duplicate detection. If the source export is too large to import or review safely, split the large CSV file before deduplication.

The process to merge duplicates can be summed up with:

1. Load your CSV files into a Datablist collection
2. Select the properties to perform the duplicates check
3. Merge automatically non conflicting duplicates
4. Merge manually remaining duplicates

Step 1: Load your CSV files into a Datablist collection

Create a new collection

The first step in our process is to load the CSV file into Datablist. Open Datablist (No signup required) to get started.

To create a new collection, click the "New collection" button with the +. Once the collection is created, give it a name and an icon.

Then click on the Import CSV button.

Create properties for your CSV file

With the CSV file now loaded, you can create properties for your collection using the CSV column names. Datablist lists every column found in the CSV so you can create a property for each of them.

CSV data is not typed. When reading a CSV file, everything is a text. To provide better filtering and sorting features, Datablist tries to detect data types by looking at the first 100 lines. For example, when it finds only numbers, it set the property data type to number. Same with date, email, checkboxes (true or false values), etc.

Review and import

In the review step, the CSV rows will be listed directly from the CSV file. Be sure the data is well formatted and is consistent. Then click the "Import items" button and you are done! 💪

Do it again for your other CSV files

Now you have a collection with properties configured, perform the "Import CSV/Excel" process to import your other CSV files or Excel files into the same collection.

Step 2: Find duplicates

After loading the CSV files, open Clean > Merge duplicates. Choose Remove duplicates instead when the extra rows contain no data you need.

Two modes are available:

• All Properties compares the complete rows.
• Selected Properties compares only the columns that identify the record.

Here, the email property is enough to identify a contact, so you can select the Selected Properties mode with the email property.

If aliases or wrappers make the values differ, use the email-specific workflow to dedupe those variations.

The check is read-only. The result workspace lists the groups, explains why they matched, and separates Ready groups from matches that need review.

Step 3: Merge and combine duplicates automatically

Choose Merge and preserve data when secondary CSV rows contain useful values. Datablist selects one destination record, fills empty properties, and applies your conflict rules before deleting the secondary rows.

The preview separates groups that are Ready from groups with unresolved conflicts. Read the Duplicate Finder documentation for the complete review, survivorship, and record-selection settings.

Expand Resolve conflicting values to see which properties still need a rule.

Merging non-conflicting rows

The "Merge non-conflicting duplicates" algorithm run a "smart merge". It works by merging records with similar or complementary values.

For example, the following duplicates:

email            |     First Name   |    Last Name
james@gmail.com  |     James
james@gmail.com  |                  |     Bond

Will be merged into:

email            |     First Name   |    Last Name
james@gmail.com  |     James        |     Bond

Combining duplicate values

Combining (or consolidating) duplicate values is perfect when your duplicates have conflicting values but you still want to merge them without losing data.

For example, combining the Phone property with a semi-colon:

email            |       Phone       |     First Name   |    Last Name    
james@gmail.com  |  +33 1 34 65 23   |      James       |                 
james@gmail.com  |  06 13 42 78 23   |                  |     Bond        

Will be merged into:

email            |   Phone                         |     First Name   |    Last Name
james@gmail.com  |  +33 1 34 65 23;06 13 42 78 23  |     James        |     Bond    

Any property containing text can be combined. The available delimiters are line break, semi-colon, comma, and space. One or more properties can be combined during the merging.

Merging duplicate items and combining values is ideal for leads and CRM cleaning. Merge all your duplicate leads and combine the Phone, Email, Notes properties to have a clean list. And after exporting your clean lead CSV, just reimport it into your CRM.

Keep the selected record's value

This rule keeps the value from the selected destination record and discards the other conflicting values.

Most Complete is the default Record to keep rule. Paid plans can choose other rules or select field values independently with survivorship rules.

Use the drop conflicting values option for:

• Technical properties such as Account Id that require a single value.
• Properties that are "Relation" and can't have multiple values. For example Lead owner, Account.
• Non-text properties that can't be combined. For example datetime such as Last Activity, Contacted on, and checkboxes.

Step 4: Manual Merging Assistant

Open Review group when one duplicate group needs a manual decision. The Merging Assistant lets you choose the destination and the value to keep for each property.

The manually selected destination keeps its Datablist ID. The other rows are secondary records.

Review each property, select the values to keep, and confirm the merge. You can also exclude a row from the duplicate group or skip the complete group.

Export to CSV if needed

Congrats, you have successfully deduped your CSV files! If you need to use the result in another tool, click on the "Export" button to export the collection as another CSV file.

FAQ

What are the other data manipulations available with Datablist?

CSV files are everywhere to define structured data for software applications or data sets. Despite being ubiquitous, CSV manipulation is hard and often requires technical knowledge.

For simple manipulations, spreadsheets are enough. However, they are limited when it comes to:

• Combine CSV files
• Dedupe rows in a CSV
• Data cleaning

If you have several CSV files that you want to join together using a unique column, go to the join CSV files guide.

Can Datablist handle big CSV files?

The Free plan supports up to 1 million items per collection in browser storage. Paid plans support imports up to 2 million items per collection. Large local operations still depend on the browser and device. See how to edit big CSV files.

Is the deduplication algorithm better than Microsoft Excel and Google Sheets "remove duplicates" features?

Spreadsheet tools (Microsoft Excel, Google Sheets) have a deduplication tool. It works by removing similar rows. For business use cases, simply removing rows is not ideal.

Datablist shows the duplicate groups before changing the file. You can remove redundant rows, merge complementary values, resolve conflicts, or leave uncertain groups untouched.

If you have any questions, please contact us.