This article explains how to use the Resource Import/Export Tool. The Resource Import/Export Tool allows you to bulk upload many resources at once in a more streamlined manner rather than adding each resource one at a time. This tool also allows you to update multiple existing resources at once, rather than editing them one at a time. This feature is only available for the Site Collection Administrators.
If you have a large Resource Pool, be aware that when importing, your entire Resource Pool will get re-imported, even if you only added a few resources or made a few changes. So, it may be more time saving to manually add/edit the few resources rather than use the Import/Export Tool.
This feature is only compatible with Microsoft Excel 2010 and later.
1. Resource Pool Preparation
Be sure that you have your Resource Pool configured to your business needs PRIOR to using the Resource Import/Export Tool. Configure the User Management Settings, such as Departments, Roles, Work Hours, Holiday Schedules, Site Permission Groups, etc. Do not use your own Excel file on your desktop.
Add any new fields that you want as metadata details for each resource you will add into your Resource Pool. New fields are created in the Resources List App (unless you are using a different List App for your Resource Pool). To confirm which List App you are using as the Resource Pool list, refer to the article for General Settings.
Note: If a field is set as Hidden, it will not export to the Excel file.
2. Navigate to the Resource Pool
- Select the Settings Gear icon on the Icon Bar.
- Under the User Management heading, select Resource Pool. This will direct you to the Resource Pool page.
Note: Alternatively, you could select the Resources link from the Quick Launch.
3. Select Export Excel
- Open the Tools Menu.
- Select Export Excel.
- When the status updates, click the link 'here' to launch the new Excel File/spreadsheet.
4. Review of Excel File Field Settings
If prompted, select Enable Editing.
When the Excel file launches, select Enable Content, which enables the Macros in the file. This Excel file has a number of hard-coded features and settings.
Note: Any existing resources who are already entered in the Resource Pool will export as well.
Within the Excel file, there are the following Field types and descriptions:
- Choice Fields (with single selection available): These choice fields will show as a drop down selection. Example: Department.
- Choice Fields (with multiple selection available): These choice fields will show as a pop-up selector window. Example: Permission Groups.
- True/False Fields: This aligns with fields that are Yes/No fields. Example: Generic - If the resource is a generic, mark as True/Yes.
- Text Fields: Enter a text value.
- Number Fields: Enter a number value.
- People Fields: Select a person's name from the drop down.
5. Add-Ins Menu
Sort Resources: The Excel Sort feature cannot be used from the standard ribbon menu option. Instead, the Sort feature is available via the Add-Ins ribbon.
Calculate Display Names: The Display Names field will auto-populate based on the First Name and Last Name fields.
6. Resource Pool Default Fields in Excel
The following fields are standard in the EPM Live System, unless they have been modified by a Site Collection Administrator. The Timesheet fields are automatically added to the Resource Pool if the Timesheets feature has been added to your Site App.
- ID: DO NOT enter anything in this column. The ID will auto-populate when the Excel file imports. If you edit this field in any way, the import will error.
- Generic: True/False field. Select True for Generic Resources and False for Named Users.
- Display Name: If the Generic field is False, do not type in this field, as the display name is auto-populated with the user's first and last names. If Generic is True, enter the display name for the Generic Resource.
- First Name: Text field. Enter the User's first name. Skip this field for Generic Resources.
- Last Name: Text field. Enter the User's last name. Skip this field for Generic Resources.
- SharePoint Account: Text field. If you are an on-premise customer, enter the user's SharePoint Account. If you are an online customer, DO NOT anything in this field, as it will be populated for you automatically with the EPM Live user account (based on the Email Address).
- Email: Text field: Enter the User's email address. Skip this field for Generic Resources.
- Permissions: Multi-select pop up window. Click a permission group name to select/deselect the permission groups. Skip this field for Generic Resources.
- Timesheet Manager: Drop down choice field. This field is only applicable if Timesheets are in use, and if Timesheet Approvals are enabled. Select the User who is the Timesheet Manager. Skip this field for Generic Resources.
- Timesheet Delegates: Drop down choice field. This field is only applicable if Timesheets are in use. Select the User who is the Timesheet Delegate. Skip this field for Generic Resources.
- Standard Rate: Number field. Enter the rate, if applicable. If the Cost Planning App is in use, DO NOT enter the rate, as this data will be update automatically based on the role rate and/or the Named Rate table.
- Timesheet Administrator: True/False field. This field is only applicable if Timesheets are in use. Select True if this user is a Timesheet Administrator. Select False if they are not. Skip this field for Generic Resources.
- Disabled: True/False field. Select True to disable a User. When a User is disabled, all permissions in the entire site collection are removed.
- Available From: Date field.
- Available To: Date field.
- License Type: Drop down choice field. Select the applicable license type. Skip this field for Generic Resources.
- Holiday Schedule: Drop down choice field.
- Work Hours: Drop down choice field.
- Role: Drop down choice field.
- Department: Drop down choice field.
- User: The default content type is User for all resources in the resource pool. Select True.
- EXTID: (This field may or may not appear). DO NOT enter anything in this column. The EXTID will auto-populate when the Excel file imports.
7. Save the Excel File
You will be prompted to save the file locally to your client machine. Save the file.
8. Select Import Excel
Return to the EPM Live site.
- Open the Tools Menu.
- Select Import Excel.
9. Browse to Select your Saved Excel File
- Select the Select a File button and navigate to find your locally-saved Excel file.
- When the file is selected in the window, click the Import button.
10. Import Details
- Import Details: As the import runs, you will see the status bar with percentage of completion. Each resource getting imported will be listed, with success status once complete.
- To see the Import Log, click the link for Import Log.
11. Import Log
Import Log: The log will list specific details about each resource getting imported.
12. Updated Resource Pool
Once the Import is complete, your newly added and updated resources will show in the Resource Pool.
Note: If you are an online EPM Live customer, the newly added resources will receive the alert email with the Site URL and name, just as when users are added individually.