In my previous post, I discussed some of the mechanics of using the Bulk Import tool to either import data into Project Server or to perform a batch edit of existing project data. In this post, I wanted to expand on that topic by walking through the procedures that seemed to work for me when performing both of those functions.
I would strongly recommend reading both posts before attempting to manipulate data in a production environment.
Importing Legacy Data Into Project Server
The import process begins with the data in Excel. While your legacy data may reside natively in a SharePoint list, my guess is that the data must be scrubbed to match the new requirements of a Project Server environment, and therefore will be exported to Excel for analysis and modification prior to beginning this process. If your data resides in another data repository, then that only increases the chances that you will have to export to Excel. Hence, we start with Excel.
1) Scrub the data.
- Review project names for duplicates, leading spaces, trailing spaces, strange characters, etc.
- Review date fields to confirm that they appear to be dates and not text entries.
- Confirm the owner field matches the user name as displayed in Project Server exactly.
- Confirm that all outline fields and lookup fields match the lookup tables exactly – and that outline fields only include the lowest level entry – not the entire string of the outline hierarchy (see the previous post for more information).
- I found it helpful to add a unique identifier column on the left hand side of the data set that included both text and numbers, i.e. PROJ01, PROJ02, etc. When importing, this allowed me to identify exactly which projects were causing issues – and allowed me to skip one step in the SharePoint list modification prior to import.
2) Import the list into SharePoint.
- At this point, you may wish to make a strategic decision about the basic process that you intend to follow. As I mentioned in the last post, the import process only seems to work on a specific number of projects. At various times, I was able to import projects in batches of either 200 or 300. I am not sure what drove that, and maybe it was actually 200 all along, but it seemed to me that 200 seemed to be a safe limit. You may either create multiple SharePoint lists of no more than 190-199 projects – or you may opt to create a single list, import, remove the items that successfully imported, and then reimport the remainder. I personally found the latter process to be a little less time consuming – as it naturally relegated all of the exception projects with issues to the last batch. The first approach is equally valid however. Choose one.
- You also may only select one Enterprise Project Type per batch import. You may wish to split out your various EPTs into different lists for ease of import.
- Within PWA, select View All Site Content, choose the option to create a list, and then click on the option to Import an Excel Spreadsheet. This will import the source data to SharePoint.
3) Review the List Settings
- Navigate to the list settings. Review any multiline text fields (such as Description), and convert the field from the default rich text to plain text.
- Confirm that the fields imported properly. A date field shouldn’t import as a multiline text field for instance.
- Modify the view and confirm that the Project Name field is displayed in a form that is not linked to the actual item. If it is linked, it will not be available for mapping during the import process.
4) Create a Project Server View of the imported data set
- You now should go to Project Center within PWA, and if you haven’t already, create a view of the imported data. I usually add “TEMP” as a prefix to this view so if anyone actually sees it, they will know it’s not a real view.
- I’ll configure this view to only show the fields being imported, and perhaps to include any relevant filters.
5) Run the import process.
- Click on the button to import from SharePoint and review the field mapping. If any fields don’t map automatically, I would go back into the Excel file and change the column header. Chances are that I messed up the column header, for instance including “Costs” instead of “Cost” or “Name” instead of “Project Name.”
- Validate that all of the fields from the Excel sheet and the SharePoint list actually show up to be imported. For instance, if the Project Name field was the leftmost column on import, then the field was imported as the item title. The default SharePoint view will include that column as a link field, and it won’t be available to import into Project Server. If this is the case, go back into the SharePoint list view settings and add the unlinked Project Name column back into the view to make it available for the import.
- The import tool will display a summary of the project import status after the import has concluded *if* the total number of projects being imported fall within a specific parameter. I am not sure what this parameter is exactly, but the last time I did this, it seemed to be about 200 projects. If you are trying to import more than 200 projects, the green circle will stop spinning, and your queue will stop – signifying that the import process has concluded. Simply navigate to your Project Center view and confirm the projects were imported. If you are importing less than 200 projects, the tool will display a list of project status at the end of the import process. Projects appear to be imported in the order in which they are displayed in the SharePoint list.
- Check the Force Check In option to confirm that all projects have been checked in.
6) Validate the Data Import
- The easiest way to do this is to navigate to your Project Center view, refresh it, and then export the view to Excel. Once in Excel, you can use formulas such as vlookup, match, or other comparisons to confirm that the data in fact migrated.
- If you’re importing less than 200 projects, you may wish to make edits to the SharePoint list, and then run the import process one by one for the exception projects.
- If you’re importing more than 200 projects, I would match the project names in the exported Project Center list to my source data, and then remove any items that had imported successfully. The remaining projects get saved in another Excel file (Master Projects 2), and the process is repeated.
7) Clean Up Project Server
- To clean up the import process once you’re confident the data looks good, go into the import list and make sure it doesn’t appear on the Quick Launch bar.
- Go to Site Actions > View All Site Content and delete any of the temporary lists you had to create to pull the data into Project Server. Note that those lists will go to the site Recycle Bin and get deleted permanently as per your configured policies.
Modifying Existing Data
To modify existing data, we more or less follow the same process as above with a couple of minor tweaks:
1) Prepare Project Center View
- As above, create a Project Center View that contains the projects and fields to be edited.
- Export this view to Excel.
2) Modify the Data
- Make the changes to the data in Excel. Do not change the project names. These names will allow us to map the data changes back into the Project Server data.
- When importing the Excel sheet back into the SharePoint list ensure that the project name is the leftmost column. This will import the project name as the item name.
3) Import the Changes
- Run the bulk import tool. Map only the fields you’re changing. You do not need to map the Project Name. The changes will be implemented in the Project Server data. If you are editing more than 200 projects, wait until the green circles stop spinning and the queue shows no more activity. If editing less than 200 projects, wait until the report appears at the end to show which projects were successful and which needed some modification before reimporting. Note that there is no need to select an EPT type.
- Repeat as necessary.
4) Clean Up Project Server
- Review the Force Check In interface to confirm all projects have been checked in.
- Delete any lists used to import the data into Project Server.