<p>Email lists are the start of any email campaign and newsletter management. However, a list can get messy from multiple merges or with user spammy behavior.</p>
<p>The benefits for cleaning your email list are:</p>
<ul>
<li><strong>Improve deliverability</strong> - Every email provider uses your sender score to determine where to route your emails. To avoid being labeled as SPAM and successfully reach your user, the first step is to avoid sending emails to non-existing addresses. A bad sender score is hard to revert, so better spend some time up-front and clean your list from unreachable email addresses.</li>
<li><strong>Save money</strong> - Usually, you pay for each email you send. First, pruning your email list to remove duplicates and invalid addresses will save you money. Then, you need to remove all disposable email addresses that will never be read to keep only real emails.</li>
<li><strong>Detect and fix typos</strong> - After the cleaning process, wrong email addresses will be flagged. Manually, you can find simple typos in names or domains and fix them.</li>
</ul>
<p>Email list cleaning is an important part of any digital business and must be done regularly. <strong>Datablist is a perfect data tool to perform this cleaning process.</strong> Using this step by step guide, you will learn how to:</p>
<ul>
<li><a href="#step-1-remove-duplicate-emails">Remove duplicate emails</a></li>
<li><a href="#check-email-syntax">Check email addresses syntax</a></li>
<li><a href="#check-disposable-providers">Check if emails are from disposable providers</a></li>
<li><a href="#check-mx-records">Ensure email domains exist</a></li>
</ul>
<p>👉  <a href="https://app.datablist.com/auth/signup" target="_blank" rel="nofollow">Every step will be done using Datablist. Create your account for free</a> 👈.</p>
<blockquote>
<p><strong>How does it compare to paid email cleaning services?</strong> <br/>
If you search for email verification services on Google, you'll find hundreds (thousands?) of them. Almost all of them charge a fee per email address. Datablist includes an email verification service and it is free.
It is great and enough for simple email verifications. However, if you need deeper analysis or if you have to perform verifications on hundreds of thousands of emails, please use a paid email cleaning service.</p>
</blockquote>
<h1 id="step-1-remove-duplicate-emails"><a href="#step-1-remove-duplicate-emails" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 1: Remove duplicate emails</h1>
<h2 id="create-a-collection"><a href="#create-a-collection" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Create a collection</h2>
<p>The first step in the email cleaning process is to create a collection on Datablist in which you will pour your email addresses.</p>
<p>In <a href="https://app.datablist.com" target="_blank" rel="nofollow">Datablist</a>, click the <strong>+</strong> to create a new collection. Select "Create collection from scratch" to have an empty collection and give it a name.</p>
<div class="content-video">
    <video
        autoplay muted loop playsinline controls width="100%" height="auto" class="d-block"
        poster="/howto_images/clean-emails/new-collection.png">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/clean-contact-list/create-collection-datablist.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create a new collection</div>
</div>
<h2 id="ensure-email-uniqueness-to-remove-duplicates"><a href="#ensure-email-uniqueness-to-remove-duplicates" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Ensure email uniqueness to remove duplicates</h2>
<p>It's common to have an email list built over time. The result is several email listings merged into one. <strong>Resulting in duplicates!</strong> If your list stores contact information like <em>First Name</em>, <em>Last Name</em>, etc. you might have contact information spread over several duplicates rows.</p>
<p>To prevent duplicates, create a new <strong>Email</strong> property and select the "do not allow duplicate values" option. With this property option, Datablist will automatically deduplicate and merge your contacts on data import.</p>
<div class="content-video">
    <video
        autoplay muted loop playsinline controls width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/clean-contact-list/email-property-datablist.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Add an Email property</div>
</div>
<h2 id="import-your-email-addresses"><a href="#import-your-email-addresses" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Import your email addresses</h2>
<p>Now you have a collection, it's time to import your email lists: whether you have only one email list or several that you want to merge!</p>
<p>Datablist offers two options to import your data:</p>
<ul>
<li>With CSV files</li>
<li>Using copy/pasting from a spreadsheet</li>
</ul>
<h3 id="option-1-import-from-csv-files"><a href="#option-1-import-from-csv-files" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Option 1: Import from CSV files</h3>
<p>The <a href="/faq/csv/csv-meaning">CSV format</a> is a simple standard to transfer tabular data between software applications. Every newsletter tool and digital marketing solution offer exports of your contacts in CSV files.</p>
<blockquote>
<p>See also our <a href="/how-to/join-csv-files-unique-identifier-or-column">How to join CSV files by a unique identifier</a> and <a href="/how-to/remove-csv-duplicates">How to remove CSV duplicates</a> guides.</p>
</blockquote>
<p>To import your CSV file, click the "Import CSV" button and select your file. Datablist will read the columns and show you a mapping page.</p>
<p><strong>Map the CSV email column with the Email collection property.</strong> For the other CSV columns, map them with existing collection properties or click the <strong>+</strong> button to automatically create a new collection property that will match the CSV column.</p>
<p>Follow the process and select a merging option:</p>
<p><img src="/howto_images/csv/csv-merge-options.png" alt="CSV Join - Merging Options"></p>
<p><strong>The merging option is important when your listing contains contact information in addition to the email address.</strong></p>
<ul>
<li>With <code>Soft Merge</code>, if a contact exists with the same email, it <strong>will not</strong> update properties with previous data (contacts already in the collection or the first contact found in the CSV). This is the default setting.</li>
<li>With <code>Hard Merge</code>, if data exists with the same email, it will <strong>update it</strong>.</li>
</ul>
<h3 id="option-2-import-with-copypasting"><a href="#option-2-import-with-copypasting" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Option 2: Import with Copy/Pasting</h3>
<p>Datablist is compatible with copy/pasting from any spreadsheet. Just select the cells from your spreadsheet, go to your Datablist collection and use the <code>Edit -> Paste</code> from your browser or directly the <code>Ctrl + v</code> keyboard shortcut.</p>
<p>On pasting, Datablist will show you the columns and rows it has detected. To import a column, map it to an existing property or create a new property.</p>
<blockquote>
<p><strong>Warning</strong>: Only mapped columns will be imported!</p>
</blockquote>
<div class="content-video">
    <video
        autoplay muted loop playsinline controls width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/clean-contact-list/import-contacts-from-copy-pasting.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Copy Pasting From Google Sheets</div>
</div>
<h2 id="merge-other-contact-lists-if-needed"><a href="#merge-other-contact-lists-if-needed" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Merge other contact lists if needed</h2>
<p>If you are building an email collection from several sources, just import all your lists into the same collection. Any duplicates will be merged and you'll end up with a single list.</p>
<h1 id="step-2-free-email-list-cleaning"><a href="#step-2-free-email-list-cleaning" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 2: Free email list cleaning</h1>
<p>Now you have a collection on Datablist with all the email addresses, it's time to clean it!</p>
<h2 id="what-does-the-service-check"><a href="#what-does-the-service-check" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>What does the service check?</h2>
<p>Datablist has a built-in free email verification service. This free service does 3 verifications:</p>
<ul>
<li>Email syntax analysis</li>
<li>Disposable providers check</li>
<li>Domain MX records check</li>
</ul>
<h3 id="email-syntax-analysis"><a href="#email-syntax-analysis" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Email syntax analysis</h3>
<p>The first check is to ensure the email conforms to the <a href="https://datatracker.ietf.org/doc/html/rfc2822#section-3.4.1" target="_blank" rel="nofollow">IEFT standard</a> and does a complete syntactical analysis.</p>
<p>This analysis will flagged addresses without the at sign (@), with invalid domains, etc.</p>
<h3 id="check-disposable-providers"><a href="#check-disposable-providers" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Check disposable providers</h3>
<p>The second check is to detect temporary emails. The service looks for domains belonging to Disposable Email Address (DEA) providers such as Mailinator, Temp-Mail, YopMail, etc.</p>
<p>The current database lists about 3000 disposable provider domains and is updated regularly using <a href="https://github.com/disposable-email-domains/disposable-email-domains/blob/master/disposable_email_blocklist.conf" target="_blank" rel="nofollow">this disposable domains list</a>.</p>
<h3 id="check-domain-mx-records"><a href="#check-domain-mx-records" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Check domain MX records</h3>
<p>A valid email address must have a corresponding domain name with configured MX records. Those MX records specify the mail server accepting the email messages for the domain. Missing MX records indicate an invalid email address.</p>
<p>For every email address domain, the service checks the DNS records and looks for the MX ones. If the domain doesn't exist, the email will be flagged as invalid. If the domain exists and doesn't have a valid MX record, it will also be flagged as invalid.</p>
<h2 id="perform-cleaning-on-your-collection"><a href="#perform-cleaning-on-your-collection" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Perform cleaning on your collection</h2>
<p>Performing an email list cleaning on Datablist is simple. Just select the email addresses in your collection and run the "Email Address Validation" action.</p>
<p>Once you have selected "Email Address Validation", a drawer opens on the right with 3 sections: <code>Settings</code>, <code>Input Properties</code>, and <code>Output</code>.</p>
<p><strong>Settings</strong></p>
<p>Select "Check for MX-records in email domain" in the settings to analyze the MX records.</p>
<p><strong>Input Properties</strong></p>
<p>Select the property from your collection that contains the email address. In this example, the collection has an "Email" property that will be matched.</p>
<p><strong>Output</strong></p>
<p>The "Email Address Validation" service returns 2 values:</p>
<ul>
<li>Valid Email - A boolean (<code>true</code> or <code>false</code>) to indicate whether the email address is valid.</li>
<li>Error status - A text explaining why the email address is invalid when "Valid" Email" is <code>false</code>.</li>
</ul>
<blockquote>
<p><strong>It's important to map the output properties to the collection to store the results.</strong></p>
</blockquote>
<p>Click the <strong>+</strong> on the right of each output property to add the result properties to the collection.</p>
<div class="content-video">
    <video
        autoplay muted loop playsinline controls width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/clean-contact-list/clean-contact-list-datablist.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Verify email addresses for free</div>
</div>
<p>Once the service is done, analyze all invalid emails to detect easy fix typos.</p>
<h1 id="step-3-remove-unsubscribed-emails"><a href="#step-3-remove-unsubscribed-emails" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 3: Remove unsubscribed emails</h1>
<p>This last step is optional. You might have a dedicated list containing all unsubscribed emails that you want to remove from your main list. If your list with unsubscribed emails has a specific column like:</p>
<pre><code>email           |     Unsubscribed
xxx@xxx.com     |     yes
xxx@xxx.com     |     yes
xxx@xxx.com     |     yes
</code></pre>
<p>Simply import it to your Datablist collection using a CSV file or copy/pasting. Don't forget to create and map the <code>Unsubscribed</code> column during the import.</p>
<p>Because you set the <code>Email</code> property to be unique, your data will be merged into your collection resulting in a single list with the <code>Unsubscribed</code> property.</p>
<hr>
<p>If you have any feedback on this guide or if you have questions, please <a href="https://docs.google.com/forms/d/e/1FAIpQLSeAZfqCjOg-ok7yYAwz4hmKxhPOeLMIwZrLozsro5HTQhrsxw/viewform?usp=sf_link" target="_blank" rel="nofollow">contact us</a>.</p>



Email lists are the start of any email campaign and newsletter management. However, a list can get messy from multiple merges or with user spammy behavior.

The benefits for cleaning your email list are:

- **Improve deliverability** - Every email provider uses your sender score to determine where to route your emails. To avoid being labeled as SPAM and successfully reach your user, the first step is to avoid sending emails to non-existing addresses. A bad sender score is hard to revert, so better spend some time up-front and clean your list from unreachable email addresses.
- **Save money** - Usually, you pay for each email you send. First, pruning your email list to remove duplicates and invalid addresses will save you money. Then, you need to remove all disposable email addresses that will never be read to keep only real emails.
- **Detect and fix typos** - After the cleaning process, wrong email addresses will be flagged. Manually, you can find simple typos in names or domains and fix them.

Email list cleaning is an important part of any digital business and must be done regularly. **Datablist is a perfect data tool to perform this cleaning process.** Using this step by step guide, you will learn how to:
- [Remove duplicate emails](#step-1-remove-duplicate-emails)
- [Check email addresses syntax](#check-email-syntax)
- [Check if emails are from disposable providers](#check-disposable-providers)
- [Ensure email domains exist](#check-mx-records)

👉  [Every step will be done using Datablist. Create your account for free](https://app.datablist.com/auth/signup) 👈.

> **How does it compare to paid email cleaning services?** <br/>
> If you search for email verification services on Google, you'll find hundreds (thousands?) of them. Almost all of them charge a fee per email address. Datablist includes an email verification service and it is free.
> It is great and enough for simple email verifications. However, if you need deeper analysis or if you have to perform verifications on hundreds of thousands of emails, please use a paid email cleaning service.


# Step 1: Remove duplicate emails

## Create a collection

The first step in the email cleaning process is to create a collection on Datablist in which you will pour your email addresses.

In [Datablist](https://app.datablist.com), click the **+** to create a new collection. Select "Create collection from scratch" to have an empty collection and give it a name.

<div class="content-video">
    <video
        autoplay muted loop playsinline controls width="100%" height="auto" class="d-block"
        poster="/howto_images/clean-emails/new-collection.png">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/clean-contact-list/create-collection-datablist.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create a new collection</div>
</div>


## Ensure email uniqueness to remove duplicates

It's common to have an email list built over time. The result is several email listings merged into one. **Resulting in duplicates!** If your list stores contact information like *First Name*, *Last Name*, etc. you might have contact information spread over several duplicates rows.

To prevent duplicates, create a new **Email** property and select the "do not allow duplicate values" option. With this property option, Datablist will automatically deduplicate and merge your contacts on data import.

<div class="content-video">
    <video
        autoplay muted loop playsinline controls width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/clean-contact-list/email-property-datablist.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Add an Email property</div>
</div>


## Import your email addresses

Now you have a collection, it's time to import your email lists: whether you have only one email list or several that you want to merge!

Datablist offers two options to import your data:
- With CSV files
- Using copy/pasting from a spreadsheet

### Option 1: Import from CSV files

The [CSV format](/faq/csv/csv-meaning) is a simple standard to transfer tabular data between software applications. Every newsletter tool and digital marketing solution offer exports of your contacts in CSV files.

> See also our [How to join CSV files by a unique identifier](/how-to/join-csv-files-unique-identifier-or-column) and [How to remove CSV duplicates](/how-to/remove-csv-duplicates) guides.

To import your CSV file, click the "Import CSV" button and select your file. Datablist will read the columns and show you a mapping page.

**Map the CSV email column with the Email collection property.** For the other CSV columns, map them with existing collection properties or click the **+** button to automatically create a new collection property that will match the CSV column.

Follow the process and select a merging option:

![CSV Join - Merging Options](/howto_images/csv/csv-merge-options.png)

**The merging option is important when your listing contains contact information in addition to the email address.**

- With `Soft Merge`, if a contact exists with the same email, it **will not** update properties with previous data (contacts already in the collection or the first contact found in the CSV). This is the default setting.
- With `Hard Merge`, if data exists with the same email, it will **update it**.


### Option 2: Import with Copy/Pasting

Datablist is compatible with copy/pasting from any spreadsheet. Just select the cells from your spreadsheet, go to your Datablist collection and use the `Edit -> Paste` from your browser or directly the `Ctrl + v` keyboard shortcut.

On pasting, Datablist will show you the columns and rows it has detected. To import a column, map it to an existing property or create a new property.

> **Warning**: Only mapped columns will be imported!


<div class="content-video">
    <video
        autoplay muted loop playsinline controls width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/clean-contact-list/import-contacts-from-copy-pasting.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Copy Pasting From Google Sheets</div>
</div>


## Merge other contact lists if needed

If you are building an email collection from several sources, just import all your lists into the same collection. Any duplicates will be merged and you'll end up with a single list.


# Step 2: Free email list cleaning

Now you have a collection on Datablist with all the email addresses, it's time to clean it!

## What does the service check?

Datablist has a built-in free email verification service. This free service does 3 verifications:

- Email syntax analysis
- Disposable providers check
- Domain MX records check

### Email syntax analysis
The first check is to ensure the email conforms to the [IEFT standard](https://datatracker.ietf.org/doc/html/rfc2822#section-3.4.1) and does a complete syntactical analysis.

This analysis will flagged addresses without the at sign (@), with invalid domains, etc.

### Check disposable providers

The second check is to detect temporary emails. The service looks for domains belonging to Disposable Email Address (DEA) providers such as Mailinator, Temp-Mail, YopMail, etc.

The current database lists about 3000 disposable provider domains and is updated regularly using [this disposable domains list](https://github.com/disposable-email-domains/disposable-email-domains/blob/master/disposable_email_blocklist.conf).

### Check domain MX records

A valid email address must have a corresponding domain name with configured MX records. Those MX records specify the mail server accepting the email messages for the domain. Missing MX records indicate an invalid email address.

For every email address domain, the service checks the DNS records and looks for the MX ones. If the domain doesn't exist, the email will be flagged as invalid. If the domain exists and doesn't have a valid MX record, it will also be flagged as invalid.

## Perform cleaning on your collection

Performing an email list cleaning on Datablist is simple. Just select the email addresses in your collection and run the "Email Address Validation" action.

Once you have selected "Email Address Validation", a drawer opens on the right with 3 sections: `Settings`, `Input Properties`, and `Output`.

**Settings**

Select "Check for MX-records in email domain" in the settings to analyze the MX records.

**Input Properties**

Select the property from your collection that contains the email address. In this example, the collection has an "Email" property that will be matched.

**Output**

The "Email Address Validation" service returns 2 values:

- Valid Email - A boolean (`true` or `false`) to indicate whether the email address is valid.
- Error status - A text explaining why the email address is invalid when "Valid" Email" is `false`.

> **It's important to map the output properties to the collection to store the results.**

Click the **+** on the right of each output property to add the result properties to the collection.

<div class="content-video">
    <video
        autoplay muted loop playsinline controls width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/clean-contact-list/clean-contact-list-datablist.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Verify email addresses for free</div>
</div>


Once the service is done, analyze all invalid emails to detect easy fix typos.

# Step 3: Remove unsubscribed emails

This last step is optional. You might have a dedicated list containing all unsubscribed emails that you want to remove from your main list. If your list with unsubscribed emails has a specific column like:

```
email           |     Unsubscribed
xxx@xxx.com     |     yes
xxx@xxx.com     |     yes
xxx@xxx.com     |     yes
```

Simply import it to your Datablist collection using a CSV file or copy/pasting. Don't forget to create and map the `Unsubscribed` column during the import.

Because you set the `Email` property to be unique, your data will be merged into your collection resulting in a single list with the `Unsubscribed` property.


---

If you have any feedback on this guide or if you have questions, please [contact us](https://docs.google.com/forms/d/e/1FAIpQLSeAZfqCjOg-ok7yYAwz4hmKxhPOeLMIwZrLozsro5HTQhrsxw/viewform?usp=sf_link).


How to clean an email list for free

<p><a href="/faq/csv/csv-uses">CSV files</a> are everywhere to define structured data for software applications or data sets. Despite being ubiquitous, CSV manipulation is hard and often requires technical knowledge.</p>
<p>For simple manipulations, spreadsheets are enough. However, they are limited when it comes to:</p>
<ul>
<li><a href="/how-to/join-csv-files-unique-identifier-or-column">Consolidate CSV files using a unique identifier</a></li>
<li>Find and remove duplicate rows from a CSV</li>
</ul>
<p>In this guide, we will focus on the last manipulation: detecting and removing duplicates from CSV files.</p>
<blockquote>
<p>If you have several CSV files that you want to join together using a unique column, go to the <a href="/how-to/join-csv-files-unique-identifier-or-column">join csv files</a> guide.</p>
</blockquote>
<h1 id="remove-duplicates-from-csv-files"><a href="#remove-duplicates-from-csv-files" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Remove duplicates from CSV files</h1>
<p>You have exported your data from an application in a CSV file and want to get rid of the duplicates? You have concatenated several CSV files into a big one and now need to clean it? Or maybe you have several sources of data no longer in sync and want to merge all those versions into a single listing. Microsoft Excel or Google Sheets will not help you with your problem because they don't allow you to set up a unique constraint on a column.</p>
<p>On a small CSV file, this task can be done manually. On bigger files, it would take hours to process the entries one by one. And you risk human errors.</p>
<p><strong>What you need is a tool to automatically detect CSV entries with similar values in one or several columns.</strong> Then, once the duplicates are found, you can edit them to consolidate their data or remove the duplicates.</p>
<p>Datablist is great to perform simple data operations that are not possible with spreadsheets.</p>
<p>In this guide, we will work with a 2 CSV files that contains thousands of entries. We will load them into a single collection and detect duplicates entries based on 1 of the 4 columns.</p>
<blockquote>
<p>To download the tutorial CSV files: <a href="https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/Contacts_1.csv" target="_blank" rel="nofollow">CSV File 1</a> and <a href="https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/Contacts_2.csv" target="_blank" rel="nofollow">CSV File 2</a></p>
</blockquote>
<p>The CSV file contains 4 columns: <code>First Name</code>, <code>Last Name</code>, <code>Email</code>, <code>Job Title</code>. We want to merge entries that share the same email address.</p>
<p>The process can be summed up with:</p>
<ul>
<li>Load your CSV files into a Datablist collection</li>
<li>Create properties matching CSV columns</li>
<li>Select the properties to perform the duplicates check</li>
<li>Edit or delete the duplicates</li>
</ul>
<h1 id="step-1-load-your-csv-files-into-a-datablist-collection"><a href="#step-1-load-your-csv-files-into-a-datablist-collection" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 1: Load your CSV files into a Datablist collection</h1>
<aside class="text-center mb-5">
    <div class="mb-2">
        <a href="https://app.datablist.com" className="btn btn-primary btn-large" target="_blank">
            Open Datablist
        </a>
    </div>
    <span class="orange-text">No signup required</span>
</aside>
<h2 id="create-a-new-collection"><a href="#create-a-new-collection" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Create a new collection</h2>
<p>The first step in our process is to load the CSV file into Datablist. <a href="https://app.datablist.com" target="_blank" rel="nofollow">Open Datablist (No signup required)</a> to get started.</p>
<p>To create a new collection, click the "New collection" button with the <strong>+</strong>. Once the collection is created, you can give it a name and an icon.</p>
<p>Then click on the <strong>Import CSV</strong> button.</p>
<div class="content-video">
    <video autoplay muted loop playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/videos/remove-duplicates-csv-create-collection.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create a new collection</div>
</div>
<h2 id="create-properties-for-your-csv-file"><a href="#create-properties-for-your-csv-file" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Create properties for your CSV file</h2>
<p>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.</p>
<div class="content-video">
    <video autoplay muted loop playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/videos/configure-properties.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create collection properties</div>
</div>
<h2 id="review-and-import"><a href="#review-and-import" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Review and import</h2>
<p>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! 💪</p>
<div class="content-video">
    <video autoplay muted loop playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/videos/review-csv-file.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Review, import and upload the second CSV file</div>
</div>
<h2 id="do-it-again-for-your-other-csv-files"><a href="#do-it-again-for-your-other-csv-files" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Do it again for your other CSV files</h2>
<p>Now you have a collection with properties configured, perform the "Import CSV" process to import your other CSV files into the same collection.</p>
<h1 id="step-2-remove-duplicates"><a href="#step-2-remove-duplicates" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 2: Remove duplicates</h1>
<p>After loading the CSV files, the second step is to search for duplicates. In the collection data listing, click the "Duplicates" button in the header (top right).</p>
<p>Two modes are available:</p>
<ul>
<li><strong>All Properties</strong> - In this mode, Datablist looks for items with similar values for all properties. Two items would be considered similar when all of their properties match.</li>
<li><strong>Selected Properties</strong> - For this second mode, you will select the properties to be used for similarity check. Two items would be considered similar when they have similar values on all the selected properties.</li>
</ul>
<p>Here, the <code>email</code> property is enough to identify a contact, so you can select the <strong>Selected Properties</strong> mode with the <strong>email</strong> property.</p>
<p>Once the analysis is done, Datablist lists all your duplicates based on the <code>email</code> property. For each items with one or more duplicates, you can:</p>
<ul>
<li><strong>Edit the item</strong> - Use values from incomplete items to merge the data into a single item.</li>
<li><strong>Delete the extra items</strong> - When duplicates don't bring value, just remove the duplicates</li>
</ul>
<div class="content-video">
    <video muted playsinline controls width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/videos/duplicates-analysis.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Find and remove duplicates</div>
</div>
<h1 id="export-to-csv-if-needed"><a href="#export-to-csv-if-needed" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Export to CSV if needed</h1>
<p>If you need to use the result in another tool, click on the "Export" button to export the collection as another CSV file.</p>
<p><img src="/howto_images/remove-duplicates/export-csv.png#918x330" alt="CSV Export"></p>
<hr>
<p>If you have any questions, please <a href="https://docs.google.com/forms/d/e/1FAIpQLSeAZfqCjOg-ok7yYAwz4hmKxhPOeLMIwZrLozsro5HTQhrsxw/viewform?usp=sf_link" target="_blank" rel="nofollow">contact us</a>.</p>



[CSV files](/faq/csv/csv-uses) 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:

- [Consolidate CSV files using a unique identifier](/how-to/join-csv-files-unique-identifier-or-column)
- Find and remove duplicate rows from a CSV

 In this guide, we will focus on the last manipulation: detecting and removing duplicates from CSV files.

 > If you have several CSV files that you want to join together using a unique column, go to the [join csv files](/how-to/join-csv-files-unique-identifier-or-column) guide.


# Remove duplicates from CSV files

You have exported your data from an application in a CSV file and want to get rid of the duplicates? You have concatenated several CSV files into a big one and now need to clean it? Or maybe you have several sources of data no longer in sync and want to merge all those versions into a single listing. Microsoft Excel or Google Sheets will not help you with your problem because they don't allow you to set up a unique constraint on a column.

On a small CSV file, this task can be done manually. On bigger files, it would take hours to process the entries one by one. And you risk human errors.

**What you need is a tool to automatically detect CSV entries with similar values in one or several columns.** Then, once the duplicates are found, you can edit them to consolidate their data or remove the duplicates.

Datablist is great to perform simple data operations that are not possible with spreadsheets.

In this guide, we will work with a 2 CSV files that contains thousands of entries. We will load them into a single collection and detect duplicates entries based on 1 of the 4 columns.

> To download the tutorial CSV files: [CSV File 1](https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/Contacts_1.csv) and [CSV File 2](https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/Contacts_2.csv)

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

The process can be summed up with:

- Load your CSV files into a Datablist collection
- Create properties matching CSV columns
- Select the properties to perform the duplicates check
- Edit or delete the duplicates

# Step 1: Load your CSV files into a Datablist collection

<aside class="text-center mb-5">
    <div class="mb-2">
        <a href="https://app.datablist.com" className="btn btn-primary btn-large" target="_blank">
            Open Datablist
        </a>
    </div>
    <span class="orange-text">No signup required</span>
</aside>

## Create a new collection

The first step in our process is to load the CSV file into Datablist. [Open Datablist (No signup required)](https://app.datablist.com) to get started.

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

Then click on the **Import CSV** button.

<div class="content-video">
    <video autoplay muted loop playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/videos/remove-duplicates-csv-create-collection.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create a new collection</div>
</div>


## 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.

<div class="content-video">
    <video autoplay muted loop playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/videos/configure-properties.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create collection properties</div>
</div>


## 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! 💪

<div class="content-video">
    <video autoplay muted loop playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/videos/review-csv-file.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Review, import and upload the second CSV file</div>
</div>


## Do it again for your other CSV files

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


# Step 2: Remove duplicates

After loading the CSV files, the second step is to search for duplicates. In the collection data listing, click the "Duplicates" button in the header (top right).

Two modes are available:

- **All Properties** - In this mode, Datablist looks for items with similar values for all properties. Two items would be considered similar when all of their properties match.
- **Selected Properties** - For this second mode, you will select the properties to be used for similarity check. Two items would be considered similar when they have similar values on all the selected properties.

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

Once the analysis is done, Datablist lists all your duplicates based on the `email` property. For each items with one or more duplicates, you can:

- **Edit the item** - Use values from incomplete items to merge the data into a single item.
- **Delete the extra items** - When duplicates don't bring value, just remove the duplicates

<div class="content-video">
    <video muted playsinline controls width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/remove-csv-duplicates/videos/duplicates-analysis.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Find and remove duplicates</div>
</div>


# Export to CSV if needed

If you need to use the result in another tool, click on the "Export" button to export the collection as another CSV file.

![CSV Export](/howto_images/remove-duplicates/export-csv.png#918x330)


---

If you have any questions, please [contact us](https://docs.google.com/forms/d/e/1FAIpQLSeAZfqCjOg-ok7yYAwz4hmKxhPOeLMIwZrLozsro5HTQhrsxw/viewform?usp=sf_link). 


How to remove CSV duplicates

<p>The CSV file format is a standard to exchange structured data between companies or different software applications. It is text-based and uses punctuation delimiters to separate columns.</p>
<p>Here is an example of CSV file data:</p>
<pre><code class="language-text">firstName, lastName, email
Joe, Vaughn, vaugh@hotmail.com
Bob, Cunnighan, bobc@gmail.com
</code></pre>
<p><a href="/faq/csv/csv-uses">CSV files</a> can be exported from most tools dealing with data, including CRM, Order Management System, Spreadsheets (Google Sheets or Microsoft Excel), and financial products. In the future, we might have <a href="/blog/common-data-model-vs-cloud-information-model">unified data models</a> to transfer structured items between applications. In the meantime, we must rely on CSV files.</p>
<p>When it comes to manipulating CSV files, the go-to solution is spreadsheet. Loading a CSV file into Google Sheets or Microsoft Excel is straightforward. However, those tools are limited when it comes to two simple operations:</p>
<ul>
<li>Joining CSV files by a unique column</li>
<li><a href="/how-to/remove-csv-duplicates">Deduplicate and merge CSV entries on a column unique constraint</a></li>
</ul>
<p>When <a href="/faq/csv/difference-join-merge">performing a join operation</a>, we are using a common column to combine data from several sources. <strong>Spreadsheets cannot define a unique constraint on a column</strong>, therefore provide limited support for joining CSV or deduplication.</p>
<p>This guide contains 2 parts:</p>
<ul>
<li><a href="#solution-1-join-csv-files-on-a-common-column-with-datablist">Join CSV files on a common column with Datablist</a></li>
<li><a href="#solution-2-join-csv-files-using-google-sheet-or-microsoft-excel">Join CSV files using Google Sheet (or Microsoft Excel)</a></li>
</ul>
<p>In this tutorial, we use 2 demo CSV files:</p>
<ul>
<li><a href="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/FirstNameLastNameEmail.csv" target="_blank" rel="nofollow">Master CSV: A CSV with <code>First Name, Last Name, Email</code></a></li>
<li><a href="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/EmailJobTitle.csv" target="_blank" rel="nofollow">A secondary CSV with <code>Email, Job Title</code></a></li>
</ul>
<h1 id="solution-1-join-csv-files-on-a-common-column-with-datablist"><a href="#solution-1-join-csv-files-on-a-common-column-with-datablist" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Solution 1: Join CSV files on a common column with Datablist</h1>
<aside class="text-center mb-5">
    <div class="mb-2">
        <a href="https://app.datablist.com" className="btn btn-primary btn-large" target="_blank">
            Open Datablist
        </a>
    </div>
    <span class="orange-text">No signup required</span>
</aside>
<p>Data manipulation is simple with Datablist. Let's see how you can join CSV files by a unique identifier. <a href="https://app.datablist.com" target="_blank" rel="nofollow">Open Datablist (No signup required)</a> to get started.</p>
<h2 id="step1-load-your-first-csv-file"><a href="#step1-load-your-first-csv-file" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step1: Load your first CSV file</h2>
<p>The first step is to create a collection to pour in all your CSV data. Click the <strong>+</strong> button in the sidebar to create a new collection.</p>
<p>Once your collection is created, go to the "Import CSV" section.</p>
<div class="content-video">
    <video autoplay muted loop playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/create-collection-csv-join.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create a new collection</div>
</div>
<blockquote>
<p>Note: Your CSV file first line must contain column names.</p>
</blockquote>
<p>Drag and drop a CSV file or click to select a file from your computer. Once the file is loaded, check that the number of lines and columns preview displayed is correct before moving to the next step.</p>
<p>Map your CSV columns with collection properties or create new properties.</p>
<p>The last step is to click on the "Import" button to launch the import process. Your first CSV file is imported!</p>
<div class="content-video">
    <video controls muted playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/import-csv.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Import your first CSV</div>
</div>
<h2 id="step2-define-which-column-to-use-as-a-unique-identifier"><a href="#step2-define-which-column-to-use-as-a-unique-identifier" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step2: Define which column to use as a unique identifier</h2>
<p>Now that you have imported your first CSV file, you can define a "unique values" constraint on a collection property. With this information, Datablist will merge new CSV imports to respect the constraint. Go to the columns configuration and edit the property that will be your unique identifier. Check the "Unique Values" attribute and save.</p>
<div class="content-video">
    <video muted loop controls width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/unique-values.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Add unique values constraint on a property</div>
</div>
<h2 id="step3-load-one-or-more-csv-files"><a href="#step3-load-one-or-more-csv-files" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step3: Load one or more CSV files</h2>
<p>Once you have set a unique constraint on a collection property, import your other CSV files one by one in the existing collection. Create new collection properties if needed during the CSV mapping step.</p>
<p>When your collection has items and has a unique constraint, you can select a merging option during a CSV file import.</p>
<p><img src="/howto_images/csv/csv-join-merging-mode.png#2740x1367" alt="Merging Mode"></p>
<p>Select how data would be merged into the collection:</p>
<ul>
<li>Soft Merge: If data exists for the item property, <strong>don't</strong> update it. This is the default setting.</li>
<li>Hard Merge: If data exists for the item property, <strong>update it</strong></li>
</ul>
<blockquote>
<p>The "Skip item" option skips the row when an entry with the same identifier value is found in the collection, it <strong>must not</strong> be selected in order to join CSV files.</p>
</blockquote>
<p><img src="/howto_images/csv/csv-merge-options.png#1587x618" alt="CSV Join - Merging Options"></p>
<h2 id="step-4-export-to-csv-if-needed"><a href="#step-4-export-to-csv-if-needed" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 4: Export to CSV if needed</h2>
<p>Congratulations! 🎉 You've successfully combined CSV files using a common column! If you need to use the result in another tool, click on the "Export" button to export the collection as another CSV file.</p>
<p><img src="/howto_images/csv/export-to-csv.png#1303x306" alt="CSV Export"></p>
<h2 id="full-step-by-step-video-to-join-csv-files-with-datablist"><a href="#full-step-by-step-video-to-join-csv-files-with-datablist" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Full step by step video to join CSV files with Datablist</h2>
<p>In the video below, the property "unique values" settings is set directly during the property creation.</p>
<div class="content-video">
    <video controls muted playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/csv-files-join-datablist.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Join CSV files on a unique column with Datablist</div>
</div>
<h1 id="solution-2-join-csv-files-using-google-sheet-or-microsoft-excel"><a href="#solution-2-join-csv-files-using-google-sheet-or-microsoft-excel" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Solution 2: Join CSV files using Google Sheet (or Microsoft Excel)</h1>
<p>Spreadsheet tools have limited support for merging CSV files with a common column. However, a cell formula can find a row in another table that matches a value. When applied to each row of a table, it can search in another table and return the value from any column in the matched row.</p>
<p>The formula is <code>VLOOKUP</code> and is available on <a href="https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1" target="_blank" rel="nofollow">Microsoft Excel</a> and <a href="https://support.google.com/docs/answer/3093318?hl=en" target="_blank" rel="nofollow">Google Sheet</a>.</p>
<blockquote>
<p><strong>Limitations</strong></p>
<ul>
<li>With spreadsheets, <strong>one CSV file is used as a master table and must contain all the possible values</strong> for the join column.</li>
<li>In all the secondary tables, the join column must be the first column.</li>
</ul>
</blockquote>
<h2 id="step-1-load-your-csv-files"><a href="#step-1-load-your-csv-files" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 1: Load your CSV files</h2>
<blockquote>
<p>In this tutorial, we use Google Sheets (the formula VLOOKUP is similar in Microsoft Excel).</p>
</blockquote>
<p>Among your CSVs, choose the one with the most values to be used as the master table. The other will be called secondary CSV files.</p>
<p>First load your master CSV file with <code>File -> Import</code> and select your CSV (go to the <code>Upload</code> tab to use a CSV file from your computer).</p>
<p>In <code>Import Location</code>, select <code>Insert new sheet(s)</code>.</p>
<p><img src="/howto_images/csv/google-sheets-import-csv.png#2030x1678" alt="Load a CSV file in Google Sheets"></p>
<p>Perform this operation for your secondary CSV file(s). <strong>Each CSV file must be uploaded to a dedicated table in the the spreadsheet.</strong></p>
<p><img src="/howto_images/csv/google-sheets-import-csv-2.png#1932x1590" alt="CSV files imported in Google Sheets"></p>
<h2 id="step-2-create-new-columns-in-the-master-sheet"><a href="#step-2-create-new-columns-in-the-master-sheet" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 2: Create new columns in the master sheet</h2>
<p>The table with your master CSV is your master table and it will receive values from other tables.
In this master table, create new columns to store data from your other tables.</p>
<p>For this tutorial, we want to have the <code>Job Title</code> data from our secondary table in our master table, so we add an empty <code>Job Title</code> column.</p>
<p><img src="/howto_images/csv/google-sheets-import-csv-3.png#1932x1590" alt="New Job Title Column"></p>
<h2 id="step-3-move-your-unique-column-as-the-first-column-in-your-secondary-tables"><a href="#step-3-move-your-unique-column-as-the-first-column-in-your-secondary-tables" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 3: Move your unique column as the first column in your secondary tables</h2>
<p>The <code>VLOOKUP</code> formula works by searching the first column in the table it searches in. In all your <strong>secondary</strong> tables, (<strong>no need to do it on the master table</strong>), move the column to use as join column in the first position.</p>
<p><img src="/howto_images/csv/google-sheets-import-csv-4.png#1932x1590" alt="Identifier Column must be the first column"></p>
<h2 id="step-3-use-the-vlookup-formula"><a href="#step-3-use-the-vlookup-formula" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 3: Use the VLOOKUP formula</h2>
<p>The final step is to use the <code>VLOOKUP</code> formula to find rows in the other tables and to display a column from the matched row.</p>
<p>The formula takes 3 arguments:</p>
<ul>
<li>search_key - The value to search for. It will be the unique identifier row's value.;</li>
<li>range - The range to consider for the search. <strong>The first column in the range is searched for the key specified in search_key.</strong> Define your whole secondary table as the range (see video).</li>
<li>index - The column index in the row to be returned. The first column in the range is numbered 1.</li>
<li>is_sorted - [TRUE by default] - Indicates whether the column to be searched (the first column of the specified range) is sorted. <strong>FALSE is recommended in most cases.</strong> If set to True and your data is not sorted, your results will be wrong!</li>
</ul>
<pre><code>VLOOKUP(search_key, range, index, [is_sorted])
</code></pre>
<p>See the video below to understand how to use <code>VLOOKUP</code> to join data on a unique column:</p>
<div class="content-video">
    <video controls muted playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/google-sheets-join-csv-vlookup.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Join CSV files on unique column with Google Sheets</div>
</div>
<p><a href="https://support.google.com/docs/answer/3093318?hl=en" target="_blank" rel="nofollow">Read more about the VLOOKUP formula on the Google Sheets documentation</a>.</p>
<p>Repeat the operation for every other column from your secondary tables 💪.</p>



The CSV file format is a standard to exchange structured data between companies or different software applications. It is text-based and uses punctuation delimiters to separate columns.

Here is an example of CSV file data:

```text
firstName, lastName, email
Joe, Vaughn, vaugh@hotmail.com
Bob, Cunnighan, bobc@gmail.com
```

[CSV files](/faq/csv/csv-uses) can be exported from most tools dealing with data, including CRM, Order Management System, Spreadsheets (Google Sheets or Microsoft Excel), and financial products. In the future, we might have [unified data models](/blog/common-data-model-vs-cloud-information-model) to transfer structured items between applications. In the meantime, we must rely on CSV files.

When it comes to manipulating CSV files, the go-to solution is spreadsheet. Loading a CSV file into Google Sheets or Microsoft Excel is straightforward. However, those tools are limited when it comes to two simple operations:

- Joining CSV files by a unique column
- [Deduplicate and merge CSV entries on a column unique constraint](/how-to/remove-csv-duplicates)

When [performing a join operation](/faq/csv/difference-join-merge), we are using a common column to combine data from several sources. **Spreadsheets cannot define a unique constraint on a column**, therefore provide limited support for joining CSV or deduplication.

This guide contains 2 parts:
- [Join CSV files on a common column with Datablist](#solution-1-join-csv-files-on-a-common-column-with-datablist)
- [Join CSV files using Google Sheet (or Microsoft Excel)](#solution-2-join-csv-files-using-google-sheet-or-microsoft-excel)

In this tutorial, we use 2 demo CSV files:
- [Master CSV: A CSV with `First Name, Last Name, Email`](https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/FirstNameLastNameEmail.csv)
- [A secondary CSV with `Email, Job Title`](https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/EmailJobTitle.csv)


# Solution 1: Join CSV files on a common column with Datablist

<aside class="text-center mb-5">
    <div class="mb-2">
        <a href="https://app.datablist.com" className="btn btn-primary btn-large" target="_blank">
            Open Datablist
        </a>
    </div>
    <span class="orange-text">No signup required</span>
</aside>

Data manipulation is simple with Datablist. Let's see how you can join CSV files by a unique identifier. [Open Datablist (No signup required)](https://app.datablist.com) to get started.


## Step1: Load your first CSV file

The first step is to create a collection to pour in all your CSV data. Click the **+** button in the sidebar to create a new collection.

Once your collection is created, go to the "Import CSV" section.

<div class="content-video">
    <video autoplay muted loop playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/create-collection-csv-join.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create a new collection</div>
</div>

> Note: Your CSV file first line must contain column names.

Drag and drop a CSV file or click to select a file from your computer. Once the file is loaded, check that the number of lines and columns preview displayed is correct before moving to the next step.

Map your CSV columns with collection properties or create new properties.

The last step is to click on the "Import" button to launch the import process. Your first CSV file is imported!

<div class="content-video">
    <video controls muted playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/import-csv.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Import your first CSV</div>
</div>

## Step2: Define which column to use as a unique identifier

Now that you have imported your first CSV file, you can define a "unique values" constraint on a collection property. With this information, Datablist will merge new CSV imports to respect the constraint. Go to the columns configuration and edit the property that will be your unique identifier. Check the "Unique Values" attribute and save.

<div class="content-video">
    <video muted loop controls width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/unique-values.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Add unique values constraint on a property</div>
</div>


## Step3: Load one or more CSV files

Once you have set a unique constraint on a collection property, import your other CSV files one by one in the existing collection. Create new collection properties if needed during the CSV mapping step.

When your collection has items and has a unique constraint, you can select a merging option during a CSV file import.

![Merging Mode](/howto_images/csv/csv-join-merging-mode.png#2740x1367)

Select how data would be merged into the collection:

- Soft Merge: If data exists for the item property, **don't** update it. This is the default setting.
- Hard Merge: If data exists for the item property, **update it**

>  The "Skip item" option skips the row when an entry with the same identifier value is found in the collection, it **must not** be selected in order to join CSV files.

![CSV Join - Merging Options](/howto_images/csv/csv-merge-options.png#1587x618)

## Step 4: Export to CSV if needed

Congratulations! 🎉 You've successfully combined CSV files using a common column! If you need to use the result in another tool, click on the "Export" button to export the collection as another CSV file.

![CSV Export](/howto_images/csv/export-to-csv.png#1303x306)


## Full step by step video to join CSV files with Datablist

In the video below, the property "unique values" settings is set directly during the property creation.


<div class="content-video">
    <video controls muted playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/csv-files-join-datablist.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Join CSV files on a unique column with Datablist</div>
</div>


# Solution 2: Join CSV files using Google Sheet (or Microsoft Excel)

Spreadsheet tools have limited support for merging CSV files with a common column. However, a cell formula can find a row in another table that matches a value. When applied to each row of a table, it can search in another table and return the value from any column in the matched row.

The formula is `VLOOKUP` and is available on [Microsoft Excel](https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1) and [Google Sheet](https://support.google.com/docs/answer/3093318?hl=en).

> **Limitations**
> - With spreadsheets, **one CSV file is used as a master table and must contain all the possible values** for the join column.
> - In all the secondary tables, the join column must be the first column.


## Step 1: Load your CSV files

> In this tutorial, we use Google Sheets (the formula VLOOKUP is similar in Microsoft Excel).

Among your CSVs, choose the one with the most values to be used as the master table. The other will be called secondary CSV files.

First load your master CSV file with `File -> Import` and select your CSV (go to the `Upload` tab to use a CSV file from your computer).

In `Import Location`, select `Insert new sheet(s)`.

![Load a CSV file in Google Sheets](/howto_images/csv/google-sheets-import-csv.png#2030x1678)

Perform this operation for your secondary CSV file(s). **Each CSV file must be uploaded to a dedicated table in the the spreadsheet.**

![CSV files imported in Google Sheets](/howto_images/csv/google-sheets-import-csv-2.png#1932x1590)


## Step 2: Create new columns in the master sheet

The table with your master CSV is your master table and it will receive values from other tables.
In this master table, create new columns to store data from your other tables.

For this tutorial, we want to have the `Job Title` data from our secondary table in our master table, so we add an empty `Job Title` column.

![New Job Title Column](/howto_images/csv/google-sheets-import-csv-3.png#1932x1590)


## Step 3: Move your unique column as the first column in your secondary tables

The `VLOOKUP` formula works by searching the first column in the table it searches in. In all your **secondary** tables, (**no need to do it on the master table**), move the column to use as join column in the first position.

![Identifier Column must be the first column](/howto_images/csv/google-sheets-import-csv-4.png#1932x1590)


## Step 3: Use the VLOOKUP formula

The final step is to use the `VLOOKUP` formula to find rows in the other tables and to display a column from the matched row.

The formula takes 3 arguments:

- search_key - The value to search for. It will be the unique identifier row's value.;
- range - The range to consider for the search. **The first column in the range is searched for the key specified in search_key.** Define your whole secondary table as the range (see video).
- index - The column index in the row to be returned. The first column in the range is numbered 1.
- is_sorted - [TRUE by default] - Indicates whether the column to be searched (the first column of the specified range) is sorted. **FALSE is recommended in most cases.** If set to True and your data is not sorted, your results will be wrong!

```
VLOOKUP(search_key, range, index, [is_sorted])
```

See the video below to understand how to use `VLOOKUP` to join data on a unique column:

<div class="content-video">
    <video controls muted playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/csv/join-csv/google-sheets-join-csv-vlookup.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Join CSV files on unique column with Google Sheets</div>
</div>

[Read more about the VLOOKUP formula on the Google Sheets documentation](https://support.google.com/docs/answer/3093318?hl=en).

Repeat the operation for every other column from your secondary tables 💪.


Guides

How to clean an email list for free

How to remove CSV duplicates

How to join CSV files by a unique identifier