To search a large CSV from a Linux or macOS terminal, use grep -F -i 'keyword' file.csv. It prints physical lines containing the keyword without loading the file into a spreadsheet. Add | wc -l to count those lines.
These commands read the file without changing it. If you plan to inspect the results in Numbers instead, check the Numbers row limit before importing the large file. They work best when each CSV record occupies one physical line. CSV fields can contain quoted line breaks; in that case grep and wc -l operate on lines, not complete records. Use a CSV-aware tool when you need reliable field matching or record counts. You can also filter CSV rows visually in Datablist when you need property conditions and a reviewed export.
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, press ⌘ + Space, type Terminal, then press Return. Use cd to enter the file's folder, or replace file.csv in the examples with its full path. To make smaller files after inspecting the data, use the Terminal CSV splitting commands only when each record occupies one physical line.
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 -F 'keyword' file.csv
To be case insensitive, add the -i option.
grep -F -i 'keyword' file.csv
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
For a whole word, add -w. Keep -F so a dot in a domain such as airtable.com is treated literally rather than as a regular expression wildcard. A whole-word match uses word boundaries, not CSV column boundaries; check the returned row if you need an exact field value.
grep -F -i -w 'airtable.com' file.csv
Search terms starting with a keyword
Use -E for a regular expression when you need a prefix match. The expression below starts at the beginning of a line or after a non-word character, then matches a word beginning with keyword.
grep -E -i '(^|[^[:alnum:]_])keyword' file.csv
Search terms ending with a keyword
For a word ending with keyword, place the boundary after the term:
grep -E -i 'keyword([^[:alnum:]_]|$)' file.csv
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 -E -i 'keyword1|keyword2' file.csv
For whole words, use -w. The alternatives remain regular expressions, so escape punctuation in either term.
grep -E -i -w 'keyword1|keyword2' file.csv
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/' file.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/)' file.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.
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 -F -i 'keyword' file.csv | wc -l
If the first line contains headers, wc -l counts it when it matches. For a CSV with quoted line breaks, the result is a count of matching physical lines rather than matching records.
For total-file counts and the header/final-newline checks, see count CSV rows in Terminal.