Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Connecting to your SQL database using Excel - how to!

37 replies [Last post]

Fri, 2006-04-21 04:19

Emma Seaton

Offline

Joined: 16 Sep 2005

Posts: 55

Groups: None

I recently wanted to get at the P3e data via excel in order to create a spreadsheet that wasn’t possible using the reports wizard in P3e (or not that I could find anyway. Since then I’ve been asked how it’s done, so I thought I’d share it with the rest of you, as it’s been invaluable to me. I have a standalone MSSQL server driving my database, so it may be different if you’re on a network or use ORACLE/another dbase. Anyway, the process is as follows: (’>’ means click)

1. Open a spreadsheet and select >Data>Get External Data>New Database Query.
2. Select ’’ >OK and type in item #1. the name you want to call it... eg. Test...
3. Select your server from item #2.... in my case this is the SQL Server.
4. In item #3. >Connect and type in or select the name of your server in the following format: SERVERNAME\PRIMAVERA and enter the user name and password in the LoginID and password boxes below. I had trouble with this bit because it’s not the login you use for getting into the P3e application, but finally found out from the Primavera website that the default for this is ’privuser’ for both, but of course you may have to sweetalk your administrator for this if ’privuser’ doesn’t work for networked versions.
5. >OK and Excel will connect to the database and open up item 4. giving you the option to select a default database table which isn’t necessary at this stage, although a good one to pick would be PROJECT as it contains the project unique ID’s and proj_short_name which is the Project ID in the P3e application. It’s up to you if you select the save login option.
6. >OK and you’ll go back to the ’choose data source’ box.
7. Select your data source, eg. Test, and >OK it.
You’ll then get the Query Wizard - Choose Columns box. This means you’ve been successful and you’re in!

The lookup tables for the database mapping is in the Documentation folder on installation disk 1, and there’s also a schema telling you what links to what. The most useful tables I found so far are PROJECT, TASK (activities), PROJCATVAL (project code values), but there are many many more... have a look around and enjoy!!

Emma

Replies

Tue, 2013-04-02 12:00

Samir N

Offline

Joined: 2 Apr 2013

Posts: 3

Groups: None

Hello Everyone,

am trying to connect my oracle database from excel and it seems am doing something wrong, Data -> External Data -> From SQL Server

excel is asking about Server name and login information

by default my server name is PMDB on my computer so i use localhost\PMDB with pubuser (or privuser) as login information and its not working...

what am doing wrong ?!

Fri, 2009-02-20 18:12

Arend Kok

Offline

Joined: 19 May 2008

Posts: 38

Groups: Petro-Chem / Oil & Gas

Thanks!

Fri, 2009-02-20 10:12

Sarah Benjamin

Offline

Joined: 28 Apr 2008

Posts: 7

Groups: None

You can install the SDK by choosing Custom on the installation choices, then checking off the box next to SDK. There is some configuration required to make sure the database connects but it’s not too difficult.

Thu, 2009-02-19 22:32

Arend Kok

Offline

Joined: 19 May 2008

Posts: 38

Groups: Petro-Chem / Oil & Gas

Hi,

Everyone here keeps talking about a SDK. I know there is a API available, but I did not now about a SDK.

Could you please tell me where to get hold of this SDK?

Thanks!

Mon, 2009-02-16 17:02

Sarah Benjamin

Offline

Joined: 28 Apr 2008

Posts: 7

Groups: None

I have successfully connected via SDK to my data, with my end goal being graphical reports. I can use any of the following: Excel, Access, or Crystal Reports. I don’t have much experience with Access or Crystal, but I have been told these are the best tools for the job. I need help navigating the data to be able to set the report to look at the right information.

I want to make a single project report that summarizes all open issues, risks, milestones, and some user-defined-fields (indicators). It’s basically a project dashboard. I want to create the report so that I can fit all this on one sheet of paper. If anybody can help, I need assistance with setting up my queries!

Thanks,
Sarah

Thu, 2009-01-08 02:40

Hannes de Bruyne

Offline

Joined: 25 Jul 2005

Posts: 154

Groups: None

Hi Mark

can you please tell me the name of the files and the path were to find them?

"
The lookup tables for the database mapping is in the >Documentation folder on installation disk 1, and there’s >also a schema telling you what links to what.
"

It is also quit interesting to connect using ACCESS, but for that its good to know what links to what.

Thu, 2008-12-18 08:52

Mark Chapman

Offline

Joined: 18 May 2006

Posts: 183

Groups: None

The first page has the handy tip.

I tried this but I don’t find the tables I am looking for such as PROJECT, TASK, PROJCATVAL, etc..I do see 20+ other tables. Why no PROJECT table?

>The lookup tables for the database mapping is in the >Documentation folder on installation disk 1, and there’s >also a schema telling you what links to what. The most >useful tables I found so far are PROJECT, TASK >>(activities), PROJCATVAL (project code values), but there >are many many more... have a look around and enjoy!!

Thu, 2007-02-01 15:24

mimoune djouallah

Offline

Joined: 14 Oct 2006

Posts: 388

Groups: Petro-Chem / Oil & Gas, Spider Project Team

thanks Emma

mimoune

PS: i think i found an excel guru

Thu, 2007-02-01 11:00

Emma Seaton

Offline

Joined: 16 Sep 2005

Posts: 55

Groups: None

Thanks for the info, Kevin. I shall give it a go.

I’ve tried using access, but as my knowledge on it isn’t as good as excel I didn’t get very far! I managed to get the data tables, but then got stuck on what to do with it!!

;o)

SDK sounds so much easier if it works!... hum, back to good old excel!

Cheers

Emma

Thu, 2007-02-01 00:19

#10

Kevin Button

Offline

Joined: 16 Jul 2003

Posts: 112

Groups: None

Emma

Accessing a networked database is the same as local, just change the SDK driver (ODBC configuration) to look at the new server and database.

Updating Primavera data via excel requires you to write some VBA code, first to change the Scope and Access levels in the Project table, then to do the updates you want in other tables. I find MS Access is so much easier, because I can do the Scope and Access queries quickly and then depending on what changes I have to make, can create more queries to change data or paste stuff into Primavera tables from Excel or elsewhere.

Personally I find extracting data via SDK is much easier using MS Access than Excel and I use Excel for pasting stuff from Primavera to do graphs etc.

Cheers

Wed, 2007-01-31 17:17

#11

Emma Seaton

Offline

Joined: 16 Sep 2005

Posts: 55

Groups: None

Also I found that the connection to the database via Excel was one way only - from the dbas to Excel, so data integrity was maintained. I didn’t really investigate going the other way except by using SDK. The other reason for using SDK was to download the Notebook fields which Primavera told me was the only way to do it - otherwise I got a load of junk across!

I always got the response in Excel of ’missing string’ when trying to get to SDK.

I gave up in the end, and can’t even use it now I’m off standalone (unless anyone knows how to access a networked P5 (not experimented yet!).

Emma

Tue, 2007-01-30 16:31

#12

Kevin Button

Offline

Joined: 16 Jul 2003

Posts: 112

Groups: None

Just remember that updating data by using a direct database connection instead of via the SDK is not a great idea. The SDK will enforce business rules that are built into the software so you can’t insert dodgy data like you can with a direct connection.

Extracting data is also better with SDK because there is so much more available. The logical database schema provided by the SDK offers much more data fields that the physical schema, and it also has extended tables which makes it much easier to do multiple table queries.

Tue, 2007-01-30 15:11

#13

mimoune djouallah

Offline

Joined: 14 Oct 2006

Posts: 388

Groups: Petro-Chem / Oil & Gas, Spider Project Team

hi andrew

we are speaking here in the case the user want to update the data using front end software excel acess etc

anyway for my case it gives me this error

database key word not present in connect string

please need help

friendly

hi Alex where are you ? i though u are our IT superhero

Tue, 2007-01-30 13:51

#14

Andrew Podolny

Offline

Joined: 16 Nov 2004

Posts: 130

Groups: Petro-Chem / Oil & Gas

Hi guys.
Connection to PMDB from Excel or any other application (Access, Crystal Report...) doesn’t require SDK installed.
All you have to do - is to create connection to your SQL / Oracle server. It works for me (Windows XP SP2, MSDE -2000, PM 5). The connection string to activities table looks like this:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=PMDB;Data Source=your_SQL_name\primavera;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=your_Workstation_ID;Use Encryption for Data=False;Tag with column collation when possible=False

Command type: TABLE

Command text is following:
"PMDB"."privuser"."TASK"

Hope this will be helpful

Best regards,
Andrew

Tue, 2007-01-30 08:08

#15

mimoune djouallah

Offline

Joined: 14 Oct 2006

Posts: 388

Groups: Petro-Chem / Oil & Gas, Spider Project Team

thanks kevin

but it seems i have the same problem now as post N° 17

i installed all the stuff; alias and so one; but it don’t want to connect although i get the login screen but afterward it gives an error.

anyway it is like a revolution in comparing to P3.

thanks

Mon, 2007-01-29 16:18

#16

Kevin Button

Offline

Joined: 16 Jul 2003

Posts: 112

Groups: None

Mimoune

You can install the SDK from the same CD that you installed P5. Check the adminguide.pdf file for instructions.

Good luck

Mon, 2007-01-29 04:29

#17

mimoune djouallah

Offline

Joined: 14 Oct 2006

Posts: 388

Groups: Petro-Chem / Oil & Gas, Spider Project Team

hi Mario, Kevins

ok thanks, as it did not worked for me; i thought it is a story of a licence.

i think it is a time to learn some sql language.

thanks

Mon, 2007-01-29 04:22

#18

Mario Sanvitale

Offline

Joined: 22 Sep 2005

Posts: 139

Groups: None

Mimoune, I think you are trying to get user access to the Java API - the SDK is a completely separate application, doesn’t work through the licencing in terms of allocating a user. If you want the Java API you need to talk to your local PAR and they can get you one from Primavera.

Mon, 2007-01-29 02:15

#19

mimoune djouallah

Offline

Joined: 14 Oct 2006

Posts: 388

Groups: Petro-Chem / Oil & Gas, Spider Project Team

hello Kevins

when i go to admin users and try to check to Integration API it says total named users exceed licenced maximum users and for concurrent users i can’t even check to box.

i have only 1 licence to pm and 1 portofolio analysis

friendly.

Sun, 2007-01-28 16:47

#20

Kevin Button

Offline

Joined: 16 Jul 2003

Posts: 112

Groups: None

Mimoun

Unless Primavera have changed their licencing you shouldn’t need a separate licence for SDK, I have always just installed it and started using it. The Java API requires a separate licence.

Sat, 2007-01-27 09:13

#21

mimoune djouallah

Offline

Joined: 14 Oct 2006

Posts: 388

Groups: Petro-Chem / Oil & Gas, Spider Project Team

hi emma

thanks for your tutorial :) it just works, unfortunately my licence don’t allow me to use sdk delivred with P5 otherwise it would be great to update data from excell.

emma as i am not expert in sql language key, table etc... can you please send me a sample file all i need is the sql query

my email is djouallah.mimoune@gmail.com

thanks in advance

friendly yours

Tue, 2006-08-15 03:16

#22

Emma Seaton

Offline

Joined: 16 Sep 2005

Posts: 55

Groups: None

Anyone have success in connecting to SDK via Excel - mine just doesn’t want to play. I’ve tried reinstalling SDK, but I think the problem might be with excel not SDK. Any hints gratefully received!

Emma

Wed, 2006-08-09 00:40

#23

Sanjiv Parekh

Offline

Joined: 13 Oct 2001

Posts: 19

Groups: Petro-Chem / Oil & Gas

Very useful.... b cos many people still love excel to play around for reports....

Tue, 2006-06-13 03:47

#24

Kevin Button

Offline

Joined: 16 Jul 2003

Posts: 112

Groups: None

The SDK documentation lists all database tables and fields, as well as the Project Manager field names. When you install the SDK the documentation is also installed. Check the file "C:\Program Files\Common Files\Primavera Common\PMSDK\Doc\index.html"

Cheers

Kevin

Wed, 2006-06-07 00:26

#25

Brian Cameron

Offline

Joined: 20 Apr 2004

Posts: 19

Groups: None

Hi Emma

Just to let you know there is another way to get to the information you are looking for (and possibly a lot more I don’t know about).

I have been experimenting with the a similar thing as you but I’ve been using Crystal Reports to connect directly to the SDK information.
The reason for this is I can produce presentable information and I can run filters within Crystal so as I can have groups, individuals, task or anything that I have data for presented just the way I want and I don’t corrupt the database information, all I have to do is hit refresh and the data is updated.
I get connected OK through Crystal to SDK but then I have to sift through the information that is there.
I found a few useful fields mostly the "X-****" information is the better data sources to look through. I have contacted Primavera but they don’t have a reference dictionary/directory that can be used to cross reference the names and information that is represented in project manager, the printout of the schema is huge and I suspect I need a bit of information from a variety of fields.

Primavera response to my query
There is nothing available like the document you have requested. I would
suggest making use of the schema as given in prim26236. These are in
HTML format and contains all tables with a brief description of each
table and each field in those tables.

Please also refer to the SDK documentation for further details including
a programmers guide. Details of this from prim28371 can be found below:

I will have to get back to trying this again when I get a chance.

Regards

Brian.

Thu, 2006-05-25 08:23

#26

Emma Seaton

Offline

Joined: 16 Sep 2005

Posts: 55

Groups: None

I’m still investigating the two way data transfer with excel, but give the Primavera SDK (Software Development Kit) a try. There’s instructions on Knowledge Base on how to install it. I’ve got it running, but excel is proving to be awkward and comes up with a ’General Error - Database Keyword not present in the connect string’ error when setting up the SDK as a new data source.. If anyone knows what this means and how to work around it I’d love to know.

Emma

Mon, 2006-05-15 02:27

#27

Amir Dadbakhsh

Offline

Joined: 13 Oct 2004

Posts: 42

Groups: Petro-Chem / Oil & Gas

Dear Emma
Thanks for sharing your valuable experience.
I’ve used other interface to connect to primavera enterprise database but it is always my wish to connect by Excel.
I knew those mentioned steps but my problem is:
I make a excel query and bring data in excel sheet, I want that when I make a change in paste data in excel, the same data will be changed in primavera but it is not happened.
When I push "refresh all", excel file synchronizes itself by primavera (data transfer from primavera to excel not from excel to primavera).
I’ll be glad to hear your solution to how solve this problem.
Kind Regards
Amir Dadbakhsh

Thu, 2006-04-27 04:51

#28

Emma Seaton

Offline

Joined: 16 Sep 2005

Posts: 55

Groups: None

The problem with all of these methods of copying from P3e to excel is that the data is out of date as soon as any changes are made - which with in excess of 40 programmes is happening all the time. Whereas using a direct connection into the database using the SQL query interface the data can be, and in this case is, set to refresh as soon as the excel file is opened, and can be refreshed at any time by clicking the refresh button on the ’external data’ toolbar. I can treat this data as live, rather than as merely a copy of the live data and can be confident that it matches P3e at any time).

Hope that makes sense!!

Emma

Wed, 2006-04-26 16:01

#29

Jonathan Johnson

Offline

Joined: 26 Apr 2006

Posts: 5

Groups: None

If you wanted to get info out of Excel and you have Primavera Project Management 5.0 you can just export to a an excel file. And if you want to import from Excel to PM5.0 you just have to export from PM5.0 to get the right column header mappings then fill in the info and import back in.

Wed, 2006-04-26 06:01

#30

Steven Oliver

Offline

Joined: 8 Nov 2002

Posts: 313

Groups: None

Pivot Tables default to "Count". Change this to "Min" (or "Max")

Wed, 2006-04-26 05:42

#31

Emma Seaton

Offline

Joined: 16 Sep 2005

Posts: 55

Groups: None

Hi Steve, hope you’re well...

I briefly looked at Pivot Tables but they seemed to be all about summarizing data - ie. how many times a value occurs in a table. Is there another way of using them that I’ve not worked out? (I suspect there is!!)

Wed, 2006-04-26 05:27

#32

Steven Oliver

Offline

Joined: 8 Nov 2002

Posts: 313

Groups: None

Copy from P3e, and Paste into Excel. Then use a Pivot Table

Wed, 2006-04-26 04:44

#33

Emma Seaton

Offline

Joined: 16 Sep 2005

Posts: 55

Groups: None

Um, those columns don’t look much like columns, but hopefully you get the idea!!

Wed, 2006-04-26 04:44

#34

Emma Seaton

Offline

Joined: 16 Sep 2005

Posts: 55

Groups: None

No, all I’m doing is creating a tabular report with projects down the left hand side and key activities across the top with dates in the cells below. This would look thus:

Project.....Remit.....Design...Authority...Site...etc
P01 Bridge..12/12/06..31/3/07..20/4/07.....1/6/07...
P02 Station.2/3/06....5/6/06...20/7/06.....15/9/07...

and so on in a list down the page (ignore the dots, just keeps the words in columns here).

I just couldn’t get the data the right way round in P3e, but it works perfectly in excel once I got the interface sorted out (aren’t Help pages great!!!).

Emma

Fri, 2006-04-21 05:10

#35

Brad Lord

Offline

Joined: 27 May 2003

Posts: 256

Groups: None

Hi Emma

What I meant was are you trying to creat an s-curve, cashflow??? Im not a technical SQL or otherwise database specialist but do have alot of experience of setting up different reports,

brad

Fri, 2006-04-21 05:08

#36

Emma Seaton

Offline

Joined: 16 Sep 2005

Posts: 55

Groups: None

Thanks Brad, I’ll send you a copy via email of the results of my SQL queries.

Emma

Fri, 2006-04-21 05:00

#37

Brad Lord

Offline

Joined: 27 May 2003

Posts: 256

Groups: None

Hi Emma

Im not quite sure what kind of report you are trying to create??? I may be able to help if you let me know

regards

bradley

Contact Us

Email: PPadmin@planningplanet.com

Planning Planet

Tips on using this forum..

Connecting to your SQL database using Excel - how to!

Replies

Top Posters

Contact Us

More like this

User login

Partners & Sponsors