IMPORTING INTO MSP FROM EXCEL

[Twenny02]

Hi,

I have set up some imports to take project plan data from an EPM data extract in Excel into an MSP plan, so that dates automatically update from the EPM data extract. End result is to show a high level roadmap with key dependencies in MSP.

I am encountering two issues though, and wondered if anyone knows how to avoid these please?

1. If a row of data in the import cannot find any matching merge-key in MSP, then the data is dumped at the end of the project plan. I only want rows to be updated in the MSP project plan if a matching merge-key is found. If no merge-key match is found for a specific row of data in the Excel extract, then I do not want any of the data in that Excel row to be taken into MSP project plan. Can I set the import up so the un-matched lines from the Excel extract are not dumped at the end of the project plan?

2. The import look-up does not work properly if the length of characters in the merge-key differs (i.e. the merge key for row 1 is 9 characters long, but the merge-key for row 2 is 8 characters long). Some milestones end up being missed. The merge-key is automatically created for each milestone (i.e. each row in Excel) in the Excel extract by concatenating the system generated project code from EPM plus the project plan milestone code. As these two items can vary in length it can mean the merge-key varies in length. Is there a way to get around this in the import, other than artificially adjusting the merge-ley components so they are all the same length with some type of look-up and replace table in Excel?

Thanks

 

[JulieInMaine]

If the data is changing, is it possible to just create a New MSP file each time - instead of trying to pick and choose what to merge?

Not sure I follow with your comment "import lookup". You merge key should be unique IDs - if you by some quirk have two merge keys with the same data - the merge will fail.

Julie

 

[Twenny02]

Hi,

Ideally I need to keep the same MSP file as there are other columns that are updated in the MSP plan that do not exist in the EPM extract.

Re the merge-key - they are all unique, it is more that they vary in character length which for some reason throws the look-up...

 

[SkipVought]

I need to keep the same MSP file as there are other columns that are updated in the MSP plan that do not exist in the EPM extract

So in Excel, you have an MS Query (Data > Get External Data > For other sources > From Microsoft Query ...) that drills down to your same workbook-sheet and produces ONLY the columns that you need for THIS import.

Once you ADD a QueryTable to your new empty sheet, all you need od is REFRESH the QT, to get new data for the next time you want to import this data into MP.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]