Maybe you could use Python: read in your excel file (you may save it as text file) line by line, look for the line in the original file that starts with these characters and save this line to a new file.

I don't really get the part "hat I do not have standardized lines aka identifiers." Could You please post a part of your original file (longer than two lines) and your desired output?

For the input/output you provided it's very simple: just grep the pattern you are looking for. For example for file test.txt including: 

HWI-ST513:311:C1JADACXX:7:1306:16522:83878

HWI-ST513:311:C1JADACXX:7:1101:10012:62095

If you want to extract the first line (or all lines with "HWI-ST513:311:C1JADACXX:7:1306" pattern) You can do:

grep "HWI-ST513:311:C1JADACXX:7:1306" test.txt

Thanks for the suggestion @Carina Pfann. However, as I mentioned in my above post it is hard to read the file (excel or text) line by line, since it consists of ca. 50,000 lines, even more for some others! Hence I really need to use a script to pull out the lines I am interested in. In simple terms, my issue is that I want to extract these 50,000 lines which are sitting here and there in a file containing ~150,000 lines. 

Sorry for the confusion @Bartosz Trzaskowski. What I meant was that the lines are all different. Btw, if I use the pattern HWI-ST513:311:C1JADACXX:7:1306:16522:83878 with the grep command, I am pulling out a larger number than what I need, I want to extract 1540 sequences only. Also, though it may sound too simple, may I ask how to copy those lines into a text file right away? With the given command, I can only see the lines on command line.

As you requested, here is part of my input file:

HWI-ST513:311:C1JADACXX:7:1306:16522:83878 n0 n0 Drosophilidae Diptera Insecta Arthropoda n0

HWI-ST513:311:C1JADACXX:7:2307:16604:175748 n0 n0 Calliphoridae Diptera Insecta Arthropoda n0

HWI-ST513:311:C1JADACXX:7:1101:20454:143707 n0 n0 Calliphoridae Diptera Insecta Arthropoda n0

HWI-ST513:311:C1JADACXX:7:2307:6872:186111 n0 n0 Calliphoridae Diptera Insecta Arthropoda n0

HWI-ST513:311:C1JADACXX:7:1101:13441:191102 n0 n0 Calliphoridae Diptera Insecta Arthropoda n0

For output, I would get this (with the manual work):

HWI-ST513:311:C1JADACXX:7:1306:16522:83878 n2 Drosophila Drosophilidae Diptera Insecta Arthropoda n0

HWI-ST513:311:C1JADACXX:7:2307:16604:175748 Calliphora vicina Calliphora Calliphoridae Diptera Insecta Arthropoda n0

HWI-ST513:311:C1JADACXX:7:1101:20454:143707 Lucilia illustris Lucilia Calliphoridae Diptera Insecta Arthropoda n0

HWI-ST513:311:C1JADACXX:7:2307:6872:186111 Calliphora vicina Calliphora Calliphoridae Diptera Insecta Arthropoda n0

HWI-ST513:311:C1JADACXX:7:1101:13441:191102 Calliphora vicina Calliphora Calliphoridae Diptera Insecta Arthropoda n0

To save the pattern search to a new file: 

grep "HWI-ST513:311:C1JADACXX:7:1306" test.txt > new.txt

To get a specific number of lines in the new.txt file: the easiest would be to open it in vi, go to line 1540 (in vi: 1540j), delete everything that's after it (in vi: :.,$d) and save the file which has now exactly 1540 lines (in vi: :x).

Now: to randomly print a specific number of lines would be a bit more complex. Using bash scripts you could first initialize the RANDOM variable:

RANDOM=12345

Then print the RANDOM line using awk:

awk '{ if (NR==RANDOM) print $0 }' test.txt >> new.txt

And then loop it to repeat 1540 times (each time the RANDOM variable would give you a different line number).

Just be aware, that the RANDOM variable in Unix may not be really that random (depends on seed). Better random numbers are found e.g. in python and perl.

Thanks for the detailed answer. The thing is that I gave the number 1540 based on manual work (technically I could also do it with grep -c command). But it means that I would need to do it for the whole identifiers, which seems to be the case for this dataset.

Btw, would RANDOM variable be the part of the identifier in this case, as in HWI-ST513:311:C1JADACXX:7:1306?

So just to make sure I understand the problem correctly: you have

A line form the input file should be kept in the output if it matches any of the patterns.

If that's what you want to do, then here is a plan for you:

grep -f patterns.csv everything.csv

grep -f takes the patterns from the file patterns.csv.

Here are two example files:

everything.csv:

aaa 123 456

bbb 555 333

aaa 765 615

ccc 312 000

patterns.csv:

aaa

bbb

When I run

grep -f patterns.csv everything.csv

then I get

aaa 123 456

bbb 555 333

aaa 765 615

If you are using Galaxy framework, you can join the two files using the column (field).

Likewise, if you load your data into a database, you can join them (loaded as different tables) using a column.

If none of above, you'll need to do some programming.

What you need to do is first load the list of identifiers that you'll be looking for (1540 sequences' names) into memory. And start scanning the big file line-by-line, each time searching from your list, if the current line is of interest to you or not. 

If you want to make it faster (if this procedure will be frequently used in the future), then you can speed this algorithm up, by removing the identifier from the list, each time it is found and processed. So, as you proceed, the searching part will take less and less time.

Hope it helps.

Cheers ;)

I totally forgot about linux join command.

You can join two files from command line using the 'join'.

Could you use egrep -w -f yourexcelfile youroriginalfile > output file, where yourexcelfile has the pattern ?

Dear @Matthias Berth, I did use the given command (grep -f pattern.txt everything.text > output), however the output file is exactly the same as everything.txt, as if I copied this file. I am a bit confused now. Thinking of the possibilities for why it could occur, given that not all the patterns are in the everything.txt file, as far as I know.

Did you try it with the files eyerything.csv and patterns.csv that I attacheda bove? What was the result?

Can you select a few lines from your files that illustrate the problem, and post these short files here? If possible, please also state the expected result for the small files.

Yes, I tried. The result was exactly the same as you mentioned.

Here:

aaa 123 456

bbb 555 333

aaa 765 615

I can send an example to what I was trying with my files. Attached files are pattern.txt file including my keywords and everything.txt, a subset of my big original file, where I want to pull out the given information based on the keywords. I am also attaching the output file, that I obtained with the grep -f command. It is the same size of everything.txt. This time it may be because the everything.txt is a subset of my original file (I ran the same command with this file, and it is running for a few hours now), and the lines in pattern.txt may be matching with the whole lines in this subset file. I am not sure though.

So could it be that your patterns contain a pattern for every line in your large input file? That would explain why you get the exact same output.

That's what happens in the last example you posted: pattern.txt has a pattern for each line of everything.txt.

When you use the attached patterns-subset.txt you will see that indeed a subset of the input lines is selected:

grep -f patterns-subset.txt everything.txt | sort

HWI-ST513:311:C1JADACXX:7:2104:11883:141517 Spilogona pusilla Spilogona Muscidae Diptera Insecta Arthropoda n0

HWI-ST513:311:C1JADACXX:7:2104:16194:172280 Xylota segnis Xylota Syrphidae Diptera Insecta Arthropoda n0

HWI-ST513:311:C1JADACXX:7:2304:14886:167800 Boettcheria latisterna Boettcheria Sarcophagidae Diptera Insecta Arthropoda n0

HWI-ST513:311:C1JADACXX:7:2307:11254:2125 Perochaeta dikowi Perochaeta Sepsidae Diptera Insecta Arthropoda n0

Yes, I think this is the exact issue (especially because when I tried the subset file, obtained the same results as you provided). Hence, I am running the same command with the original file, but it did already take half a day, and generated 5 MB of data only. I am hoping it to give me a different result, rather than the original file itself. May I ask how you derived the subset file? Have you manually searched for the matching lines and picked those or used a sorting script? I am a bit curious :)

I used a text editor to select some random lines from everything.txt :)

Making small examples that can be checked manually is pretty useful sometimes, IMHO.

In fact, I thought of another way of getting the patterns in the original file. Since they are in a way duplicates, I was planning to put the pattern into one column and the rest of the information into other columns in an excel datasheet and compare the data in two columns to find duplicates. I feel like it will be a more straightforward methodology.

@Albina Bacolla, egrep command works perfectly fine, I was able to extract all the patterns basically. However, it takes more than half a day, most likely because my dataset is relatively big (original file containing ~50,000 lines). Although the command is convenient, it is pretty time-consuming at the same time. Is there any way to speed this step up you think?

I'm surprised that it takes half a day. Try using grep instead of egrep. If that still is slow, there must be another problem. Maybe your computer's RAM is used up?

How big (in terms of megabytes) are your files?

You can try fgrep, it should be faster than egrep since it does not use extended regular expressions (which you do not seem to have anyway). If you have duplicates in your pattern file, you can do a "sort -u myexcelfile > mysortedexcelfle" and use the output to fgrep. However, as mentioned, 50,000 lines is no big deal.

If your files are not very big (50000 lines is fine! Even 200000 is fine, but millions could take time, provided you have the RAM) and this is not part of an efficient pipeline then use perl :) I've made a script for you. It lacks optimization, but it will work and it's very simple. 

use strict;

use warnings;

my %data;

my $lines = 0;

my $number = 10; #number of lines to extract

open INPUT, "text.txt";

while() {

chomp;

$data{$lines} = $_;

$lines++;

}

close INPUT;

open OUTPUT1, ">output1.txt"; #if it's ok to have the same line extracted twice

for(my $n = 0; $n < $number; $n++) {

my $random = int(rand($lines));

print OUTPUT1 "$data{$random}\n";

}

close OUTPUT1;

open OUTPUT2, ">output2.txt"; #if it's not ok to have the same line extracted twice;

my %data2;

for(my $n = 0; $n < $number; $n++) {

my $random = int(rand($lines));

if(!exists $data2{$random}) {

print OUTPUT2 "$data{$random}\n";

$data2{$random} = 0;

}

else {

$n--;

}

}

close OUTPUT2;