How can I grep data in Excel?

Question

I have an Excel file that contains data in two columns like :

Operator ID           MXS1268
Name                  ramesh    

I need to check for duplicates in multiple Excel files which have the same value for the operator ID and display or put it in some log file.

Answer 1

I found a nifty util written by a Polish guy in 2013. Since then, the program has moved to github. The Makefile was removed (github version), and instead the current maintainer is using CMake by the looks of it.

However, we are savvy superusers, so we know how to compile stuff:

$ link=https://github.com/vobys/xls2txt/archive/refs/heads/master.tar.gz 
$ wget -nv "$link" \
    && tar xf master.tar.gz \
    && cd xls2txt-master \
    && gcc -O2 -lm cp.c ieee754.c ole.c ummap.c xls2txt.c -o xls2txt

Extract the data. (Since you said grep, I assume you know how to use a shell)

$ awkf() { awk -F\\t '{ printf "%20s | %-20s\n", $1, $2 }' ; }
$ x2t() { ./xls2txt $1 C4:D5 | awkf > `basename "$1" .xls`.txt ; }
$ x2t ramesh2.xls ; x2t ramesh.xls

And the report:

$ grep MXS1268 ramesh*txt
ramesh.txt:         Operator ID | MXS1268
ramesh2.txt:         Operator ID | MXS1268

---

For identification purposes:

$ ./xls2txt ; echo; sed -n '/Copy/{ s/<.*//; p ; q;  }' < xls2txt.c 

usage: xls2txt [-C cs] [-n sheetnum|-A] [-f] file.xls [X:X]
       xls2txt [-C cs] -l file.xls
 X:X    cell range (eg. A1:C5, D2:E)
 -l list sheets
 -n num select sheet
 -A all sheets (\f separated)
 -C cs  output charset (utf8 asc iso1 iso2), utf8 is default
 -f don't try to format numbers
 -a ascii output (same as -C asc)

 *  Copyright (C) 2005-2011 Jan Bobrowski 

---

Update 2022-05: It looks like original author does not want to host this anymore, because the cert expired in November 2021 and the old URL produces HTTP 403.

original (dead) link http://wizard.ae.krakow.pl/~jb/xls2txt/xls2txt-0.13.tar.gz

Lucky for us, there is a copy at github (link to master.tar.gz above).

Old instructions

$ wget -nv "$link" && \
  tar xf `basename "$link"` && \
  cd $(basename "$link" .tar.gz) &&\
   make
2013-07-24 URL:(...)/xls2txt-0.13.tar.gz [12419/12419] -> "xls2txt-0.13.tar.gz"
cc -O2 -g -DVERSION=0.13 -c xls2txt.c -o xls2txt.o
cc -O2 -g   -c -o ole.o ole.c
cc -O2 -g   -c -o cp.o cp.c
cc -O2 -g   -c -o ummap.o ummap.c
cc -O2 -g   -c -o ieee754.o ieee754.c
cc -lm  xls2txt.o ole.o cp.o ummap.o ieee754.o   -o xls2txt

Answer 2

On Linux or Cygwin:

$ xlsx2csv myfile.xlsx | grep MXS1268 

Answer 3

You might try using openpyxl --or any number of Python packages-- if you know basic Python. If you're dead set on using grep, output the excel to text and pipe it through grep. Or just write a pygrep script to do it all.

Answer 4

I just had a similar issue: I needed to be able to find a string in a large number of excel (xlsx) files. I was able to solve this with a tool called "xlsxgrep", which is available here: https://pypi.org/project/xlsxgrep/.

Overview

xlsxgrep is a CLI tool to search text in XLSX, XLS, CSV, TSV and ODS files. It works similarly to Unix/GNU Linux grep.

Features

Grep compatible: xlsxgrep tries to be compatible with Unix/Linux grep, where it makes sense. Some of grep options are supported (such as -r, -i or -c).

Search many XLSX, XLS, CSV, TSV and ODS files at once, even recursively in directories.

Regular expressions: Python regex.

Supported file types: csv, ods, tsv, xls, xlsx