0

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.

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
1
  • Thank you msp9011, AdminBee. I have multiple excel files to do the same. Therefore, I tried to save all excel files in for file in *.xls and used "$file" instead of genome.xls and end up with error. I also tried to call all the file as *.xls instead of genome.xls, but the results have been printed in the same file or single file. In order to store each excel file grep result separately, I have tried this syntax "$file" | uniq > "new_${file}" , but this also not working. Sorry, I have not mentioned this condition in my question. could you please suggest something to do the same.
    – Kumar
    Nov 19 '19 at 13:39
0

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.

2
  • @user357294 updated the answer. I still assume that id.txt can be left hard-coded in the implementation.
    – AdminBee
    Nov 19 '19 at 14:07
  • Thank you @AdminBee
    – Kumar
    Nov 20 '19 at 15:08

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.