Importing Microsoft Excel Workbooks Project Management Help

Files are created in Microsoft Excel with the XLSfile suffix, Pivot Tables, which also have the XLS suffix, cannot be imported into Project. Spreadsheet files created using other applications should either be imported into Excel or be saved in either tab-delimited TXT files or comma-separated CSV files, and then imported following the directions for Importing Text Files.

If you wish to import specific records or columns of data, you have to open the file in Excel and modify the data so that it only contains the information to be imported, This can be done by filtering, editing, or grouping and saving the result as a new file Because data is mapped by field name, it is not necessary to sort the spreadsheet in the same order as the project, Each column must, however, have a unique name.

Importing Data from Excel Using an Existing Import Data Map

In this example, we will import an Excel spreadsheet that contains the same fields as the “Who Does What” report import data map.

Figure 19.11

Figure 19.11

This file may have originally been exported from Project using the “Who Does What” map, Or, you or another thoughtful user may have arranged the worksheet so that it uses the same field names as the map. It takes less time to rearrange columns and change a few’headings in an Excel worksheet than it does to create a custom data map, To import data from· Excel using an existing data map:

1. In Project, select File Open, The File Open dialog box is displayed.

2. Change the Files of Type to Microsoft Excel Workbook (XLS).

3. Use the Look In top down list to locate the proper folder and file.

4. Select the file and click the Open button. The Import Mapping dialog box will open, The Selective data tab will be enabled.

5. Choose the map you want to use and click the Open button, The data is Imported into Project.

Figure 19.12

Figure 19.12

Importing Data from Excel with a Custom Import Data Map

There are two ways to create a customized import data map by copying and modifying an existing map, or by starting from scratch and adding fields from the Import source and then mapping them to their corresponding fields in the Project Resource, Assignment, and Task tables.

Modifying a Copy of an existing Map A Notes column has been added to our Excel spreadsheet, We want to import the spreadsheet, including Importing data from this column to the Notes field of our project’s Resources table. We’ll copy and modify the “Who Does What” Report Map to add this field to the import, and save the modified data map as Annotated Resources and Assignments.

Figure 19.13

Figure 19.13

1. In Project, open the project file into which you will import the data. (If you do not open a project file, a new project will be created with the imported data.)

2. Choose File  Open. The File Open dialog box is displayed.

3. Change the Files of Type to Microsoft Excel Workbook (XLS).

4. Use the Look In drop- down list to locate the proper folder and file.
5. Select the file and click the Open button, The Import Mapping dialog box is displayed with the Selective Data tab active.

Figure 19.14

Figure 19.14

6. Select the report you want to modify and click the Copy button to display the Define Import/Export Map dialog box.

7. Select and replace the Import/Export Map Name Copy of with the name you’ll use for the template.

8. Enable the Export Header Row/Import Includes Headers check box.

9. If your spreadsheet contains fields in the Assignment table, click the Include Assignment Rows in Output check box, This organizes exported data so that assignment rows appear below tasks or resources, similar to the Task Usage and Resource Usage views.

10. Click OK to return to the Import Mapping dialog box .

11. The map name you entered in step 7 should be selected, If it is not, select it Click the Edit button.

12. Select the table tab you want to map, The Field Mapping Detail dialog box is displayed, Fields that were included in the predefined import map are already mapped in the Mapped Field List.

Figure 19.15

Figure 19.15

13. If your Excel file has more than one worksheet, select the worksheet you wish to import from the Source Worksheet Name drop-down list.

14. Select the Method for Incorporating Imported Data.

15. Map a field by clicking in the first empty cell in the From: Worksheet Field column. The drop-down list contains unmapped fields from the selected work sheet, Select the field you wish to map.

16. Click on the first empty cell in the To Microsoft Project Field column. This list contains all the unused fields in the table shown on this dialog box tab. Select the Project field that the data should be imported Into, repeat Steps 15 and 16 for each field you Wish to map.

17. Click OK to save the changes to the Import Map.

18. Click the Open button in the Import Mapping DIALOG BOX to begin the import.

Creating a New Import Map to Update Existing Project Data A spreadsheet, contains resource names and new email addresses, We will update the specific project fields with the new email addresses based on a unique merge key a field with no duplicate values that appears in both the project table and the Excel worksheet. The unique key is used by Project to put the email addresses with the right names, which we think is a good idea.

Figure 19.16

                     Figure 19.16

In our example, we’ll use Resource Name as the unique merge key, The spreadsheet contains each person’s name only once, and each resource name in Project must be unique, To facilitate this update, we’ll create a new import data map named Resource mail Addresses.

1. In Project 2000, open the project file into which you will import the data, Go to the view that will contain your updated data, Make sure that all fields you want to import to are displayed in the view.

Figure 19.17

                             Figure 19.17

2. When you create the import data map, you need to know the-full name of each Project field you want to map to Often, field names are shortened for use in views and reports, To determine the full correct name for a field, double-click the field heading to open the Column Definition dialog box, The field’s name appears in the Field Name text box. Note the Name, and then close the Column Definition dialog box without making changes.

Figure 19.18

Figure 19.18

3. Choose File >- Open.

4. In the Open dialog box, change the Files of Type to Microsoft Excel Work book (XLS).

5. Use the Look In drop-down list to locate the proper folder and file.

6. Select the file and click the Open button. The Import Mapping dialog box is displayed with the Selective Data tab activated.

7. Click the New Map Button. The Define Import/Export Map dialog box is displayed, Enter a name for the map.

8. Select the tables you are importing data into in the Data to Import/Export section to activate the table tabs.

9. Enable the Export Header Row/Import Includes Headers check box on the Options tab.

10. Select the first table Mapping Tab. The Field Mapping Detail dialog box is displayed.

Figure 19.19

Figure 19.19

11. Select the Source Worksheet Name in the Excel workbook.

12. Select Merge (by key) Into Current Project as the Method for Incorporating imported data, The Import File header names are now displayed.

Figure 19.20

Figure 19.20

13. Click on the first empty cell in toe To Microsoft Project Field column, This list contains all the unused fields in the table, select the fields you want to import data into and their corresponding worksheet fields.

14. Select the field with unique records in both the project table and the import data source that will be used to find records for the merge process, and click the Set Merge Key button, The merge fields are now set.

Figure 19.21

Figure 19.21

15. Click OK to save the changes to the import map.

16. Make sure that your new map is selected, and then click the Open button in the Import Mapping dialog box to begin the import and update your Project files.

Posted on November 26, 2015 in Importing and Exporting Project Data

Share the Story

Back to Top