0

Ask the Expert: Import Excel Data into Project – Tips & Troubleshooting

Ellen Lehnert 24 Comments 0 like Articles

MS Project’s import/export function is not as clear cut as it seems, but if you follow some of the tips below the procedure should work more smoothly for you. The design of the data, the wording of the Excel tabs, column headings, and options all need to be considered to have import/export working in a way that will allow you to achieve your goals.

The information below applies to MS Project 2010, 2013, 2016, and MS Project On-Line Import Wizard.

Options:

There are option settings and formatting in Excel that can inhibit the import function from working correctly. When the import doesn’t work, unfortunately, you will not see an error message. You will just notice that the import did not bring in the data. To ensure that the import works correctly, set the options as shown below. Once set, they will remain an MS Project setting regardless of the project you are working with.

Set the Legacy settings.

File → Options → Trust Center → Trust Center Settings → Legacy Formats → select the 3rd option as shown below.

You should note several things regarding data design of MS Project and how the import/export function works:

  • MS Project consists of three major sections of data (Task, Resources, and Assignments).
  • The Import can import only to one of the data areas at a time.
  • The Excel spreadsheet should be designed to only consist of data for import to Task, Resources, or Assignments.

FAQ:  Can I import resource work updates from Excel to MS Project?

No. To accomplish this type of import, the data needs to update all three data areas. Therefore, the import function is not designed to perform this type of update. Most solutions for this requirement are custom written. A web search should reveal several solutions.

Format of Excel file:

  • The Import Wizard will default to importing from Sheet1. If your data in the Excel file is contained in another sheet, you will need to adjust the import map to be aware of the data location.
  • In Excel, datasheet names and column header names must be one word without special characters. Underlines are the best characters to use. Spaces will not work in the name format. Short names work the best.
  • Selected columns may be imported and mapped to MS Project columns of like data format. For example, text to text, number to number, etc. Bring dates into date fields.
    Have a solid block of data without blank lines. The import wizard will stop at the first blank line.

The MS Project file:

  • Create any customized fields before starting the import process.
  • Make sure the customized fields are of the same data type as the fields that will be imported from Excel.
  • When possible, create the customized field names to match the names in the Excel file.
  • For Task imports, create a custom table in MS Project that mirrors the Excel table. Doing this will allow for a direct copy and paste of the data, and the import wizard will not be needed.

The Import Wizard:

Before you start the Import Wizard:

  • Be aware of which Excel file fields will be mapped to which MS Project file fields before starting the Import Wizard.
  • Not all fields must be imported from the Excel worksheet into MS Project. When an import map is created, you will have the opportunity to pick which fields you will import.
  • Make sure you know which Excel file tab names will be imported into which data areas within MS Project.
  • Know if your import file has column headers. If it does, you should indicate that information when creating the import map.
  • Blank lines in the Excel file will be imported as blank lines in the project file.
  • Start and Finish dates when imported into the Start and Finish fields in Project will create Start No Earlier Than or Finish No Earlier Than constraints for the tasks. Remember that indicating that a task is completed should be updated into the Actual Finish column.
  • If you will be using a previously created Import map, the map must reside in the Global.mpt file on the machine you are working on. If the map is not located within the Global.mpt file, the Import Wizard will not display the map as a selection choice. By default, Import Maps when created are added to the local Global.mpt. If you want the map to stay with the file, use the Organizer to copy the map into the global for the file.

Running the Import Wizard:

  • Open the file you would like to import the data into or create a blank file.
  • File → Open → Files of type → Excel workbook – Locate the file and click on it, Click on Open. The Import Wizard will start.
  • Click
  • If you have previously created a map, you can use the existing map. If this is a new import, select New Map.
  • Click
  • Select if the import will be bringing the data in a new file, appending an existing open file, or merging the data with existing data.
    • If you select “New Project” a new file will be opened using default option settings and default blank project file.
    • If you select “Append” the imported data will be placed at the end of the current open file.
    • If you select “Merge” a merge key must be contained in the MS Project file and in the imported Excel data. The merge key is one field and must be specified at the time the import map is created.

  • Click 
    Select data types:

    • Select whether the data will be directed toward Task, Resources, and/or Assignment data.
      One, two or all three may be selected, but each one will only import one sheet at a time.
    • Select whether the imported file has column headers.
      • If you have column headers in the Excel file, this information will be used to match field names from the import file to field names in the Project file. The header row will not be considered valid content data and will not be imported.
      • If the imported data does not contain headers, column positioning will be matched with the fields on the import map.

  • Click Next.
  • Select the source worksheet. Default is None or
    • If the sheet is recognized (ie: correct sheet name format), the data fields will appear with preview at the bottom of the view.
  • The left side of the view will have the fields in the imported file. Select field values on the right side of the view to map these values to the MS Project fields.
  • Click Next to save the new map OR click Finish to start the import.

  • If you selected Next, click on Save Map. You will be asked to assign the new map
    a name.
  • Click Save to save the map.
  • Click Finish to start the import.

If the import does not occur properly or at all, re-check that you’ve followed the tips above.

MS Project Export:

When a column with a symbol (like the Indicators column) is exported to Excel, the export will produce a numeric code and not the symbol you see in MS Project.

Using the import/export wizard will not maintain the WBS structure, but if you copy and paste a Task table from MS Project to Excel, the WBS structure will remain.

 

Related Content

Webinars (watch for free now!):
Exporting and Importing Data into Project, Excel and Outlook
Project and Excel Integration – the application dream team!

Articles:
Microsoft® Excel Keyboard Shortcuts
8 Places where Microsoft Excel Scores for Project Management
A Free Project Pipeline Tracker for Excel
6 Practical Scenarios Where Excel Import is Useful
Build KPIs with Project Online and Excel 2013

Written by Ellen Lehnert
Ellen Lehnert, PMP, Microsoft Project MVP, MCP, is a independent consultant and trainer on Microsoft Project and Project Server. She has taught Microsoft Project over 400 times and is the author of  MS Project 2010 and 2013 published courseware. Ellen is also a contributor and tech editor for many reference books, a developer for the Microsoft Project certification tests and is a frequent meeting speaker for Microsoft, MPUG and PMI. Contact Ellen at ellen@lehnertcs.com.
Share This Post
Have your say!
00
24 Comments

  1. My guess is that you are bringing in the resource names and creating an assignment which is calculating the work value. When you import the dates you are also creating a constraint on every task which is not recommended. Since you are bringing both the start and finish you probably have a finish no earlier constraint on each task. You should create your links and clear all of the constraints.

  2. The import wizard will only bring data into the 3 data tables separately and not combine the data across the tables. If you mean budget hours as baseline work this is a valid field to import as well as remaining work. I think the problem you are having is that you are trying to effect all 3 data tables with the import and it is not working for you. If you just import the summary data into the task data these fields should come in using the merge which will overwritten what was previously in the file.

  3. Can I assume that you are doing a merge for your import? when you bring in the data make sure that there is not a value such as hrs. in the imported work field. this could result in the blank you are seeing.

    When you bring in the dates without the work, the calculation is kicking in and calculating the values for you. Fixed unit tasks will work better than fixed duration tasks for tracking.

    Try some of this. If it doesn’t work, I might have you send me the files so I can try a few things. Let me know. My direct email is ellen@lehnertcs.com.

  4. The import only allows you to import the data into 1 of the 3 database sections. You are probably trying to import into the task side. The problem is that you need to import into both the task and the assignment side which is not a capability of MS Project import. You might have to find an add-on that could help you with this and I would not be surprised if it is costly.

  5. I need more information about what you are trying to do. You are trying to update the work field where and how?

  6. If you bring in dates to the task table for the start and finish fields you will end up with a constraint on each task. Not a best practice.

    I have a client that had me create their import process and discovered that the format of the data from their system was not compatible with MS Project formats. We changed the format of the fields in Excel to match the field types on MS Project and then the import worked.

  7. I tried a few different options on this and I don’t think you can import %complete. Copy and paste didn’t work as well as the import wizard. This might not be something you can do.

  8. Srikanth – the sheet name does not have to stay “Sheet1” etc. The key is that the sheet name is one word with no special characters. I have done a lot of these at all levels with different sheet names.

    There is also an option to import workbooks with or without column headers. The task name column is actually called Name in the MSP database. Task Name is just a display name for the column.

    I believe that Phil’s problem is the actual content of the column.

  9. Importing dates into start and finish columns is really not a good idea. A constraint will be placed on each task and you are not letting MSP do what it does best which is to calculate the schedule. Manual or auto should not make a difference.

    If you are using the import for project updating, you should be importing the dates into the actual dates and not the planned dates.

  10. What is the specific issue he is having?

  11. great thread!

  12. Hi – there is not a dynamic link after the import. So the import is a one time occurance and you would need to find a method for future updates.

  13. Jay – importing can only be to the task table or to the resource table. what you are trying to do in import to the assignment table which is not a feature of MS Project. You might be able to purchase the code to do this from some MS Project vendors. Sorry to tell you this but it will explain why your import efforts are not working.

  14. Make sure the sheet name does not contain spaces or special characters. If it does, the sheet name is considered invalid.

  15. Si Le – MS Project is really not designed to do this. Look into Project Server to have projects updated when resources enter time. Sorry.

  16. It is really not recommended that dates are imported into MSP. If you import a start or finish date a constraint is placed on a task which can cause other problems. The Start date format when exported is a complete date including time. My experience has been if you want to import dates, import them to one of the free use date fields. I see that I have showed importing the Start-date but it is really not a good idea. Sorry if I have mislead you.

  17. Importing dates into start and or finish fields will set a constraint on each task. this can cause a negative slack situation that could be a miscalculation of your schedule. If you bring in dates to actual finish the task will be marked completed on a specific date. If you are doing this because you want to calculate the dates and not have MS Project calculate the dates, it would be better to manage the project in Excel. The question is – what is the result you are looking for?

    I hope this helps.

  18. Lucy – custom programming is the only way you will be able to get the import that you want to do. In MS Project there are 3 major parts to the database – tasks, resources & assignments. You can only import to 1 of the 3 parts. Your need is to import to all 3.

    The way to do this is to use either Project Server or Server through Project On line. If that is not possible, you should look into customized code to make this happen for you.

  19. Keep in mind that Project on-line and Project Server do this. The resources enter their own time and the PM approves it.

    There are a few sources that have the connector and I am not sure of the pricing. Googling for the connector I didn’t come up with much. could we take this discussion off-line? email me at ellen@lehnertcs.com. thanks.

  20. The import can’t adjust tasks for WBS levels. What most people do is bring the tasks into the bottom of a schedule, move them into the correct locations and then manually adjust for the WBS level.

  21. Notes is a weird field. In reality Notes is 255 characters. Any extra goes into an overflow field that you can’t access. So for importing and exporting only 255 char. are allowed. The custom text fields are limited to 255 as well. I am not sure there is a solution for you. The only way to exceed the 255 limit is to enter the text through the Task Information box which can’t be done with an import. I hope this helps.

  22. The setting is in Project and not in Excel. If the setting is turned off, the import will not bring in anything to Project from Excel. The blank column could be that you are bringing in data of the wrong type for the field of that the column header has in improper Excel column heading.

    Hope this helps.

  23. Hello, Anand. if you are importing dates in the start and finish columns, that could adjust the schedule. During the import if data gets into the wrong columns it can also cause a problem.

    How are you getting the data into project – copy and paste or import map?

    Ellen Lehnert

  24. Do not try to store the file name or the tab name in the import object. They do not keep in the object and both need to be selected when the import is used.

Leave a Reply