How to extract the key words containing row from an excel file?

Question

I need to extract the key-words containing row from an excel file (.xls) as per the order specified in the given key-word file. For example I have an excel file namely genome.xls as given below,

NC_0208.1   18918   94692   amyl4_A0A0H         1   54  194
NC_0208.1   18839   86123   prot4_A0A0          1   79  137
NC_0208.4   29761   74985   lip10_H8FLU5        2   393 48
NC_0208.2   29687   67745   lysin6_A0A0Q5       5   38  49 

And I have a key word file namely id.txt as given below,

prot
lip
cellulase
lysin
amyl

The expected output is

NC_0208.1   18839   86123   prot4_A0A0          1   79  137
NC_0208.4   29761   74985   lip10_H8FLU5        2   393 48

NC_0208.2   29687   67745   lysin6_A0A0Q5       5   38  49 
NC_0208.1   18918   94692   amyl4_A0A0H         1   54  194

In addition to this, if the key-words are not there in genome file, it should leave the entire row as empty in the output file. Inorder to do this, I used grep command as follows,

grep 'prot\|lip\|cellulase\|lysin\|amyl' genome.xls > result.xls

Above mentioned command is extracting the key word containing entire row, but the order has been changed. Moreover, it is not leaving an empty row for the missing key words. Therefore, kindly help me to do the same. Thanks in advance.

Answer 1

Try this,

while read a ; do grep "$a" genome.xls || printf "\n" ; done < id.txt 

NC_0208.1   18839   86123   prot4_A0A0          1   79  137
NC_0208.4   29761   74985   lip10_H8FLU5        2   393 48

NC_0208.2   29687   67745   lysin6_A0A0Q5       5   38  49 
NC_0208.1   18918   94692   amyl4_A0A0H         1   54  194

Answer 2

I think your requirements demand a loop over the keywords. I would recommend the following shell-script (although you can absorb in into a one-liner if you want to):

#!/bin/bash

while read keyword
do
    # Apply grep, but store result in variable
    MATCH="$(grep $keyword genome.xls)"

    # If grep result is an empty string, output an empty line,
    # otherwise the grep result as-is.
    if [[ -z "$MATCH" ]]
    then
        echo
    else
        echo "$MATCH"
    fi
done < id.txt

By looping about the content of id.txt you can assure the ordering of the output.

Call the script with

user@host$ ./extract_lines.sh > result.xml

In order to make the script more flexible, we can make the file to be processed a command-line argument, or even a list of command-line arguments:

#!/bin/bash

if [[ "$#" == "0" ]]
then
    echo "Usage: $0 <filename(s)>"
    exit 1
fi


for file in "$@"
do
    outfile=${file/.xls/_result.xls}
    :>$outfile

    echo "Process $file, write results to $outfile"


    while read keyword
    do
    # Apply grep, but store result in variable
    MATCH="$(grep $keyword $file)"

    # If grep result is an empty string, output an empty line,
    # otherwise the grep result as-is.
    if [[ -z "$MATCH" ]]
    then
            echo "" >> $outfile
    else
            echo "$MATCH" >> $outfile
    fi
    done < id.txt
done

This will loop over all files specified as command-line arguments (and be it *.xls) and write the results to <input_filename>_result.xls.

Note however that this syntax is somewhat rudimentary in that you should not mix filename specifications with "globs" (i.e. wildcards like *) and regular filenames in your argument list.