How-To Guide: By Sheet
Overall Dataflow Diagram for the Remote Tables Import and Transform:
Objective
Import data from users' Excel files into JustPerform remote tables.
Business users update their Excel files, which contain cash schedules with divestment information for their respective companies. These Excel files have semi-structured information with many blocks of data in each sheet. Each block of data has a different structure of rows/columns with no standard list of values, varied parameters, and different data ranges. Once updated, all the companies send their multiple offline Excel files to the central team for consolidation.
Now, the group wants to import all the files' information into structured tables in JustPerform for further data processing and to take the information forward to group cash flow.
Scenario 1
Import an Excel worksheet with multiple sets of data into remote tables.
Refer to the user's Excel files for reference we will use for this scenario:
To Import this Excel data to JustPerform remote tables:
Scenario Flow: Visual dataflow diagram for this scenario for representation purposes:
This is a two-step approach: Create remote tables and Upload Excel files.
1. Create Remote Tables
Define Connection file schema
Based on the assessment of the Excel file, we need to update the Connection File Schema as per the detailed guidelines and instructions provided in the section Connection Schema.
Refer to the updated Connection File, after mapping changes required for the user's file in this scenario:
Summary of changes made to the Connection file, based on the user's file review:
- General Sheet: Connection name details are updated based on the scenario.
-
Instance Header: Upload cash schedule, hence updated the instance details accordingly.
- Apply to Sheets: Set the import to be applicable to all sheets with the prefix "Cash Schedule*".
- Instance Model: Setting the instance model as JP_Revenue, available in the workspace.
-
Instance Details
- COMPANY: Mapping the company field values to pick from Excel cell B2. In case of multiple sheets, each sheet will be mapped to the respective sheet cell B2 and imported.
- VERSION: Mapping to user selection while importing the file.
- Parameters : Mapping the Heading name range from the user's Excel file as parameter table values.
-
Tables:There are four blocks of data, identified with Excel ranges/name ranges in the user's Excel. Mapping these to four tables with table ID and reference to individual name ranges to pick from.
- Table Fields: Each table value in the user's file is mapped to data table field values individually.
- Column axis: Updated specific columns to map to table field values.
- Row axis: Updated column ranges to transpose while mapping to field values.
- Measure: With reference as data, has been mapped to field values for amount.
Create connection
Quick preview on how-to steps of creating a connection:
Upload Activity flow
Quick preview on how-to update activity flow with import data task from Excel to remote tables:
2. Upload excel files
Select task from Activity flow and Import file
Based on the authorization and task access, users can execute the task deployed for importing the file. Users can upload the file (single/multiple) along with user selection parameters if any defined and then execute the task. Check the log for the status of the execution.
Please refer to the user's Excel files for reference we will use for this scenario:
Execute and Review Remote tables
Quick preview on how-to upload file, execute, and finally review the remote table data refresh.
Note: Authorization: The remote tables import follows the role authorization set for the user. Only the users who have access to the respective instance field values, as defined in the instance details connection schema, will be able to import/upload the data. Example: In the above scenario COMPANY, if users have access to 1010 only, JustPerform will not upload data for 1030.
Note: By default, the re-import will always replace data based on the instance.