How-To Guide: By Sheet
Overall Dataflow Diagram for the Remote tables Import & transform
Objective
Import data from user's excel files into JustPerform remote tables
Business users are updating their excel files, which contain Cash schedule 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 different structure of rows/columns with no standard list of values, varied parameters & different data ranges. Once updated, all the companies with their multiple Offline excel files send 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 & take the Information forward to Group cashflow.
Scenario 1
Import 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 -
We will now see on how to Import this excel data to JustPerform remote tables.
Scenario Flow : Visual dataflow diagram for this scenario for representation purposes
As mentioned in previous sections, Its a Two Step approach -
Step1 - Create Remote Tables
1.1 Define Connection file schema
Based on the assessment of the excel file, we need to update Connection File Schema as per the detailed Guidelines & Instructions provided in 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 scenario
Instance Header - Upload Cash schedule, hence updated the Instance details as same
Apply to Sheets : Set the import to applicable to all Sheets with 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. Incase of multiple sheets, each sheet will be mapped to the respective sheet cell B2 & imported
VERSION : Mapping to user selection while importing the file
Parameters : Mapping the "Heading" name range from user excel file as parameter table values
Tables : there are 4 blocks of data, identified with excel ranges/name ranges in user's excel. Mapping these to 4 tables with table ID & reference to Individual name ranges to pick from
Table Fields: each table values in user's file are 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
1.2 Create connection
Quick preview on How-To Steps of creation Connection
1.3 Upload Activity flow
Quick Preview on How-To update Activity flow with Import data task from excel to Remote tables
Step2 - Upload excel files
2.1 Select task from Activity flow & Import file
Based on the authorization & task access, users can execute the task deployed for Importing the file, User's can upload the file (single/multiple) along with user selections parameters if any defined & then execute the task. Check log for the status of the execution.
Refer to the User's excel files for reference we will use for this Scenario -
2.2 Execute & Review Remote tables
Quick Preview on How-To upload file, execute & finally review the remote table data refresh.
_______________________________________________________________________________________________________
Authorization
Note that 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
Update by default , the Re-Import will always REPLACE data based on Instance
Last updated