Integration via CSV File

This part of the wiki describes structure of CSV files as well as next configuration steps and upload of such into Epicflow system.

To start, use the project and resource pool templates found by the next link CSV Template. It is not mandatory to use this template, but the structure is optimized for Epciflow usage and helps in understanding what information is missed in files imported from third parties system.

Resource Pool

Epicflow use Resource Pool as the source of the Project’s resources. It must contain required information is the scope of specified CSV file structure. Template of such can be download from the corresponding Wiki page.

For the purpose of Epicflow, the Resource Pool requires:

  • adding the Groups name to the Resource Pool;
  • adding the Resource names to the Resource Pool and specify belonging of the particular Resource to a specific Group;
  • Set a special key for identification difference between Resource and Group for the Epicflow; The R of G column.
  • Optionally: Define resource availability

If Resource Pool file is going to be imported from the third party system and later uploaded into Epicflow system, then you would need additionally to:

  • verify that imported file does contain all required fields and information. If not, add those which are miss
  • verify that appropriate format of values is used for each particular field and correct such in case of necessity
  • remove all duplicate fields if there such
  • verify that separator used in CSV file is “;” unless a different one separator is configured for usage on mutual agreement.

Structure

Resource Pool structure is defined with next columns:

  • “ResID” contains ID of the Resource or Group Resource. It should be unique withing the file.
  • “ResName” – describes Resource or Group Name.
  • (Optional) “Email” – required for further invite procedure of the users into the system.
  • “ResGrID” – should contain “ResID” of the Resource Group to which Resource is assigned to. For the Resource Group it should be an empty field.
  • “Type” – special keys column. For each Resource should contain “R” key and for the Resource Group it should be “G”.
  • “CapFactor” – specifies capacity of the resource where 1 is equal to 1 FTE. For Resource Group field should be empty.
  • “StartDate” – usually contains a date when resource was added into the system. By default it should be in format “DD-MM-YYYY” unless opposite is defined and agreed.
  • (Optional) “EndDate” – date after which capacity of the resource will be equal to “0”.By default it should be in format “DD-MM-YYYY” unless opposite is defined and agreed.

Screen #1 – Resource Pool – Structure

Creating Resource Pool

  • Save downloaded template in the Epicflow file folder and rename it according to the project naming convention. i.e. “resourcepool.csv”. 
  • As next step you need to specify Resource Groups to which all resources will be connected in result. For that next three fields must be filled in per each record of the group:
    • “ResID” unique ID in the scope of the file.
    • “ResName” – specifies Group Name.
    • “Type” – special keys column for which key “G” must be set.

Screen #2 – Resource Pool – Resource Group

Also there is a special group called “UCLT” (“Un-Compressible Lead Time”) which can be used as a resource in case of ordering time. If task is assigned on UCLT group, then on daily basis, Epicflow automatically subtracts Capacity of the group from the tasks’ Remaining Work.

  • To add UCLT group you need to add a group named “UCLT” as described at previous step.

Be aware that UCLT group shouldn’t contain any resource assigned to it and it doesn’t work with the task’s duration. That’s why you need always specify Remaining Work equal to required duration.

Screen #3 – Resource Pool – UCLT Group

  • As soon as groups are specified next step will be adding Resources with linking such to each group. For that next fields must be filled in per each record of the resource:
    • “ResID” unique ID in the scope of the file.
    • “ResName” – specifies Name of the Resoruce.
    • “ResGrID” – should contain “ResID” of the Resource Group to which Resource is assigned to.
    • “Type” – special keys column for which key “R” must be set.
    • “CapFactor” – specifies capacity of the resource where 1 is equal to 1 FTE.
    • “StartDate” – usually contains a date when resource was added into the system. By default it should be in format “DD-MM-YYYY”.

Screen #3 – Resource Pool – Resoruces

NOTE

Avoid an empty cells for mandatory fields cause it may prevent further reading of the table.

Avoid input of inappropriate types cause it may prevent further reading of the table.

For the case when one resource must be in two groups simultaneously next actions must be performed:

  • Add resource (for example “Radio Drieënhuizen”) within first group (for example “SecondOrder”) as described above
  • Add second resource with slightly different name (for example “Radio Drieënhuizen_v2”) within second group (for example “NewFirstOrder”)
  • Set the same Email Address for both resources.

In such way Epicflow system accepts such identification as one resource with the shortest name “Radio Drieënhuizen” which do belong to “NewFirstOrder” and “SecondOrder” groups.

Screen #4 – Resource Pool – Resource within multiple Groups

  • Last but not the least, do not forget to save and make a backup copy of the Resource Pool file.
Note

Please note that as soon as resource is uploaded in to the Epicflow system any change of Resource parameters and values must be done in Epicflow directly.

This restriction came from the fact that one Epicflow resource can be linked to many external systems where resource parameters may differ, so update synchronization from Epicflow to Resource Pool and via verse will be not correct for already uploaded resources.

Project File

In a similar way to Resource Pool file it must contain required information is the scope of specified CSV file structure. Template of such can be download from the corresponding Wiki page.

For the purpose of Epicflow, the Project requires:

  • Beforehand prepared Resource Pool file
  • Project Name
  • Defined Start and end Project dates
  • Defined intermediate Milestones
  • Defined summary tasks if such are applicable in your project structure
  • Defined tasks list with estimations and resource allocations
  • Specified network dependency connections

If Project file is going to be imported from the Third Party system and later uploaded into Epicflow system, then you would need additionally to:

  • verify that imported file does contain all required fields and information. If not, add those which are miss
  • verify that appropriate format of values is used for each particular field and correct such in case of necessity
  • verify that separator used in CSV file is “;” unless a different one separator is configured for usage on mutual agreement.

Structure

Project file structure is defined with next columns:

  • “Project_ID” – Defines ID of the appropriate project. Number should be unique for each project but will be the same for each item of the Project (Summary, Task, Milestone).
  • “Project_Name” – This cell will contain Name of the Project and should be specified for all tasks or summary tasks records in csv file.
  • “Task_UID”– Column contains ID of the appropriate task from the external system. Number should be unique in project scope.
  • (Optional) “Priority” – for priority identification from external system.
  • “Task_Name” – Column basically contains the name of the Tasks or the Tasks group or Project itself.

If the Tasks in the cell Bn, Bn + 1, … Bn + x, going successively one after another, contains the same description and each of them corresponds to the type “Task”, from column F, the system will record the record data as one task with multiple assignment of resources to this task.

  • “Task_Type” – Column defines context of the record in a row and may contain one of the next description:
    – ProjSum
    – Summary
    – Task
    – Milestone
  • “UID_Summary” – Contains the task group ID (Summary ID) to which the task belongs.The Task Group ID is taken from the corresponding cell in the “Task_UID” column
  • “Res_UID” – A unique resource identifier of the resource assigned to the specified task. Value is taken from corresponding “ResID” field of the Resource Pool file.
  • “Res_Name” – Column contains Resource Name assigned to the task specified in the current line. Do correspond to “ResName” field of the Resource in Resource Pool file.
  • “Group_UID” – This column contains ID of the group to which resource does belong or which is assigned for task fulfillment. Value is taken from the corresponding “ResID” field of the Resource Pool file.
  • “Unique_ID_Predecessors” – Cell indicates predecessor (task which must be accomplished before) to the current task. It contain a number from the “Task_UID” column.
  • “Remaining_Work” -The remaining task execution time specified in minutes.
  • “Actual_Work” – The actual task execution time specified in minutes.
  • “Scheduled_Work” – An initial estimate of the time required to perform the current task in minutes.
  • “Deadline” – Contains planned Deadline for the project or task or summary task to be accomplished.
  • “Start” – Contains planned Start Date
  • “Finish” – Contains planned Finish Date
  • (Optional) “Baseline_Work” – Contains initial snapshot of your schedule when you first save your project information to track progress and compare your updates
  • “Assignment_ID” – It’s ID of resource assignment on appropriate task from the
    external system. Must be unique.
  • (Optional) “Hyperlink” – may be used to specify hyperlink on any external source.
  • (Optional) “ReadyToStartByPM” – if you would like to mark task as one which can be started before predecessor fulfillment then this field can be sued to identify such case
  • “Assignment_Units” – Contains a description of the number of specified resources for task accomplishment. By default always 1.

Next fields are skipped in description since they are not obligatory and meaning of such is clear from the column naming: “ID”, “Proj_Keyword”, “Proj_Manager”, “Proj_Folder”, “Pro_Category”, “Task_Cost”, “Task_Remark1”, “Task_Remark2”, “Task_Remark3”, “Name_Closest_MS”, “Deadline_First_MS”, “Can_Start”, “Skill”, “Notes”, “Duration UCLT”.

Creating Project

  • Save downloaded template in the Epicflow file folder and rename it according to the project naming convention. i.e. “Project_Name.csv”. 
  • Adding of the project starts with first record where we declare such. For that next fields must be filled in per each project:
    • “Project_ID” – Defines ID of the project. Number should be unique for each project.
    • “Project_Name” – defines Name of the Project.
    • “Task_UID”– defines internal in scope of project ID and should be unique in the project scope.
    • “Task_Name” – contains name of the Project itself which will be displayed in Epicflow system.
    • “Task_Type” – Column defines context of the record and in our case it should be “ProjSum”
    • “Start” – Contains planned Start Date of the project
    • “Finish” – Contains planned Finish Date of the project
    • “Assignment_ID” – for declaration record start it with 1.
    • “Assignment_Units” – description of the number of specified resources for task accomplishment but for the first declaration record it should be ‘0’
    • As for other fields keep them empty for the declaration row.

Screen #5 – Project file – Declaration line

  • Next step is to define top level Summaries. For that next fields must be filled in per each Summary
    • “Project_ID” – ID of the project which should be the same as we have declared at previous step
    • “Project_Name” – should be also taken from the same column of the previous step.
    • “Task_UID”– defines internal in scope of project ID and should be unique in the project scope.
    • “Task_Name” – contains name of the Summary itself which will be displayed in Epicflow system.
    • “Task_Type” – Column defines context of the record and in our case it should be “Summary”
    • “Assignment_ID” – must be specified unique value. In our case we can simply increment it.

Screen #6 – Project file – Summary 

  • As top level Summaries are added you can complicate structure of the project by adding next level Summaries. It should be performed in a similar way as described above but in addition field “UID_Summary” must be specified. It should contain the Summary ID to which the Summary belongs.It is taken from the corresponding cell in the “Task_UID” column.
  • Next step of iteration is to add description for the tasks. For that next fields must be filled in per each Task:
    • “Project_ID” – ID of the project which should be the same as we have declared at first step
    • “Project_Name” – should be also taken from the same column of the first step.
    • “Task_UID”– defines internal in scope of project ID and should be unique in the project scope.
    • “Task_Name” – contains name of the Task itself which will be displayed in Epicflow system.
    • “Task_Type” – Column defines context of the record and in our case it should be “Task”
    • “UID_Summary” –  should contain the Summary ID to which the task belongs.It is taken from the corresponding cell in the “Task_UID” column.
    • “Res_UID” – A unique resource identifier of the resource assigned to the specified task. Value is taken from corresponding “ResID” field of the Resource Pool file.
    • “Res_Name” – Column contains Resource Name assigned to the task specified in the current line. Do correspond to “ResName” field of the Resource in Resource Pool file.
    • “Group_UID” – This column contains ID of the group to which resource does belong or which is assigned for the task fulfillment. Value is taken from the corresponding “ResID” field of the Resource Pool file.
    • “Remaining_Work” -The remaining task execution time specified in minutes.
    • “Actual_Work” – The actual task execution time specified in minutes.
    • “Scheduled_Work” – An initial estimate of the time required to perform the current task in minutes.
    • “Start” – Contains planned Start Date
    • “Finish” – Contains planned Finish Date
    • (Optional) “Baseline_Work” – Contains initial snapshot of your schedule when you first save your project information to track progress and compare your updates
    • “Assignment_ID” – must be specified unique value. In our case we can simply increment it.
    • (Optional) “Hyperlink” – may be used to specify hyperlink on any external source.
    • (Optional) “ReadyToStartByPM” – if you would like to mark task as one which can be started before predecessor fulfillment then this field can be sued to identify such case
    • “Assignment_Units” – Contains a description of the number of specified resources for task accomplishment. By default always 1.

Screen #7 – Project file – Tasks creation

  • Next step is to build-up connections between items of the Project to have chains of tasks in place. For that must be used field:
    • “Unique_ID_Predecessors” – which indicates predecessor (item which must be accomplished before) to the current item. It contain a number from the “Task_UID” column.
  • To finish planning you also need to specify milestone as a deadline and for that next fields must be specified:
    • “Project_ID” – ID of the project which should be the same as we have declared at first step
    • “Project_Name” – should be also taken from the same column of the first step.
    • “Task_UID”– defines internal in scope of project ID and should be unique in the project scope.
    • “Task_Name” – contains name of the Milestone itself which will be displayed in Epicflow system.
    • “Task_Type” – Column defines context of the record and in our case it should be “Milestone
    • “UID_Summary” –  should contain the Summary ID to which the Milestone belongs.It is taken from the corresponding cell in the “Task_UID” column.
    • “Unique_ID_Predecessors” – which indicates predecessor (item which must be accomplished before) to the current item. It contain a number from the “Task_UID” column.
    • “Deadline” – Contains planned Deadline for the project or task or summary task to be accomplished. By default it should be in format “DD-MM-YYYY” unless opposite is defined and agreed.

Screen #8 – Project file – Milestone creation

  • Last but not the least is to save all changes and make backup of the CSV file.

Please note that CSV file can contain more than one project description. However, you can also divide all projects and store them in separate files.

NOTE

Avoid an empty cells for mandatory fields cause it may prevent further reading of the table.

Avoid input of inappropriate types cause it may prevent further reading of the table.

First data Import

Prerequisites for this step is to have Resource Pool and Project CSV files ready. Please note that before files upload they must be placed at server side. For that you should use ftp site provided by Epicflow. Information how to configure it is described at Getting your Reports article. Other related and required information can be provided by Support team on your request.

Connection

As soon as files are placed at server side in required location

  • Select “Import” item at the left menu bar to be redirected to the CSV Import configuration page

Screen #9 – CSV Import – Connection configuration

  • Define location of the CSV files where they been placed at server side in “Directory where the files are placed” field. Be default it should be something like “C:\inetpub\ftproot\<CompanyHomeFolder>”
  • Project files mask” – can be used to set key to filter out project csv files but for the first import keep it as it is without changes.
  • Resource Pool file name” – name of csv Resource Pool file must be specified here but if you’ve used standard no changes to this field should be done.
  • On editing finish press “Next” button to start “Scope and Projects” configuration.

Scope and Projects

Screen #10 – CSV Import – Scope and Projects

  • With “Jira Scope” drop-down list you can specify set of CSV files which must be imported into the system.
  • Project Field Mapping – defines what you would like to have as a project in Epicflow. For CSV import only one possible option is available:
    • Project – Each separately created project in CSV file will be imported as a Project to the Epicflow

On finishing previous two steps system performs readout of Resource Pool and selected Project files. Result of such action are defined “Groups“, “Users” lists and “Projects“.

  • Select those project which you would like to have imported into the system.

Screen #10 – CSV Import – Projects to import

  • If you put a tick on Automatic Import, the system will automatically import any newly appeared projects from the defined scope if related projects have no shared tasks between projects. Be aware, that in case you have unchecked any Project in the Projects list (despite the automatic import button is on), the project won’t be imported to the system neither now, not later automatically, unless it’s selected again.
  • In case if NO Epicflow CSV templates been used you need to specify mapping of corresponding fields of your CSV to the values required by system. For that Press “Expand” link to the right from “Fields Mapping” text and set such relation.

Screen #11a – CSV Import – Fields Mapping

Screen #11b – CSV Import – Fields Mapping

  • Scroll down in the “Scope nad Projects” block and press “Next” button. Such action will redirect you to the “Groups and Users” configuration step.

Groups and Users

Third step will require your attention to configure and define Groups and Users for the import

  • Import Users – Epicflow allows you to choose all users from the Jira system or users from the selected scope of Projects.
  • Group assignee – defines source of group assignee
  • Group Field Mapping and Prefix –  It will allow you to specify the Prefix(es) which should be used to refine the groups list.

Closer to the top of the screen, you’ll see the list of Groups with the specified number of Users in the loop and a separate field displaying the names of the users and groups they belong to.

  • Review this section to determine the right relation of users to groups and select those which are needed to be imported into the Ecpiflow. Be aware that all deselected users will appear in the unmapped area of “Resource Management” page and you still will be able to draw them to the process later on.

You can either remove a group relation or add a new one to a specific user by simple left mouse click.

Screen #12 – CSV Import – Groups and Users

  • To finalize third step press “Next” button at the right bottom part of the frame.

Finalize Import

  • Now, on condition that everything’s configured correctly, you can finalize the import by left mouse click over the “Import” button. Be aware that even that this action cannot be undone Import settings can be reconfigured later as needed.

Screen #13 – CSV Import – Finalize Import

Having imported your CSV data into the Epicflow system will redirect you to the Pipeline page with all projects imported into the system.

Note

As soon as resource is uploaded in to the Epicflow system any change of Resource parameters and values must be done in Epicflow directly.

This restriction came from the fact that one Epicflow resource can be linked to many external systems where resource parameters may differ, so update synchronization from Epicflow to Resource Pool and via verse will be not correct for already uploaded resources.

Second Import Configuration

If previously your system was already configured to connect with a different instances you’ll see a list of such connections as soon as you visit Import page.

Screen #13 – CSV Import – Connections list

  • To create a new connection, press “New connection” button. As result you’ll be automatically redirected to the Connection configuration page . All you would need to do is to cover all steps as during First Import Configuration described above.

Epicflow system doesn’t allow multiple connection to one instance.Only one connection per instance.

Edit connection

To edit any previously created connection you would need to

  • navigate to the Import page.
  • by calling a context menu as on screenshot select option “Edit”. As result you’ll be redirected to the Connection Configuration page.
  • going step by step you will be able to edit configuration but for final apply of such changes all steps must be covered and “Import” button should be clicked as designed.

Screen #14 – CSV Import – Connection editing 

The system will allow you to specify new rules and add projects you’d like to import OR remove an established connection from the list. At the end you’ll be asked to confirm your actions and in case you approve, all changes will be implemented.

Delete connection

To delete any previously created connection you would need to

  • navigate to the Import page.
  • by calling a context menu as on screenshot select option “Delete”.You’ll be asked for confirmation of your action.
  • As soon as you confirm your action Connection link will be removed as well as all projects related to that with the corresponding resource pool and mapping links. Nevertheless internal Epicflow users and groups will remain.Repeat Connection configuration will be treated by system as a new one and no historical or configuration data will be restored.

Troubleshooting

General recommendation is to communicate with Support department in case of any questions or issues related to the files upload.

Nevertheless several hints that can be used in case of issue with files upload:

  • Incorrect dates format is specified either in Project or Resource Pool file
  • “UID_Summary” field contains not the Summary id but id of a Task or Milestone
  •  “Res_UID” or “Res_Name” or “Group_UID” in the Project file contains incorrect or none existing values in Resource Pool file.
Updated on March 10, 2020

Was this article helpful?

Related Articles

Leave a Comment