# Exco
[](https://github.com/thegangtechnology/exco/actions?query=workflow%3ABuild)
[](https://github.com/thegangtechnology/exco/actions?query=workflow%3ASonarqube)
[](https://sonarcloud.io/dashboard?id=thegangtechnology_exco)
[](https://github.com/thegangtechnology/exco/actions?query=workflow%3ACodeQL)
[](https://codecov.io/gh/thegangtechnology/exco)
[](https://badge.fury.io/py/exco)
Excel Comment ORM. Declare ORM Spec descriptively right on the excel file.
# What it does
The package allows you to declare orm mapping right in the excel file in the comments
then use it to extract data from other similar files.
An example of a template is shown below.
Dynamic Location, Validation, Assumptions, custom Parser are also supported.
# Installation
```
pip install exco
```
# Simple Usage
```
import exco
processor = exco.from_excel('./quickstart_template.xlsx')
result = processor.process_excel('./quickstart_data_file.xlsx')
print(result.to_dict())
```
See Also [Quick Start Notebook](notebooks/quickstart/0%20Quick%20Start.ipynb)
# Exco Block
Exco relies on yml blocks inside excel comment to build a spec.
Each comment can contain multiple blocks.
There are three types of echo block: a cell block, a table block, and a column block.
## Cell Block
A cellblock is a block of yml that is surrounded by `{{--` and `--}}`
```
{{--
key: some_int
parser: int
--}}
```
This means that parse this cell as `int` and put it in the result with key `some_int`.
Other advance features like, fallback, locator, assumptions, validator are optional.
The full specification is shown below.
```
{{--
key: some_value
# Optional default at_comment_cell
locator: {name: at_comment_cell}
assumptions: # Optional
- {key: right_of, name: right_of, label: marker}
parser: int
# Optional
fallback: 7
# Optional. Dict[name, value].
params: {}
validators: # Optional
- {key: between, name: between, low: 5, high: 10 }
metadata: {unit: km}
--}}
```
### Processing Flow.
The cell processing flow is
1. Locating the Cell
2. Check Assumption
3. Parse
4. Validation
### Features
#### `key`
Key where the result will be put. This is required.
#### `fallback`
This is the most useful one where if the cell is failed locating, testing assumption, parsing.
The fallback value is assumed. If it is not specified, the value None is used. (yml's none is `null`).
#### `locator`
Locator is a dictionary. The key `name` is the class name to be used to locate the cell. Other parameters
can be flattened and put in the same dictionary. These parameters will be pass through the constructor
of such class.
If the locator is left out, it is assumed to be `at_comment_cell` which locate the cell with exactly
the same coordinate as the commented cell in the template.
#### `assumptions`
List of dictionaries. The `key` is the name of the check. `name` is the name of the class to be used in
testing an assumption. Other parameters can be flattened and included in the same dictionary.
#### `parser`
Required. Name of the parser class.
#### `params`
Optional. Dictionary of the parameter name to its value. The spread values are passed through the parser
constructor.
#### `validations`
Optional. List of Dictionary. Each dictionary must have `key` for the validation key and `name`,
validator's class name, specified. Other parameters to be passed to the validator constructor can be flattened and specified in this dictionary as well.
### `metadata`
Optional. Metadata. Dictionary of any object can be put here. The object will be parsed on to the result.
## Table Block
A table block is for specifying the properties of the table. The table block is surrounded by
`{{--table` and `--}}`. The simplest table block is
```
{{--table
key: some_table
--}}
```
This is typically what you need. A more complicated example is shown below:
```
{{--table
key: some_table
# optional. default at_comment_cell
locator: {name: at_comment_cell}
# optional. default downward
item_direction: downward
end_conditions:
- {name: all_blank}
- {name: max_row, n: 10, inclusive: true}
--}}
```
### Features
#### `key`
where the table will be put in the result. The output for the table is a list of dictionaries
where the key in the dictionaries is the column name specified in column blocks.
#### `locator`
Optional. Default at_comment_cell. Locator for the table anchor cell. Same as a cell block.
#### `item_direction`
Optional Direction for items in the table. Default downward. (rightward or downward)
#### `end_conditions`
A list of dictionary specifying each termination condition. The dictionary contains `name` and flattened
parameters.
If any of the end condition is met then the parsing for the table terminates. Depending on the end condition's
class, it may or may not include the matching row.
## Column Block
Column block specifications are very similar to the cell block. The simplest example is shown below.
```
{{--col
table_key: some_table
key: some_col
parser: int
}}
```
The column block is very similar to the cell block. It is the cell block with `table_key` and without
`locator`. The `table_key` tells which table it belongs to. It must match one of the table's `key` defined in the table block.
The position of the column cell when extracting value is computed from the relative position to the table
block in the template.
All other features like fallback, assumptions, validations are also supported in the same
fashion as the cell block.
# Built-in Functions
## Locator
### `at_comment_cell`
Locate the cell right at the comment cell's coordinate in the template.
### `right_of`
Locate the cell to the right of the cell or merged cell with the given label.
In the case of a merged cell, right_of will pick the rightmost column and
topmost row of the merged cell togo to the right of.
Parameters:
- `label` label to match.
- `n` Optional. Default value is 1. Indicates the number of columns to move from label cell to located cell.
### `search_right_of`
Searches for non-empty cell right of the cell with the given value.
Parameters:
- `label` label to match.
- `max_empty_col_search` Indicates the maximum number of columns to search for non-empty cell right of label.
### `right_of_regex`
Locate the cell to the right of the cell with a regex match.
Similarly, in the case of a merged cell, right_of_regex will
pick the rightmost column and topmost row of the merged cell
togo to the right of.
Parameters:
- `regex` string for the regular expression.
- `n` Optional. Default value is 1. Indicates the number of columns to move from regex matched cell to located cell.
### `below_of`
Locate the cell below of the cell with the given label.
In the case of a merged cell, below_of will pick the
bottommost row and leftmost column of the merged cell togo
to the bottom of.
Parameters:
- `label` label to match.
- `n` Optional. Default value is 1. Indicates the number of rows to skip from label cell to located cell.
### `search_below_of`
Searches for non-empty cell below of the cell with the given label.
Parameters:
- `label` label to match.
- `max_empty_row_search` Indicates the maximum number of rows to search for non-empty cell below of label.
### `below_of_regex`
Locate the cell below of the cell with a regex match.
Similarly, in the case of a merged cell, below_of_regex will
pick the bottommost row and leftmost row of the merged cell
togo to the bottom of.
Parameters:
- `regex` string for the regular expression.
- `n` Optional. Default value is 1. Indicates the number of rows to move from regex matched cell to located cell.
### `within`
Locate the cell between a cell with the given label.
This function searches to the right of or below of the
cell with the given label. If it is to the right of, the function
searches row by row while staying in between the column ranges
of the cell with the given label. If it is below of, similarly,
we search row by row while staying in between the row ranges of
the cell with the given label. On top of this you have the option
on how to pick your data (to the right of or beneath of) once you have found your desired key within
the cell range.
Parameters:
- `label` label of cell you want to search in between
- `find` the name of the cell you want to find in between the cell with that label's range
- `direction` direction you want to search for your value, choices are:
- `right_of` this will search the right of the cell between the columns row wise
- `below_of` this will search the beneath the cell between the rows row wise
- `perform` the action you want to perform to fetch your data, your options are:
- `right_of` fetch the value to the right of the cell labeled in `find`
- `below_of` fetch the value to beneath of the cell labeled in `find`
- `n` pick the value n values away in the specified perform direction (right_of or below_of)
## Assumption
### `left_cell_match`
This is useful in batch processing allowing us to check that we have the correct label to the left.
Parameters:
- `label` string to check that it matches.
## Parser
### `string`
parse value as string.
### `int`
parse the value as an integer.
### `float`
parse the value as float
### `date`
parser the value as a date.
## Validator
### `between`
valid if the value is between(inclusively) `high` and `low`
#### Parameters
- `high` upper bound value.
- `low` lower bound value
## TableEndCondition
### `all_blank`
Evaluate to true if all columns are blank.
### `max_row`
Evaluate to true if the row number(start from 1) including the parsing row is greater than or
equal to `n`.
#### Parameters
- `n` number of row
- `inclusive` Optional. Default True. Whether to include the row in which it evaluates to true.
### `cell_value`
Evaluate to true if any column in row contains matching `cell_value`. Table terminates the row before.
#### Parameters
- `cell_value` cell value to match.
# Dereferencing
There are two types of dereferencing
- Spec Creation time dereferencing. The string similar to ``<<A1>>`` will be resolved
to the value at A1 of the template file.
- Extraction time dereferencing. This string similar to ``==A1==`` will be resolved
to the value at A1 of the extracted file.
Here is an example
```
{{--
key: ==D2==
assumptions: # Optional
- {key: right_of, name: right_of, label: <<A1>>}
parser: int
fallback: ==D3==
--}}
```
# Handle Identical Sheet with Different Names
When an excel file to be extracted has a different sheet name than the one in the template file,
use ``sheet_name_checkers`` parameter in ``exco.from_excel()`` to create ``ExcelProcessor``
with sheet name alias checker.
For example, when the sheet name in the template file is 'Test 1/1/2021',
but sheet names can be varied in the extracting files, i.e., 'Test 2/2/2022, 'Test 3/3/2023', etc.
```
import exco
SheetName = str
SheetNameAliasChecker = Callable[[SheetName], bool]
test_sheet_checker: SheetNameAliasChecker = lambda sheetname: 'Test' in sheetname
checkers: Dict[SheetName, SheetNameAliasChecker] = {'Test 1/1/2021': test_sheet_checker}
processor = exco.from_excel(template_excel_path, sheet_name_checkers=checkers)
```
In the case where there are hidden sheets that might have information that you dont want to extract,
make sure to set the accept_only_visible_sheets parameter to True
```
processor = exco.from_excel(template_excel_path, sheet_name_checkers=checkers, accept_only_visible_sheets=True)
```
# Custom Locator/Parser Etc.
See [Advance Features Notebook](notebooks/quickstart/1%20Advance%20Features.ipynb). But, in essence,
```python
processor = exco.from_excel('./custom_locator/custom_locator_template.xlsx',
extra_locators={'diagonal_of': DiagonalOfLocator})
```
# Working with .xls files.
Exco will not read Excel 97-2003 workbook (.xls) files. Use [XLS2XLSX](https://pypi.org/project/xls2xlsx/) to convert .xls files to the supported .xlsx format.
Raw data
{
"_id": null,
"home_page": "https://github.com/thegangtechnology/exco",
"name": "exco",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "",
"author": "Piti Ongmongkolkul",
"author_email": "piti118@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/27/82/f0189e5a0cd92f8946db4cbf3a47e1888bce49e0d39b5614a08766b17d37/exco-0.2.10.tar.gz",
"platform": null,
"description": "# Exco\n\n[](https://github.com/thegangtechnology/exco/actions?query=workflow%3ABuild)\n[](https://github.com/thegangtechnology/exco/actions?query=workflow%3ASonarqube)\n[](https://sonarcloud.io/dashboard?id=thegangtechnology_exco)\n[](https://github.com/thegangtechnology/exco/actions?query=workflow%3ACodeQL)\n[](https://codecov.io/gh/thegangtechnology/exco)\n[](https://badge.fury.io/py/exco)\n\nExcel Comment ORM. Declare ORM Spec descriptively right on the excel file.\n\n# What it does\n\nThe package allows you to declare orm mapping right in the excel file in the comments\n then use it to extract data from other similar files.\n \nAn example of a template is shown below.\n\n\n\nDynamic Location, Validation, Assumptions, custom Parser are also supported.\n\n\n# Installation\n\n```\npip install exco\n```\n\n# Simple Usage\n\n```\nimport exco\nprocessor = exco.from_excel('./quickstart_template.xlsx')\nresult = processor.process_excel('./quickstart_data_file.xlsx')\nprint(result.to_dict())\n```\n\nSee Also [Quick Start Notebook](notebooks/quickstart/0%20Quick%20Start.ipynb)\n\n# Exco Block\n\nExco relies on yml blocks inside excel comment to build a spec.\nEach comment can contain multiple blocks.\nThere are three types of echo block: a cell block, a table block, and a column block.\n\n## Cell Block\n\nA cellblock is a block of yml that is surrounded by `{{--` and `--}}`\n```\n{{--\nkey: some_int\nparser: int\n--}}\n```\n\nThis means that parse this cell as `int` and put it in the result with key `some_int`.\n\n\n\nOther advance features like, fallback, locator, assumptions, validator are optional.\nThe full specification is shown below.\n\n```\n{{--\nkey: some_value\n# Optional default at_comment_cell\nlocator: {name: at_comment_cell} \nassumptions: # Optional\n - {key: right_of, name: right_of, label: marker}\nparser: int\n# Optional\nfallback: 7 \n# Optional. Dict[name, value].\nparams: {} \nvalidators: # Optional\n - {key: between, name: between, low: 5, high: 10 }\nmetadata: {unit: km}\n--}}\n```\n\n### Processing Flow.\n\nThe cell processing flow is\n\n1. Locating the Cell\n2. Check Assumption\n3. Parse\n4. Validation\n\n### Features\n\n#### `key`\nKey where the result will be put. This is required.\n\n#### `fallback`\n\nThis is the most useful one where if the cell is failed locating, testing assumption, parsing.\nThe fallback value is assumed. If it is not specified, the value None is used. (yml's none is `null`).\n\n#### `locator`\nLocator is a dictionary. The key `name` is the class name to be used to locate the cell. Other parameters\ncan be flattened and put in the same dictionary. These parameters will be pass through the constructor\nof such class.\n\nIf the locator is left out, it is assumed to be `at_comment_cell` which locate the cell with exactly\nthe same coordinate as the commented cell in the template.\n\n#### `assumptions`\nList of dictionaries. The `key` is the name of the check. `name` is the name of the class to be used in\ntesting an assumption. Other parameters can be flattened and included in the same dictionary.\n\n#### `parser`\nRequired. Name of the parser class.\n\n#### `params`\nOptional. Dictionary of the parameter name to its value. The spread values are passed through the parser\nconstructor.\n\n#### `validations`\nOptional. List of Dictionary. Each dictionary must have `key` for the validation key and `name`,\nvalidator's class name, specified. Other parameters to be passed to the validator constructor can be flattened and specified in this dictionary as well.\n\n### `metadata`\nOptional. Metadata. Dictionary of any object can be put here. The object will be parsed on to the result.\n\n## Table Block\nA table block is for specifying the properties of the table. The table block is surrounded by\n`{{--table` and `--}}`. The simplest table block is\n```\n{{--table\nkey: some_table\n--}}\n```\nThis is typically what you need. A more complicated example is shown below:\n\n```\n{{--table\nkey: some_table\n# optional. default at_comment_cell\nlocator: {name: at_comment_cell}\n# optional. default downward\nitem_direction: downward\nend_conditions:\n - {name: all_blank}\n - {name: max_row, n: 10, inclusive: true}\n--}}\n```\n### Features\n#### `key`\nwhere the table will be put in the result. The output for the table is a list of dictionaries\nwhere the key in the dictionaries is the column name specified in column blocks.\n\n#### `locator`\nOptional. Default at_comment_cell. Locator for the table anchor cell. Same as a cell block.\n\n#### `item_direction`\nOptional Direction for items in the table. Default downward. (rightward or downward)\n\n#### `end_conditions`\nA list of dictionary specifying each termination condition. The dictionary contains `name` and flattened\nparameters.\n\nIf any of the end condition is met then the parsing for the table terminates. Depending on the end condition's\nclass, it may or may not include the matching row.\n\n## Column Block\n\nColumn block specifications are very similar to the cell block. The simplest example is shown below.\n```\n{{--col\ntable_key: some_table\nkey: some_col\nparser: int\n}}\n```\nThe column block is very similar to the cell block. It is the cell block with `table_key` and without\n`locator`. The `table_key` tells which table it belongs to. It must match one of the table's `key` defined in the table block.\n\nThe position of the column cell when extracting value is computed from the relative position to the table\n block in the template.\n \n All other features like fallback, assumptions, validations are also supported in the same\n fashion as the cell block.\n\n# Built-in Functions\n\n## Locator\n### `at_comment_cell`\nLocate the cell right at the comment cell's coordinate in the template.\n\n### `right_of`\nLocate the cell to the right of the cell or merged cell with the given label.\nIn the case of a merged cell, right_of will pick the rightmost column and \ntopmost row of the merged cell togo to the right of.\n\nParameters:\n- `label` label to match.\n- `n` Optional. Default value is 1. Indicates the number of columns to move from label cell to located cell.\n\n### `search_right_of`\nSearches for non-empty cell right of the cell with the given value.\n\nParameters:\n- `label` label to match.\n- `max_empty_col_search` Indicates the maximum number of columns to search for non-empty cell right of label.\n### `right_of_regex`\nLocate the cell to the right of the cell with a regex match.\nSimilarly, in the case of a merged cell, right_of_regex will \npick the rightmost column and topmost row of the merged cell \ntogo to the right of.\n\nParameters:\n- `regex` string for the regular expression.\n- `n` Optional. Default value is 1. Indicates the number of columns to move from regex matched cell to located cell.\n\n### `below_of`\nLocate the cell below of the cell with the given label.\nIn the case of a merged cell, below_of will pick the \nbottommost row and leftmost column of the merged cell togo \nto the bottom of.\n\nParameters:\n- `label` label to match.\n- `n` Optional. Default value is 1. Indicates the number of rows to skip from label cell to located cell.\n\n### `search_below_of`\nSearches for non-empty cell below of the cell with the given label.\n\nParameters:\n- `label` label to match.\n- `max_empty_row_search` Indicates the maximum number of rows to search for non-empty cell below of label.\n\n### `below_of_regex`\nLocate the cell below of the cell with a regex match.\nSimilarly, in the case of a merged cell, below_of_regex will \npick the bottommost row and leftmost row of the merged cell \ntogo to the bottom of.\n\nParameters:\n- `regex` string for the regular expression.\n- `n` Optional. Default value is 1. Indicates the number of rows to move from regex matched cell to located cell.\n\n\n### `within`\nLocate the cell between a cell with the given label.\nThis function searches to the right of or below of the\ncell with the given label. If it is to the right of, the function\nsearches row by row while staying in between the column ranges\nof the cell with the given label. If it is below of, similarly,\nwe search row by row while staying in between the row ranges of\nthe cell with the given label. On top of this you have the option\non how to pick your data (to the right of or beneath of) once you have found your desired key within\nthe cell range.\n\nParameters:\n- `label` label of cell you want to search in between\n- `find` the name of the cell you want to find in between the cell with that label's range\n- `direction` direction you want to search for your value, choices are:\n - `right_of` this will search the right of the cell between the columns row wise\n - `below_of` this will search the beneath the cell between the rows row wise\n- `perform` the action you want to perform to fetch your data, your options are:\n - `right_of` fetch the value to the right of the cell labeled in `find`\n - `below_of` fetch the value to beneath of the cell labeled in `find`\n- `n` pick the value n values away in the specified perform direction (right_of or below_of)\n\n\n## Assumption\n\n### `left_cell_match`\nThis is useful in batch processing allowing us to check that we have the correct label to the left.\n\nParameters:\n\n- `label` string to check that it matches.\n\n## Parser\n### `string`\nparse value as string.\n\n### `int`\nparse the value as an integer. \n\n### `float`\nparse the value as float\n\n### `date`\nparser the value as a date.\n\n## Validator\n\n### `between`\nvalid if the value is between(inclusively) `high` and `low`\n\n#### Parameters\n\n- `high` upper bound value.\n- `low` lower bound value\n\n## TableEndCondition\n\n### `all_blank`\nEvaluate to true if all columns are blank.\n\n### `max_row`\nEvaluate to true if the row number(start from 1) including the parsing row is greater than or\nequal to `n`.\n\n#### Parameters\n- `n` number of row\n- `inclusive` Optional. Default True. Whether to include the row in which it evaluates to true.\n\n### `cell_value`\nEvaluate to true if any column in row contains matching `cell_value`. Table terminates the row before.\n\n#### Parameters\n- `cell_value` cell value to match.\n# Dereferencing\nThere are two types of dereferencing\n- Spec Creation time dereferencing. The string similar to ``<<A1>>`` will be resolved \nto the value at A1 of the template file.\n- Extraction time dereferencing. This string similar to ``==A1==`` will be resolved\nto the value at A1 of the extracted file.\n\nHere is an example\n```\n{{--\nkey: ==D2==\nassumptions: # Optional\n - {key: right_of, name: right_of, label: <<A1>>}\nparser: int\nfallback: ==D3== \n--}}\n```\n\n# Handle Identical Sheet with Different Names\n\nWhen an excel file to be extracted has a different sheet name than the one in the template file,\nuse ``sheet_name_checkers`` parameter in ``exco.from_excel()`` to create ``ExcelProcessor`` \nwith sheet name alias checker.\n\nFor example, when the sheet name in the template file is 'Test 1/1/2021', \nbut sheet names can be varied in the extracting files, i.e., 'Test 2/2/2022, 'Test 3/3/2023', etc.\n```\nimport exco\n\nSheetName = str\nSheetNameAliasChecker = Callable[[SheetName], bool]\ntest_sheet_checker: SheetNameAliasChecker = lambda sheetname: 'Test' in sheetname\ncheckers: Dict[SheetName, SheetNameAliasChecker] = {'Test 1/1/2021': test_sheet_checker}\nprocessor = exco.from_excel(template_excel_path, sheet_name_checkers=checkers)\n```\n\nIn the case where there are hidden sheets that might have information that you dont want to extract,\nmake sure to set the accept_only_visible_sheets parameter to True\n\n```\nprocessor = exco.from_excel(template_excel_path, sheet_name_checkers=checkers, accept_only_visible_sheets=True)\n```\n\n# Custom Locator/Parser Etc.\n\nSee [Advance Features Notebook](notebooks/quickstart/1%20Advance%20Features.ipynb). But, in essence,\n```python\nprocessor = exco.from_excel('./custom_locator/custom_locator_template.xlsx',\n extra_locators={'diagonal_of': DiagonalOfLocator})\n```\n# Working with .xls files.\n\nExco will not read Excel 97-2003 workbook (.xls) files. Use [XLS2XLSX](https://pypi.org/project/xls2xlsx/) to convert .xls files to the supported .xlsx format.\n",
"bugtrack_url": null,
"license": "Private",
"summary": "Excel Comment Orm",
"version": "0.2.10",
"project_urls": {
"Homepage": "https://github.com/thegangtechnology/exco"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "2782f0189e5a0cd92f8946db4cbf3a47e1888bce49e0d39b5614a08766b17d37",
"md5": "e682b18c390a3e756a3905e0881fe4f2",
"sha256": "98d8f1712540d716a2a385f706bf83ec101ced640b48040ebb984c342296b286"
},
"downloads": -1,
"filename": "exco-0.2.10.tar.gz",
"has_sig": false,
"md5_digest": "e682b18c390a3e756a3905e0881fe4f2",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 40147,
"upload_time": "2023-05-15T17:05:39",
"upload_time_iso_8601": "2023-05-15T17:05:39.901234Z",
"url": "https://files.pythonhosted.org/packages/27/82/f0189e5a0cd92f8946db4cbf3a47e1888bce49e0d39b5614a08766b17d37/exco-0.2.10.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-05-15 17:05:39",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "thegangtechnology",
"github_project": "exco",
"travis_ci": false,
"coveralls": true,
"github_actions": true,
"lcname": "exco"
}
JSON
