This was a fun experiment. No, really, I love beating my head against the invisible Project wall, don’t you?
I needed to find a way to create a schedule for our ongoing admin tasks which simply took an average % allocation on tasks, per resource, directly from an excel report generated by our billing system and input it into a new schedule in Project, preferably with as few clicks on the user end as possible.
I tried copying and pasting from Excel, and imported a mapped excel workbook about 700 different ways, all to no avail. I tried all the built-in maps, with high hopes for things like “Task List with Embedded Assignment Rows” and “Task and Resource PivotTable report” etc, but they hated me. The problem was that you have to have 3 separate pages to import Tasks, Resources, and Assignments from Excel, and setting that up was a nightmare.
What I found was that using a comma delimited csv file works MUCH BETTER. Super way better to the max, in fact!
The csv file looked effectively like this in Excel:
|Resource Name||Task Name||Units||Start||Finish|
|Rimmer; Arnold||Essential Routine Maintenance||25%||6/23/14||6/27/14|
|Lister; Dave||Essential Routine Maintenance||25%||6/23/14||6/27/14|
|Lister; Dave||Generally Slob Around||50%||6/23/14||6/27/14|
I created a new project in Project Pro – since this task list is “duration/units” based, I changed the default new task type to Fixed Duration. If you were using a work based task, you’d want it to be Fixed Work, etc.
Then I did a File=>Open, changed to csv, selected my file, and created a new map. The settings looked like this:
And the mapping automatically popped in, because I named the titles so very cleverly. You can even save the map after you set it up, which is very handy for later use.
Once it’s in Project, it looks like you would expect:
So that’s my quick and easy cheat for getting data to import nicely into Project from Excel. I really found the “three tab” version to be a bear, but maybe that’s just me!
Hope this helps!