none
Problem merging data from Excel into Project with 6000+ tasks RRS feed

  • Question

  • Project 2010 consistently hangs when I'm trying to merge data into a project from an Excel file. I've set the Unique_ID as the merge key and am only attempting to merge data in one column. The target project file is rather large (6,500 tasks) and the unique IDs go up to 8 digits in length, which I think this is the main reason why the merge is failing. I've tried merging the data in batches, but that didn't work. Any suggestions on how I might be able to merge on unique ID would be much appreciated.
    Tuesday, December 17, 2013 11:55 PM

Answers

  • Victor,

    If the size of the unique ID number indeed is relevant to the success of import than I have a potential fix. It's straightforward but will require some work.

    With a Project file that has been heavily edited and the unique IDs are very complex, one way to reset the unique IDs is to use method 4 of FAQ 43 found on the MVP website at: http://project.mvps.org/faqs.htm

    Since this will reset the unique ID structure, you will need some way to re-sync the unique IDs in the Project file with those in the Excel file. I would use something like the following.

    1. Copy the current Unique ID field to an extra number field (e.g. Number1)

    2. Use method 4 to reset the Project file

    3. Copy the Unique ID field (now with new values) and the number field from step 1 and paste to the Excel worksheet

    4. In Excel use whatever method is easiest to reset the unique ID column in Excel. Although it could be done manually I personally might develop a macro in Excel to do the re-setting.

    Hope this helps

    John

    • Marked as answer by viktorbox Thursday, December 19, 2013 12:02 AM
    Wednesday, December 18, 2013 4:49 PM

All replies

  • victorbox,

    I don't think the file size matters but I am concerned about using the Unique ID as the merge key. When a Project file is edited, (some tasks deleted, new tasks added), the unique ID structure can get pretty complex. For example when a task is deleted, the unique ID associated with that task is gone forever and unless the Excel workbook is fully in sync with the unique ID structure, problems with the import may occur. The fact that you have some unique IDs with 8 digits indicates the file has seen a lot of editing.

    What is the field you are trying to merge? And when you say the merge "fails", what exactly does that mean (i.e. wrong data, error message, etc.)?

    And just to "ring out" your installation, does an import using a unique ID merge key work okay on a smaller sample file, say of 100 tasks?

    John

    Wednesday, December 18, 2013 2:07 AM
  • Hi John,

    Thanks for your response. I'm trying use the Excel file to populate a multiple custom text fields in the Project file that do not contain any data. For the time being I'm just working with one text field.

    After setting up the field/column mapping and proceeding with the merge, Project will attempt to perform the merge and then quickly enters and remains in a "Not Responding" state -- eventually, I'm forced to terminate the application. I have tried the merge with smaller number of tasks, which worked until I begin dealing with the larger unique IDs and then Project would stop "responding".

    VB

    Wednesday, December 18, 2013 6:20 AM
  • Victor,

    If the size of the unique ID number indeed is relevant to the success of import than I have a potential fix. It's straightforward but will require some work.

    With a Project file that has been heavily edited and the unique IDs are very complex, one way to reset the unique IDs is to use method 4 of FAQ 43 found on the MVP website at: http://project.mvps.org/faqs.htm

    Since this will reset the unique ID structure, you will need some way to re-sync the unique IDs in the Project file with those in the Excel file. I would use something like the following.

    1. Copy the current Unique ID field to an extra number field (e.g. Number1)

    2. Use method 4 to reset the Project file

    3. Copy the Unique ID field (now with new values) and the number field from step 1 and paste to the Excel worksheet

    4. In Excel use whatever method is easiest to reset the unique ID column in Excel. Although it could be done manually I personally might develop a macro in Excel to do the re-setting.

    Hope this helps

    John

    • Marked as answer by viktorbox Thursday, December 19, 2013 12:02 AM
    Wednesday, December 18, 2013 4:49 PM
  • Thank you for your help, John. I have tried it out yet, but I can see this being a viable workaround. Much appreciated!

    VB

    Thursday, December 19, 2013 12:02 AM
  • viktorbox,

    You're welcome and thanks for the feedback. If you do try my suggestion, I'd be interested to hear if it solves the problem. If it does not, there may be other approaches (e.g. VBA).

    John

    Thursday, December 19, 2013 2:22 AM