dc/dc
dc/dc
4
1
Fork 0
Code Issues 16 Pull Requests Actions Packages Projects Releases 48 Wiki Activity

Complex Excel Uploads #69

New Issue
Closed
opened 2023-12-22 15:08:54 +00:00 by allan · 1 comment
allan commented 2023-12-22 15:08:54 +00:00
Owner
Copy Link

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:

image

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

image

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 commented 2024-01-01 16:10:44 +00:00
Author
Owner
Copy Link
  • 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
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
bug
Something is not working
duplicate
This issue or pull request already exists
enhancement
New feature
help wanted
Need some help
invalid
Something is wrong
question
More information is needed
wontfix
This won't be fixed
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
allan dcbot DylanLeong henrik mihajlo sabir yury
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: dc/dc#69
No description provided.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?