2

I am using Windows 7 64 bit

I have large .txt (800 mb plus) file containing following formate

the file is space separated

8232394 06774483 N 19850910 19870818 19910818 EXP. 
8309716 06774483 N 19850910 19870818 19910319 REM.
4687262 06908244 N 19860917 19870818 19990815 EXP. 
4687262 06908244 N 19860917 19870818 19990309 REM. 
4687262 06908244 N 19860917 19870818 19950221 M184 
4687262 06908244 N 19860917 19870818 19910108 M173 
4687262 06908244 N 19860917 19870818 19880802 ASPN 
4687263 06868897 N 19860527 19870818 19990128 M185
4687263 06868897 N 19860527 19870818 19950509 RMPN
4687263 06868897 N 19860527 19870818 19950509 ASPN 
4687263 06868897 N 19860527 19870818 19950119 M184 
4687263 06868897 N 19860527 19870818 19910311 ASPN 
4687263 06868897 N 19860527 19870818 19910124 M173 
4687264 06882047 N 19860703 19870818 19990815 EXP. 
4687264 06882047 N 19860703 19870818 19990309 REM. 
4687264 06882047 N 19860703 19870818 19950503 RMPN 
4687264 06882047 N 19860703 19870818 19950503 ASPN 
4687264 06882047 N 19860703 19870818 19950119 M184 
4687264 06882047 N 19860703 19870818 19910311 ASPN 
RE45781 14176526 N 20140210 20151027 20150929 ASPN 
RE45786 14260890 N 20140424 20151027 20150929 ASPN 
RE45790 14454285 Y 20140807 20151103 20151008 ASPN 
RE45793 13445791 N 20120412 20151103 20151006 ASPN 

I have another .txt file (small) containing following formate

4687264 
4687264 
4687264 
RE45781 
RE45786 
RE45790 
RE45793 

Now I want to extract lines from big file having match from the small file with respect to column 1 which will only contain lines which are present in small txt file

The result file should look like this

4687264 06882047 N 19860703 19870818 19990815 EXP. 
4687264 06882047 N 19860703 19870818 19990309 REM. 
4687264 06882047 N 19860703 19870818 19950503 RMPN 
4687264 06882047 N 19860703 19870818 19950503 ASPN 
4687264 06882047 N 19860703 19870818 19950119 M184 
4687264 06882047 N 19860703 19870818 19910311 ASPN 
RE45781 14176526 N 20140210 20151027 20150929 ASPN 
RE45786 14260890 N 20140424 20151027 20150929 ASPN 
RE45790 14454285 Y 20140807 20151103 20151008 ASPN 
RE45793 13445791 N 20120412 20151103 20151006 ASPN 

Is there any way?

I initially posted it on vim general forum as I found that vim is able to open large text but I Think This is bigger forum as it can also be done using other programmes.

VIM FORUM

  • In Excel you can use the AutoFilter, then copy/paste the visible cells. You could use VBA to populate the AutoFilter criteria. – Ron Rosenfeld Oct 24 '15 at 0:47
2

GREP

Using GNU grep you can do

$ grep -f small_list.txt big_file.txt
4687264 06882047 N 19860703 19870818 19990815 EXP.
4687264 06882047 N 19860703 19870818 19990309 REM.
4687264 06882047 N 19860703 19870818 19950503 RMPN
4687264 06882047 N 19860703 19870818 19950503 ASPN
4687264 06882047 N 19860703 19870818 19950119 M184
4687264 06882047 N 19860703 19870818 19910311 ASPN
RE45781 14176526 N 20140210 20151027 20150929 ASPN
RE45786 14260890 N 20140424 20151027 20150929 ASPN
RE45790 14454285 Y 20140807 20151103 20151008 ASPN
RE45793 13445791 N 20120412 20151103 20151006 ASPN

(tested on Linux after cutting and pasting your exact data)

If you are willing and able to install and use vim, grep should be no problem.

FINDSTR

You can also do this with the native findstr

C:> findstr /G:small_list.txt big_file.txt
4687264 06882047 N 19860703 19870818 19990815 EXP.
4687264 06882047 N 19860703 19870818 19990309 REM.
4687264 06882047 N 19860703 19870818 19950503 RMPN
4687264 06882047 N 19860703 19870818 19950503 ASPN
4687264 06882047 N 19860703 19870818 19950119 M184
4687264 06882047 N 19860703 19870818 19910311 ASPN
RE45781 14176526 N 20140210 20151027 20150929 ASPN
RE45786 14260890 N 20140424 20151027 20150929 ASPN
RE45790 14454285 Y 20140807 20151103 20151008 ASPN
RE45793 13445791 N 20120412 20151103 20151006 ASPN

I only mentioned grep first out of habit and because I often forget that findstr isn't quite as braindead as I used to think it once was.

Caveat

I'm pretty sure grep will have no problem with very large files. I'm less sure of findstr but I can't think of any reason not to try it.


Addendum:

Regular Expressions

Both grep and findstr can find fixed strings, but they can also work with regular expressions.

If you want to find those strings in the first word of each line, you can prefix the strings with a caret symbol ^ to indicate that the string to be found must be at the start of a line.

^4687264 
^RE45781 
^RE45786 
^RE45790 
^RE45793 

You can do this in vim using :%s/^/^/ - you could also use sed, awk, perl etc to edit the search string file just as easily. Note that the first ^ in the search-expression part of the s (substitute) command is a meta-character meaning "start of line". The second ^ is in the replacement-string part where it means the literal character ^.

If I didn't want to edit my file of strings to be found, I'd do something like

sed -e 's/^/^/' < small_list.txt > temp.txt; grep -f temp.txt big_file.txt; rm temp.txt

grep assumes regular expressions. With findstr you need to add the /R option to the command (untested).

  • Grep just worked fine on 800mb file. it searched all the colum of large file and returned with undesired results also. Is it possible to just search on column 1? – Rahul Patel Oct 23 '15 at 12:21
  • @Rahul: Yes, you can use a regular expression. For example, insert a caret ^ at the start of each line in your small file of search strings. – RedGrittyBrick Oct 23 '15 at 12:54
0

Powershell

no idea about runtime performance but powershell is installed by default on all current Windows operating systems

$small = (gc small.txt | group).Name
gc large.txt | ? {$small -contains $_.Split(" ")[0]}

Breakdown

$small = (gc small.txt | group).Name
 - Get-Content of small.txt
 - Group the content to retain a list of unique values as to speed up the search
 - Store in $small variable

gc large.txt | ? {$small -contains $_.Split(" ")[0]}
 - Get-Content of large.txt
 - ? => where clause
   - $_ contains a complete line from large.txt. 
     Split it up by spaces and just select the first element of that result
   - Search the first element in the $small list

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.