none
Problem importing task data from excel RRS feed

  • Question

  • Project  2010 / Excel 2007.  I thought this was a straight forward operation but...... 

    I have a LOB manufacturing system which produces excel reports.  We configured the reports to display a task name,  a task num, % complete, actual start, actual finish.  I made sure that the excel cells were formatted as follows,  task name =text, task num=text, % complete=percent, Actual start=date, actual finish=date.  I have made multiple attempts to merge this data into an existing project.  Using the following mappings   task name=name, task num=EV key (a custom task field), % complete=%complete, actual start=actual start, actual finish=actual finish.  When I perform the import, I do not get an error message but the task is not updated with the updated date and % complete info.  Interestingly, if I do the import into a new project, all of the task data appears.  In the preview, everything looks perfect.  Is there something simple I am missing or does some type of project logic override the updates I am trying to get merged?  If I can't do this via the import wizard, does anyone know of a utility or script I can use.  I am trying to avoid having to work directly with the sql tables.  Thanks.

    Wednesday, June 22, 2011 9:12 PM

Answers

All replies

  • Hi,  I'm intrigued how you import an Excel file into an existing project....  What menu commands do you use?


    Ben Howard [MVP] blog | web
    Wednesday, June 22, 2011 9:58 PM
    Moderator
  • Cousin Ben --

    You use the Import/Export Wizard.  To do this, open an existing project which you will update using an Excel workbook.  Click File > Open and then select and select the file type as Excel Workbook in the Open dialog. Navigate to the location of the Excel file, select it, and then open it. Microsoft Project will launch the Import Wizard.

    In the Import Wizard dialog, click the Next button to continue.  Depending on your situation, you will select either an existing Import/Export map, or you will need to create an entirely new map.  The purpose of the map is to link (map) fields in the Excel workbook with fields in the Microsoft Project file.  Assuming you do not have an existing map, select the "New Map" option and then click the Next button.

    In the next page of the Import Wizard, select the "Merge the data into the active project" option and then click the Next button.  This is the option that you use to import an Excel file into an existing project.  The most important thing to do is to make sure you map the Excel data to something in the Microsoft Project plan that does not change, such as the task Unique ID field for example.  Anyway, this gets you moving in the right direction.

    I learned about all this years ago when I was a contributing author in the Special Edition: Using Microsoft Project 2002 book from Queue Publishing. One of the two chapters I wrote was how to use the Import/Export wizard.  :) Hope this helps.


    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Thursday, June 23, 2011 2:07 AM
    Moderator
  • I am still at a loss as to why the actual start / finish and the % completes will not update.  The only field that seems to update is the custom text field.
    Thursday, June 23, 2011 2:18 AM
  • Bikerjohn1 --

    Have you tried using Unique ID as the Merge Key for the data import?  I just tried that, and it worked for me in an existing project by pulling data in from an Excel Workbook.  Let us know and we will try to help.


    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Thursday, June 23, 2011 4:45 PM
    Moderator
  • I got it working but there are a few things which defy common sense.  To recap, what I am doing is taking an excel spread sheet which is an output of a LOB system.  The columns on the spread sheet are; name, ve key, percent complete, actual start, actual finish and unique id. All of the fields are mapped to the corrosponding fields in Project with ve key being a custom text field.  The thing that kept throwing me is that I followed normal logic and formatted the actual start / finish columns in excel as date fields like they are in project.  Everything worked fine on the rows when there was a start or finish date in the sheet.  If the actual finish date column is empty or I put "NA", the project would import it and mark the task as 100% complete and insert the  actual start / finish date of the last row from the excel sheet which had a  actual start /  actual finish date.  When I changed the cell formats of the actual start / finish dates  to standard text, then it works properly meaning if there was no actual start / finish date, it would put "NA" into the task in project and not automatically mark the task as 100% complete.  Also, I found that formatting the % complete column in excel works alot easier if it is formatted as percentage and not as a standard number.

    Thursday, June 23, 2011 10:00 PM
  • Bikerjohn1 --

    If it is any consolation to you, I ran into some of the same problems as you with data in the Start, Finish, Actual Start, and Actual Finish fields.  So you are not alone, my friend.  But I'm glad you got the thing working anyway!  :)


    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Friday, June 24, 2011 2:58 PM
    Moderator
  • Dale,

    Thanks for looking into it.  I thought I was losing my mind...

    Yeah, I am pretty sure there is some backround project logic going on there with the dates and the % complete.  For me, I just choose 'as a new project' which luckily in our situation will work. It seems that when importing into a new project, there is less backround logic happening so it imports without deciding to calculate things on it's own.

    Friday, June 24, 2011 4:55 PM
  • Cousin Dale, Thank you.  I should start writing books! 
    Ben Howard [MVP] blog | web
    Monday, June 27, 2011 8:51 PM
    Moderator
  • Cousin Ben --

    Yes, you should.  :)


    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Tuesday, June 28, 2011 12:28 AM
    Moderator