eparse 0.2.1

Scan

To scan one or more directories for Excel files with descriptive information, you can use the scan command like so:

$ eparse -v -i <path_to_files> scan

Increase the verbosity with additional flags, such as -vvv, for more descriptive information about the file(s), including sheet names.

Parse

Excel files can be parsed as follows:

$ eparse -v -i <path_to_files> parse

This mode will list each table found in each Excel file to the command-line. This mode is useful for initial discovery for parseable data.

eparse uses a simple algorithm for identifying tables. Table “corners” are identified as cells that contain empty cells above and to the right (or sheet boundaries). A densely or sparsely populated 2x2 table must follow in order for data to be extracted in relation to that cell. eparse will automatically adjust for rowspan labels and empty table corners and the dense vs. sparse criterion can be controlled with the --loose flag.

eparse was written to accomodate various types of output formats and endpoints, including null:///, stdout:///, and sqlite3:///.

$ eparse -i <path_to_files> -o stdout:/// parse -s "Sheet1"

$ eparse -t -i <path_to_files> -o stdout:/// parse -s "Sheet1"

$ eparse -t -i <path_to_files> -o stdout:/// parse -z

$ mkdir .files
$ eparse -i <path_to_files> -o sqlite3:/// parse

Query

Once you have stored parsed data, you can begin to query it using the peewee ORM. This can be done with the tool or directly with the database.

For example, query distinct column header names from a generated SQLite3 database as follows:

$ eparse -o stdout:/// query -i from_sqlite3 .files/<db_file> -m get_c_header
               c_header  Total Rows  Data Types  Distinct Values
  0             ABC-col         150           2               76
  1             DEF-col        3981           3               15
  2             GHI-col          20           1                2
  ..                ...         ...         ...              ...

This command will give descriptive information of each distinct c_header found, including total rows, unique data types, and distinct values.

You can also get raw un-truncated data as follows, which is the default behavior:

$ eparse -t -o stdout:/// query -i from_sqlite3 .files/<db_file>

Filtering data on content is easy. Use the --filter option as follows:

$ eparse -t -o stdout:/// query -i from_sqlite3 .files/<db_file> --filter f_name "somefile.xlsx"

The above command will filter all rows from an Excel file named somefile.xlsx. You can use any of the following django-style filters:

• __eq equals X

• __lt less than X

• __lte less than or equal to X

• __gt greater than X

• __gte greater than or equal to X

• __ne not equal to X

• __in X is in

• __is is X

• __like like expression, such as %somestr%, case sensitive

• __ilike like expression, such as %somestr%, case insensitive

• __regexp regular expression matching such as ^.*?foo.*?$

Filters are applied to the ORM fields like so:

• --filter row__gte 4 all extracted table rows >= 5

• --filter f_name__ilike "%foo%" all data from filenames with foo

• --filter value__ne 100 all data with values other than 100

Queried data can even be stored into a new database for creating curated data subsets, as follows:

$ eparse -t -o sqlite3:/// query -i from_sqlite3 .files/<db_file>

Since database files the tool generates when using sqlite3:/// are SQLite native, you can also use SQLite database client tools and execute raw SQL like so:

$ sudo apt-get install -y sqlite3-tools
$ sqlite3 .files/<db_file>
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE IF NOT EXISTS "excelparse" ("id" INTEGER NOT NULL PRIMARY KEY, "row" INTEGER NOT NULL, "column" INTEGER NOT NULL, "value" VARCHAR(255) NOT NULL, "type" VARCHAR(255) NOT NULL, "c_header" VARCHAR(255) NOT NULL, "r_header" VARCHAR(255) NOT NULL, "excel_RC" VARCHAR(255) NOT NULL, "name" VARCHAR(255) NOT NULL, "sheet" VARCHAR(255) NOT NULL, "f_name" VARCHAR(255) NOT NULL);
sqlite> .header on
sqlite> SELECT * FROM excelparse limit 1;
id|row|column|value|type|c_header|r_header|excel_RC|name|sheet|f_name
1|0|0|ABC|<class 'str'>|SomeCol|SomeRow|B2|MyTable|Sheet1|myfile.xlsm