The Bulk Import Tool is one of the superstars of the solution starters that were released onto Codeplex to augment the native functionality of Project Server 2010. Again and again, I have been forced to use this tool – and to support clients who are trying to use this tool. This post represents the first in a two part series based on my experiences trying to understand how the tool works, and how it can be best used to support an EPM implementation.
This has been a difficult post for me to write. Honestly, I think I’ve written and rewritten this post about three times. This has been one of those times where every time I think I’ve figured out exactly how the tool works, I’ve then been proven wrong. As of this writing, I think that I can at least predict how the tool works with reasonable precision.
In the end, I realized that I probably had too much content for a single post, and thus am splitting this into two. Today’s post will focus on the mechanics of the tool, how to get it installed, and an overview of what works and what doesn’t seem to work. Tomorrow’s post will focus more on recommended procedures and how to use the tool to both import project data and to perform bulk updates of existing project data.
Note that the following observations were made after using the tool in three distinct environments with different configurations. I was using the version in the December 2010 release.
The Bulk Import Tool basically imports projects from a SharePoint list into Project Server.
Why would you use this? Well, for a couple of reasons. If you’re just now implementing Project Server 2010, then there’s a good chance that your organization has a list of projects lying around that must be entered into the system. The list doesn’t have to be in SharePoint. Any old spreadsheet or Access database will suffice. As long as you can get the data into a spreadsheet, you’re pretty much good to go.
The other reason you might use this technique is to update the project level metadata on existing projects. You can use the tool to effectively import specific fields into the Project Center views. This kind of conflicts with another solution starter, the Bulk Field Edit tool, which will be a topic for another post. For now, let’s just say that I am still trying to understand the rules upon which the Bulk Edit Tool works, and am far from making it behave predictably.
Installing the Tool
The tool installation is pretty straightforward. Simply download the Zip file from Codeplex. Unzip the directory, and copy the Bulk Import Tool folder to the desktop of your server.
Open the Deploy Powershell file in Notepad. Change the Site URL to the correct URL for your Project Server instance.
Right click on the Deploy Powershell file, and select the option to Run As Administrator.
Confirm that you get the following message.
Navigate to the Project Center to confirm the new button appears on the Ribbon.
Prepare the Data
Careful preparation will ensure that the import process occurs smoothly. Unless your processes require otherwise, my general recommendation would be to export any list to Excel, clean the data in Excel, and then create a custom list based on the spreadsheet. This will make your life a lot easier.
In my real life experience, this part was complicated by the fact that there’s some weird issue with SharePoint 2010 which means some site collections don’t allow you to create a list by importing a spreadsheet. Some site collections do. The first site collection caused the following error: Method ‘Post’ of object ‘IOWSPostData’ Failed. There are fixes for this if you simply do a search on the Web.
That had me sweating for a while, until I realized that I could use a quick and dirty workaround of just creating the list in a datasheet view, then copying and pasting the data from my spreadsheet. The second site collection worked just fine. If you’re importing a large number of projects (in my case, about 1,000), your experience will be much smoother if you find a site collection that actually works, or can simply fix the issue.
The other thing that will make your life easier is to understand exactly what data types will import using this tool, and what data types do not import. I did a rough test with almost each of the SharePoint field types with the following results.
Note that much of the content below applies to SharePoint lists. If you’re using Excel to clean the data and simply importing into SharePoint for the purpose of staging your data for the tool, you may not have to worry about such things as calculated fields, etc. Calculated Excel fields will simply import into SharePoint as text or number fields.
- Text, Single Line – imports to text fields, no problem.
- Text, Multiple Line – imports to multiline text fields, no problem – provided that the field has been set to plain text within SharePoint. When importing from Excel, the default setting is a rich text field – which will not import into Project Server. I’ll discuss that in more detail tomorrow.
- Text, Multiple Line (w/ Append) – imports the most recent entry to multiline text fields. Does not import historical records. See the note above with regards to plain text vs. rich text.
- Choice field – imports into text fields, no problem.
- Number field – imports into number fields, no problem.
- Number field (percentage) – imports into number fields as a decimal.
- Currency – imports into an available currency field. Note that the import process will convert the currency symbol to whatever the default Project Server currency is. I.e. a SharePoint field denominated in Chinese Yuan will import into Project Server as US Dollars.
- Date – imports into date fields. There’s a weird quirk (which could have been my virtual environment) that dates get appended with a 5:00 AM start time. So 3/21/2011 gets imported as 3/21/2011 5:00 AM (if you’re displaying time, which few people do for project level fields). This may be annoying if you end up importing SharePoint date fields into Project Server text fields.
- Date and Time – imports into date fields. This too demonstrated that weird quirk where the import seemed to add 5 hours to the imported time. For the most part, that’s probably not an issue. 3/21/2011 6:30 AM becomes 3/21/2011 11:30 AM. However, in some cases, that will flip the date to the next day, i.e. 3/21/2011 10:00 PM imports as 3/22/2011 3:00 AM. I didn’t spend much time troubleshooting this, but keep it in mind if your dates seem to change randomly.
- Lookup Single Item – imports provided the lookup table in Project Server has exactly the same values. I’ve noticed when exporting from SharePoint to Excel however that Lookup fields do get a bit garbled – so you may take that in consideration if you’re using SharePoint columns that reference other lists.
- Lookup Multiple Item – imports provided the lookup table in Project Server has exactly the same values. Note also the caveat above.
- Yes or No – Doesn’t import. Period. Doesn’t even give you the option of importing this field. (The workaround for this was to add a text field to the list, populate it with “Yes” or “No” values, copy this into a temporary Project Server field, then use the Bulk Edit Tool to filter on the temporary field and populate the actual Project Server flag field.)
- Person or Group – Looks like it should import, but doesn’t. Probably because it contains the “\” character.
- Hyperlink – Does not appear to import.
- Calculated field – Imports only as a text field. Even if you calculate a number value, this will only offer you the option of mapping it to a text field.
Note that Outline fields function a bit differently than you might expect. For instance if you have an outline in Project Server configured as 1.Red, 1.Blue, 2.Purple, and 2.Mauve, you do not want the source data to include each level of the outline. If you try to import “1.Red,” the tool will not import the value and leave the cell blank. If you import “Red,” the value will get imported and mapped to the outline structure appropriately. I am not sure how the system will behave if you have identical multiple lower level values such as 1.Red and 2.Red. My guess is that you would want to change that temporarily to 1.Red and 2.Red2, import the data, then change the lookup table back to 2.Red. As the project values are tied to the lookup table GUID, the change should cascade through existing projects. As usual, you should test that in a test environment before deploying in production.
A couple more notes:
- The Owner field is a key part of the Project Server security configuration. This field will import provided that the list entry matches the AD name displayed in Project Server exactly. Do not try to include the actual AD login account name, as this won’t work, and the tool will balk at importing the “/” that is part of the AD. So, import “Scott Smith” and not “AD/SSmith.” If the system cannot recognize the owner name, the field will default to the account of whomever is actually performing the import. For this reason, you may want to confirm that you are logged in as the service account for the import. I’d also recommend considering opening up the Project Manager:My Organization permissions immediately after importing so project managers can change the ownership of mislabeled projects to themselves. Once that’s been sorted out, ratchet security permissions back down.
- The Project Name field will throw an error on special characters. As far as I can tell, the only characters that won’t throw an error are the dash or parentheses. You’ll have to remove periods, quotation marks, colons, semi-colons, and commas from project names prior to importing them. Also remove any leading or trailing spaces in the project name. Duplicate project names will throw an error, so you may want to confirm that this will not be an issue.
After assessing which fields will or will not import into Project Server using this tool, confirm that your enterprise custom fields and lookup tables have been created within Project Server. You may wish to turn off any required fields as they may cause the import process to fail.
Next, if importing a large number of fields, modify the column names to match the Project Server field names exactly:
- The tool will automatically map the fields provided the names match.
- Automatic mapping won’t work for required fields as they show up in the tool as with the “*” prefix, i.e. *Project Departments.
- The tool won’t automatically map an imported Project Departments Field to the *Project Departments field in Project Server – but allows you to manually map these fields.
At this point, you should also pay attention to project names, and ensure that project names do not include any “strange” characters such as “&” or “/.” If you’ve used Excel to clean the data, go ahead and create a new custom list using the option to import the spreadsheet.
Now the real fun starts.
Import the Data
Before running the tool, you may wish to log into the server using your service or administrator account. Whomever runs the tool ends up being the owner of all of the imported projects where the owner field could not be resolved, and aesthetically, it probably looks better to have 1,000 projects assigned to a service account than to your own name.
Let’s run the import tool. From the Project Center, click on the button in the Project Center and add the URL of the source list. Click the Validate button.
You’ll see the tool will take a stab at mapping the fields that share a name with the target fields. As mentioned above, Yes or No fields do not get the option of importing into Project Server.
Also note that the calculated SharePoint field attempts to map to a text field even though it has been set within SharePoint to be a calculated number. When reviewing the options to map it to, the list only displays text fields as options.
I assign a default EPT, select the option to save settings, and kick off the import process. I had a couple of experiences here at different times. The first time I tried this, I left the Update Existing Projects field unchecked as I was importing projects into a blank environment. Nothing happened. Then I checked the box and ran the import again, and it actually imported projects. I am not sure if that was a consistent experience, but watch your queue after kicking off the import process, and if nothing happens, that could be the culprit.
If everything works well, I get the following confirmation message. Generally, I only got this message when I was importing projects in batches of approximately 300 or less. Trying to exceed that arbitrary 300 project limit often meant that I had to monitor the queue until it looked like the import process had completed, the green dots stopped swirling, and I could click off the import window. I’ll discuss this phenomenon in more detail for my next post.
Also note that after the import tool has been run, you should check the Force Check In option under Server Settings. A couple of times, one or two projects was stuck in checked out mode.
Now let’s review how well that worked…. To do so, I create a custom Project Center view with all of the custom fields except for the multiline fields (which do not appear in the Project Center.) At first glance, everything looks good.
When I scroll over to the right however, it doesn’t look as good.
The following fields failed to import:
- Lookup Single and Multiple Item
- Yes or No
- Person or Group
- Hyperlink Custom
- Created By
- Modified By
On closer examination, I realized that my lookup table listed Task 1-10, but should have read Test 1-10. I reimport, and get the following results:
So the lookup fields do import when the lookup table values match exactly.
What about the multiline fields? I build a quick and dirty Excel report to see if those came over:
Looks like everything worked there – with the possible exception that the multiline field with append only picked up the present value and not the previous values. Still, that’s a bit better than I was expecting in all honesty.
On the other hand, when I actually got into a full blown test migration, I kept running into that arbitrary 300 project limit. I am not entirely sure why that was the case, but I could never get the tool to import more than 300 projects from the same list. I tried multiple views, different sorting schemes, and different filters, different item limits within the list view, but 90% of the time, the tool kept importing the same projects (or updating the same projects) over and over again. The only technique that ended up working for me was essentially the following process:
- Import all 1,000 projects into a SharePoint list.
- Import all 1,000 projects into Project Server. Only about 300 import.
- Identify the remaining projects. Import these 700 projects into a second SharePoint list.
- Import those 700 projects into Project Server. Only about 300 import.
- Repeat as required.
After importing, note that the entire mapping schema has been saved to a newly created list. You may wish to go into the List Settings for this list to toggle it off of the Quick Launch bar.
To reimport from the same list with the same mapping, simply select that option the next time you run the Bulk Import Tool.
After importing the projects, you may wish to go through the following steps to clean up your data:
- Validate that all of your projects indeed imported. To do this, I would use the Project Center Export to Excel option and compare the results to my source data.
- Restore any Project Server enterprise custom fields that must be set to required.
- Take the Bulk Import Tool configuration list off of the Quick Launch bar lest it confuse your end users.
- Review the imported projects for additional data that must be added.
- Identify the appropriate ownership for the imported projects, and set them accordingly. The Bulk Edit Tool is useful for this.
- Set any flag fields as appropriate and remove any temporary fields added to facilitate this process. Again, the Bulk Edit Tool is useful for this.
…and that’s it for the basics. With my next post, I plan to walk through the import process more from a procedural standpoint and less from a mechanical standpoint. I also plan to address how to use the Bulk Import Tool to perform bulk edits of existing project data.