xgrep 0.2.4

xgrep: A grep for Excel (and CSV/TSV) files.

xgrep

Documentation

Can be found at https://xgrep.readthedocs.io

Example usage

To give an example of using xgrep, suppose you have an Excel file (example.xlsx)) with a sheet that looks like this:

https://github.com/terrycojones/xgrep/tree/main/docs/source/images

You can download example.xlsx (or find it in the docs/source directory in the repo) if you want to try the following commands.

Find cells based on a regular expression

Look for the regular expression 'Xia|radius|Jilin':

$ xgrep 'Xia|radius|Jilin' docs/source/example.xlsx

Note that the command-line output is all text. It is produced using the Table class of the wonderful rich package.

Display the row and column information

Add the row numbers (--rn) and Excel column (--ec) information from the input Excel:

$ xgrep --rn --ec 'Xia|radius|Jilin' docs/source/example.xlsx

De-emphasize unmatched cells

If you don't care about the values in cells that were not matched, you can give a value to display instead:

$ xgrep -u . --rn --ec 'Xia|radius|Jilin' docs/source/example.xlsx

Only show matching columns

To exclude columns with no matching cells, you can show only matching columns (--omc):

$ xgrep --omc --rn --ec 'Xia|radius|Jilin' docs/source/example.xlsx

Write CSV (or TSV)

The default output format is a rich Table. You can also produce CSV, TSV, or Excel using the --format option:

$ xgrep --format csv 'Xia|radius|Jilin' docs/source/example.xlsx

If you use --format excel you will also need to give an output filename using --out.

Usage

$ xgrep --help
Usage: xgrep [OPTIONS] PATTERN FILENAMES...

  Command-line interface.

Options:
  -o, --out FILE                  The output file. If not given, output is
                                  written to standard out. Note that if the
                                  --quiet (-q) options is given, no output is
                                  written to the output file.
  --header / --no-header          Don't look for a header line. In this case,
                                  text in what would otherwise be considered a
                                  header can also be matched by the grep
                                  pattern.
  --skip INTEGER RANGE            Skip this many rows at the start of the
                                  input file(s).  [x>=0]
  --format [csv|excel|rich|tsv]   The output format.
  -c, --count                     Only print the number of matching lines.
  --width INTEGER                 The width to use for --format rich tables.
  -v, --invert                    Print non-matching lines.
  -q, --quiet                     Do not show any output, just exit with a
                                  status indiacting whether a match was found
                                  (0) or not (1).
  --only-matching-cols, --omc, --mco
                                  Only show columns that have a matching cell.
  -i, --ignore-case               Ignore case while matching.
  --color TEXT                    The highlight color.
  -u, --unmatched TEXT            The string to show for cells whose values do
                                  not match. If not given, non-matching cells
                                  are shown with their value (in which case
                                  you will need to use the output color to see
                                  matches).
  --row-numbers, --rn             Show row numbers.
  --col-numbers, --cn             Show column numbers.
  --excel-cols, --ec              Add Excel column labels to column names.
  Filenames: [mutually_exclusive]
                                  Whether to display names of matching files.
    -H, --filenames-always, --fa  Always print the name of matching files
                                  (like grep -H).
    -h, --no-filename, --nf       Never print the name of matching files (like
                                  grep -h).
    --only-filename, --of         Only print the names of matching files, not
                                  their matched content.
  --help                          Show this message and exit.

Todo

1. Make it possible to specify a sheet (only works if there is one Excel file?).
2. Adjust col names Row and File in case they're already present.
3. Allow for processing multiple Excel files into individual sheets.
4. Write tests for unequal numbers of cols.