Comma Separated Value(s), also known as CSV, is a format to store structured data using text files.
The CSV format is not standardized, so various implementations exist.
In 2005, <a href="https://www.internetsociety.org/" target="_blank" rel="nofollow">the Internet Society</a> published <a href="https://www.ietf.org/rfc/rfc4180.txt" target="_blank" rel="nofollow">guidelines for creating CSV files</a> They wrote down best practices to structure and process CSV data.
From those guidelines and giving the lack of standardization, the header line is optional in a CSV file.
When present, the header line must be the first line in the file and must contain the same number of fields as the records. Header and records lines must use the same field delimiters.
Example:
<pre><code>column_1, column2, column3
aaa, bbb, ccc
111, 222, 333
</code></pre>


Comma Separated Value(s), also known as CSV, is a format to store structured data using text files.
The CSV format is not standardized, so various implementations exist.

In 2005, [the Internet Society](https://www.internetsociety.org/) published [guidelines for creating CSV files](https://www.ietf.org/rfc/rfc4180.txt) They wrote down best practices to structure and process CSV data.

From those guidelines and giving the lack of standardization, the header line is optional in a CSV file.

When present, the header line must be the first line in the file and must contain the same number of fields as the records. Header and records lines must use the same field delimiters.

Example:

```
column_1, column2, column3
aaa, bbb, ccc
111, 222, 333
```


Comma Separated Value(s), also known as CSV, is a format to store structured data using text files.
The CSV format is not standardized, so various implementations exist.
In 2005, the Internet Society published guidelines for creating CSV files They wrote down best practices to structure and process CSV data.
From those guidelines and giving the lack of standardization, the header line is optional in a CSV file.
When present, the header line must be the first line in the file and must contain the same number of fields as the records. Header and records lines must use the same field delimiters.
Example:
column_1, column2, column3
aaa, bbb, ccc
111, 222, 333


Do I need to have a header line in my Comma Separated Value file?

CSV stands for "Comma Separated Value(s)" and 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.
Unfortunately, the CSV file format is not standardized. Encoding format, delimiters, or escaping rules might differ between implementations. As a result, most applications accepting CSV files as data sources have different options to read the CSV data.
To view the content of an CSV file, you can uses an online <a href="/csv-editor">CSV editor</a> that is compatible with CSV exported by most applications.


CSV stands for "Comma Separated Value(s)" and 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.

Unfortunately, the CSV file format is not standardized. Encoding format, delimiters, or escaping rules might differ between implementations. As a result, most applications accepting CSV files as data sources have different options to read the CSV data.

To view the content of an CSV file, you can uses an online [CSV editor](/csv-editor) that is compatible with CSV exported by most applications.


CSV stands for &ampquot;Comma Separated Value(s)&ampquot; and 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 &ampquot;;&ampquot; 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.
Unfortunately, the CSV file format is not standardized. Encoding format, delimiters, or escaping rules might differ between implementations. As a result, most applications accepting CSV files as data sources have different options to read the CSV data.
To view the content of an CSV file, you can uses an online CSV editor that is compatible with CSV exported by most applications.


What is a CSV file?

A CSV file is a text file used to store structured data. The CSV format is used to transfer structured data between applications or to export data in order to perform data analytics.
The first use case is data transfer. Applications such as e-commerce software, mailing list providers, or CRM deal can import and export data in the CSV format. In e-commerce software, it is common to import a CSV containing the product catalog. When managing a mailing list, importing a CSV with all the email addresses is faster than adding each contact one by one.
The second use case is data analytics. The CSV format is flat and is perfectly compatible with spreadsheet or data analytics tools. As a result, it is a good source to analyze and visualize data. A CSV file has no size limit and can contain millions or more of records. It is a good file format to use in Big Data.


A CSV file is a text file used to store structured data. The CSV format is used to transfer structured data between applications or to export data in order to perform data analytics.

The first use case is data transfer. Applications such as e-commerce software, mailing list providers, or CRM deal can import and export data in the CSV format. In e-commerce software, it is common to import a CSV containing the product catalog. When managing a mailing list, importing a CSV with all the email addresses is faster than adding each contact one by one.

The second use case is data analytics. The CSV format is flat and is perfectly compatible with spreadsheet or data analytics tools. As a result, it is a good source to analyze and visualize data. A CSV file has no size limit and can contain millions or more of records. It is a good file format to use in Big Data.


A CSV file is a text file used to store structured data. The CSV format is used to transfer structured data between applications or to export data in order to perform data analytics.
The first use case is data transfer. Applications such as e-commerce software, mailing list providers, or CRM deal can import and export data in the CSV format. In e-commerce software, it is common to import a CSV containing the product catalog. When managing a mailing list, importing a CSV with all the email addresses is faster than adding each contact one by one.
The second use case is data analytics. The CSV format is flat and is perfectly compatible with spreadsheet or data analytics tools. As a result, it is a good source to analyze and visualize data. A CSV file has no size limit and can contain millions or more of records. It is a good file format to use in Big Data.


When to use a CSV file?

Dedupe is a synonym for data deduplication or de-duplicate. Dedupe is the process of eliminating duplicate entries from a data set.
The term is used both in computing and for non-technical people.
In computing, deduping is performed in software development to remove duplicates in a list. Still with computing, data deduplication is used with files too. In a system that store files, deduplication is a technique to list all similar files and their reference in documents, emails, etc., and to remove all redundant files to keep only one version and have all references point to those single files.
Dedupe is also used for non-technical people to define the action of finding and merging redundant duplicates. A good example is CRM deduplication. CRM applications store contact information for leads, customers, etc. Several CRM records can represent the same person or business and hold different and complementary data. In this example, <a href="/features/duplicates-remover">merging duplicates</a> in the CRM is important to build a single view for every user. It also lower software cost as it reduces the number of items to store in a database.
<a href="/how-to/remove-csv-duplicates"><img src="/learn_images/csv/merge-duplicate-rows-modal.png#1732x1060" alt="Merging tool to dedupe contacts"></a>


Dedupe is a synonym for **data deduplication** or **de-duplicate**. Dedupe is the process of eliminating duplicate entries from a data set.

The term is used both in **computing** and for **non-technical people**.

**In computing**, deduping is performed in software development to remove duplicates in a list. Still with computing, data deduplication is used with files too. In a system that store files, deduplication is a technique to list all similar files and their reference in documents, emails, etc., and to remove all redundant files to keep only one version and have all references point to those single files.

**Dedupe is also used for non-technical people to define the action of finding and merging redundant duplicates.** A good example is CRM deduplication. CRM applications store contact information for leads, customers, etc. Several CRM records can represent the same person or business and hold different and complementary data. In this example, [merging duplicates](/features/duplicates-remover) in the CRM is important to build a single view for every user. It also lower software cost as it reduces the number of items to store in a database.

[![Merging tool to dedupe contacts](/learn_images/csv/merge-duplicate-rows-modal.png#1732x1060)](/how-to/remove-csv-duplicates)


Dedupe is a synonym for data deduplication or de-duplicate. Dedupe is the process of eliminating duplicate entries from a data set.
The term is used both in computing and for non-technical people.
In computing, deduping is performed in software development to remove duplicates in a list. Still with computing, data deduplication is used with files too. In a system that store files, deduplication is a technique to list all similar files and their reference in documents, emails, etc., and to remove all redundant files to keep only one version and have all references point to those single files.
Dedupe is also used for non-technical people to define the action of finding and merging redundant duplicates. A good example is CRM deduplication. CRM applications store contact information for leads, customers, etc. Several CRM records can represent the same person or business and hold different and complementary data. In this example, merging duplicates in the CRM is important to build a single view for every user. It also lower software cost as it reduces the number of items to store in a database.
Merging tool to dedupe contacts


What tools to dedupe a CSV file?

Join and Merge are two operations to combine data from several files. When merging, you are combining several files with the same structure into a single listing.
When joining, you are combining several files with different data structure but with at least one common field. This common field will be used as a key to combine the data and generate a single listing combining the fields from all the files.
<h2 id="user-content-merge-and-join-examples"><a href="#merge-and-join-examples" aria-hidden="true" tabindex="-1"></a>Merge and Join examples</h2>
<h3 id="user-content-merge-example"><a href="#merge-example" aria-hidden="true" tabindex="-1"></a>Merge example</h3>
Let's say you have a File A:
<pre><code> firstName | lastName | jobTitle
 Jean | Doe | CEO
 Morgan | Stanlet | Marketing Officer
</code></pre>
And a File B:
<pre><code> firstName | lastName | jobTitle
 Joe | Kanigan | Finance
 Marie | Filman | Dev
</code></pre>
Then, the resulting of a merge operation would be:
<pre><code> firstName | lastName | jobTitle
 Jean | Doe | CEO
 Morgan | Stanlet | Marketing Officer
 Joe | Kanigan | Finance
 Marie | Filman | Dev
</code></pre>
<h3 id="user-content-join-example"><a href="#join-example" aria-hidden="true" tabindex="-1"></a>Join example</h3>
Now, let's look at the join operation. When performing a join, your files can have different structures but must have a common identifier that will be used to combine the data together.
We have a File C:
<pre><code> email | jobTitle
 jean.doe@gmail.com | CEO
 morgan@gmail.com | Marketing Officer
</code></pre>
Than we want to join with a File D:
<pre><code> email | City
 jean.doe@gmail.com | Paris
 morgan@gmail.com | New York
</code></pre>
The resulting join operation using the <code>email</code> identifier is
<pre><code> email | jobTitle | City
 jean.doe@gmail.com | CEO | Paris
 morgan@gmail.com | Marketing Officer | New York
</code></pre>
A very common data manipulation task is to bring two or more sets of data together based on a common key.


Join and Merge are two operations to combine data from several files. When merging, you are combining several files **with the same structure** into a single listing.

When joining, you are combining several files **with different data structure** but with at least one common field. This common field will be used as a key to combine the data and generate a single listing combining the fields from all the files.


## Merge and Join examples

### Merge example

Let's say you have a *File A*:

```
  firstName |    lastName    |  jobTitle
    Jean    |       Doe      |  CEO
   Morgan   |     Stanlet    |  Marketing Officer
```


And a *File B*:

```
  firstName |    lastName    |  jobTitle
    Joe     |     Kanigan    |  Finance
   Marie    |     Filman     |  Dev
```

Then, the resulting of a merge operation would be:

```
  firstName |    lastName    |  jobTitle
    Jean    |       Doe      |  CEO
   Morgan   |     Stanlet    |  Marketing Officer
    Joe     |     Kanigan    |  Finance
   Marie    |     Filman     |  Dev
```


### Join example

Now, let's look at the join operation. When performing a join, your files can have different structures but must have a common identifier that will be used to combine the data together.

We have a *File C*:

```
          email           |      jobTitle
    jean.doe@gmail.com    |        CEO
     morgan@gmail.com     |    Marketing Officer
```

Than we want to join with a *File D*:

```
          email           |         City
    jean.doe@gmail.com    |        Paris
     morgan@gmail.com     |       New York
```

The resulting join operation using the `email` identifier is

```
          email           |      jobTitle         |       City
    jean.doe@gmail.com    |        CEO            |       Paris
     morgan@gmail.com     |    Marketing Officer  |      New York
```

A very common data manipulation task is to bring two or more sets of data together based on a common key.


Join and Merge are two operations to combine data from several files. When merging, you are combining several files with the same structure into a single listing.
When joining, you are combining several files with different data structure but with at least one common field. This common field will be used as a key to combine the data and generate a single listing combining the fields from all the files.
Merge and Join examples
Merge example
Let&amp#39;s say you have a File A:
  firstName |    lastName    |  jobTitle
    Jean    |       Doe      |  CEO
   Morgan   |     Stanlet    |  Marketing Officer
And a File B:
  firstName |    lastName    |  jobTitle
    Joe     |     Kanigan    |  Finance
   Marie    |     Filman     |  Dev
Then, the resulting of a merge operation would be:
  firstName |    lastName    |  jobTitle
    Jean    |       Doe      |  CEO
   Morgan   |     Stanlet    |  Marketing Officer
    Joe     |     Kanigan    |  Finance
   Marie    |     Filman     |  Dev
Join example
Now, let&amp#39;s look at the join operation. When performing a join, your files can have different structures but must have a common identifier that will be used to combine the data together.
We have a File C:
          email           |      jobTitle
    jean.doe@gmail.com    |        CEO
     morgan@gmail.com     |    Marketing Officer
Than we want to join with a File D:
          email           |         City
    jean.doe@gmail.com    |        Paris
     morgan@gmail.com     |       New York
The resulting join operation using the email identifier is
          email           |      jobTitle         |       City
    jean.doe@gmail.com    |        CEO            |       Paris
     morgan@gmail.com     |    Marketing Officer  |      New York
A very common data manipulation task is to bring two or more sets of data together based on a common key.


<ol>
<li>Open the Terminal. (on MacOs, you can use Spotlight with keyboard shortcut ⌘ + Space)
<img src="/learn_images/csv/words-count/open-terminal.png#1675x607" alt="Open Terminal using Mac Spotlight"></li>
<li>Move terminal working directory to the folder containing your CSV file. Use ls to show current directory files and folders and cd to move inside a folder.</li>
<li>Enter the following command. Replace filepath with your CSV file path:</li>
</ol>
<pre><code>wc -l filepath.csv
</code></pre>
<ol start="4">
<li>The line count will be printed before the file name
<img src="/learn_images/csv/words-count/word-count.png#1448x518" alt="Word Count Command"></li>
</ol>
The wc command is used to count the number of words or lines (with the -l option) in a file. In a CSV file, each line is a data record. Counting the number of rows is an easy way to have the number of records in your CSV file.
<h2 id="user-content-get-the-number-of-rows-containing-a-keyword-in-a-csv-file"><a href="#get-the-number-of-rows-containing-a-keyword-in-a-csv-file" aria-hidden="true" tabindex="-1"></a>Get the number of rows containing a keyword in a CSV file</h2>
If you want to filter rows in your CSV file and count the matching lines, combine wc with the command grep.
<pre><code>grep "keyword" filepath.csv | wc -l
</code></pre>
<img src="/learn_images/csv/words-count/matching-rows-count.png#1336x518" alt="Filter and Count Lines">
You can also search for multiple keywords with grep using a regular expression (the -E option is for --extended-regexp). Any line with at least one of the keywords will be counted.
<pre><code>grep -E "keyword1|keyword2" filepath.csv | wc -l
</code></pre>
<img src="/learn_images/csv/words-count/sum-rows-multiple-patterns.png#1336x462" alt="Filter and Count Lines with multiple keywords">
By default, grep is case sensitive (uppercase and lowercase characters are distinct). To ignore case in the search, add the -i (for --ignore-case) in your command:
<pre><code>grep -i "keyword" filepath.csv | wc -l
</code></pre>
or
<pre><code>grep -i -E "keyword1|keyword2" filepath.csv | wc -l
</code></pre>
<h2 id="user-content-get-the-number-of-rows-of-multiple-csv-files"><a href="#get-the-number-of-rows-of-multiple-csv-files" aria-hidden="true" tabindex="-1"></a>Get the number of rows of multiple CSV files</h2>
To have the sum of the rows of all the CSV files in the current folder, use the following command:
<pre><code>cat *.csv | wc -l
</code></pre>
<img src="/learn_images/csv/words-count/sum-rows-csv-files.png#1336x518" alt="Get the sum of all csv files">
To view the content of your CSV file, you can uses an online <a href="/csv-editor">CSV editor</a>.



 1. Open the Terminal. (on MacOs, you can use Spotlight with keyboard shortcut **⌘** + **Space**)
    ![Open Terminal using Mac Spotlight](/learn_images/csv/words-count/open-terminal.png#1675x607)
 2. Move terminal working directory to the folder containing your CSV file. Use **ls** to show current directory files and folders and **cd** to move inside a folder.
 3. Enter the following command. Replace *filepath* with your CSV file path:
 ```
 wc -l filepath.csv
 ```
 4. The line count will be printed before the file name
 ![Word Count Command](/learn_images/csv/words-count/word-count.png#1448x518)


 The **wc** command is used to count the number of words or lines (with the **-l** option) in a file. In a CSV file, each line is a data record. Counting the number of rows is an easy way to have the number of records in your CSV file.


## Get the number of rows containing a keyword in a CSV file

If you want to filter rows in your CSV file and count the matching lines, combine **wc** with the command **grep**.

```
grep "keyword" filepath.csv | wc -l
```

![Filter and Count Lines](/learn_images/csv/words-count/matching-rows-count.png#1336x518)

You can also search for multiple keywords with **grep** using a regular expression (the **-E** option is for **--extended-regexp**). Any line with at least one of the keywords will be counted.

```
grep -E "keyword1|keyword2" filepath.csv | wc -l
```

![Filter and Count Lines with multiple keywords](/learn_images/csv/words-count/sum-rows-multiple-patterns.png#1336x462)

By default, **grep** is case sensitive (uppercase and lowercase characters are distinct). To ignore case in the search, add the **-i** (for --ignore-case) in your command:

```
grep -i "keyword" filepath.csv | wc -l
```
or
```
grep -i -E "keyword1|keyword2" filepath.csv | wc -l
```


## Get the number of rows of multiple CSV files

To have the sum of the rows of all the CSV files in the current folder, use the following command:

```
cat *.csv | wc -l
```

![Get the sum of all csv files](/learn_images/csv/words-count/sum-rows-csv-files.png#1336x518)



To view the content of your CSV file, you can uses an online [CSV editor](/csv-editor).


Open the Terminal. (on MacOs, you can use Spotlight with keyboard shortcut ⌘ + Space)
Open Terminal using Mac Spotlight
Move terminal working directory to the folder containing your CSV file. Use ls to show current directory files and folders and cd to move inside a folder.
Enter the following command. Replace filepath with your CSV file path:wc -l filepath.csv

The line count will be printed before the file name
Word Count Command

 The wc command is used to count the number of words or lines (with the -l option) in a file. In a CSV file, each line is a data record. Counting the number of rows is an easy way to have the number of records in your CSV file.
Get the number of rows containing a keyword in a CSV file
If you want to filter rows in your CSV file and count the matching lines, combine wc with the command grep.
grep &quotkeyword&quot filepath.csv | wc -l
Filter and Count Lines
You can also search for multiple keywords with grep using a regular expression (the -E option is for --extended-regexp). Any line with at least one of the keywords will be counted.
grep -E &quotkeyword1|keyword2&quot filepath.csv | wc -l
Filter and Count Lines with multiple keywords
By default, grep is case sensitive (uppercase and lowercase characters are distinct). To ignore case in the search, add the -i (for --ignore-case) in your command:
grep -i &quotkeyword&quot filepath.csv | wc -l
or
grep -i -E &quotkeyword1|keyword2&quot filepath.csv | wc -l
Get the number of rows of multiple CSV files
To have the sum of the rows of all the CSV files in the current folder, use the following command:
cat *.csv | wc -l
Get the sum of all csv files
To view the content of your CSV file, you can uses an online CSV editor.


Count the number of rows of a CSV file on Linux or Mac OS Terminal

CSV files are simple text files. A way to look at it is to use a text editor. But CSV files contain structured data and can be hard to navigate with a text editor.
The easier way to open a CSV file is using a spreadsheet application.
Microsoft Excel, Apple Numbers, or Google Sheets can open <code>.csv</code> files. Once open, you will be able to view and edit your data directly in the spreadsheet application.
<img src="/learn_images/csv/google_sheet_csv.png#1616x886" alt="CSV file in Google Sheets">
However, spreadsheet apps are designed for accounting and lack common features when it comes to structured data management. In <a href="https://www.datablist.com/" target="_blank" rel="nofollow">Datablist</a>, CSV files are first-class citizens! Open, define data types, and manage your data directly in our <a href="https://www.datablist.com/csv-editor" target="_blank" rel="nofollow">CSV Editor</a>.
Larger files (more than 1 million rows) require specific tools. For those files, use data analytics software, big data tools, or custom development to process your files.


CSV files are simple text files. A way to look at it is to use a text editor. But CSV files contain structured data and can be hard to navigate with a text editor.

The easier way to open a CSV file is using a spreadsheet application.
Microsoft Excel, Apple Numbers, or Google Sheets can open `.csv` files. Once open, you will be able to view and edit your data directly in the spreadsheet application.

![CSV file in Google Sheets](/learn_images/csv/google_sheet_csv.png#1616x886)

However, spreadsheet apps are designed for accounting and lack common features when it comes to structured data management. In [Datablist](https://www.datablist.com/), CSV files are first-class citizens! Open, define data types, and manage your data directly in our [CSV Editor](https://www.datablist.com/csv-editor).

**Larger files (more than 1 million rows) require specific tools.** For those files, use data analytics software, big data tools, or custom development to process your files.


CSV files are simple text files. A way to look at it is to use a text editor. But CSV files contain structured data and can be hard to navigate with a text editor.
The easier way to open a CSV file is using a spreadsheet application.
Microsoft Excel, Apple Numbers, or Google Sheets can open .csv files. Once open, you will be able to view and edit your data directly in the spreadsheet application.
CSV file in Google Sheets
However, spreadsheet apps are designed for accounting and lack common features when it comes to structured data management. In Datablist, CSV files are first-class citizens! Open, define data types, and manage your data directly in our CSV Editor.
Larger files (more than 1 million rows) require specific tools. For those files, use data analytics software, big data tools, or custom development to process your files.


What tools to open a CSV file?

Few <a href="/csv-editor">CSV editors</a> can manage large CSV files. Spreadsheet tools like Excel and Google Sheet break before reaching the million of lines. And dedicated tools target data scientists so they are expensive and complex to use.
A free alternative is to use your terminal and manage your big CSV file using the command line.
A CSV file is a text file, with each line being a data record. And each record is made of fields separated by commas (or sometimes by semicolons ";" or tabular keys). Because a CSV file is a text file and each line is a record, you can use Linux/Unix commands for manipulating texts.
The command line might seem scary but they are also very powerful. I'm listed here the command I use to search in big CSV files.
<blockquote>
Note: All the commands listed bellow are just reading your files. They don't overwrite or delete your CSV files.
</blockquote>
A summary of the commands listed on this page:
<ul>
<li><a href="#searching-in-a-big-csv-file-for-a">Searching in a CSV file for a single keyword</a></li>
<li><a href="#searching-in-a-big-csv-file-for-multiple-keywords">Searching in a CSV file for multiple keywords</a></li>
<li><a href="#how-to-count-the-number-of-matching-rows">How to count the number of matching rows in a big CSV file</a></li>
</ul>
Before moving to the commands, open your Terminal. On MacOs, you can use Spotlight with keyboard shortcut ⌘ + Space and type Terminal. Then hit "Enter"/"Return" to open the Terminal.
<img src="/learn_images/csv/words-count/open-terminal.png#1675x607" alt="Open Terminal using Mac Spotlight">
<h2 id="user-content-searching-in-a-big-csv-file-for-a-single-keyword"><a href="#searching-in-a-big-csv-file-for-a-single-keyword" aria-hidden="true" tabindex="-1"></a>Searching in a big CSV file for a single keyword</h2>
To perform a simple search and list all rows that match a specific keyword, use <a href="#grep">grep</a>.
<pre><code>grep "keyword" filepath.csv
</code></pre>
To be case insensitive, add the -i option.
<pre><code>grep -i "keyword" filepath.csv
</code></pre>
<img src="/learn_images/csv/search-big-csv-file-terminal/simple-search-case-insensitive.png#916x406=.with-shadow.rounded-3.mb-4" alt="Search CSV for a single keyword">
In my example, I search in a companies dataset the lines matching the keyword airtable.com. I get the two matching rows. One for the company with the website airtable.com (the good one), and another for chinaairtable.com (unexpected).
<h4 id="user-content-search-for-whole-keyword"><a href="#search-for-whole-keyword" aria-hidden="true" tabindex="-1"></a>Search for whole keyword</h4>
To avoid unexpected results like that, you can switch to Regular Expression (RegEx) and search for the whole keyword.
By adding the -E option to grep, I tell grep to interpret the pattern as a regular expression. And I add \b before and after my keyword. \b is a RegEx operator that means word boundary.
<pre><code>grep -E -i "\bkeyword\b" filepath.csv
</code></pre>
<img src="/learn_images/csv/search-big-csv-file-terminal/simple-search-whole-word.png#986x322=.with-shadow.rounded-3.mb-4" alt="Search CSV for a single keyword with word boundary">
<h4 id="user-content-search-terms-starting-with-a-keyword"><a href="#search-terms-starting-with-a-keyword" aria-hidden="true" tabindex="-1"></a>Search terms starting with a keyword</h4>
By using the same RegEx operator \b at the beginning of your pattern, you are searching all the lines with a word starting with your keyword.
<pre><code>grep -E -i "\bkeyword" filepath.csv
</code></pre>
<h4 id="user-content-search-terms-ending-with-a-keyword"><a href="#search-terms-ending-with-a-keyword" aria-hidden="true" tabindex="-1"></a>Search terms ending with a keyword</h4>
And by using it at the end of your pattern, you are searching the lines with a word ending with your keyword.
<pre><code>grep -E -i "keyword\b" filepath.csv
</code></pre>
<img src="/learn_images/csv/search-big-csv-file-terminal/simple-search-ending-width.png#1476x1106=.with-shadow.rounded-3.mb-4" alt="Search CSV for words ending with a keyword">
<h2 id="user-content-searching-in-a-big-csv-file-for-multiple-keywords"><a href="#searching-in-a-big-csv-file-for-multiple-keywords" aria-hidden="true" tabindex="-1"></a>Searching in a big CSV file for multiple keywords</h2>
When searching for multiple keywords in a CSV file, you can search for one of the keywords or for all the keywords.
<h3 id="user-content-searching-for-one-of-several-keywords-in-a-big-csv-file"><a href="#searching-for-one-of-several-keywords-in-a-big-csv-file" aria-hidden="true" tabindex="-1"></a>Searching for one of several keywords in a big CSV file</h3>
Grep with a RegEx is once again the perfect choice. In a RegEx, the symbol | means OR.
<pre><code>grep -i -E "keyword1|keyword2" filepath.csv
</code></pre>
And to search for whole keywords, we can add \b for each keyword.
<pre><code>grep -i -E "\bkeyword1\b|\bkeyword2\b" filepath.csv
</code></pre>
<img src="/learn_images/csv/search-big-csv-file-terminal/multiple-keywords-or.png#1252x434=.with-shadow.rounded-3.mb-4" alt="Search CSV for multiple keywords">
<h3 id="user-content-searching-for-all-the-keywords-in-a-big-csv-file"><a href="#searching-for-all-the-keywords-in-a-big-csv-file" aria-hidden="true" tabindex="-1"></a>Searching for all the keywords in a big CSV file</h3>
To search for lines in your CSV file that have all the requested keywords, you can use another command: awk. Awk is another command tool to deal with text files. It has more advanced features.
Awk also works with RegEx and interprets text enclosed in slashes (/) as regular expressions.
<pre><code>awk '/keyword1/ &#x26;&#x26; /keyword2/' filepath.csv
</code></pre>
Awk processes the file line by line. To be case insensitive, you can convert the line to lower text before searching your keywords:
<pre><code>awk '{lc=tolower($0)}; (lc ~ /keyword1/) &#x26;&#x26; (lc ~ /keyword2/)' filepath.csv
</code></pre>
Here, we take the current line $0 and create a variable lc containing the current line in lower case. Then we check for keywords in it.
In the screenshot below, I search in my CSV for a line with the keywords france AND google.
<img src="/learn_images/csv/search-big-csv-file-terminal/exact-multiple-keywords.png#1378x938=.with-shadow.rounded-3.mb-4" alt="Search CSV for all keywords">
<h2 id="user-content-how-to-count-the-number-of-matching-rows"><a href="#how-to-count-the-number-of-matching-rows" aria-hidden="true" tabindex="-1"></a>How to count the number of matching rows?</h2>
Several commands can be combined. An important command is wc for word count. It does a great job at counting words or lines in a text file.
Use the -l option to count lines in a CSV file or after another command.
For example, to count how many lines match a keyword, combine it with the <a href="#searching-in-a-big-csv-file-for-a-single-keyword">single keyword search</a>.
<pre><code>grep -i "keyword" filepath.csv | wc -l
</code></pre>
<img src="/learn_images/csv/search-big-csv-file-terminal/search-and-count.png#1028x350=.with-shadow.rounded-3.mb-4" alt="Count how many lines match a keyword">


Few [CSV editors](/csv-editor) can manage large CSV files. Spreadsheet tools like Excel and Google Sheet break before reaching the million of lines. And dedicated tools target data scientists so they are expensive and complex to use.

**A free alternative** is to use your terminal and manage your big CSV file using the command line.

A CSV file is a text file, with each line being a data record. And each record is made of fields separated by commas (or sometimes by semicolons ";" or tabular keys). Because a CSV file is a text file and each line is a record, you can use Linux/Unix commands for manipulating texts.

The command line might seem scary but they are also very powerful. I'm listed here the command I use to search in big CSV files.

> **Note**: All the commands listed bellow are just **reading** your files. They don't overwrite or delete your CSV files.

A summary of the commands listed on this page:

- [Searching in a CSV file for a single keyword](#searching-in-a-big-csv-file-for-a)
- [Searching in a CSV file for multiple keywords](#searching-in-a-big-csv-file-for-multiple-keywords)
- [How to count the number of matching rows in a big CSV file](#how-to-count-the-number-of-matching-rows)


Before moving to the commands, open your Terminal. On MacOs, you can use Spotlight with keyboard shortcut **⌘** + **Space** and type **Terminal**. Then hit "Enter"/"Return" to open the Terminal.

![Open Terminal using Mac Spotlight](/learn_images/csv/words-count/open-terminal.png#1675x607)


## Searching in a big CSV file for a single keyword


To perform a simple search and list all rows that match a specific keyword, use [grep](#grep).

```
grep "keyword" filepath.csv
```

To be case insensitive, add the **-i** option.

```
grep -i "keyword" filepath.csv
```

![Search CSV for a single keyword](/learn_images/csv/search-big-csv-file-terminal/simple-search-case-insensitive.png#916x406=.with-shadow.rounded-3.mb-4)


In my example, I search in a companies dataset the lines matching the keyword **airtable.com**. I get the two matching rows. One for the company with the website **airtable.com** (the good one), and another for **chinaairtable.com** (unexpected).

#### Search for whole keyword

To avoid unexpected results like that, you can switch to Regular Expression (RegEx) and search for the whole keyword.

By adding the **-E** option to **grep**, I tell grep to interpret the pattern as a regular expression. And I add **\b** before and after my keyword. **\b** is a RegEx operator that means word boundary.

```
grep -E -i "\bkeyword\b" filepath.csv
```
![Search CSV for a single keyword with word boundary](/learn_images/csv/search-big-csv-file-terminal/simple-search-whole-word.png#986x322=.with-shadow.rounded-3.mb-4)


#### Search terms starting with a keyword

By using the same RegEx operator **\b** at the beginning of your pattern, you are searching all the lines with a word starting with your keyword.

```
grep -E -i "\bkeyword" filepath.csv
```

#### Search terms ending with a keyword

And by using it at the end of your pattern, you are searching the lines with a word ending with your keyword.

```
grep -E -i "keyword\b" filepath.csv
```

![Search CSV for words ending with a keyword](/learn_images/csv/search-big-csv-file-terminal/simple-search-ending-width.png#1476x1106=.with-shadow.rounded-3.mb-4)



## Searching in a big CSV file for multiple keywords

When searching for multiple keywords in a CSV file, you can search for **one of** the keywords or for **all** the keywords.

### Searching for one of several keywords in a big CSV file

Grep with a RegEx is once again the perfect choice. In a RegEx, the symbol **|** means OR.

```
grep -i -E "keyword1|keyword2" filepath.csv
```

And to search for whole keywords, we can add **\b** for each keyword.

```
grep -i -E "\bkeyword1\b|\bkeyword2\b" filepath.csv
```

![Search CSV for multiple keywords](/learn_images/csv/search-big-csv-file-terminal/multiple-keywords-or.png#1252x434=.with-shadow.rounded-3.mb-4)


### Searching for all the keywords in a big CSV file

To search for lines in your CSV file that have all the requested keywords, you can use another command: **awk**. Awk is another command tool to deal with text files. It has more advanced features.

Awk also works with RegEx and interprets text enclosed in slashes (**/**) as regular expressions.

```
awk '/keyword1/ && /keyword2/' filepath.csv
```

Awk processes the file line by line. To be case insensitive, you can convert the line to lower text before searching your keywords:

```
awk '{lc=tolower($0)}; (lc ~ /keyword1/) && (lc ~ /keyword2/)' filepath.csv
```

Here, we take the current line **$0** and create a variable **lc** containing the current line in lower case. Then we check for keywords in it.

In the screenshot below, I search in my CSV for a line with the keywords **france** AND **google**.

![Search CSV for all keywords](/learn_images/csv/search-big-csv-file-terminal/exact-multiple-keywords.png#1378x938=.with-shadow.rounded-3.mb-4)


## How to count the number of matching rows?

Several commands can be combined. An important command is **wc** for **word count**. It does a great job at counting words or lines in a text file.

Use the **-l** option to count lines in a CSV file or after another command.

For example, to count how many lines match a keyword, combine it with the [single keyword search](#searching-in-a-big-csv-file-for-a-single-keyword).


```
grep -i "keyword" filepath.csv | wc -l
```

![Count how many lines match a keyword](/learn_images/csv/search-big-csv-file-terminal/search-and-count.png#1028x350=.with-shadow.rounded-3.mb-4)


Few CSV editors can manage large CSV files. Spreadsheet tools like Excel and Google Sheet break before reaching the million of lines. And dedicated tools target data scientists so they are expensive and complex to use.
A free alternative is to use your terminal and manage your big CSV file using the command line.
A CSV file is a text file, with each line being a data record. And each record is made of fields separated by commas (or sometimes by semicolons &ampquot;;&ampquot; or tabular keys). Because a CSV file is a text file and each line is a record, you can use Linux/Unix commands for manipulating texts.
The command line might seem scary but they are also very powerful. I&amp#39;m listed here the command I use to search in big CSV files.
Note: All the commands listed bellow are just reading your files. They don&amp#39;t overwrite or delete your CSV files.

A summary of the commands listed on this page:
Searching in a CSV file for a single keyword
Searching in a CSV file for multiple keywords
How to count the number of matching rows in a big CSV file

Before moving to the commands, open your Terminal. On MacOs, you can use Spotlight with keyboard shortcut ⌘ + Space and type Terminal. Then hit &ampquot;Enter&ampquot;/&ampquot;Return&ampquot; to open the Terminal.
Open Terminal using Mac Spotlight
Searching in a big CSV file for a single keyword
To perform a simple search and list all rows that match a specific keyword, use grep.
grep &quotkeyword&quot filepath.csv
To be case insensitive, add the -i option.
grep -i &quotkeyword&quot filepath.csv
Search CSV for a single keyword
In my example, I search in a companies dataset the lines matching the keyword airtable.com. I get the two matching rows. One for the company with the website airtable.com (the good one), and another for chinaairtable.com (unexpected).
Search for whole keyword
To avoid unexpected results like that, you can switch to Regular Expression (RegEx) and search for the whole keyword.
By adding the -E option to grep, I tell grep to interpret the pattern as a regular expression. And I add \b before and after my keyword. \b is a RegEx operator that means word boundary.
grep -E -i &quot\bkeyword\b&quot filepath.csv
Search CSV for a single keyword with word boundary
Search terms starting with a keyword
By using the same RegEx operator \b at the beginning of your pattern, you are searching all the lines with a word starting with your keyword.
grep -E -i &quot\bkeyword&quot filepath.csv
Search terms ending with a keyword
And by using it at the end of your pattern, you are searching the lines with a word ending with your keyword.
grep -E -i &quotkeyword\b&quot filepath.csv
Search CSV for words ending with a keyword
Searching in a big CSV file for multiple keywords
When searching for multiple keywords in a CSV file, you can search for one of the keywords or for all the keywords.
Searching for one of several keywords in a big CSV file
Grep with a RegEx is once again the perfect choice. In a RegEx, the symbol | means OR.
grep -i -E &quotkeyword1|keyword2&quot filepath.csv
And to search for whole keywords, we can add \b for each keyword.
grep -i -E &quot\bkeyword1\b|\bkeyword2\b&quot filepath.csv
Search CSV for multiple keywords
Searching for all the keywords in a big CSV file
To search for lines in your CSV file that have all the requested keywords, you can use another command: awk. Awk is another command tool to deal with text files. It has more advanced features.
Awk also works with RegEx and interprets text enclosed in slashes (/) as regular expressions.
awk &#39/keyword1/ &amp&amp /keyword2/&#39 filepath.csv
Awk processes the file line by line. To be case insensitive, you can convert the line to lower text before searching your keywords:
awk &#39{lc=tolower($0)}; (lc ~ /keyword1/) &amp&amp (lc ~ /keyword2/)&#39 filepath.csv
Here, we take the current line $0 and create a variable lc containing the current line in lower case. Then we check for keywords in it.
In the screenshot below, I search in my CSV for a line with the keywords france AND google.
Search CSV for all keywords
How to count the number of matching rows?
Several commands can be combined. An important command is wc for word count. It does a great job at counting words or lines in a text file.
Use the -l option to count lines in a CSV file or after another command.
For example, to count how many lines match a keyword, combine it with the single keyword search.
grep -i &quotkeyword&quot filepath.csv | wc -l
Count how many lines match a keyword


Search big CSV file using Linux or Mac OS Terminal

To convert a Numbers (<code>.numbers</code>) file into a <code>.csv</code> file, you can directly use the Apple Numbers app.
First, load your Numbers file file with the Apple Numbers app. Then, use the File -> Export To -> CSV menu action.
<img src="/learn_images/csv/export_apple_numbers_to_csv/apple_numbers_to_csv.png#1488x966" alt="File -> Export To -> CSV">
If needed, change the text encoding format in the "Advanced Options".
<blockquote>
Do not check the "Include table names" option! It adds a first row with the name of the table in your CSV file, which will be considered as a CSV header with only one column by most applications.
</blockquote>
<img src="/learn_images/csv/export_apple_numbers_to_csv/apple_numbers_csv_options.png#1410x826" alt="CSV Exporting Options">
Then, click the Next button to select a save location. Finally, click the Export button.


To convert a Numbers (`.numbers`) file into a `.csv` file, you can directly use the Apple Numbers app.

First, load your Numbers file file with the Apple Numbers app. Then, use the **File -> Export To -> CSV** menu action.

![File -> Export To -> CSV](/learn_images/csv/export_apple_numbers_to_csv/apple_numbers_to_csv.png#1488x966)

If needed, change the text encoding format in the "Advanced Options".

> Do not check the "Include table names" option! It adds a first row with the name of the table in your CSV file, which will be considered as a CSV header with only one column by most applications.

![CSV Exporting Options](/learn_images/csv/export_apple_numbers_to_csv/apple_numbers_csv_options.png#1410x826)

Then, click the **Next** button to select a save location. Finally, click the **Export** button.


To convert a Numbers (.numbers) file into a .csv file, you can directly use the Apple Numbers app.
First, load your Numbers file file with the Apple Numbers app. Then, use the File -&ampgt; Export To -&ampgt; CSV menu action.
File -&ampgt; Export To -&ampgt; CSV
If needed, change the text encoding format in the &ampquot;Advanced Options&ampquot;.
Do not check the &ampquot;Include table names&ampquot; option! It adds a first row with the name of the table in your CSV file, which will be considered as a CSV header with only one column by most applications.

CSV Exporting Options
Then, click the Next button to select a save location. Finally, click the Export button.


Export Numbers spreadsheet into a CSV file

Spreadsheet and database both store sets of data. However, each one has advantages and disadvantages and is suited to specific use cases.
A spreadsheet represents a digital worksheet and was invented for accounting tasks. In a spreadsheet, data is stored in a flat structure consisting of rows and columns, also known as tabular format. Spreadsheets are simple to use and require no technical knowledge. Because a spreadsheet is flexible and doesn't have a data schema, any value can be entered in any cell. The drawback of this flexibility is the lack of data schema. Spreadsheets cannot be used safely as a source of data for other applications as they cannot provide a strict structure.
Finally, spreadsheets are designed to handle a limited volume of data.
In databases, data is stored in tables, which have a defined structure. This data structure describes for each table, the attributes and data types expected. For example, attributes can be text, date, text, or relationship to another table. This data structure adds constraints and triggers errors directly when inserting a new record in the database. Thanks to this strict structure, other applications can rely on the data provided by a database.
Unlike spreadsheets, databases are better for storing large amounts of data and when several users or applications access the data simultaneously.


Spreadsheet and database both store sets of data. However, each one has advantages and disadvantages and is suited to specific use cases.

A spreadsheet represents a digital worksheet and was invented for accounting tasks. In a spreadsheet, data is stored in a flat structure consisting of rows and columns, also known as tabular format. Spreadsheets are simple to use and require no technical knowledge. Because a spreadsheet is flexible and doesn't have a data schema, any value can be entered in any cell. The drawback of this flexibility is the lack of data schema. **Spreadsheets cannot be used safely as a source of data for other applications as they cannot provide a strict structure**.
Finally, spreadsheets are designed to handle a limited volume of data.

In databases, data is stored in tables, which have a defined structure. This data structure describes for each table, the attributes and data types expected. For example, attributes can be text, date, text, or relationship to another table. This data structure adds constraints and triggers errors directly when inserting a new record in the database. **Thanks to this strict structure, other applications can rely on the data provided by a database.**
Unlike spreadsheets, databases are better for storing large amounts of data and when several users or applications access the data simultaneously.


Spreadsheet and database both store sets of data. However, each one has advantages and disadvantages and is suited to specific use cases.
A spreadsheet represents a digital worksheet and was invented for accounting tasks. In a spreadsheet, data is stored in a flat structure consisting of rows and columns, also known as tabular format. Spreadsheets are simple to use and require no technical knowledge. Because a spreadsheet is flexible and doesn&amp#39;t have a data schema, any value can be entered in any cell. The drawback of this flexibility is the lack of data schema. Spreadsheets cannot be used safely as a source of data for other applications as they cannot provide a strict structure.
Finally, spreadsheets are designed to handle a limited volume of data.
In databases, data is stored in tables, which have a defined structure. This data structure describes for each table, the attributes and data types expected. For example, attributes can be text, date, text, or relationship to another table. This data structure adds constraints and triggers errors directly when inserting a new record in the database. Thanks to this strict structure, other applications can rely on the data provided by a database.
Unlike spreadsheets, databases are better for storing large amounts of data and when several users or applications access the data simultaneously.


How spreadsheets and databases differ technically?

To convert a <code>.xlsx</code> or <code>.xls</code> file into a <code>.csv</code> file, Microsoft Excel is the perfect tool.
First, load your <code>.xlsx</code> or <code>.xls</code> file with Excel. Then, use the File -> Save As menu action.
<img src="/learn_images/csv/export_excel_to_csv/microsoft_excel_convert_to_csv_save_as.png#1488x966" alt="File -> Save As">
Choose where you want to save your CSV file and select CSV UTF-8 (Comma delimited) (.csv) in the File format dropdown.
<img src="/learn_images/csv/export_excel_to_csv/microsoft_excel_convert_to_csv_select_format.png#1378x892" alt="Select CSV in file formats">
Finally, click the Save button.


To convert a `.xlsx` or `.xls` file into a `.csv` file, Microsoft Excel is the perfect tool.

First, load your `.xlsx` or `.xls` file with Excel. Then, use the **File -> Save As** menu action.

![File -> Save As](/learn_images/csv/export_excel_to_csv/microsoft_excel_convert_to_csv_save_as.png#1488x966)

Choose where you want to save your CSV file and select **CSV UTF-8 (Comma delimited) (.csv)** in the File format dropdown.

![Select CSV in file formats](/learn_images/csv/export_excel_to_csv/microsoft_excel_convert_to_csv_select_format.png#1378x892)


Finally, click the **Save** button.


To convert a .xlsx or .xls file into a .csv file, Microsoft Excel is the perfect tool.
First, load your .xlsx or .xls file with Excel. Then, use the File -&ampgt; Save As menu action.
File -&ampgt; Save As
Choose where you want to save your CSV file and select CSV UTF-8 (Comma delimited) (.csv) in the File format dropdown.
Select CSV in file formats
Finally, click the Save button.


Export Microsoft Excel spreadsheet into a CSV file

When it comes to storing data online, spreadsheets and databases are two possible options. Understanding the differences between them is crucial to find the best tool for your task.
Spreadsheets (Excel and Google Sheets) are easy to use and inexpensive. They are the go-to solution when it comes to simple analytics and data manipulation. For example, spreadsheet tools offer analytics features to display charts or create pivot tables.
Databases are designed to handle larger datasets and structured data. They offer built-in data constraints to force field format or to avoid duplicate information. Databases have powerful query languages to search and filter their data. These features are powerful but add complexity compared to spreadsheets. As a result, databases have a steep learning curve.
If you need to connect to your data programmatically, databases are the go-to solution. Thanks to its data structure, a database ensure the data is valid before saving it.
If you need to store less than 1 million records and don't need to connect your data to an application, spreadsheets do the job.


When it comes to storing data online, spreadsheets and databases are two possible options. Understanding the differences between them is crucial to find the best tool for your task.

Spreadsheets (Excel and Google Sheets) are easy to use and inexpensive. They are the go-to solution when it comes to simple analytics and data manipulation. For example, spreadsheet tools offer analytics features to display charts or create pivot tables.

Databases are designed to handle larger datasets and structured data. They offer built-in data constraints to force field format or to avoid duplicate information. Databases have powerful query languages to search and filter their data. These features are powerful but add complexity compared to spreadsheets. As a result, databases have a steep learning curve.

If you need to connect to your data programmatically, databases are the go-to solution. Thanks to its data structure, a database ensure the data is valid before saving it.

If you need to store less than 1 million records and don't need to connect your data to an application, spreadsheets do the job.


When it comes to storing data online, spreadsheets and databases are two possible options. Understanding the differences between them is crucial to find the best tool for your task.
Spreadsheets (Excel and Google Sheets) are easy to use and inexpensive. They are the go-to solution when it comes to simple analytics and data manipulation. For example, spreadsheet tools offer analytics features to display charts or create pivot tables.
Databases are designed to handle larger datasets and structured data. They offer built-in data constraints to force field format or to avoid duplicate information. Databases have powerful query languages to search and filter their data. These features are powerful but add complexity compared to spreadsheets. As a result, databases have a steep learning curve.
If you need to connect to your data programmatically, databases are the go-to solution. Thanks to its data structure, a database ensure the data is valid before saving it.
If you need to store less than 1 million records and don&amp#39;t need to connect your data to an application, spreadsheets do the job.


Knowledge Center

CSV Manipulation

Do CSV files need headers?

What does CSV stand for?

What is a CSV file used for?

What does Dedupe mean?

What is the difference between join and merge?

Get the number of rows of a CSV file on Linux or Mac OS Terminal

How do I open a .csv file?

How to search in a big CSV file using Linux or Mac OS Terminal?

Spreadsheet

How to convert an Apple Numbers file to CSV?

What is the difference between a spreadsheet and a database?

How to convert an XLSX file to CSV with Microsoft Excel?

Should I use a spreadsheet or a database?