
Localization is a must if you want your content to be accessible to an international audience. **Machine translation** using machine learning offers high-quality translations for a fraction of the cost compared to [professional human translators](#human-translation-vs-machine-translation).

But how to translate your content without spending your time copy-pasting your text to Google Translate and back to your application?

[CSV files](#why-use-csv-files-for-bulk-translations) are perfect to store structured data and are used by most applications to export your data into a generic format.

Product catalogs can be exported to [CSV files](/learn/csv/csv-uses). CMS articles can be extracted into flat files. **CSV has become the de-facto format to share generic data between applications.**

But you can't translate a CSV file as a simple text file. Some columns might be names, or dates and must be kept unchanged. **How do you translate some columns from a CSV file?**

In this guide, we’ll go through these steps:

- [How to import your CSV file on Datablist](#step-1-import-your-csv-file)
- [How to translate the text on a CSV column to another language](#step-3-translate-your-csv-file)
- [Export your translated data as CSV or Excel files](#step-5-export-your-translations-as-csv-or-excel-files)

Let's dive in.


## Step 1: Import your CSV file

In this guide, all the work will be done online with Datablist [CSV Viewer and Editor](/csv-editor).

<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">Sign up for free</span>
</aside>

> I will use a "Book Summaries" CSV file in this example. [The demo CSV file is accessible here](https://storage.googleapis.com/public_datablist/website/guides/translate-csv-files/demo-book-summaries.csv).

Start by creating a new collection. The collection will hold your CSV data. Click on the **+** button in the sidebar or the "**Start with a CSV/Excel file**" shortcut on the home page.

> When using the "+" in the sidebar, select "Import CSV/Excel" to go to the CSV importer.

![Create a new collection](/howto_images/translate-csv/create-collection.png#2121x865)


After loading your CSV file, Datablist lists the columns found. Every column is analyzed to detect its data type. **On the *column* with your data to translate, select the "Long Text" data type if not already selected.**

With "Long Text", you will have a better user experience managing your texts.

![Select LongText for your text column to translate](/howto_images/translate-csv/select-long-text.png#2263x1217)

Review your data and **import your CSV** content into Datablist.

## Step 2: Select the items to translate

Datablist offers "Actions" to interact with your data. You can [enrich Facebook leads](/how-to/scrape-facebook-group-members-linkedin) or [clean email listings](/how-to/email-list-cleaning) directly from your data. In this guide, you will use the **Google Translate** action to translate your CSV into another language.

**Actions are visible once at least one item is selected.**

> **Important** - Actions are only available for registered users. Sign up for free to start translating your CSV files.

<aside class="text-center mb-5">
    <div class="mb-2">
        <a href="https://app.datablist.com/auth/signup" className="btn btn-primary btn-large" target="_blank">
            Sign up for free
        </a>
    </div>
</aside>

### Select all items

To select all your collection items, click the "All" checkbox on the table header.

![Select all items](/howto_images/translate-csv/select-all-items-to-translate.png#2445x1199)

### Select specific items

To translate only some of your items, **click and move on the checkboxes while holding the click**.

<div class="content-video">
    <video muted loop controls playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/translate-csv-files/translate-selected-items.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Select specific items</div>
</div>


## Step 3: Translate your CSV file

Two machine translation providers are available on Datablist: Google Translate and Deepl.

### Translate using Google Translate

Select "Google Translate" in the listed actions to open the configuration drawer.

![Select Google Translate Action](/howto_images/translate-csv/select-google-translate-action.png#2478x1266)

Before translating your text, fill out the configuration settings.

![Configure Google Translate Action](/howto_images/translate-csv/translate-csv-action-settings.png#2472x1417)


- **Source Language** - *Optional* - **If not defined, the source language will be auto-detected.** If your texts are in different languages, keep the source language unselected.
- **Target Language** - *Mandatory* - Select the target language. Language names are listed with English names. [See supported languages.](#what-languages-are-supported)
- **Input Properties** - *Mandatory* - The property (= CSV column) in your Datablist collection with the texts to translate. In this example, the book summaries are listed in the "Description" property. I select "Description". **Important:** Only Text/Long Text properties are translatable. Other data types are ignored.
- **Output(s)** - *Mandatory* - Where will be saved the translated texts. Map the "Translated Text" output with one of the collection properties or click the **+** icon to create a new property in your collection. **If your output property contains text, the item will be skipped and your text will NOT be overwritten**.


> **Note** - You must define an **Output** property to hold the translated texts. The output property cannot be the input property as it would overwrite its data. [You will be able to manage your properties in the last step to have the desired column names](#step-5-export-your-translations-as-csv-or-excel-files).

Once configured, click "**Run action**" to translate your texts.


<div class="content-video">
    <video muted loop controls playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/translate-csv-files/translate-run-action.mp4" type="video/mp4" />
    </video>
    <div class="text-center">See the video for the full process</div>
</div>


### Translate using Deepl

Deepl is a machine translation service launched in 2017. Deepl is known for its accuracy compared to Google Translate. Under the hood, [Deepl uses a different kind of neural network](https://en.wikipedia.org/wiki/DeepL_Translator) called convolutional neural networks (CNNs). This technology keeps the context as it processes long texts. So, Deepl is better suited for longer texts and Google Translate for texts with unrelated sentences.

To use Deepl, follow the same process as [Google Translate](#translate-using-google-translate) but select the new Deepl action.


![Deepl](/howto_images/translate-csv/deepl-action.png#2248x616)


## Step 4: Review your translations

Translated texts are saved in the "Translated Texts" output property.

Automatic translations are convenient but error-prone. A manual review of your translations is important.
Check the translated texts to:

- **Find names and domain-specific vocabulary** - Google might translate product names or technical words. Note: words  beginning with a capital letter are usually not translated.
- **Review variable placeholders** - If your texts are used for software applications, check variable placeholders that must not be translated.

**To quickly read and edit your translated texts, press "Enter" on a cell to enter edit mode.**

![Review text in table](/howto_images/translate-csv/review-csv-translated-text-inline.png#1560x1040)


Or open the details drawer.

![Review text with item details](/howto_images/translate-csv/review-csv-item-drawer.png#2462x1324)


## Step 5: Export your translations as CSV or Excel files

The final step is to export your data back to a file to reimport it into your application. See our documentation to [export your data in CSV or Excel files](/docs/export-data).

![Export collection](/howto_images/translate-csv/export-csv-data.png#2409x1153)

**To export your data with the same CSV columns as the imported file**: edit your properties to match the original columns:

 1. Rename the source property and hide it.
 1. Rename the targeted property.

See the video below for a step-by-step guide.

<div class="content-video">
    <video muted loop controls playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/translate-csv-files/export-visible-properties.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Export data with original CSV columns</div>
</div>


## Everything you need to know about CSV file translations

### Why use CSV files for bulk translations?

CSV is a format to store structured data using text files. In a CSV file, each line is a data record. And each record is made of fields separated by commas (or sometimes by semicolons ";" or tabular keys).

CSV files are a common way to transfer data between applications. Because it relies on text files, the CSV format is a simple way to export database or spreadsheet data. Any text editor can open it but you need a [CSV editor](/csv-editor) to avoid errors when manipulating CSV files.

You can export your data in CSV in most applications, translate specific columns, and reimport the translated CSV file back into your application.

Any structured listings with text columns are perfect to be represented with CSV.

For example:

- Translating a Product Catalog with text columns such as title and description
- Translating blog articles using structured files with title and article content
- Translating a list of User Reviews

### How many translations can I perform for free?

Datablist uses [Google Translate API](https://cloud.google.com/translate/) and [Deepl](https://www.deepl.com/translator) which have a cost. As much as we would like to offer this service for free, we have to set a limit for free translations.

Free users can translate up to 50 items per month for free. [Upgrade to the **Standard** plan](https://app.datablist.com/account/upgrade) for unlimited translations.


### Human translation vs machine translation

Machine translation is powered by automated software that translates source content into target languages. The best automatic translation providers use artificial intelligence and machine learning to offer high-quality translations.

Human translations by professional translators shine on complex texts. When the context is important, the text is long, and when vocabulary is technical or specific, machine translation is surpassed.

**I like hybrid translation**, combining machine translation with human review and rewrite. The hybrid translation is perfect for webpage internationalization.


### What languages are supported?

Datablist relies on both Google Translate API and Deepl for automatic translations. [The accepted languages for Google Translate are listed on this  page.](https://cloud.google.com/translate/docs/languages) At the time of writing, 111 languages are available. Translations from and to any of the listed languages are possible.

Deepl supports fewer languages. [Currently 28 languages are available](https://en.wikipedia.org/wiki/DeepL_Translator).


CSV files are perfect to store structured data and are used by most applications to export your data into a generic format. But you can't translate all the CSV as a simple text file. Some column might be names, or dates and must be kept unchanged. How do you translate some columns in a CSV file?

How to translate CSV files online for free

Import your CSV file

Translate your CSV file

Review your translations

Re-Export your translation as CSV


**Facebook Groups are great to build a leads database.** Reach out to your competitor group members or scrape thematic groups to find prospects.

The only problem is: **scraping Facebook groups will get you a lot of bad-quality leads!** Groups are filled with students, interns, and other people you don't want to target.

Facebook groups are powerful but group members need to be filtered. Here is my advice: **Facebook group members must be a source to find relevant people to contact, but you must not mass mail the entire group!** The challenge is to enrich your Facebook leads, segment them, and then contact the relevant prospects.

Facebook is a personal social network and personal data is protected. Some email-finding tools offer to find an email from a Facebook profile but I've found the result to be disappointing. **It is easier to use information from the Facebook profile to find LinkedIn profiles.** LinkedIn is great for cold messaging and because it's a business social network, lookup services to get email addresses from LinkedIn profiles work great.

Using this step by step guide, you will learn:
- [How to export Facebook group members into a CSV file](#step-1-extracting-leads-from-facebook-group-members)
- [How to enrich members with their LinkedIn profile URL](#step-2-find-linkedin-profiles)
- [How to keep your exports up to date with new group members](#step-3-update-collection-with-new-members)

## Step 1: Extracting leads from Facebook Group members

Facebook is a personal network and doesn't have built-in group members export. You need a third-party tool to export your group members.

Two options:
- Use cloud extraction services like Phantombuster that run scripts on their servers using your Facebook credentials.
- Use scripts or extensions inside your web browser to scrape data when you browse Facebook.

**The only way to access Facebook group members is through the "New members" listing available on the group "People" tab.** Scripts and browser extensions scrape data as you scroll the page, and cloud services do the same by mimicking user behavior in their servers.


![New members are listed on the "People" page.](/howto_images/facebook-members-scraper/facebook-group-people-tabs.png#1921x770)

Facebook combats scraping and has protection to detect automatic scrapers. To avoid being blocked, you need to use a proxy with cloud extraction services. Still, Facebook keeps improving its protection; so be careful with automatic scraping.

**In this guide, I show you how to use a web browser script to scrape data as you browse the "People" tab.**


> **Notes** <br/>
> To extract data, the "People" tab must be accessible on the group page. So, the group must be public or **you must be a member for private groups.**



### Export Facebook group members into a CSV file

To extract Facebook group members, just **copy-paste the following script into Chrome Developer Console**.

<code id="scrape-facebook-code" src="https://api.github.com/repos/floriandiud/facebook-group-members-scraper/contents/dist/main.min.js" >
</code>

> **Notes**<br/>
> The source code for the [Facebook group scraper is available on Github](https://github.com/floriandiud/facebook-group-members-scraper). The script reads API calls done when loading members on the web page to extract members' data.


![Open Chrome Developer Console - Keyboard shortcut "Ctrl + Shift + I" (on Windows) or "Cmd + Option + I" (on Mac).](/howto_images/facebook-members-scraper/open-chrome-developer-console.png#2327x1207)


![Copy-Paste Facebook Group Extractor script into the console tab.](/howto_images/facebook-members-scraper/copy-paste-facebook-extractor.png#2289x1362)


### Scrape Facebook group members profiles

**Press 'Enter' with your keyboard to run the script.** A button with the text "Download X members" appears in the bottom right on the page.

You can close the `Developer Console`, your script is active 👍

![A new button pop up](/howto_images/facebook-members-scraper/facebook-extractor-button.png#2170x1024)


Then, go to the "People" tab from the group page. Facebook displays a list of new members with automatic loading on scroll. Scroll down to load new members.

As you scroll, the script catches Facebook API calls and store the data in memory.

<div class="content-video">
    <video autoplay muted loop controls playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/scrape-facebook-group-members/video-scrape-facebook-group-members.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Scroll to scrape Facebook Group Members</div>
</div>



### Export Facebook members

Click on the "Download x members" button to export the members into a CSV file.

The CSV contains 8 columns:

- **Profile Id**: Unique facebook identifier. Multi-digit number.
- **Full Name**: First name and last name concatenated.
- **Profile Link**: Profile URI in the format https://www.facebook.com/{{username}}. When not available, default to generic URI https://www.facebook.com/profile.php?id={{profile_id}}
- **Bio**: Member bio text. Can be a job title, a school name, a city, or it can be empty.
- **Image Src**: Profile picture URL.
- **Group Id**: Facebook group identifier. Multi-digit number.
- **Group Joining Text**: Relative time since user join the group. In the format: "Member since XX".
- **Profile Type**: Facebook profile type. Can be "User" or "Page".

> **Notes**<br/>
> To stop scraping members and to remove the button, just reload the web page.

### FAQ

#### How many members can I extract?

Facebook shows up to 10k members on the "New members" page. Members are sorted by date added. We recommend extracting members periodically and adding them to your existing leads database. See [keep your exports up to date with new group members](#update-collection-with-new-members).


#### How long does it take to scrape group members?

The script scans Facebook API calls as you scroll the members' page. API calls are transparent and each call loads an average of 10 new members. By scrolling for a few minutes, I successfully extracted thousands of members.


#### Can my account be blocked by Facebook?

The script catches genuine API calls done inside the web page. No extra calls are performed, and no bot clicks are done. Everything happens inside your natural browsing behavior. **This script is safer than other scraping applications running in the cloud and using proxy and bots to extract profiles.**

That being said, always be careful and don't abuse Facebook. Avoid using fully automated scripts. If you can, use a second Facebook profile that is not your personal one.


## Step 2: Find LinkedIn profiles

Do you have a CSV file with a lot of prospects? Great! Now it's time to turn it into a leads database!

Cold messaging on Facebook is spam. Facebook is a social network. I don't want to get business inquiries, and neither do your prospects.

**With a prospect name and a company name (or domain), you can search for a LinkedIn profile which can be used for LinkedIn messaging or to get a business email address.**

In this guide, you will learn how to enrich your Facebook prospects with their LinkedIn Profile URL.


### Create a Datablist collection and import your CSV file

Datablist is perfect to [view and edit CSV files](/csv-editor) and to run actions on your data. In this guide, I'll show you how to use the "**LinkedIn Profile Finder**" action on your prospects. **It takes a name and a keyword and returns a LinkedIn profile URL if found.**

First, [sign-up on Datablist](https://app.datablist.com/auth/signup) and [create a collection](/docs/collections).

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

Click the "+" button on the sidebar to create a collection. Then, click on "[Import CSV/Excel](/docs/import-data)".

![Import CSV/Excel file](/howto_images/facebook-members-scraper/create-collection-import-csv-excel.png#2436x952)

Upload your CSV file, create a property for each CSV column and launch the import. Check our [Import Data documentation](/docs/import-data) for a step-by-step process.

![Create a property for each column](/howto_images/facebook-members-scraper/auto-create-properties.png#2444x1087)


### Clean your leads list

First, [sort your collection](/docs/items#search-and-sort-items) by "Profile Type" and remove "Page" profiles to keep only "User".

Then, if you like a minimalist view as I do, disable non-important properties. Hide all but the "Full Name", "ProfileLink" and "Bio" properties.

![Manage Properties](/howto_images/facebook-members-scraper/manage-properties.png#1209x430)

![Hide Properties](/howto_images/facebook-members-scraper/hide-properties.png#1020x898)

Then filter your collection to remove non-professional leads. Use [Datablist search feature](/docs/items#search-and-sort-items) to look for the keywords "school", "university", "student", "college".

![Remove students, interns, etc.](/howto_images/facebook-members-scraper/clean-facebook-members.png#2447x1312)

And if you run a local business, filter for bios with your city/country.

> **Notes**<br />
> Sometimes Facebook bio is misleading. For students, bio might be "Works at {school name}". Browse your leads and remove all leads with a school name in the bio.


### Extract professional information

For group members with only a name and no info about where they work, you have to rely on manual search and enrich the profiles manually. The best way is to search on Google for "{full name} site:linkedin.com/in/" and look at the profiles listed by Google.

Searching for a LinkedIn profile with just a name usually returns homonyms. Look at their profile page, and use the location, job title, photo to find the profile that can be a prospect.

Hopefully, **some Facebook profiles have a bio you can use to automatically narrow the profiles.**

First, you need to extract keywords from the bio in a new property. Create a "Text" property with the name "Keyword". This property will be used to run Datablist ["LinkedIn Profile Finder"](#run-the-linkedin-profile-finder-action) (see below).

![Create a new property](/howto_images/facebook-members-scraper/add-properties.png#1022x1016)


![Keyword Property](/howto_images/facebook-members-scraper/create-keyword-property.png#1028x772)

Look for profiles with professional bios and fill the "Keyword" property with differentiating terms. Interesting bios contain the following terms: "works", "founder", "CEO", "marketing", etc. Adapt them with your business and your Facebook language.

> **Notes**<br />
> To edit a cell, click on it and press "Enter" to switch to editing mode.

![Fill keyword property](/howto_images/facebook-members-scraper/linkedin-finder-fill-keywords.png#1610x954)

### Run the "LinkedIn Profile Finder" action

"LinkedIn Profile Finder" uses search engines to find LinkedIn profiles automatically. **This action is available in the Datablist "Standard" plan.**

Sort your collection to show first the leads with a "Keyword" property filled. Then select leads using checkboxes on the left of your items. Datablist Actions panel will appear from the bottom. Select the action "LinkedIn Profile Finder" to open the action configuration drawer.

> **Important** <br />
> The "LinkedIn Profile Finder" accuracy is directly proportional to the quality of the keywords.

![Select LinkedIn Profile Finder](/howto_images/facebook-members-scraper/datablist-linkedin-profile-finder.png#2128x1082)

![Map Input Properties and add the "LinkedIn Page" result property to your collection](/howto_images/facebook-members-scraper/run-datablist-linkedin-profile-finder.png#2112x1164)


Run "LinkedIn Profile Finder" by clicking the "Run action" button. This action is limited to 30 lookups at once. Process your Leads Database by chunks of 30 items.


> **Tips for better leads**<br />
> LinkedIn is widely used in North America and Europe and less in Asia. To improve your LinkedIn profiles search, target regional groups. For example, look for groups with "European", or countries "French", "Canadian", etc.


## Step 3: Update collection with new members

After the first scrape of Facebook group members, keep monitoring new members once in a while and update your Datablist collection.

Datablist lets you define a [unicity constraint](/docs/properties#ensure-unique-values-for-a-property) on any collection property. When "Do not allow duplicate values" option is enabled, duplicate rows will be skipped (or merged) during CSV import.

Enable "Do not allow duplicate values" for your "Profile Id" property. Values are unique for each Facebook profile and don't change.

![Add unicity constraint on Profile Id](/howto_images/facebook-members-scraper/add-unicity-constraint.png#1260x750)

---

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


With thousands of thematic Facebook Groups available, follow this step by step guide to extract Facebook group member profiles. Enrich them with LinkedIn profile url to build your perfect leads database.

Export Facebook group members and find their LinkedIn Profile

Extract leads from Facebook Group members

Find LinkedIn profiles

Update collection with new members

How to scrape Facebook group members and enrich them with LinkedIn profiles


Excel files are widely used to store listing data. To manage lists of customers or companies, or to clean products catalogs, Microsoft Excel is an easy-to-use spreadsheet tool to manage data. But like any spreadsheet tool, it's not possible to add data constraints to forbid duplicate information. When several people edit the same Excel file or when data get added frequently, the likelihood of finding duplicate values increases.

Hopefully, it's possible to process the data afterward to remove or merge duplicate values.

Removing duplicates is easy using Microsoft Excel (Menu "Data" -> "Remove Duplicates") but merging duplicates without losing information gets tricky. When merging duplicate rows, you want to remove similar rows and merge complementary rows when possible. Finally, conflicting values should be flagged to be processed manually.

Datablist is an easy-to-use online tool to manipulate data. It's a perfect tool to find and merge duplicate values in an Excel file. Datablist [Duplicates Finder](/features/duplicates-remover) automatically finds and merges complementary duplicates. And conflicting items are skipped for manual action. **[You can use Datablist directly online for free without even signing up.](https://app.datablist.com)**

> **Why not use Excel to merge duplicates?** <br/>
> Microsoft Excel is a very powerful application and has a "Remove Duplicates" feature. However, "Remove Duplicates" keeps the first "matching" row and deletes any other duplicate rows. If two rows are complementary, it leads to a loss of data.
> Merging duplicates should not be complicated. Spreadsheet tools are great but they are generic and they don't focus on data manipulation. Datablist targets structured data listing and provides easy-to-use data manipulation features.

In the following guide, you will learn to find and merge duplicates from an Excel file without losing information.
To illustrate this guide, we'll use an Excel file containing Contacts data with 4 fields: `First Name`, `Last Name`, `Email`, `Job Title` and we will find and merge all contacts based on their email address.

> [Click here to download the tutorial Excel file.](https://storage.googleapis.com/public_datablist/website/guides/excel-merge-duplicates/Book1.xlsx)

The process can be summed up with the following steps:

1. [Load your Excel file](#step-1-load-excel-file)
2. [How to Find Duplicates in your Excel File](#step-2-find-duplicates-in-the-excel-file)
3. [How to automatically merge duplicates with no conflict](#step-3-bulk-merge-duplicate-items-with-no-conflict)
4. [How to manually merge remaining duplicates](#step-4-manually-merge-conflicting-items)


## Step 1: Load Excel file

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

The first step is to import your Excel file into [Datablist](https://app.datablit.com). To do so, create a collection that will hold your Excel listing data. Use the "Import CSV/Excel" assistant to load your file.

![Create a collection to hold your data](/howto_images/merge-duplicates-excel/new-collection.png#1851x1011)

### Create properties and map with Excel columns

A Datablist collection is like a spreadsheet but with a data structure. And spreadsheet columns are called "Properties" in Datablist. If a property is defined as `Email`, Datablist will ensure only valid email addresses are filled out. Same with dates, checkboxes, etc.

Once your Excel file is loaded, you need to select which columns will be imported and map them with an existing collection property or create new properties.

Datablist auto detects common data types by analyzing the first rows to find patterns.

<div class="content-video">
    <video muted loop controls playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/excel-merge-duplicates/map-import-excel.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Import Excel file</div>
</div>

> **Important**
> - Cells with formulas will be computed during import.
> - Your data must be in the file first worksheet


If your data is scattered across different worksheets or files, please load them all in one Datablist collection and map them with the same properties.


## Step 2: Find duplicates in the Excel file

With your Excel file data now imported, it's time to use Datablist [Duplicates Finder](/features/duplicates-remover) to find and merge 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.

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

> The duplicate analysis is a non-destructive action. Until you decide to merge automatically or manually your items, items are not impacted.


<div class="content-video">
    <video muted loop controls playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/excel-merge-duplicates/find-duplicates-excel.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Find duplicate items on one or more property/ies</div>
</div>


## Step 3: Bulk merge duplicate items with no conflict

Once all duplicate rows are listed, it's time to merge them! The process is done in two steps:

- First, use Datablist "smart merge" feature to **automatically merge non-conflicting items.**
- Then, use the "Merging Assistant" for the **remaining** duplicate values.

Click "Merge non-conflicting duplicates" to run the "smart merge". It works by merging records with similar or complementary values.

For example:

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

Will be merged into:

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

![Bulk merge complementary duplicate values](/howto_images/merge-duplicates-excel/auto-merge-non-conflicts.png#2265x1270)


## Step 4: Manually merge conflicting items

Automatic merging is great for heavy lifting. If after running it, duplicate items remain, it means they are conflicting. Conflicts occur when at least two items have different values for a property. To merge them, you need to select which value to keep.

To do so, you can directly edit the items from the listing or launch Datablist Merging assistant. The assistant selects a primary item by looking at the item with the most data. Then, you can select property values from any other item to be merged into the primary item. At the end of the process, all items are deleted except for the primary item.

<div class="content-video">
    <video muted loop controls playsinline width="100%" height="auto" class="d-block">
            <source src="https://storage.googleapis.com/public_datablist/website/guides/excel-merge-duplicates/manually-merge-excel-duplicate-rows.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Manually merge remaining duplicates</div>
</div>


## Export results in CSV or Excel format

Well done! **Your data is deduped!**

To export the collection to be used by another application, click "Export" in the collection header and select CSV or Microsoft Excel.

![Export in Microsoft Excel format](/howto_images/merge-duplicates-excel/export-excel.png#1412x670)


Your step-by-step guide to combine duplicate rows in an Excel file. Merge rows automatically and resolve conflicting values with an easy to use interface.

Consolidate duplicate rows in an Excel file.

Load Excel file

Find duplicates

Automatically merge duplicates

Manually merge conflicting items

How to merge duplicate rows in an Excel file without losing data?


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-2-find-and-merge-duplicates)
- [How to verify email addresses syntax](#email-syntax-analysis)
- [How to check if emails are from disposable providers](#check-disposable-providers)
- [How to ensure email domains exist](#check-domain-mx-records)

Datablist can be used without registration to [view and edit CSV files](/csv-editor). **However, the email verification service that we'll use later require an account.**

👉  [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: Import email addresses

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

<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>
</aside>


In [Datablist](https://app.datablist.com), click the **+** to create a new collection. Give it a name (and an icon 😍).

### Import your lists of 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](/learn/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. [CSV files](/csv-editor) are first-class citizens in Datablist.

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

In this example, we will use a demo contact CSV file with three columns: `First Name`, `Last Name`, `email`. [Download the demo file here](https://storage.googleapis.com/public_datablist/website/guides/clean-contact-list/datablist-contact-list.csv).

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.

Create a property with the **+** button for every CSV column. On property creation, select the data type for each column. **Select Email for the email address column so Datablist can perform data integrity analysis.**

<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/import-contact-list.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Create a new collection and import CSV</div>
</div>



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


### Import other contact lists if needed

If you are building an email collection from several sources, just import all your lists into the same collection. Just ensure all the list are mapped to the same properties.


## Step 2: Find and merge duplicates

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


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.

With all your email addresses in one place. The second step is to [remove or merge duplicates entries](/features/duplicates-remover).

Use Datablist "Duplicates Finder" features by clicking on the "Duplicates" button in the collection header (on the right).

In the configuration screen, you need to select how duplicates will be checked:

- **All Properties** - Look for full items similarity: Two items would be considered similar when all of their properties match.
- **Selected properties** - Two items would be considered similar when all of their properties match.

In our example, two contacts are duplicates if they share the same email address. So, pick the **Selected Properties** mode and select the **Email** property.

Run the duplicates check to see a preview of all duplicates found in the collection.

If duplicates are found, three actions are available:
- remove duplicate items
- edit them
- merge (and consolidate) contacts.

To merge duplicate contacts, click on the "Merge Items" button on the left of every duplicate contacts group.

![Merge contacts](/howto_images/clean-emails/merge-duplicates.png#2170x528)

Datablist has a merging tool. On the right, a "Primary Item" is shown and on the left the remaining duplicate contacts are called "Secondary Items". Datablist elects the contact with the most data as "Primary item".

![Eliminate duplicate rows in a CSV](/howto_images/clean-emails/merge-duplicate-rows-modal.png#1908x1162)

When possible, property values from secondary items are auto selected to be merged into the primary items. If several values conflict, you will have to make a decision and select which value to keep.

If the resulting "Primary item" suits you, click the **Merge** button to confirm the merge process. All the secondary items will be deleted to keep only one combined item.

Once all the duplicates have been processed, go back to the collection.


## Step 3: Free email list cleaning

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


> **Notes**<br />
> **You must be register to use the email verification service.** [Sign up (it's free)](https://app.datablist.com/auth/signup). If you already have an anonymous collection, import it into your workspace.


### 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
        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 4: 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
```

It is possible to perform a join operation and add the `Unsubscribed` information to your contact list.

To do so, go to the properties configuration modal and select "Do no allow duplicate values" on the Email property. 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.

<div class="content-video">
    <video
        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-uniqueness.mp4" type="video/mp4" />
    </video>
    <div class="text-center">Define unique option for Email property</div>
</div>

Then, simply import your unsubscribed email list in the Datablist collection using a CSV file or copy/pasting.

> Don't forget to **create and map** the "Unsubscribed" column during the import.


## Extra Step: Automatic merge duplicates during import

To prevent and merge duplicates automatically during the import process. Check the "do not allow duplicate values" option on the Email property ([see video on step 4](#step-4-remove-unsubscribed-emails)). With this property option, Datablist will automatically deduplicate and merge your contacts on data import.

During the import stepper, a merging option will be shown to configure how Datablist must deal with duplicates.

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

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

---

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


Learn how to remove duplicate emails, check email addresses syntax and remove unreachable or disposable emails. And for free!

Tutorial to clean an email addresses listing online for free

Import email addresses

Merge duplicate email addresses

Free email list cleaning

Remove unsubscribed emails

How to clean an email list for free


[CSV files](/learn/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)
- [Dedupe rows in a CSV](/features/duplicates-remover)

 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.


## Merge 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 or merge them to consolidate their data and remove the duplicates.

Datablist is great to perform simple data operations that are not possible with spreadsheets. Use it when you are looking for a great [online CSV editor](/csv-editor).

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

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

1. [Load your CSV files into a Datablist collection](#step-1-load-your-csv-files-into-a-datablist-collection)
2. [Select the properties to perform the duplicates check](#step-2-find-duplicates)
3. [Merge or delete the duplicates](#step-3-merge-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, 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.

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

<div class="content-video">
    <video muted loop playsinline controls 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/Excel" process to import your other CSV files or Excel files into the same collection.


## Step 2: Find duplicates

After loading the CSV files, the second step is to search for duplicate values. 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 duplicate items 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.
- **[Merge duplicates](#step-3-merge-duplicates)** - Merge values from secondary items to a selected primary 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>

## Step 3: Merge duplicates

Usually, you want to merge all your CSV duplicate rows into a single item and consolidate the data. Thus, merging duplicates without losing data in the process.

To merge duplicates, click on the "Merge Items" button on the left of every duplicates group.

![Merge duplicates](/howto_images/remove-duplicates/merge-duplicates.png#2170x528)

It opens a merging tool. On the right, a "Primary Item" is shown and on the left the remaining duplicate items are called "Secondary Items".
Datablist elects the item with the most data as "Primary item".

![Eliminate duplicate rows in a CSV](/howto_images/remove-duplicates/merge-duplicate-rows-modal.png#1908x1162)

When possible, property values from secondary items are auto selected to be merged into the primary items. If several values conflict, you will have to make a decision and select which value to keep.

If the resulting "Primary item" suits you, click the **Merge** button to confirm the merge process. All the secondary items will be deleted to keep only one combined item.


## Export to CSV if needed

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

![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).


Save time with our online CSV editor and our Duplicates Finder. Automatically merge duplicate items and resolve conflicting values with our Merging Assistant.

Dedupe CSV files online

Load CSV files

Merge duplicates

How to remove CSV duplicates online


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](/learn/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](/learn/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 💪.


Your step-by-step guide to combine several CSV files by a unique column. Use our CSV online editor without signing up and for free.

Guides

How to translate CSV files online for free

How to scrape Facebook group members and enrich them with LinkedIn profiles

How to merge duplicate rows in an Excel file without losing data?

How to clean an email list for free

How to remove CSV duplicates online

How to join CSV files by a unique identifier