Creating Project Data Import Files

To create or conduct mass edits on project data, import project data into Accolade. Before running the import, create two specially named reference tables that will create a file that contains the data to import and map the data to fields in the Accolade database. The reference table pair begins with ACC_PI_, which identifies the tables as reference tables that contain project import data.

Note: To import this type of data to Accolade, you must have the Process Designer, Project Importer, and Reference Table Manager user roles. To import a new version of an existing file, you must be assigned as the owner of the reference table file used for the import.

Follow these steps to import project data:

Create a file that contains the data to import (Data table).
Map the data to fields in the Accolade database (Config table).
Upload, validate and run the data import.
Troubleshoot the import.

Accolade supports reference tables as spreadsheet, CSV, and XML files through the Reference Tables page, and through an auto-loader service. The information and examples provided are in spreadsheet format, contact Sopheon Customer Support for example files in additional formats.

Create a File That Contains the Project Data to Import (Data Table)

The data table contains the data to import to Accolade. Administrators and Process Designers add the initial versions of the ACC_PI data table. The assigned table owner, which requires the Reference Table Manager user role, can add new versions of the table as needed.

Create the project data table as a spreadsheet, CSV, or XML file, ensuring that the contents of the file meets the following requirements:

Important! Enter all dates in mm/dd/yyyy format, and enter the decimal separator in numbers as a period (.).

Important! In order to successfully import projects you must use the ProjectSecurityList System Name.

Component	Requirements
File Name	Project data must be in a file named ACC_PI_`<pair identifier>`_Data, where `<pair identifier>` matches the `<pair identifier>` in the project importer configuration table and is unique within Accolade. For example, ACC_PI_ConsumerElectronics_Data.
Rows	Each row contains a different project, and each project exists in only one row.
Column Names and Position	Column headings are in the first row of the worksheet. You can name columns as you see fit for your installation. The configuration table described below maps the columns in the data table to the respective fields in the Accolade database.
Data	The following column and data is *required* to create an imported project or to update an existing project: Project ID - Either the Project ID displayed on the project header, not the system ID, or a string metric that is used to identify the project. No special characters or spaces are allowed. If the project code does not match any project on the importing server, the import creates a new project. If the project code does match, the data in the import is used to modify the matching project. Important! If project IDs for existing projects are calculated by a metric or created automatically by the Auto-Generate Project IDs system parameter, use a string metric as the unique identifier in the imported data. If you set the unique identifier to Project Code the projects import successfully, but create duplicate projects. Use a string metric as the unique identifier to avoid duplicating projects. You can ensure that projects are added by giving them project codes that you are certain do not match those of any existing projects in Accolade, such as a, b, c, etc. Project Codes entered as numbers strip all leading and trailing zeros, unless you first import with a reference table of the same name without data that sets the project code to a String data type. The following columns and data are *required* to create an imported project, but *optional* when updating an existing project: Project Name - The name, up to 64 characters long, that identifies the project's purpose. If the project name is generated using a calculated metric, this field can be blank and is not required. Model Name - The name of the model the project follows for completion. You can update projects that use an in-active process model. Access Group Name - The name of the access group that determines which users have access to this project. The access group must already exist in Accolade, the import process does not create access groups. The following columns and data are *optional* when creating or updating projects: Metrics - Each metric field is in a separate column. When importing values for multi-select list metrics, enter each selection for the metric in a single cell in a pipe ( \| ) delimited list. For example, Value-A\|Value-B. The data type for each imported metric must match the data type of its reference table column in the Data table. List metrics are in string type columns. To set the data types of the reference table columns, see Requirements and Considerations for Reference Tables. Rich text metrics cannot be imported. Delete Project - Enter Yes, Y, True, or 1 to delete the project. All other values are treated as No. Team Leader - The login of the Accolade user that is assigned as the project's project manager. Only users with the Project Manager user role can be assigned as team leaders. If you create a project without designating a project manager, you can select one later. Team Leader Can Manage - Enter Yes, Y, True, or 1 if the user in the Team Leader field also is given the rights to assign and replace team members on the project. All other values are treated as No. Team Members - The user login and system name of the function the user is assigned, using the following format: `<domain>`\`<UserLogin1>`:`<systemNameFunction1>`. Separate users with a pipe ( \| ) character to create a delimited list. If the colon is not specified, all text between the pipes ( \| ) is considered a user login. Auto Migrate Rules - The system name of the workflow containing rules to migrate a project to a different model. The workflow does not need to be in a certain state or associated with a model. If the import contains projects with migrations, a slight delay in migrating the projects could occur after the import completes. Enforce Project Security - Enter Yes, Y, True, or 1 if the team members and the project manager must have access to the project through access groups, security lists, or security profiles. All other values are treated as No. Any users that do not have the same security as the project are not added to the team. Existing users on projects that are being updated are removed accordingly. Project Description - Enter the project description. Project Start Date - Enter the project start date in mm/dd/yyyy format. Metadata Fields - Each metadata field is in a separate column. Enter Yes, Y, True, or 1 for metadata fields that are either on or off, such as VisibleToReports. See Allowed Project Metadata for a list of allowed metadata. Security Lists - For the project's security list access, create a column for each security list in the system. For each project, enter the ID numbers of the project's selected security list items in a pipe delimited list that begins and ends with a pipe ( \| ) character. The pipes ensure that each security list column is created as a String type in the reference table. If the lists were created manually, the IDs can be found in the database. If the lists were created in reference tables, the IDs are found in the ID column.

Download Sample

Map the Project Data to the Accolade Database (Config Table)

The configuration table contains the data mapping information so Accolade knows what type of data to expect in the accompanying data file. Administrators and Process Designers add the initial versions of the ACC_PI config table. The assigned table owner, which requires the Reference Table Manager user role, can add new versions of the table as needed.

Create the projects configuration table as a spreadsheet, CSV, or XML file, ensuring that the file meets the following requirements:

Component	Requirements
File Name	The project configuration must be in a file named ACC_PI_`<pair identifier>`_Config, where the `<pair identifier>` matches the project data file and is unique within Accolade. For example, ACC_PI_ConsumerElectronics_Config.
Rows	Each row maps a column in the data table to a specific type of project data.
Column Names and Position	The project configuration must have the following column names, from left to right as listed below: Export Column Name - Enter each column heading that is included in the data table. Metric System Name - Contains the system name of the metric whose column in the data table is in the same row. Project Metadata Name - Identifies the data in the data table whose name is in the same row. Unique Identifier - Specifies the column containing the data that uniquely identifies the projects. The column can be either the metadata ProjectCode or a string metric. Only one string metric can serve as the identifier for the import. Enter TRUE in the row that contains the identifier column heading name. Important! If project IDs for existing projects are calculated by a metric or created automatically by the Auto-Generate Project IDs system parameter, use a string metric as the unique identifier in the imported data. If you set the unique identifier to Project Code the projects import successfully, but create duplicate projects. Use a string metric as the unique identifier to avoid duplicating projects. A unique identifier is required to run a successful import. If Accolade is set to auto-generate project IDs, use a string metric as the unique identifier in the imported data. New projects are created with an auto-generated ID.
Accolade Metadata Names	Use the metadata names as listed in Allowed Project Metadata in the Metadata Name column of the configuration table to map the data to the appropriate locations in the Accolade database.

Download Sample

Allowed Project Metadata

Use the metadata names as listed in below the Project Metadata Name column of the configuration table to map the data to the appropriate locations in the Accolade database. Unless otherwise noted, if an item is missing or invalid, the import ignores the data and makes no updates in Accolade.

Column Name	Accepted Values	Additional Notes
ProjectCode	String
ProjectName	String
ProcessModelName	Valid Process Model display name
AccessGroup	Valid Access Group display name
DeleteProject	Yes, Y, True, 1*
TeamLeaderLogin	user login	As it displays for the user on the User Admin page.
TeamLeaderCanManageTeam	Yes, Y, True, 1*
AllowTeamMemberDeletion	Yes, Y, True, 1*
TeamMemberLogins	user login	As it displays for the user on the User Admin page. Login and function combinations are in a pipe-delimited string: `<domain>`\`<UserLogin1>`:`<systemNameFunction1>`\|`<domain>`\`<UserLogin2>`:`<systemNameFunction2>`. Invalid team member logins are skipped but the rest of the project is created.
AutoMigrateRules	Configured migration rule name	Values can be found on the workflow details page. The workflow must be checked as Is Smart with a migration rule.
EnforceProjectSecurity	Yes, Y, True, 1*
ProjectDescription	Any
ProjectStartDate	Date
ProjectEndDate	Date
ProjectClosed	Yes, Y, True, 1*
ProjectClosedNotes	Any	Only updated if the project is closed.
VisibleToReports	Yes, Y, True, 1*
ExcludeFromLineUp	Yes, Y, True, 1*
LastGateNumber	Numeric	Requires LastGateDecisionName in the table. Important! LastGateNumber and LastGateDecisionName changes can only be applied to current or future gates. If you are updating a project and the LastGateNumber is set to a gate that has already past, the import does not update the gate information. For example, you have an existing project that had gate decisions of Go for gates 1 & 2, and is currently approaching gate 3. If your project import has a LastGateNumber of 2 and a LastGateDecisionName value of Conditional Go, the import does not update the changes for the gate 2.
LastGateDecisionName	String	Requires LastGateNumber in the table. Values are available on the Configuration > Entity Names page.
IdeaSubmitterLogin	user login	As it displays for the user on the User Admin page.
IdeaSubmitterEmail	String	Must be in a valid email format (with the @ and . in the address, for example, name@sopheon.com)
IdeaSubmitterName	String
NotifySubmitter	Yes, Y, True, 1*
InTrouble	Yes, Y, True, 1*
InTroubleReason	String
InTroubleNotes	String
TeamLeaderChangeReason	Event Reason Code	Event reason code is configured on the Event Reason List tab of the Class definition page.
GateDate01, GateDate02,...GateDate20	Date	Updates to gate dates through an import are considered manually entered dates for the project. If gate dates for a project are set using a metric defined in the model, any updates to the gate date through an import override the date set by the metric. Each gate date must be represented in individual columns.
ProjectCurrencyCode	three character currency code value	Currency codes are configured using the currency reference table.
ProjectSecurityList1...ProjectSecurityList 5	Security List value ID	If security lists are disabled in your system, or you provide a security list that does not exist, the project is created without a security list designation. To include more than one security list use a ( \| ) pipe character.

* For any column that accepts Yes, Y, True, or 1, you can also enter No, N, False, or 0 if it helps you when entering data in the spreadsheet. All values other than Yes, Y, True, or 1 are treated as No when you upload the spreadsheet.

Notes:

If you want to use a saved team for projects created through the import process, these teams must be manually assigned on the project's Team page. See Adding Saved Teams to Projects for more information.

Learn More: