Topic: Import MS Excel data into MS Project – Tips & Troubleshooting
by Ellen Lehnert, MS Project MVP, PMP, MCT, MCP
www.lehnertcs.com
April, 2014
There are several things you should be aware of regarding the import function to avoid having problems. There are option settings and formatting in the Excel file that can inhibit the import function from working correctly. When the import doesn’t work you will not see an error message; you will just notice that the import did not bring the data into the project file. Below are a few tips you should be aware which apply to the MS Project 2010 & 2013 Import Wizard:
Options:
Set the Legacy settings:
File Options Trust Center Trust Center Settings Legacy Formats select the 3rd option – see
below
The Excel file contents:
The Import Wizard will default to importing from Sheet1. If your data is on another sheet, you will need to adjust the import map to be aware of where the data is located. Another option is to rename the tabs so the data is located on Sheet1.
In Excel datasheet names and column header names must be one word without special characters. Spaces will not work in the name format. If you would like to use a special character in the names use underlines. Ie: Task_Name
Data is imported to 3 distinct data areas: Task, Assignment or Resources. Imports will not update more than one data area. Excel datasheets will need to be divided into these areas of data.
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. Ie: Text into Text, Date into Date
When possible, create the customized field names to match the names in the Excel file. Optional but helpful.
The Import Wizard:
Before you start the Import Wizard – what you should know:
Be aware of which Excel file fields will be mapped to which MS Project file fields before starting the Import Wizard.
Not all fields in the Excel field are required to be imported. 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. Ie: Task_data, Resource_data
Be aware if your import file has column headers
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 MS Project will create Start No Earlier Than constraints for the tasks.
If you will be using a previously created Import map, the map must reside in the Global.mpt 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 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 MS Project 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 Next
If you have previously created a map you can use the existing map. If this is a new import select New Map.
Click Next
Select if the import will be bringing the data in a new file, append to the existing open file or merge the data with existing data.
o If you select “As a New Project” a new file will be opened using default option settings and default blank project file.
o If you select “Append the data to the active project” the imported data will be placed at the end of the current open file
o If you select “Merge the data into the active project” a merge key must be
contained in the MS Project file, in the imported Excel data and in the import map. The merge key is one field and must be specified at the time the import map is created.
Select data types:
Select if the data will be directed toward Task, Resources and/or Assignment data One, two or all 3 may be selected
Select if the imported file has column headers.
o If there is column headers in the Excel file this information will be used to match field names from the import file to field names in the MS Project file. The header row will not be considered valid content data and will not be imported.
o 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 Sheet1 (or other available name)
o If the sheet is recognized (ie: correct sheet name format – no spaces, no special characters), the data fields will appear with preview at the bottom of the view for the mapped fields
If you selected to Next, click on Save Map and you will be asked to assign the new map a name.
Click Save to save the map Click Finish to start the import.
After the Import is completed:
All tasks will be imported at Outline level 1
Data might have been imported to columns that are not being shown on the currently viewed table. Insert imported columns into any table to view the imported data. Watch for error messages that occur during the import. They might be indicating scheduling issues with some of the imported tasks which might need attention.
