Connecting to your SQL database using Excel - how to!

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 ?! 

Thanks!

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.

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!

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

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.

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!!

thanks Emma



mimoune











PS: i think i found an excel guru

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

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.

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

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

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

Mimoune



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



Good luck

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

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.

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.

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 [email protected]



thanks in advance



friendly yours

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

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

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.

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

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.

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

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