
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

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>Deduplicate and merge CSV entries on a column unique constraint</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 join CSV files by a unique identifier

We need you!