2

I'm using Ubuntu Linux and I can't find a way to find a text in multiple excel (xls) files. My desire is do this by command line, but other alternatives are welcome too.

2

I made one using ssconvert tool (from Gnumeric). You need to install the Gnumeric first:

sudo apt-get install gnumeric

You can save the content as script and call:

./script-find-in-xls.sh text

The content of the script.

#!/bin/sh

# $1 - text to find

if [ -z "$1" ]
  then
    echo 'Please, the text is mandatory'
    exit 1
fi

rm -rf /tmp/xls-csv/
mkdir /tmp/xls-csv/

cd /tmp/xls-csv/
cp /location/of/excel-files/*.xls /tmp/xls-csv/

for f in *.xls; do
    ssconvert -S --import-encoding=ISO8859-1 ./"$f" ./"${f%.xls}.csv"
done

cat *.csv.* > all-xls-content.txt
rm *.csv.*

if cat all-xls-content.txt | egrep --color $1; then
    echo 'found'
else
    echo 'not found'
fi

The script convert all xls files in csv files, join the csv files in a single one file and use the egrep to find the text.

The code it's not perfect, but do the job.

1

If you are ok with installing an indexing application, it could be an answer.

I personnaly use recoll, wich is easy to setup and install. Its defaults interface is a GUI, but it is also possible to use it in CLI:

recoll -q <search terms>

You can setup a scheduled task to update its index, or launch it manually with recollindex (in both cases you can restrict the index to the path of your choices).

0

I had the same issue. Run this script:

python search.py /home/user/directory string

Here you find my solution:

#!/usr/bin/python

import os, sys
import xlrd

def find(path, word):

    l = []

    d = os.listdir(path)

    for file in d:

        filename = str(path) +'/' + str(file)

        print ('Finding in %s' %file)

        if filename.endswith('.xlsx'):

            wb = xlrd.open_workbook(filename)
            ws = wb.sheet_by_index(0)

            for i, row in enumerate(range(ws.nrows)):
                for j, col in enumerate(range(ws.ncols)):
                    if str(word) in str(ws.cell_value(i, j)):
                        l.append((file,row,col))
    if l:
        print ('Word %s found %d times in:' %(word,len(l)))

        for fn, row, col in l:
            print ('File: %s, row: %s ,column: %s' %(fn,row,col))        
    else:
        print ('Word %s not found' %word) 


if __name__ == "__main__":
    try:
        find (sys.argv[1],sys.argv[2])
    except IndexError:
        print('\tExecute: python searchpy <path> <word>')
        print('\tEg: python searchpy /home/user/files/ Fox')

You can pull from Github: searb-in-xlsx

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.