vsbabu.org

MPP to SQLite via Ruby

I still love Python. Thought I will try my luck with Ruby. I still like Python’s syntax better. However, since everyone these days is talking about Ruby on Rails, I will look into it as well. As part of that, I am in the process of figuring out Ruby and the components that make RoR.

I especially liked ActiveRecord. It turned out to be very easy to write a quick script on windows that opens up an MS Project file, loads the tasks into a SQLite database and run some queries. Motivation for this is the fact that most reports that come with MS Project are not very useful to me. Once I get it into a SQL database, it becomes much more easier for me to automate further actions.

Here’s the script.

require 'active_record' #set to true, if we want to clear the table and load stuff fresh from project update_from_project = false mpp_file = 'e:\\mycurrentproject.mpp' sqlite_file = "e:\\tmp\\mpp.db" ActiveRecord::Base.establish_connection(:adapter => "sqlite3", :dbfile => sqlite_file) #~ #My table definition is below: #~ create table tasks ( #~ ID integer primary key, #~ OutlineLevel integer, #~ Name text, #~ PercentComplete integer, #~ Start varchar(18), #~ Finish varchar(18), #~ Milestone varchar(5), #~ Predecessors varchar(50), #~ ResourceNames text #~ ) class Task < ActiveRecord::Base end if update_from_project #clean all tasks; Task.delete_all require 'win32ole' mspApp = WIN32OLE.new('MSProject.Application') mspApp.FileOpen(mpp_file) project = mspApp.ActiveProject mspApp.Visible = false project.Tasks.each do |mpptask| task = Task.new(:ID => mpptask.ID) task.OutlineLevel = mpptask.OutlineLevel task.Name = mpptask.Name task.PercentComplete = mpptask.PercentComplete #my default date format in MS Project is yyyy/mm/dd hh:mi:ss; SQLite’s default is #yyyy-mm-dd. So adjust it. task.Start = mpptask.Start.gsub('/','-').split(' ')[0] task.Finish = mpptask.Finish.gsub('/','-').split(' ')[0] task.Milestone = mpptask.Milestone task.Predecessors = mpptask.Predecessors task.ResourceNames = mpptask.ResourceNames task.save() end mspApp.Quit() end #~ # In progress tasks print "In progress tasks\n\n" tasks = Task.find_by_sql("select * from tasks where PercentComplete between 1 and 99 and ResourceNames <> '' order by ResourceNames") tasks.each do |task| printf("\t%-40s %-30s %2d%% %s\n", task.ResourceNames, task.Name, task.PercentComplete, task.Finish) end #~ # Tasks due today print "\nTasks due today\n\n" Task.find_by_sql("select * from tasks where PercentComplete<100 and Finish=current_date and ResourceNames <> '' order by ResourceNames").each do |task| printf("\t%-40s %-30s %2d%% %s\n", task.ResourceNames, task.Name, task.PercentComplete, task.Start) end #~ # Tasks to be started today print "\nTasks to be started today\n\n" Task.find_by_sql("select * from tasks where PercentComplete=0 and Start=current_date and Finish != current_date and ResourceNames <> '' order by ResourceNames").each do |task| printf("\t%-40s %-30s %s\n", task.ResourceNames, task.Name, task.Finish) end

Installing modules were pretty easy. For example, just give gem install --remote sqlite3-ruby to install SQLite module.

It is possible to give conditions to find() method; since I still know more SQL than Ruby, I preferred to use find_by_sql() method.

Hopefully, if I ever get around to learning RoR, I will try to make a web front-end -- hey, from what I can read, everyone uses RoR to make todo-lists first! -- for assignments to be displayed on the web. It should be easy to provide the ability to update status right there and another script can then take the data from SQLite and sync it up to MS Project file. (I know, Project Central does all this and more, but what is the fun in that?)