Importing Data

Data can be loaded into Amplify in bulk from an Excel spreadsheet, using an integration. An integration imports one type of data, such as initiatives, tasks or cost values, into an initiative and the initiatives beneath it. You map each column in the spreadsheet to a field in Amplify, preview the result, and then import it. The same integration can be run again to load more data, or to update data already in Amplify.

Integrations are found under an initiative's Tools menu, as Integrations. Users are the exception: they're imported from Administration > Manage Users > Import Users. Schedules can also be imported from a schedule file — see Importing and Exporting Schedules.

This article covers both the Advanced and Simple Models. Imports are different from Administration > Integrations, which connects third-party applications — see Connecting a Third-Party Application.

What can be imported

Each integration has an Import Data Type, which sets what it imports. Where your instance calls benefits Impacts, these labels read Impacts instead.

• Initiatives/Projects — initiatives, and projects on a program. See Importing Initiatives.
• Benefits — benefits on an initiative. See Importing Benefits.
• Benefit Values (Time Phased) — Plan, Forecast and Actual values of benefits that already exist. See Importing Benefit Values.
• Costs — the costs on an initiative. See Importing Costs.
• Cost Values (Time Phased) — Budget, Forecast and Actual values of costs that already exist. See Importing Cost Values.
• RAID — RAID items. See Importing RAID Items.
• Schedule/Tasks — tasks and milestones. See Importing Tasks.
• Measures and Measure Values (Time Phased) — measures and their values, on the Advanced Model only. They aren't offered on a program. See Importing Measures.

Users are imported separately — see Importing Users.

Loading data for the first time

An import never creates the records it depends on, so load data in this order:

1. Import users, so they can be set as owners.
2. Import initiatives.
3. Import benefits, costs, measures, RAID items and tasks onto those initiatives.
4. Import benefit values, cost values and measure values onto those records.

Classifications, cost categories, units and custom fields aren't imported. Set them up in Administration first, and map custom fields to the classifications that use them.

Before you start

Turning on integrations

The initiative you set the integration up on needs integrations turned on:

• Advanced Model — check Enable Integrations on the initiative's classification, on its General Configuration tab. See Configuring Initiative Classifications (Advanced Model).
• Simple Model — turn on integrations on the program's Configuration > Features page. See Program Features.

Who can import

Creating and running integrations needs a team role on the initiative with the Integrations permission set to Full. The permission has no other levels: it covers creating, editing and running integrations. See Team Roles.

A Full Admin, or a user whose admin role sets Hierarchy to Full, can import without a team role. Integrations still need to be turned on for the initiative. See Admin Roles.

Choosing the initiative

Every record an import creates or updates must sit in the integration's initiative or beneath it. A row for anything outside it fails. Set the integration up on the highest initiative that holds everything in the file:

• Advanced Model — the initiative at the top of the part of the hierarchy you're loading. For a whole portfolio, that can be the top level of the hierarchy, which needs a Full Admin or an admin role with Hierarchy set to Full.
• Simple Model — the program. Projects don't have an Integrations menu item. Rows for a project's benefits, costs, RAID items and tasks name the project they belong to.

Anyone who can run the integration can change everything beneath its initiative, so don't set it up higher than the file needs.

Preparing the spreadsheet

• File type — an Excel file (.xlsx) of 5 MB or less. Other file types, such as .xls or .csv, can't be uploaded.
• Rows — one row per record. For the time-phased types, one row per value: a cost with 12 months of Actual values takes 12 rows.
• Row limit — a sheet can have up to 2,000 rows. Split larger files into several sheets or files, and import them one after the other.
• One sheet per run — only the sheet chosen when the file is uploaded is read.
• Values that match Amplify — classifications, owners, cost categories and other values must match what's set up in Amplify, or the row fails.
• Column order — the mapping is kept by column position, so keep the columns in the same order from one run to the next.

Creating an integration

To create an integration:

1. Open the initiative the integration is set up on.
2. Navigate to Tools > Integrations. The initiative's integrations are listed.
3. Click New Integration.
4. Enter a Name for the integration.
5. Select the Import Data Type.
6. Click Create.

The Adapter is always Excel.

Uploading the spreadsheet

To upload the spreadsheet:

1. On the initiative's integrations, click Run... next to the integration. A window opens.
2. Where the integration has been run before, select Upload New to choose a file, or Use Previous to use the last file again.
3. Choose the file.
4. Select Header? where the sheet's first row holds column headings.
5. Select the Sheet that holds the data.
6. Click Continue. The session page opens, showing the rows from the sheet, and a preview runs.

Mapping the columns

Each column is mapped to a field in Amplify. To map the columns:

1. Click Edit Mapping. A header appears above each column.
2. Select the Type for a column.
3. Set the fields that appear for that type, such as the Attribute or the Match on Attribute.
4. Repeat for each column that is imported.

The mapping saves as you go, and it's kept on the integration for the next run. A column left as No Action isn't imported. Until the mapping includes every column the import needs, a message says the minimum requirement hasn't been met, and Preview and Import... are disabled. Each import article lists the columns its type needs, and Import Mapping Reference describes every Type.

Previewing and importing

Previewing the import

The preview checks every row against Amplify and saves nothing. It runs when the session opens. After changing the mapping, click Preview to run it again.

Each row shows whether it will create or update a record, and whether it succeeds. A red icon on a cell marks an error: the row fails. An amber icon marks a warning: the row still imports. Hover over an icon to read the message.

Where rows fail, a banner says how many were unsuccessful. Click View these rows only. to show just those rows, and Show all rows. to return to the full list. Fix the mapping, or fix the file and run the integration again.

Importing the data

To import the data:

1. Click Import.... A confirmation opens.
2. Click Confirm. The rows update as they're imported.
3. Click End Session once the import has finished. The integrations list opens.

Rows with errors aren't imported, but every other row is. Failed rows don't stop the import, except for benefits being created: then Import... stays disabled until a preview has no failed rows.

Until the session is ended, the integration shows Terminate in place of Run... and can't be run again. Where a session was left open, click Terminate on the integrations list to end it.

Updating existing data

An integration can update records already in Amplify, as well as create new ones. To update records, include a column that identifies each one, and map it as Identifier Lookup, with Match on Attribute set to:

• Initiatives, benefits, costs and measures — ID or External ID.
• Tasks — Task ID or ID.
• RAID items — ID.
• Users — ID, name or email.

Each row updates the record it matches. Where no record matches and the mapping has everything needed to create one, the row creates a new record. Without an Identifier Lookup column, every row creates a new record, so running the same file twice creates everything twice.

Only mapped columns are updated. Map every other column as No Action, so its values are left as they are. A record's classification and the initiative it sits under can't be changed by an import.

The time-phased types don't need an Identifier Lookup: a value for a month that already has one replaces it.

Importing users

Users are imported from Administration > Manage Users, by clicking Import Users. The User Integrations page works in the same way as an initiative's integrations. Importing users needs Manage Users set to Full in your admin role, or Full Admin. See Importing Users.

For other ways to bring data into Amplify, see Connecting a Third-Party Application and Connecting to Amplify APIs.