How to Export Data to MS Excel
Import functionalities in Communigram include the following:
- Import/export resources
- Import/export competencies
- Import/export tasks and assignments between resources and tasks
Auto-detection of sheet format
To check the format of a sheet, Communigram reads the first line in the sheet which is supposed to contain the names of sheet columns.
At least two columns of the appropriate sheet format should exist in order for the sheet to be considered as recognized. A recognized sheet could be resources, competencies, tasks or assignments sheet.
Example:
The first line of resources sheet should contain the following columns names:
rsrc_short_name, rsrc_name, parent_rsrc_short_name, role_id, email_addr, user_id, office_phone.
If two columns at least exist in the sheet then the sheet will be considered as recognized excel sheet.
The order of columns is not critical which means for example in the resources sheet, if rsrc_name occurs at the first column and rsrc_short_name in the second one, then communigram will recognize the first column as the names of resources and the second as the short names of resources.
The second line of each sheet is supposed to have short explanation about the column name so its content is not interpreted as data and data is expected to start from the third line of sheet.
Notes:
Columns names are not case sensitive.
Unrecognized columns will be ignored.
Auto-detection of file?s sheets
Resources, competencies (roles), tasks and assignments should be separated in different sheets; these sheets could be in one file or in separated files and communigram detects automatically the sheets presented in the file.
Logically, the user imports the roles, then the resources and finally the tasks with the assignments between the resources and the tasks for a certain project.
We differentiate between two cases:
- Resources, competencies, tasks and assignments are at the same Excel file: Communigram checks all the sheets existing in the file, competencies are imported first if competencies sheet exists, then resources if resources sheet exists then tasks then assignments. The order of sheets in the Excel file doesn?t affect the order of importing data, this means that competencies are always imported before resources (when both sheets exist at the same file) even if resources sheet occurs before the roles sheet in the excel file. Assignments couldn?t be imported without importing the tasks first so if assignments sheet exists and tasks sheet doesn?t exist then assignment sheet will be ignored.
- Resources, competencies and tasks are in different Excel files: Communigram examines the format of the sheet, if it?s a role sheet then it imports roles and if it?s a resources sheet then it imports resources and so on. Assignments couldn?t be in a separated file since they couldn?t be imported without importing the tasks first. Sheets with unrecognized format will be ignored. This gives us the possibility of adding one or more sheets to the file containing some explanations and comments.
1. Import/export resources
The format of resources sheet is expected to be like the following:
As we mentioned before, the order of columns in the resources sheet is not important.
Resources sheet may contain invalid data so it?s important to check the validity of data before importing it and here is the description of communigram behavior when it performs the operation:
- If resource short name or resource name are not defined, the entire line will be ignored.
- Resources short names are unique and if a new resource in the excel file has the same short name of already existing one, communigram checks the name of existing resource: - If the two resources names are equivalent, then communigram considers the new excel resource as an old one and updates the other resource attributes (parent_rsrc_short_name, email, phone?) - If the two resources names are not equivalent, communigram changes the short name of the new resource by adding the suffix " _new " and creates it
- If the file contains short names of parents that don't exist neither in Communigram resources nor in the excel file, the invalid parents will be ignored
- If the file contains roles short names that don?t exist in communigram, the invalid roles will be ignored.
- If the file contains invalid users login names, the invalid users will be created with empty login passwords.
- Hierarchy between resources will be respected even if children occur before their parents in the Excel file.
2. Import/export Competencies
The format of roles sheet is expected to be like the following:
Description of communigram behavior:
- If role short name or role name are not defined, the entire line will be ignored.
- Roles short names are unique so if a new role in the excel file has the same short name of already existing one, communigram checks the name of existing role: - If the two role names are equivalent, then communigram considers the new excel role as an old one and updates the other role attributes (parent_role_short_name). - If the two roles names are not equivalent, communigram changes the short name of the new role by adding the suffix " _new " and creates it
- If the file contains short names of parents that don't exist neither in Communigram roles nor in the excel file, then the invalid parents will be ignored.
- Hierarchy between roles will be respected even if children occur before their parents in the excel file.
3. Import/export tasks and assignments between resources and tasks
The format of tasks sheet is expected to be like the following:
Description of Communigram behavior:
- If task activity (name) is not defined, the entire line will be ignored.
- Line IDs and parent IDs need to be integers, non-integer values will be ignored.
- Lines with IDs? that exist in both Id and PARENT_ID columns will be considered as sub-projects else it will be considered as task or milestone.
- TASK_TYPE cells could be assigned only two values, (T for tasks and M for milestones) and the default value is T.
- When the line is considered as a sub-project, the cell of TASK_Type for this line is expected to be empty and its value will be ignored when defined.
- If the line is a sub-project or Milestone, duration and predecessors will be ignored when defined (duration, predecessors could be defined only for tasks)
- Parents? IDs that don?t exist in the ID column of excel file will be ignored.
- Duration is supposed to be in double, non-double values will be ignored
- The expected format of dates is (dd/mm/yyyy), Dates with invalid format will be ignored.
- Predecessors ?ids are separated by comma, Predecessors? ids which don?t exist in ID column will be ignored.
Assignments between resources and tasks
The format of the assignments sheet is expected to be like the following:
Description of Communigram behavior:
- All tasks IDs that don?t exist in task sheet will be ignored and if task id is not defined, the entire row will be ignored.
- Invalid resources' and competencies' short names will be ignored
- If both resource short name and competency short name is defined in a line, and if resource short name is valid then competency short name will be ignored.
- If task_id is the id of sub-project or milestone, then the resource or role is a responsible by default and workload is ignored.
- Is_responsible can take only one value (R) and when it?s not defined for a task, the resource or competency will be considered as participant.
- If workload is not defined or if it?s not a valid double value, it will take the default value (100.0).
- If the file contains more than responsible for a single task/subproject/milestone, then the last resource/competency mentioned in the file will be considered as the responsible