Import assignment work changes from excel with Project Excel Templ

[K Martin]

I am trying to import changes to Work at the assignment level into
Project 2003 from Excel. I first export the data from the Project file -
I have tried the Project Excel Template, every predefined
mapping template, and some that I've created - but I cannot get
the hours to go back into the Project assignments correctly.

When using the Project Excel Template output, the only changes I
am making are to the Assignment Table Tab where I adjust the
Work - simply changing 80 hrs to 100 hrs for example. They seem
to go back into the first resource of a subtask correctly, but
they never flow into the remaining resources on the subtask
correctly.

What am I missing? It seems like this should work very easily.

 

[JulieS]

Hello KMartin,

In the export map, make sure you capture the Assignment Unique ID
(you'll need to modify the map to get this data.) Then you'll need to
spread the extra hours over the assignments -- if you have three
assignments to a single task then you'll need to tell Project how to
distribute the 100 hours across the 3 assignments. Use the Unique ID to
merge the data back into the project file and you should be all set.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

 

[K Martin]

Thanks Julie...unfortunately, I am now having a different problem.

I am trying to add new resource assignments to the task table via excel and
when I import the data back into Project the assignments overwrite each other
and only the last resource assignment shows up. Also, the Work Contour is not
updated.

The import table contains the Unique Task ID, WBS, WP ID, Milestone ID, Task
Name, Resource Name, Work Contour and Work.

I am using the Merge option when pulling the data in.

Do I need to import into both the task table and the assignment table? If
so, how do I know which fields need to be in which tab?

Thanks

 

[JulieS]

Hello KMartin,

I just tried a quick experiment with an export of data and then an
import. Initially I exported the following info from the project file:

Assignment Data:
Unique ID
Task Unique ID
Task Name
Resource Unique ID
Resource Name
Regular Hours

I then created a new worksheet tab and copied the row headings from the
export. I then added a new assignment of an existing resource to an
existing task. So, I had only the new information on the worksheet tab.
I then imported but *appended* the new assignment back in. I omitted
the Assignment Unique ID field (as that would be generated by Project)
but mapped all the other data. Although you are adding data to an
existing file, I think the problem you are having is the merge you are
doing is overwriting any existing assignments on the task. If you are
adding new assignments (not changing existing) you need to append, not
merge.

The new assignment was added to the existing task.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

 

[K Martin]

Julie,

Thank you so much, that did the trick. I only had use the Task UID,
Resource Name, and Work and appended into the Assignment table, not the Task
table.

I suppose if I have a mixture of new assignments and updates to existing
assignments I would have to break them into two different imports.

I was unable to get the Work Contour to import though. It looks like it is
part of the Resource Assignment and needs am Assignment UID before it can be
imported. Do you know if that would have to be accomplished in a separate
import or if it can be done with a 2 table import?

 

[JulieS]

Hello,

Glad to know my suggestion worked for you and thanks for the feedback.

To change work loading on existing assignments, you will need the
assignment unique ID to be able to update the information without
overwriting other information. So, yes, I'd import the new assignments
first, discover the assignment unique id created by Project and then use
that information to later change data with a merge on the unique ID.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

 

[K Martin]

Hi Julie,

My latest dilemma is that I have been unable to update the new assignments
with revised work data. I am merging (since these are existing resources) the
Resource Assignment UID and Work into the assignment table. The tasks are
set to fixed duration and they are not effort driven. I've tried merging the
Resource Assignment UID into the Task Unique ID, Unique ID and Resource
Unique ID, but none of them works.

Using the Task Usage View and looking at the Unique ID, Work Package (WP)
and the assigned resources columns I see the Task UID at the WP level and
another unique ID in italics at the Resource level. I assume the unique ID I
see at the Resource level is a Resource Assignment UID (see below).

Unique ID/Resource Assignment UID
----------------------------------------
17796 - WP level UID
2120157 - 1st assigned resource UID
2120519 - 2nd assigned resource UID
2120520 - 3rd assigned resource UID
2120521 - 4th assigned resource UID

Is there some type of Unique ID that I don't know about? Do you have any
suggestions?

Thanks,

Kim

 

[JulieS]

Hello,

Assuming you are not using Project 2007, the Unique ID number you see in
Task Usage view on the row with the resource's name is the Assignment
Unique ID.

Copied from your example below:
Unique ID/Resource Assignment UID
----------------------------------------
17796 - WP level UID
2120157 - 1st assigned resource UID
2120519 - 2nd assigned resource UID
2120520 - 3rd assigned resource UID
2120521 - 4th assigned resource UID

ID #17796 is the Task Unique ID
2120157 is the Assignment Unique for the assignment of Resource 1 to
Task 17796
2120519 is the Assignment Unique for the assignment of Resource 2 to
Task 17796
etc.

Again, create a custom export map to export the *assignment data* out of
project. I would export:

Unique ID
Task Unique ID
Task Name
Resource Unique ID
Resource Name
Regular Hours

Change the work data in excel and then merge back into the assignment
table using the Unique ID as the merge key.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project