Import Excel file into MS Project. Task mapping wizard not picking up all fields

Question

I figured out how to connect my Excel file to MS Project. However, when I map the fields to be included in the Project file, it does not show all the fields included in the Excel file. For instance, I downloaded an Excel template, copied and pasted my file into the template. I went to "open" in MS Project, retrieved the file and the Import Wizard Task Mapping appeared. I followed the steps and tried to match all of the fields in Excel to the fields in MS Project (example: most of the fields I marked as text, except for the title field. I marked that as 'Name'). I hit finish once I was done and the only output I received from this mapping, was the title field. I need the other 15 fields from my Excel file to show up in MS Project.  I have attached screenshots for your reference. Any help is appreciated. 

Answer 1

I have a similar problem to the one described by WhitneyLee.  I am using Office 2013. I am attempting to use the Import Wizard merge function to update start/finish dates.  I did the following:

1 - created the task list in my mpp by copying/pasting from my Excel workbook. I am unable to use the import wizard to create .or append tasks, as I have several categories of tasks (all with sub-tasks) spread over multiple worksheets (e.g., I am creating a phase-based project plan that includes initiation, planning, readiness, execution, and post-execution phases).  Most worksheets have tasks in all phases. So, to create the mpp, I started with a task called "Initiation" as a summary task, then copied/pasted tasks from each of the worksheets as sub-tasks to "Initiation".  I repeated this for all phases.

2 - Uniquely named every task in my workbook, so I am using Name as my merge key.

3 - Attempted to use the merge function in the Import Wizard to update start_/finish_ fields in my mpp (btw - the dates in the Excel file are data type text)

The resulting update is incorrect as follows:

• two of the fields update correctly ( the dates for sub-tasks to Initiation and Post-Execution).  None of the other dates are updated.  In addition, the tasks that are not updated have their names appended to the (mpp) names field. 

Is my process no supported?

Answer 2

DebA_725,

I'm not quite clear on the structure of your Excel Workbook. You say you multiple worksheets and you have 5 phases. It sounds like each worksheet has all 5 phases and each phase thereon has multiple subtasks. So for example, under the "Initiation" heading on sheet 1 you have Task A and Task B and under the "Initiation" heading on sheet 2 you have Task C and Task D, etc. Is that correct?

What is the purpose of separate worksheets?

On the two phase groups (i.e. Initiation and Post-Execution) that do seem to merge, do ALL of the dates for the subtasks merge, so just some of them?

The fact that the data for the other 3 phase groups do not merge but instead append indicates that the task names are not matching between your Project file and your Excel Workbook. I'd review that very carefully. A inadvertent leading or trailing space, or a missed capitalization is all it takes.

Just for reference, when importing dates into Project, it's better to import only the start date and the duration since Project's scheduling engine calculates finish dates based on those parameters (and any task linkages).

John

Answer 3

Hi John,

I did indeed slug through to generate a "working" template. But I still want to understand why/how I can do this more efficiently, as I will need to recreate the mpp over time.  The only other option I came up with is to use copy/paste (I guess one other option is to integrate all worksheets into one master worksheet before importing into Project. In practice, this is my least desirable option from a support perspective).

Finally - the functional mgrs. are partial to spreadsheets, but the principle pgm mgr wants to take advantage of the features Project offers (i.e., resource leveling, dependency mgmt., ability to easily generate a Gantt chart, etc).

Thanks,

Deb_A

Answer 4

Hi John,

Thank you for the response!  I'll try to explain a bit further

  It sounds like each worksheet has all 5 phases and each phase thereon has multiple subtasks. So for example, under the "Initiation" heading on sheet 1 you have Task A and Task B and under the "Initiation" heading on sheet 2 you have Task C and Task D,
 etc. Is that correct?

~~~~~end snip~~~~~~~

-->deb\_A: yes, that is correct.

~~~~~snip~~~~~~~~

What is the purpose of separate worksheets?

~~~~~end snip~~~~~

-->deb\_A: I'm building a Project template for a recurring program and have a requirement to build it based on project phases. To do so, we id'ed all functional areas (and associated tasks), captured in an Excel workbook (this team is quite partial to spreadsheets.) 
 Each sheet in the workbook represents a different functional area, and will be populated by the functional area lead. For the most part, functional leads  will be working on their tasks in parallel.  So - while the Tasks are unique, the Phases are common. 

~~~~~~~~snip~~~~~~~~

On the two phase groups (i.e. Initiation and Post-Execution) that do seem to merge, do ALL of the dates for the subtasks merge, so just some of them?

The fact that the data for the other 3 phase groups do not merge but instead append indicates that the task names are not matching between your Project file and your Excel Workbook. I'd review that very carefully. A inadvertent leading
 or trailing space, or a missed capitalization is all it takes.

~~~~~~end snip~~~~~~~

-->deb\_A: The issue is actually with copying/pasting tasks to the mpp AFTER I import tasks from the first worksheet.  The only tasks that are updated properly are the ones that are added via the Import Wizard.  Over the weekend,
 I (iteratively) executed the Import Wizard and (iteratively) used the "Delete Task" function to build the task list solely within the use of MSP functions.  IT WORKED- but was fairly painful.  Once I got a "working" mpp, I created xmls from the working and
 "failing" versions.  Unfortunately, I could not find any differences (so I don't know why "copy / paste" doesn't work).

Finally - I agree with your aside (i.e., it's better to provide start date and duration).  Unfortunately, the team often works "back" from the "finish date" that is provided by the Pgm Mgr, and they tend to use relative dates to communicate their schedule
 (e.g., "finish date" - 3 weeks).  Typically, this is the date a task must be completed.

Again - I appreciate your reply.

Deb

Answer 5

Deb,

Okay I understand the thinking behind the separate worksheets. Each sheet is a functional group, that makes sense.

It's not clear why you are copying/pasting after you did the import. I thought you set up all the tasking in your original copy/paste process and were then having problems importing thereafter. However, it sounds like you slugged through it over the weekend. Does that mean you resolved your issue or do you still have a question?

As an aside, if all the functional managers are partial to spreadsheets and that is how they are managing their effort, what is the purpose of translating to Project?

John