Complex Excel Uploads #69

New Issue

allan · 2023-12-22T15:08:54Z

allan commented

2023-12-22 15:08:54 +00:00

This feature enables the use of "EXCEL MAPS". It will enable a series of cells / ranges to be dynamically extracted from anywhere within a workbook - either through absolute / relative positioning, or by reference to a "matched" (search) string.

Data is always loaded to the same target table - MPE_EXCEL_UPLOADS

To drive the behaviour, a new configuration table must be added to the control library - MPE_EXCEL_MAP. The columns are defined as follows:

XLMAP_ID - a unique reference for the excel map
XLMAP_RANGE_ID - a unique reference for the specific range being loaded
XLMAP_SHEET - the sheet name in which to capture the data. Rules start with a forward slash (/). Example values:
- Sheet2 - an absolute reference
- /1 - the first tab in the workbook
XLMAP_START - the rule used to find the top left of the range. Use "R1C1" notation to move the target. Example values:
- ABSOLUTE F4 - an absolute reference
- RELATIVE R[2]C[2] - In the XMLMAP_START case, this is the same as ABSOLUTE B2
- MATCH P R[0]C[2]: My Test - search column P for the string "My Test" then move 2 columns right
- MATCH 7 R[-2]C[-1]: Top Banana - search row 7 for the string "Top Banana" then move 2 rows up and 1 column left
XLMAP_FINISH - The rule used to find the end of the range. Leave blank for individual cells. Example values include those listed under XLMAP_START, plus:
- BLANKROW - search down (from XLMAP_START) until an entirely blank row is found, then choose the row above it
- LASTDOWN - The last non blank cell below the XLMAP_START cell
- In the RELATIVE R[x]C[x] case, this is offset from from XLMAP_START rather than A1

To illustrate with an example - consider the following excel. The yellow cells need to be imported.

The MPE_EXCEL_MAP configuration entries might (as there are multiple ways) be as follows :

XLMAP_ID	XLMAP_RANGE_ID	XLMAP_SHEET	XLMAP_START	XLMAP_FINISH
MAP01	MI_ITEM	Current Month	`MATCH B R[1]C[0]: ITEM`	`LASTDOWN`
MAP01	MI_AMT	Current Month	`MATCH C R[1]C[0]: AMOUNT`	`LASTDOWN`
MAP01	TMI	Current Month	`ABSOLUTE F6`
MAP01	CB	Current Month	`MATCH F R[2]C[0]: CASH BALANCE`
MAP01	RENT	/1	`MATCH E R[0]C[2]: Rent/mortgage`
MAP01	CELL	/1	`MATCH E R[0]C[2]: Cell phone`

To import the excel, the end user simply needs to navigate to the UPLOAD tab, select the appropriate map (eg MAP01), and upload. This will stage the new records in MPE_EXCEL_UPLOADS which will go through the usual approval process and quality checks. A copy of the source excel file will be attached to each upload.

The corresponding MPE_EXCEL_UPLOADS table will appear as follows:

LOAD_REF	XLMAP_ID	XLMAP_RANGE_ID	ROW_NO	COL_NO	VALUE_TXT
DC20231212T154611798_648613_3895	MAP01	MI_ITEM	1	1	Income Source 1
DC20231212T154611798_648613_3895	MAP01	MI_ITEM	2	1	Income Source 2
DC20231212T154611798_648613_3895	MAP01	MI_ITEM	3	1	Other
DC20231212T154611798_648613_3895	MAP01	MI_AMT	1	1	£2,500.00
DC20231212T154611798_648613_3895	MAP01	MI_AMT	2	1	£1,000.00
DC20231212T154611798_648613_3895	MAP01	MI_AMT	3	1	£250.00
DC20231212T154611798_648613_3895	MAP01	TMI	1	1	£3750
DC20231212T154611798_648613_3895	MAP01	CB	1	1	£864
DC20231212T154611798_648613_3895	MAP01	RENT	1	1	£800.00
DC20231212T154611798_648613_3895	MAP01	CELL	1	1	£45.00

Some additional notes:

The MPE_AUDIT table will NOT be updated (as the target will only ever receive appends)
LOAD_REF will be applied after submit (will display "TBC" in UI prior to submit)
No need for a timestamp on the table as one will be already linked to the LOAD_REF

UI Changes

Given that these uploads are not an "EDIT" (yet, an edit can be accurately described as a "LOAD") the top bar should be modified as follows:

The LOAD screen can then have a dropdown in the left similar to VIEW, but this time with "Tables" and "Files" as follows:

The Tables view is the regular menu (showing LIB.DS values from MPE_TABLES). The Files view will show a list of XLMAP_ID's from MPE_EXCEL_MAP.

Tasks:

DDL for new tables in build job
Entries for both tables in MPE_TABLES
Validation for MPE_EXCEL_UPLOADS (eg LOAD_REF) should NOT be in the POST_EDIT_HOOK (as that could be used for business validations)
Include sample configuration (attached is Basel Pillar 3 reporting excel for Royal Bank of Canada)
Rename EDIT tab to LOAD
Create Tables / Files dropdown
Display list of maps in sidebar
Fetch rules for a particular map (services/editors/getxlmaps)
Enable screen to ingest the excel, and display the extracted data prior to submit

This feature enables the use of "EXCEL MAPS". It will enable a series of cells / ranges to be dynamically extracted from anywhere within a workbook - either through absolute / relative positioning, or by reference to a "matched" (search) string. Data is always loaded to the same target table - MPE_EXCEL_UPLOADS To drive the behaviour, a new configuration table must be added to the control library - MPE_EXCEL_MAP. The columns are defined as follows: * **XLMAP_ID** - a unique reference for the excel map * **XLMAP_RANGE_ID** - a unique reference for the specific range being loaded * **XLMAP_SHEET** - the sheet name in which to capture the data. Rules start with a forward slash (/). Example values: * `Sheet2` - an absolute reference * `/1` - the first tab in the workbook * **XLMAP_START** - the rule used to find the top left of the range. Use "R1C1" notation to move the target. Example values: * `ABSOLUTE F4` - an absolute reference * `RELATIVE R[2]C[2]` - In the XMLMAP_START case, this is the same as `ABSOLUTE B2` * `MATCH P R[0]C[2]: My Test` - search column P for the string "My Test" then move 2 columns right * `MATCH 7 R[-2]C[-1]: Top Banana` - search row 7 for the string "Top Banana" then move 2 rows up and 1 column left * **XLMAP_FINISH** - The rule used to find the end of the range. Leave blank for individual cells. Example values include those listed under XLMAP_START, plus: * `BLANKROW` - search down (from XLMAP_START) until an entirely blank row is found, then choose the row above it * `LASTDOWN` - The last non blank cell below the XLMAP_START cell * In the `RELATIVE R[x]C[x]` case, this is offset from from XLMAP_START rather than A1 To illustrate with an example - consider the following excel. The yellow cells need to be imported. ![](https://git.datacontroller.io/attachments/ec28debd-a236-4635-9369-be4bfa6111fd) The MPE_EXCEL_MAP configuration entries _might_ (as there are multiple ways) be as follows : |XLMAP_ID|XLMAP_RANGE_ID|XLMAP_SHEET|XLMAP_START|XLMAP_FINISH| |---|---|---|---|---| |MAP01|MI_ITEM|Current Month|`MATCH B R[1]C[0]: ITEM`|`LASTDOWN`| |MAP01|MI_AMT|Current Month|`MATCH C R[1]C[0]: AMOUNT`|`LASTDOWN`| |MAP01|TMI|Current Month|`ABSOLUTE F6`|| |MAP01|CB|Current Month|`MATCH F R[2]C[0]: CASH BALANCE`|| |MAP01|RENT|/1|`MATCH E R[0]C[2]: Rent/mortgage`|| |MAP01|CELL|/1|`MATCH E R[0]C[2]: Cell phone`|| To import the excel, the end user simply needs to navigate to the UPLOAD tab, select the appropriate map (eg MAP01), and upload. This will stage the new records in MPE_EXCEL_UPLOADS which will go through the usual approval process and quality checks. A copy of the source excel file will be attached to each upload. The corresponding MPE_EXCEL_UPLOADS table will appear as follows: | LOAD_REF | XLMAP_ID | XLMAP_RANGE_ID | ROW_NO | COL_NO | VALUE_TXT | |---------------|----------|----------------|--------|--------|-----------------| | DC20231212T154611798_648613_3895 | MAP01 | MI_ITEM | 1 | 1 | Income Source 1 | | DC20231212T154611798_648613_3895 | MAP01 | MI_ITEM | 2 | 1 | Income Source 2 | | DC20231212T154611798_648613_3895 | MAP01 | MI_ITEM | 3 | 1 | Other | | DC20231212T154611798_648613_3895 | MAP01 | MI_AMT | 1 | 1 | £2,500.00 | | DC20231212T154611798_648613_3895 | MAP01 | MI_AMT | 2 | 1 | £1,000.00 | | DC20231212T154611798_648613_3895 | MAP01 | MI_AMT | 3 | 1 | £250.00 | | DC20231212T154611798_648613_3895 | MAP01 | TMI | 1 | 1 | £3750 | | DC20231212T154611798_648613_3895 | MAP01 | CB | 1 | 1 | £864 | | DC20231212T154611798_648613_3895 | MAP01 | RENT | 1 | 1 | £800.00 | | DC20231212T154611798_648613_3895 | MAP01 | CELL | 1 | 1 | £45.00 | Some additional notes: * The MPE_AUDIT table will NOT be updated (as the target will only ever receive appends) * LOAD_REF will be applied after submit (will display "TBC" in UI prior to submit) * No need for a timestamp on the table as one will be already linked to the LOAD_REF # UI Changes Given that these uploads are not an "EDIT" (yet, an edit can be accurately described as a "LOAD") the top bar should be modified as follows: ![image](/attachments/3fa4f3e3-cb8c-4806-9e05-e007b6ddafbb) The LOAD screen can then have a dropdown in the left similar to VIEW, but this time with "Tables" and "Files" as follows: ![image](/attachments/1c16e126-223b-4c38-acb8-084357d19c47) The Tables view is the regular menu (showing LIB.DS values from MPE_TABLES). The Files view will show a list of XLMAP_ID's from MPE_EXCEL_MAP. Tasks: - [x] DDL for new tables in build job - [x] Entries for both tables in MPE_TABLES - [x] Validation for MPE_EXCEL_UPLOADS (eg LOAD_REF) should NOT be in the POST_EDIT_HOOK (as that could be used for business validations) - [x] Include sample configuration (attached is Basel Pillar 3 reporting excel for Royal Bank of Canada) - [x] Rename EDIT tab to LOAD - [x] Create Tables / Files dropdown - [x] Display list of maps in sidebar - [x] Fetch rules for a particular map (services/editors/getxlmaps) - [x] Enable screen to ingest the excel, and display the extracted data prior to submit

excel_map.png

254 KiB

image.png

9.7 KiB

image.png

13 KiB

2023q4pillar3-quantitative-tables.xlsx

859 KiB

allan referenced this issue from a commit

2023-12-27 16:04:58 +00:00

feat: model changes for #69

allan referenced this issue from a commit

2024-01-01 14:10:53 +00:00

chore: adding xlmaps in startupservice response, #69

allan commented

2024-01-01 16:10:44 +00:00

services/public/startupservice now contains the lits of XLMAP_IDs
services/editors/getdata now contains an ISMAP attribute
new services/editors/getxlmaps created for fetching rules for a particular XLMAP_ID
services/editors/stagedata now adds the LOAD_REF to the uploaded data

* services/public/startupservice now contains the lits of XLMAP_IDs * services/editors/getdata now contains an ISMAP attribute * new services/editors/getxlmaps created for fetching rules for a particular XLMAP_ID * services/editors/stagedata now adds the LOAD_REF to the uploaded data

allan referenced this issue from a commit

2024-01-24 13:48:09 +00:00

feat: Complex Excel Uploads

allan closed this issue

2024-01-24 13:48:09 +00:00

gitea-actions referenced this issue from a commit

2024-01-24 17:50:02 +00:00

chore(release): 6.4.0 [skip ci]

Sign in to join this conversation.