In a previous post, I took the time to review the Bulk Import Tool, one of the Project Server 2010 Solution Starters available for free download from Codeplex. The Bulk Import Tool imports large numbers of projects from SharePoint lists. As I discussed in that post, some of the data doesn’t quite make it into Project Server – or may make it into Project Server in a form that must be edited.
That makes the Bulk Edit Tool a natural next step. This tool allows the user to open any number of projects within the Project Center, and then to change the project level fields in a convenient datasheet interface. In all honesty, I‘ve had decidedly mixed luck with getting this tool to work, and was about to skip a discussion of it, but then I decided to give it a second try.
Thus, I spun up my Hyper V machine, installed the add-in and spent some time understanding how it works. This post represents the results of that second look.
First off, the installation is pretty much the same as the Bulk Import Tool. If you need guidance on how to install it, I refer you to that post.
That being said, the main quirks that I identified in this tool are pretty much as follows:
- Sometimes the tool just doesn’t work. I am not sure why. I suspect that it just stops working when the number of projects exceeds a certain limit, but I don’t know. In environments where I can’t get it to work, I fall back on using the Bulk Import Tool to perform bulk edits (as documented here).
- When selecting the projects to be edited, the user has the option to add a filter. For instance, if I only want to see the active projects, I can set a filter to only display projects where a custom field called Active=”Yes.” The filter actually seems to work pretty well on non-flag fields. Every time I ran the filter on a flag field, the tool would freeze.
- Filters to identify which fields are blank seem to be problematic. For instance, when I filtered on a multiline text field to see if it was blank (=” ”), the filter wouldn’t run.
- Filters on text and date fields seem to work just fine.
If you can get past the filter step, the tool allows you to set the values for the selected project fields. Some things that caught my eye here:
- Flag fields, which show up in Project Server as offering the option of only Yes or No, show in the tool as having three possible states: Yes, No, and Unknown. As near as I can tell, Unknown corresponds to a blank cell, which is a bit weird as I’ve always learned that a blank cell is the same as entering “No.”
- The owner field may not be changed in bulk, i.e. the user can’t select an option at the top and then use the highlight and fill option to populate the cells underneath. The Bulk Edit Tool may still be used to edit the owner field, but each cell must be clicked on one by one. (again, here the Bulk Import Tool is slightly more useful.)
- Some fields seem to freeze the tool, for instance in preliminary testing, trying to bulk edit the Hyperlink tool didn’t quite succeed.
After clicking on the option to update the projects, I would recommend the following:
- Watch the Manage Queue page to confirm that the server is still processing the projects. Sometimes it’s hard to get the feedback that the server has completed publishing all of the projects.
- Review the option to Force Check In Enterprise Objects. Occasionally, some projects got stuck in checked out mode.
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.
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.