5

I'm dealing with a CSV file that contains over 2 million lines. Quite a big one.

I need to use grep (or any better way) to retrieve the entire row if the value of the second column matches 'jpn' or 'por'.

I tried using grep '<\jpn\>' || grep '<\por\>' file1.csv > file2.csv but had no success so far. The major issue being that 'por' is a common occurrence on the third column, which generates over a million of undesired rows.

Would any more experienced user be se kind and lend me some help?

Thanks in advance! :D

Improve this question

5 Answers 5

Reset to default
14

What Linux OS are you on? Try using egrep if you want to use regular expressions in systems whose grep version is outdated (e.g. Solaris).

Anyway, here is an awk solution:

awk -F, '$2 ~ /jpn|por/ {print}' file1.csv > file2.csv

Explanations:

  • awk for column-based operations
  • -F, to define the column separator/tokenizer, in this case I use a comma
  • $2 ~ /jpn|por/ tests column #2 with expression /jpn|por/
    • $2 is column #2
    • /jpn|por/ is a regular expression to match jpn or por
  • {print} specifies what the awk should output if it found a matching line
    • print to print the whole input line (alternatively, print $3 will just print column #3)
  • ... file1.csv specifies to read from an input file instead of stdin
Improve this answer
5
  • I'm using OS X 10.10. - Your tip worked like a charm! Thank you very much! ;)
    – YLeven
    Oct 17, 2014 at 23:24
  • Old question I know but, I was trying to filter my csv on those where the column 32 is TX: awk -F, '$32 ~ /TX/ {print}' input.csv > TEXAS_ONLY.csv. It mostly worked, yet a few don't make sense. I'm looking at one for example that's in AZ ...
    – nute
    Mar 27, 2020 at 9:48
  • @nute If it works on some lines, my guess is the data might be not clean. Try checking at the lines that does not make sense, and the lines above and below that line: whether there is an extra comma/separator in the data, and whether there are extra line-breaks or any non-printable characters in the data.
    – aff
    Mar 28, 2020 at 4:29
  • @aff this is awesome ! thanks for a detailed answer. If I want to use a regex expression to say "starts with jpn" and add two columns, can I do awk -F, '$1 ~ /^jpn|^por/ && $2 ~ /^jpn|^por/ {print}' file1.csv > file2.csv ?
    – vagabond
    Aug 28, 2020 at 3:57
  • @vagabond That expression will tell awk to print the lines if column 1 and column 2 both starts with either 'jpn' or 'por', if that's what you wanted.
    – aff
    Sep 8, 2020 at 2:41
1

grep '; jpn;\|; por;' /path/to/file1.csv > file2.csv

Assuming that semicolon (;) is the separator.

Improve this answer
1

I think grep is a limited solution for this problem, because it wouldn't consider different separators and escaped separators.

I'd suggest you check out TextQ (disclaimer - I'm its developer). It can import a big CSV file and allows you to manage its schema/structure.

You can perform queries via a UI Query Builder or via SQL (select, join, group by, etc).

Here is a video demo and more detailed tutorial.

Finally, you can export any query to a CSV file, which can be imported in MS Excel or others. You can get it from the Mac App Store or Microsoft Store (soon).

Improve this answer
0

I'm not sure, but maybe this could work:

grep '^[^,]*,\(por\|jpn\)\(,\|$\)' file1.csv > file2.csv

Assuming that "," is the separator.

Improve this answer
0

This seems to work for me:

egrep '^.?,(jpn|por),.*$' file1.csv > file2.csv
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.