Tuesday, May 06, 2008

Microsoft Project Files, SAS, and .NET

Ok, another fun, fun time with SAS and an obscure area. Here's the scenario, the client needs data from an MPP file (Microsoft Project) converted into a SAS dataset. I tried lots of routes, all to no avail. As usual, I get to go through the mess of COM interop, lack of documentation on the web, and SAS not supporting write access in the local data provider. Ahhhh, the joys of consulting ;-]


I finally got the following C# code operational and it writes the data into a SAS-friendly XML format. I hope this helps someone else:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.MSProject;
using System.Reflection;
using Savian.Core;
using Savian.DataManagement.Delimited;

namespace Client.ReadProjectFile
{
public class Main
{
List tasks = new List();

public void Process(string file)
{
ReadMppFile(file);
ConvertFileToSas();
Console.ReadLine();
}

private void ConvertFileToSas()
{
DateTime start = DateTime.Now;
Console.WriteLine("Start converting to SAS..." + start.ToShortTimeString());
DataTable dt = tasks.ToDataTable("TABLE");
dt.WriteXml(@"c:\temp\Client.xml");
DateTime end = DateTime.Now;
Console.WriteLine("Finished conversion..." + end.ToShortTimeString());
Console.WriteLine("Elapsed time: " + (end - start).TotalSeconds + " seconds");
}

private void ReadMppFile(string file)
{
ApplicationClass app = new ApplicationClass();
app.DisplayAlerts = false;
app.ScreenUpdating = false;
app.Visible = false;
app.MacroVirusProtection = false;
app.FileOpen(file, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, PjPoolOpen.pjDoNotOpenPool,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
var mppTasks = app.ActiveProject.Tasks;
int i = 0;
DateTime start = DateTime.Now;
Console.WriteLine("Start creating records..." + start.ToShortTimeString());
foreach (Task task in mppTasks)
{
ProjectInfo pi = GetProjectInfo(task);
tasks.Add(pi);
i++;
if (i % 100 == 0)
{
Console.Write("Records created: " + i);
Console.SetCursorPosition(0, 1);
}
}
Console.WriteLine("Records created: " + i);
DateTime end = DateTime.Now;
Console.WriteLine("Record read completed..." + end.ToShortTimeString());
Console.WriteLine("Elapsed time: " + (end-start).TotalSeconds + " seconds");
app.FileClose(PjSaveType.pjDoNotSave, Missing.Value);
}

private static ProjectInfo GetProjectInfo(Task task)
{
ProjectInfo pi = new ProjectInfo();
AssignFields(ref pi, task);
return pi;
}

private static void AssignFields(ref ProjectInfo pi, Task t)
{
pi.Name = t.Name;
pi.Start = (DateTime)t.Start;
pi.Finish = (DateTime)t.Finish;
pi.Id = t.ID;
pi.PercentComplete = Extensions.GetIntValue(t.PercentComplete);
pi.Duration = Extensions.GetIntValue(t.Duration);

}
}
}

class ProjectInfo
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime Start { get; set; }
public DateTime Finish { get; set; }
public int? PercentComplete { get; set; }
public int? Duration { get; set; }
}

No comments:

Cisco Information Server (CIS) and MS OLAP

Ok, so my recent issue was integrating CIS with Microsoft OLAP cubes. The normal way to do this is to set up MS OLAP (SSAS) with a website a...