Update #ProjectServer lookup tables using #PowerShell #PS2010 #SP2010 #MSProject #PS2013

I recently wrote a PowerShell script that updates a Project Server lookup table with values from a text file. The script is available to download, I have uploaded it to the Microsoft Script Center below:

http://gallery.technet.microsoft.com/scriptcenter/Update-Server-Lookup-table-bb1ae14f

****Update – this also works for Project Server 2013****

This post will detail what needs to be updated in the script, the text file and a walkthrough of using the script.

Firstly lets take a look at the text file containing the values that I want to add to my lookup table. Worthing noting is that it can either be an empty lookup table or a lookup table that already contains values. The values I am going to add can be see below:

This script does assume that the lookup table values are all level 1.

As mentioned there are variables / lines in the script that will need to be updated, these can be seen below:

• $values = Get-Content “C:\Lookupvaluestoadd.txt”  – This line will need to be updated to reference the filename and path for the text file that contains the lookup table values.
• $lookupTablename = “Test Lookup Table” – This variable will need to be updated to reference the lookup table you wish to update
• $svcPSProxy = New-WebServiceProxy -uri “http://vm353/pwa/_vti_bin/PSI/LookupTable.asmx?wsdl” – This line will need to be updated to reference your Project Server PWA URL, replace http://vm353/pwa with your PWA URL

The following methods will need to be updated to reference to correct local ID, the script currently uses 1033:

• ReadLookupTables
• ReadLookupTablesbyUids (used twice)
• UpdateLookupTables

Now that we have the lookup table values ready in the text file and the lookup table exists, in this example the lookup table is call “Test Lookup Table”, we are ready to execute the script. Also make sure the lookup table is checked in.

As you can see below, currently the “Test Lookup Table” is empty:

After closing the lookup table (the lookup table needs to be checked in), we will execute the PowerShell script to insert the values from the text file. Open Windows PowerShell with a Project Server administrator account and navigate to the location of the script, in this case it is on the desktop:

Press enter to execute:

The script states that everything was successful, now see the lookup table:

I have created some other PowerShell scripts working with lookup tables, I will upload these in the next week or so.

As always, this script is provided as is with no warranties etc. use at your own risk and test on a test environment before using on a production environment.

Advertisement