Solve Primavera P6 Import from Excel Problems with ASAP Utilities

Solve Primavera P6 Import from Excel

 

Many P6 users have trouble importing data from Excel.  Many of the problems are focused on importing costs or other numeric data fields that have been manually typed into Excel.

Why can’t I import Primavera P6 cost data from Excel?

Primavera P6 only imports data that is formatted as “Text” in Excel.  If you have manually typed in a value like “$32000” into an Excel cell, Excel will auto-format that field as “Currency”. Then on re-import, P6 will not accept the data, as it needs to be “Text”.

Setting Excel’s Format Type Doesn’t Work

If you try to set a cost cell or a number cell to Excel’s “Text” format type, P6 will still not import it.  Why?

You Need The Apostrophe ( ‘ ) !

P6 won’t import the field unless it has a leading apostrophe.  The apostrophe is a legacy indicator that a field is textual and P6 looks for it.  It your cell’s data does not have the leading apostrophe, P6 won’t import the data in that cell.

P6 excel import problems

Ok, I have the Apostrophe, and I still can’t import Costs into P6!

As our good friend Zoltan Palffy has pointed out, to import costs from Excel, you’ll need to ensure that P6 is not calculating costs for you. On the resource tab, you can add a column called “Calculate Costs from Units”, which is a checkbox field you can check on or off. If your resource assignment has “Calculate Costs from Units” checked ON, then you won’t be able to specify a cost, and thus import costs from Excel. Make sure this field is checked off before you try to import.

To turn that setting off for all resource assignments, build a Global Change, that sets “Calculate Costs from Units” to No.

The Apostrophe is still important.

ASAP Utilities Plug-in for Excel Can Help

I’ve been searching for an easy solution to this problem.  You can of course, use Excel’s text manipulation formulas to add the leading apostrophe, but then you’re probably adding extra columns to your spreadsheet and P6 won’t like that either. ASAP Utilities is a plug-in for Excel that offers many features, including a text manipulation feature that can quickly add the missing apostrophe to a range of cells.  It turns out to be a pretty invaluable too.

ASAP Utilities plug-in for Excel offers a free trial version for 90-days.  It’s also very affordable at $49USD as of this writing.  Goto http://www.asap-utilities.com to check it out.

 

Have Your Say

Do you have some other solution to problems with data types while importing data in Excel?  Share your comments below.

New Call-to-action

Author:
Michael is an avid project controls blogger and is the Chief Learning Officer here at Plan Academy. Michael has taught 1000s professionals how to use project controls software like Primavera P6 over the past 10 years through his online courses and tutorials. Michael is a member of AACE, the Guild of Project Controls and holds his PMP certification from PMI.