Import data from Excel with main tasks and subtasks

I'm new to MS Project and need help with importing data from Excel into 
Project 2000.  The data consists of Work Request numbers, individual phase 
numbers for the Work Request, name of each phase, and (phase) start and end 
dates.

I can open the Excel file in Project and map the fields, however, the data 
shows up as individual tasks.  I need to have the Work Request number and 
project name be a main, or top-level, task, and the data related to the 
phases be sub-tasks.

Here's an example of how the raw data appears in the Excel file:
WorkReq#   ProjectName
Phase#     PhaseName     StartDate     EndDate

Note there's typically more than one phase per Work Request, just trying to 
keep it simple in the example.

Is there a way to specify this within Project, or do I need to change the 
data layout in the Excel file?

Any help, thoughts, etc is greatly appreciated!!!
0
Utf
6/1/2010 4:11:01 PM
project 1276 articles. 0 followers. Follow

5 Replies
6528 Views

Similar Articles

[PageSpeed] 4

Try adding a field to the Excel sheet called Outline Level.  Populate it 
with 1,2,3 or 4 (or more) - then import into the corresponding Outline Level 
field in MS Project.

....or invest in the upgrade to MS Project 2010 and simply cut/paste it from 
Excel while retaining the outline structure.

- Andrew Lavinsky
Blog: http://blogs.catapultsystems.com/epm

> I'm new to MS Project and need help with importing data from Excel
> into Project 2000.  The data consists of Work Request numbers,
> individual phase numbers for the Work Request, name of each phase, and
> (phase) start and end dates.
> 
> I can open the Excel file in Project and map the fields, however, the
> data shows up as individual tasks.  I need to have the Work Request
> number and project name be a main, or top-level, task, and the data
> related to the phases be sub-tasks.
> 
> Here's an example of how the raw data appears in the Excel file:
> WorkReq#   ProjectName
> Phase#     PhaseName     StartDate     EndDate
> Note there's typically more than one phase per Work Request, just
> trying to keep it simple in the example.
> 
> Is there a way to specify this within Project, or do I need to change
> the data layout in the Excel file?
> 
> Any help, thoughts, etc is greatly appreciated!!!
> 



0
Andrew
6/1/2010 4:25:30 PM
Hi Amy,

Is the work request a project in itself or are you trying to 
combine multiple work requests into one project file?

 From your brief description, I'm not sure Project will suit what 
you need.  If there are multiple steps to the "Phase" the start 
and end date for the phase is driven by the subtasks and you 
cannot specify the Phase start and end.

You can import the other data, but importing dates into Project 
is going to create constraints which will, in turn, go a log way 
towards defeating using Project.

If you want individual line items, consider creating the Excel 
file with data for each entity (Work Request) on a single row 
with the details underneath in another row.  There are spare text 
fields you could import some of the additional information into.

Please note, this newsgroup was slated to close on June 1. 
Please visit the forums:

http://social.answers.microsoft.com/Forums/en-US/addbuz/threads

or

http://social.technet.microsoft.com/Forums/en-US/category/projectserver2010,projectprofessional2010

I hope this helps.  Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

On 6/1/2010 12:11 PM, amycwhitfield wrote:
> I'm new to MS Project and need help with importing data from Excel into
> Project 2000.  The data consists of Work Request numbers, individual phase
> numbers for the Work Request, name of each phase, and (phase) start and end
> dates.
>
> I can open the Excel file in Project and map the fields, however, the data
> shows up as individual tasks.  I need to have the Work Request number and
> project name be a main, or top-level, task, and the data related to the
> phases be sub-tasks.
>
> Here's an example of how the raw data appears in the Excel file:
> WorkReq#   ProjectName
> Phase#     PhaseName     StartDate     EndDate
>
> Note there's typically more than one phase per Work Request, just trying to
> keep it simple in the example.
>
> Is there a way to specify this within Project, or do I need to change the
> data layout in the Excel file?
>
> Any help, thoughts, etc is greatly appreciated!!!
0
JulieS
6/1/2010 10:24:31 PM
Thank you both for your replies!

Essentially each Work Request is considered it's own project and the phases 
for the Work Request is the breakdown of the work necessary to complete the 
entire Work Request.  For example one Work Request is to renovate a specific 
building, and the various phases are: estimating, general construction, 
plumbing, electrical, painting, etc.  Each phase has it's own start and 
finish date.

My manager wants to see all the Work Requests, and phases, displayed in a 
Gantt chart to see where the Work Requests are overlapping regarding start 
and finish times in order to better allocate the resources.

So I guess the answer to your question Julie is I'm trying to combine 
multiple work requests into one project file.  

The data is originally coming from a Crystal Report which does have a Gantt 
chart but isn't able to handle the large volume of data.  My manager has been 
exporting the data to Excel and then doing a manual copy-paste to get it into 
Project.  On average there's typically 50+ Work Requests with multiple phases 
per Work Request.

I'd like to automate this process via a custom import map but can't seem to 
figure out how to set it up so that each work request appears as a summary 
task and the phases appear as sub-tasks.

Presently when I import from Excel into Project both the Work Requests and 
related Phases all show up as individual tasks.  I've been reading about 
Outline levels, codes, and WBS, but do not seem to be grasping how to make 
use of those concepts to achieve the above.

Hope this makes a bit more sense. :-) 

Will this work if I create a WBS in Project and specify the Work Request 
number as Level 1 and the Phase number as Level 2?

Also, I did see the note about this forum being closed on the 1st and will 
use the links provided for future questions.  Thank you!
0
Utf
6/2/2010 3:35:01 PM
It looks like Andrew's suggestion to add an 'Outline Level' field to the 
Excel file may be the solution I need.

The Work Requests have a value of '1' and the phases have a value of '2' for 
the Outline Level in the Excel file.  I created a custom import map in 
Project with the following fields: Outline Level, ID, Name, Start, and 
Finish.  The 'ID' field corresponds to the Work Request or Phase number and 
the 'Name' field corresponds to the Work Request or Phase Title from the 
Excel file.

Thus far everything is looking good.  I'm going to send a copy of the 
Project file to my manager for review.

Thank you both again so much for your help!!!

-1
Utf
6/2/2010 4:21:02 PM
Great, glad to hear you were able to work things through Amy.  To 
answer your earlier question about where to go with future 
questions see the forums:

http://social.technet.microsoft.com/Forums/en-US/category/projectserver2010,projectprofessional2010


Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

On 6/2/2010 12:21 PM, amycwhitfield wrote:
> It looks like Andrew's suggestion to add an 'Outline Level' field to the
> Excel file may be the solution I need.
>
> The Work Requests have a value of '1' and the phases have a value of '2' for
> the Outline Level in the Excel file.  I created a custom import map in
> Project with the following fields: Outline Level, ID, Name, Start, and
> Finish.  The 'ID' field corresponds to the Work Request or Phase number and
> the 'Name' field corresponds to the Work Request or Phase Title from the
> Excel file.
>
> Thus far everything is looking good.  I'm going to send a copy of the
> Project file to my manager for review.
>
> Thank you both again so much for your help!!!
>
0
JulieS
6/3/2010 10:00:18 AM
Reply:

Similar Artilces:

Excel 2007 VBA Pivot table fetch records
I have a table 'tbl_Final' in MS Access 2007 where the data is used and cached in a pivot table found in the 'Data' worksheet in MS Excel 2007. In this MS Excel 2007 workbook, I have various worksheets reports which links to the pivot table values found 'Data' worksheet. I linked formulae in one of the cells found the worksheet reports, looks like this: =GETPIVOTDATA("Amount",Data!$B$4,"Month", 2,"Year","2008","Scenario","Budget") Lets say the cell value total is: 1000 So, In pivot...

removing empty cells in a rows of data
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I have may rows of data and I want to remove the empty cells. eg <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A B C D E F G H <br> 1 X X X X <br> 2 Y Y Y Y <br> 3 Z Z Z Z <br><br>I want it to look like this without having to copy/cut and paste. <br> &nbsp;&nbsp;&nbsp;&nbsp;A B C D <br> 1 X X X X <br> 2 Y Y Y Y <br> 3 Z Z Z Z <br><br>Is there any automated way to achieve this? Thanks Allan ...

Need help with Excel worksheet....I'm lost.
I have to complete this assignment in the next few hours and have no idea where to begin....any help would be sincerely appreciated. Here it is: A man has 5 years remaining on a six year car loan, his interest rate is 8.25% and his monthly payment is $525.00. His credit union is willing to accept the present value (PV) of the loan as a payoff. Develop an amortization schedule that shows how much he must pay at the end of each of the six years. Then create a worksheet that includes the beginning and ending balance, the amount paid on the principal and the interest paid for years two through ...

Excel Arrange Tabs not functioning
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am trying to send a picture to the back, I have used this function often in other MS office versions, but in the 2008 version for Mac it doesn't seem to work. Size, Rotation and ordering - Arrange function. Nothing happens? <br><br>How can I send pic to the back? Inserted graphic objects are always on the surface of an Excel sheet. The command you're trying to use is for changing the stacking order of objects which overlap one another. I'm not sure what you're trying to ach...

Data Shifting Between Records
Has anyone ever had experience with data moving from one record to another? I have a form in which I'm using the FMS rich text control ("Total Access Memo"). Several times when there has been an error (such as the user unable to print, or other error), the user finds that data in one or two of the rich text fields are actually data that had previously been saved in another record. This has happened two or three times in the past few months. And, while not a frequent problem, it is especially troubling when it happens. I've checked the FMS web site, and haven't seen...

Excel Formating
Is it possible to format cells in excel so that the value of 1000000 would be come 1m? I know this is possible with thousands using k, is something similar available for millions? --- Message posted from http://www.ExcelForum.com/ Hi jtidyman! Try custom format: #,###,,"m" -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "jtidyman" <jtidyman.ynklm@excelforum-nospam.com> wrote in message news:jtidyman.ynklm@excelforum-nospam.com......

Insert a row and data after a page break
I have created subtotals and page break after every change in department. What I want now is to insert a row after the page break and add data -- yepidu ...

Error using Excel add-in with INDIRECT.EXT function
When I use the function INDIRECT.EXT and a worksheet that I'm looking for isn't in the workbook, there is an error message displayed in the background, but I can't access it until I brute force close Excel. Any suggestions? Thanks When this occurs, look down on your task bar, or use "Alt+Tab" to switch views or minimize Excel. "Barb Reinhardt" wrote: > When I use the function INDIRECT.EXT and a worksheet that I'm looking for > isn't in the workbook, there is an error message displayed in the background, > but I can't access it unt...

Print just the list after performing a "find all" in Excel
I am working with a gigantic Excel workbook. I've used "find all" to locate a name in the entire workbook. It returns a list of 100 rows contained in 3 different worksheets. All I want is that list. Can I print just the list? ...

External data err
Excel 2k, win2k both updated Wishing to link to an Access2k db. Any attempt to 'get external data' causes excel to stop responding; no link or external data window opens. Have tried a repair, a reinstall, cleared the excel reg.key, cleared all tmp files, uninstalled and reinstalled, all with no change. Suggestions as to what next? This works fine on my other sys, win2k and winxp You may need to custom install ODBC (Open Database connectivity) fro the office setup disk. You should be able to run the disk to add thi functionality without re-installing everything. I don't think th...

Does Access store its data in fixed length or variable length rows
-- Dennis Hi, I would assume variable length. Of course it may not even store all of the the data for a particular row in a contiguous section of the file. You could do a quick and dirty test to see if it is variable or fixed length. Create a new database with one table with a text column of 200 or so characters. Create a method that lets you specify to append a thousand or so rows of data. Make a copy of the database. Run the process on one database telling it to save a short value, say a single character. In the other run the process on the second telling it to sav...

automatic tranfer of data from worksheet to time sheets
i use excell to both keep reports and i would like to have data such as time/dates from the reports transfered to a separtate worksheet to track time spent on different projects ...

Excel on website
Hi, I have an Excel file which opens maximum at my desk, but it opens minimum when I view it on my company's intranet. I use FrontPage 03 and Excel 07. Please advice! Thanks Chi After moving the file to server and linked it to the page I got it! Thanks Chi "Chi" wrote: > Hi, > > I have an Excel file which opens maximum at my desk, but it opens minimum > when I view it on my company's intranet. I use FrontPage 03 and Excel 07. > > Please advice! > > Thanks > Chi > > > > > > Aft...

Excel Shift Roster
Has anyone any idea where I can find a spreadsheet template for setting up a shift roster? Thanks - Dave Excel is not a particularly good tool for this unless you can get someone else to do the hard work. I might have a file with a programming solution at home, I will post back tonight if I still have it -- Regards, Peo Sjoblom "Dasco" <Cadasco@SUPERhotmail.com> wrote in message news:5oeqd7Fmh0o3U1@mid.individual.net... > Has anyone any idea where I can find a spreadsheet template for setting up > a shift roster? > > Thanks - Dave > "...

Creating a schedule / fairly for teams in excel
Version: 2008 Operating System: Mac OS X 10.3 (Panther) I want to create a schedule for my golf league. Each girl will need to have a new partner every week with one bye week. What type of formula would work in Excell to do something like this? Is it possible at all? See here: http://www.teamopolis.com/tools/round-robin-generator.aspx And here: http://round-robin-formula.qarchive.org/ The Excel solutions out there require VBA, which you do not have in Excel 2008. Hope this helps On 6/05/10 6:28 AM, in article 59bb8049.-1@webcrossing.JaKIaxP2ac0, "tlhamilton@officefor...

Analysis services connection using Excel
Server- remote win2k3 server running sql2k8 in workgroup setup - no AD. Client - Xp prof connect to server via vpn and rdp I am trying to connect to sql database from Excel , data , from other sources, from analysis services using windows authentication and I am getting an error " Errors in OLD DB provider. Could not connect to the redirector. Ensure that the SQL browser services is running on the 'dbservername' server. Can you pls update on what needs to be corrected to remotely connect to analysis services via vpn from client and also make a odbc connection ...

Arrow Keys No Longer Work in EXCEL
The arrow keys no longer work properly in Excel. I used to be able to move left/right/up/down with the arrow keys. Now, every time I hit an arrow key the whole sheet moves. I noticed that the solution for this on the PC is that the scroll key is locked. I have a MAC - hence no scroll key (i believe). Any thoughts??? Press Scroll Lock one more time -- Kind regards, Niek Otten "RJM" <rmcgivney@hartford.edu> wrote in message news:1133794598.726649.59010@g14g2000cwa.googlegroups.com... > The arrow keys no longer work properly in Excel. I used to be able to > move...

How do I prevent Excel from auto-correcting the date format?
I am using Excel, and every time I enter 2/1 in the spreadsheet - it corrects it to Feb-1. How do I prevent it from doing this? One way is to format the input column / range as Text first (via Format > Cells > Number tab > Text > OK) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Watts" <Watts@discussions.microsoft.com> wrote in message news:16725A60-590A-4658-857A-2E6C8420D253@microsoft.com... > I am using Excel, and every time I enter 2/1 in the spreadsheet - it corrects > it to Feb-1. How do I prevent it from do...

Unable to Export Data from GP to Excel
We're having a issue when a user attempts to export data from GP to Excel he receives an error message that: "Unexpected Error; quitting" Checked the event logs on PC, nothing present. Running GP 8.00g34 and Excel 2000. The error occurs when the user clicks on SmartLists, highlights any account and then clicks Excel. I reinstalled GP w/ IM and reinstalled Office 2k and patched it. The user is still experiencing the same issue. I did notice that when I open Excel I get and error message stating that: "C:\Pr.....\Integration Manager\Add-In 2000\IAXLAddin.xla coul...

XY charts not displaying correctly in Excel 2007
I have a suite of Excel spreadsheets for the design of reinforced concrete that have been developed in Excel 2003. When loaded by Excel 2007, the numerous XY charts are not displaying correctly when series ranges are separated by commas (for example C21:W21,C36:W36). Only the part of the series before the first comma displays. When a very similar problem occurred some years ago with the release of a new version of Excel, MS prepared a fix/upgrade. -- Rod Webster FIStructE of Concrete Innovation & Design The problem seems to have been fixed by Excel 2007 SP2. - Jon ------- Jon Pelti...

Compare File Data ?
Is there any way to compare data in two Excel spreadsheets? Let's say I've got 10000 line items in one file and 9000 in another. I'd like to find out which 1000 items are missing from the one file. Assuming that I have a column with a primary key, is it possible to compare the two files? Thank you. Chip Pearson has lots of techniques to work with duplicates: http://www.cpearson.com/excel/duplicat.htm Erskin! wrote: > > Is there any way to compare data in two Excel spreadsheets? Let's say > I've got 10000 line items in one file and 9000 in another. I'd like ...

BROWSE AN IMAGE from hardisk an past to excel
my .xls must be simply a foto and a button "browse". I want that i can browse a photo from hardisk and, when i selected, it appear on document. Can you help me? thx Try this Disperso MyPath = "C:\" Change this to your path Sub test() Dim FName As Variant Dim wb As Workbook Dim MyPath As String Dim SaveDriveDir As String SaveDriveDir = CurDir MyPath = "C:\" ChDrive MyPath ChDir MyPath FName = Application.GetOpenFilename(filefilter:= _ "Pictures(*.jpg;*.gif;*.bmp...

how do I set up a chart using data ?
I am having trouble figuring out how to set up a chart using two different data types. I want to plot the sunrise and sunset as lines according to date. The area inbetween the two lines will be the total amount of sunlight in the day. Excel will not let me format the chart the way I imagine it to be (x-axis is date and y-axis is time). Suppose your dates are in col. A starting with A2, the sunrise times in B and the sunset times in C. Ensure A1 is empty (if it is not delete the current content and put it back later). This makes one of the steps below much simpler. Click anywhere in t...

DDE very slow with Excel 2002 repost
I have a DDE server app that my clients use to import data into Excel. After some of my clients upgraded to Excel 2002 & Windows XP I received numerous complaints about sevier performans deterioration. For example a spreadsheet with 150 DDE links used to update for 2-3 min with Excel 200 on Windows 2000. With Excel 2002 on Windows XP it take 15 to 20 min for the same spreadsheet !!! As they spent lots of money for the upgrade, everithing was blamed on my DDE server. I took some of their documents and made some test with different versions of Windows, Excel and OpenOffice 1.1. I o...

Odd issue with Excel
Hi, I am hoping some one else has seen this. My group at work recently upgrade to Office 2007 (approx 3 weeks) . We have been using a shared excel document on a share drive for about a year now. Since the upgrade we have had the file just disappear from the share drive. It has happened twice so far. We have to have some who has the work book up save it locally and then we need to move it back to the share. Has any one seen anything like this and do you know a fix. TIA ...