
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:

- [Consolidate CSV files using a unique identifier](/how-to/join-csv-files-unique-identifier-or-column)
- Remove duplicate rows from a CSV
- Merge CSV rows that have a similar identifier column

 In this guide, we will focus on the last two manipulations: removing and/or merging 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 a specific column.** Then depending on your settings, when several entries with the same unique value are detected, the entries are either dropped or merged.

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

In this guide, we will work with a single CSV file that contains thousands of entries. Not only we will remove duplicate entries, but we will merge data from the matching entries and consolidate them.

> You can [download the tutorial CSV file here](https://storage.googleapis.com/public_datablist/website/guides/csv/remove-duplicates/deduplicates-csv-remove-duplicates.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 file into a Datablist collection
- Create properties matching CSV columns
- Define a **unique constraint** on the email property
- Import the CSV file


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

The first step in our process is to load the CSV file into Datablist. [Log in to Datablist](https://app.datablist.com) or [join the beta](#private_beta) to get started.

When creating a new collection, we can select data types with built-in properties ([see also our article on common data models](/blog/common-data-model-vs-cloud-information-model)) or create an empty collection with no property.

For this example, we will select an empty collection so the properties will be created from the CSV columns.

Once the collection is created, 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/csv/remove-duplicates/deduplicates-csv-create_collection.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create a new collection</div>
</div>


# Step 2: Create properties for your CSV columns

With the CSV file now loaded, we can create properties for our collection using the CSV column names. Datablist lists every column found in the CSV so we 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/csv/remove-duplicates/deduplicates-csv-create-properties.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create collection properties</div>
</div>


# Step 3: Define your unique constraint

**Here is the important step!** We want to remove duplicates entries from the CSV! In our example, duplicates are entries with the same email address. To add this constraint, we add the `Unique values` option on the `email` property.

With this `Unique values` constraint, Datablist will ensure two entries can't share the same `email`. When it happens, entries will be merged or skipped depending on the option we choose on the **Import** step.

<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/remove-duplicates/deduplicates-csv-unique-constraint.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Define a unique constraint on the email property</div>
</div>


# Step 4: Review and import

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

The last step is to select the merging mode. **What should Datablist do when several entries have the same `Email`?**

The default mode is "Soft Merge". When an entry from the CSV has a value (`Email` in our example) that already exists (in the CSV entries, or the collection), it will merge the two entries without overwriting existing data (see schema above).

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


We click the "Import items" button and we are done! 💪
Duplicate CSV entries are merged and we have a clean collection! If needed, we are now able to import other CSV files into this collection.


# 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/csv/export-to-csv.png)
*CSV Export*


---

If you have any questions, please [contact us](https://docs.google.com/forms/d/e/1FAIpQLSeAZfqCjOg-ok7yYAwz4hmKxhPOeLMIwZrLozsro5HTQhrsxw/viewform?usp=sf_link). And if you want to join the beta, please fill in the form bellow 👇.


<p>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.</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>Remove duplicate rows from a CSV</li>
<li>Merge CSV rows that have a similar identifier column</li>
</ul>
<p>In this guide, we will focus on the last two manipulations: removing and/or merging 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 a specific column.</strong> Then depending on your settings, when several entries with the same unique value are detected, the entries are either dropped or merged.</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 single CSV file that contains thousands of entries. Not only we will remove duplicate entries, but we will merge data from the matching entries and consolidate them.</p>
<blockquote>
<p>You can <a href="https://storage.googleapis.com/public_datablist/website/guides/csv/remove-duplicates/deduplicates-csv-remove-duplicates.csv" target="_blank" rel="nofollow">download the tutorial CSV file here</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. The process can be summed up with:</p>
<ul>
<li>Load your CSV file into a Datablist collection</li>
<li>Create properties matching CSV columns</li>
<li>Define a <strong>unique constraint</strong> on the email property</li>
<li>Import the CSV file</li>
</ul>
<h1 id="step-1-load-your-csv-file-into-a-datablist-collection"><a href="#step-1-load-your-csv-file-into-a-datablist-collection" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 1: Load your CSV file into a Datablist collection</h1>
<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">Log in to Datablist</a> or <a href="#private_beta">join the beta</a> to get started.</p>
<p>When creating a new collection, we can select data types with built-in properties (<a href="/blog/common-data-model-vs-cloud-information-model">see also our article on common data models</a>) or create an empty collection with no property.</p>
<p>For this example, we will select an empty collection so the properties will be created from the CSV columns.</p>
<p>Once the collection is created, click on the "Import CSV" 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/csv/remove-duplicates/deduplicates-csv-create_collection.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create a new collection</div>
</div>
<h1 id="step-2-create-properties-for-your-csv-columns"><a href="#step-2-create-properties-for-your-csv-columns" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 2: Create properties for your CSV columns</h1>
<p>With the CSV file now loaded, we can create properties for our collection using the CSV column names. Datablist lists every column found in the CSV so we 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/csv/remove-duplicates/deduplicates-csv-create-properties.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create collection properties</div>
</div>
<h1 id="step-3-define-your-unique-constraint"><a href="#step-3-define-your-unique-constraint" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 3: Define your unique constraint</h1>
<p><strong>Here is the important step!</strong> We want to remove duplicates entries from the CSV! In our example, duplicates are entries with the same email address. To add this constraint, we add the <code>Unique values</code> option on the <code>email</code> property.</p>
<p>With this <code>Unique values</code> constraint, Datablist will ensure two entries can't share the same <code>email</code>. When it happens, entries will be merged or skipped depending on the option we choose on the <strong>Import</strong> step.</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/remove-duplicates/deduplicates-csv-unique-constraint.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Define a unique constraint on the email property</div>
</div>
<h1 id="step-4-review-and-import"><a href="#step-4-review-and-import" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Step 4: Review and import</h1>
<p><img src="/howto_images/csv/csv-merge-options.png" alt="CSV Join Merging Options">
<em>Merging Options</em></p>
<p>The last step is to select the merging mode. <strong>What should Datablist do when several entries have the same <code>Email</code>?</strong></p>
<p>The default mode is "Soft Merge". When an entry from the CSV has a value (<code>Email</code> in our example) that already exists (in the CSV entries, or the collection), it will merge the two entries without overwriting existing data (see schema above).</p>
<div class="content-video">
    <video controls muted loop playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/csv/remove-duplicates/remove-csv-duplicates-review-import.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Review and import!</div>
</div>
<p>We click the "Import items" button and we are done! 💪
Duplicate CSV entries are merged and we have a clean collection! If needed, we are now able to import other CSV files into this collection.</p>
<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/csv/export-to-csv.png" alt="CSV Export">
<em>CSV Export</em></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>. And if you want to join the beta, please fill in the form bellow 👇.</p>


How to remove CSV duplicates


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 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)

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

Data manipulation is simple with Datablist. Let's see how you can join CSV files by a unique identifier. [Log in to Datablist](https://app.datablist.com) or [join the beta](#private_beta) to get started.


## Step1: Load your first CSV file

The first step is to create a collection to pour in all your CSV data.
Datablist main idea is to use [common data models](/blog/common-data-model-vs-cloud-information-model) to benefit from built-in properties and actions. If you only need to join CSV files, select "Start from scratch" to create a collection with no property.

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)
*Merging Mode*

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)
*Merging Options*

## 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)
*CSV Export*


## 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)
*Load a CSV file in Google Sheets*

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)
*CSV files imported in Google Sheets*


## 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)
*New Job Title Column*


## 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)
*Identifier Column must be the first column*


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


<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>CSV files 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>Spreadsheets cannot define a unique constraint on a column, 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>
<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">Log in to Datablist</a> or <a href="#private_beta">join the beta</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.
Datablist main idea is to use <a href="/blog/common-data-model-vs-cloud-information-model">common data models</a> to benefit from built-in properties and actions. If you only need to join CSV files, select "Start from scratch" to create a collection with no property.</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" alt="Merging Mode">
<em>Merging Mode</em></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" alt="CSV Join Merging Options">
<em>Merging Options</em></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" alt="CSV Export">
<em>CSV Export</em></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" alt="Load a CSV file in Google Sheets">
<em>Load a CSV file in Google Sheets</em></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" alt="CSV files imported in Google Sheets">
<em>CSV files imported in Google Sheets</em></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" alt="New Job Title Column">
<em>New Job Title Column</em></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" alt="Identifier Column must be the first column">
<em>Identifier Column must be the first column</em></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>


Guides

How to remove CSV duplicates

How to join CSV files by a unique identifier

We need you!