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

5 Answers 5

15

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
5
  • I'm using OS X 10.10. - Your tip worked like a charm! Thank you very much! ;)
    – YLeven
    Commented 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
    Commented 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
    Commented 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
    Commented 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
    Commented Sep 8, 2020 at 2:41
1

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

Assuming that semicolon (;) is the separator.

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

0

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

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

Assuming that "," is the separator.

0

This seems to work for me:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .