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.
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:
- [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
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
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
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:
Sheet2- an absolute reference/1- the first tab in the workbookABSOLUTE F4- an absolute referenceRELATIVE R[2]C[2]- In the XMLMAP_START case, this is the same asABSOLUTE B2MATCH P R[0]C[2]: My Test- search column P for the string "My Test" then move 2 columns rightMATCH 7 R[-2]C[-1]: Top Banana- search row 7 for the string "Top Banana" then move 2 rows up and 1 column leftBLANKROW- search down (from XLMAP_START) until an entirely blank row is found, then choose the row above itLASTDOWN- The last non blank cell below the XLMAP_START cellRELATIVE R[x]C[x]case, this is offset from from XLMAP_START rather than A1To 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 :
MATCH B R[1]C[0]: ITEMLASTDOWNMATCH C R[1]C[0]: AMOUNTLASTDOWNABSOLUTE F6MATCH F R[2]C[0]: CASH BALANCEMATCH E R[0]C[2]: Rent/mortgageMATCH E R[0]C[2]: Cell phoneTo 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:
Some additional notes:
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: