Wednesday, December 20th, 2006...11:18 am

Importing an MS Project Plan into Basecamp using Ruby

Jump to Comments

Basecamp is a web based project management tool. It's just fun to use. Microsoft Project....not so much.
That being said MS Project does a whole lot of stuff that Basecamp doesn't. Being leet web 2.0 doods we want to use Basecamp! Of course MS Project is the standard that most clients like to see, so we're kind of stuck.

So why not the best of both worlds, MS Project and Basecamp without having to retype everything into Basecamp!

With a bit of ruby and the help of the Basecamp API we can save a lot of time.

Introduction

MS Project gives us tons of different ways to organize our project, Basecamp has only a few simple ways so our mapping can't really be one to one. Basecamp offers the following concepts: Milestones, To-Do Lists (made up of items, may be linked to a milestone).

Here is the basic strategy I used for my project plan.

MS Project Milestone = Basecamp Milestone
MS Project Tasks under Milestone = Basecamp to-do list linked to milestone.

So our script simply loops through the project, when it hits a milestone we create a milestone, we collect all the tasks into a list until we hit the next milestone and the cycle begins again. So we end up with one whole to-do list per milestone.

Prerequisites:

  • Activate the Basecamp API for your project in your Account Settings (the same page where you would go to upgrade your account to pro). If you don't do this you'll see the script complain with 404 errors
  • Download the ruby Basecamp API Wrapper. Put this in the same directory as your script.
  • Install the xml-simple gem.

Exporting from MS Project

MS Project has a very nice export wizard. It allows you to export to a CSV file and specify filters and fields.

To access the export wizard, open up your project plan and choose File->Save As, for the 'Save as type' choose CSV (Comma delimited) (*.csv).

When prompted by the wizard to create or choose an export map, choose New map and click next, in the next dialog box choose Tasks as the type of data to export, uncheck 'Export includes headers', make sure our text delimiter is comma and click Next.

Now it's time to create our map, this is where we tell Project what columns to put in our CSV file.

For this script I just want the following data: [task name],[baseline finish date],[milestone].

So choose those three options, here's how mine looks:

Click Next, you can save the map for later if you want to do this again.

Another wrinkle, I like the following date format in MS Project: Tues 11/06/06 (Day MM/YY/DD). If you don't like it, no biggie you just have to figure out how to turn your preferred date format into YYYY-MM-DD later on in the script. I hope you like regex's!

The Script

Now it's time to write our script, so let's review what it should do:

Login to Basecamp
Select our project
Loop through our CSV
Create a collection that looks like this: Milestone->Task List
Insert milestones and task lists into basecamp!

Disclaimer: I have the ruby programming skills of a 4 year old girl, I know this. I do not claim to be an expert ruby ninja, feel free to improve the script.

Here is the script, so place this in the same directory as your csv file and the basecamp.rb file from 37 Signals.

#!/usr/bin/ruby

#Script by weheartcode.com, modify however you like.
#WARNING THIS SCRIPT CAN HOSE YOUR BASECAMP ACCOUNT
#Don't be stupid, try it on a test account first

require 'basecamp'

#Fill in these variables, PLEASE TRY ON TEST ACCOUNT FIRST
basecampurl = 'YOURBASECAMPURL' #Ex. weheartcode.grouphub.com

basecampuser = 'CHANGEME'
basecamppass = 'CHANGEME'

msprojcsvfile = 'CHANGEME' #Ex. export.csv

#Connect to basecamp using the Basecamp API Ruby Wrapper
session = Basecamp.new(basecampurl,basecampuser,basecamppass)

#Assume we're working on the first project
project = session.projects.first

milestones = Array.new

puts "Creating data structure..."

#Read our file line by line
open(msprojcsvfile){|f|
  f.each_line {|line|
  data = line.split(',')
  milestone = Hash.new

  ismile = data[2]

  #if we just hit a milestone, create one and add it to our Array
  if 'Yes' == ismile.strip
    #reconbobulate date for deadline
    #Expecting Day dd/mm/yyyy format (ex. Tues 12/25/2006)
     m1 = /.+\s(\d+?)\/(\d+?)\/(\d+)/.match(data[1].strip)
     deadline = "20" + m1[3] + "-" + m1[1] + "-" + m1[2]

      milestone["title"] = data[0]
      milestone["deadline"] = deadline
      milestone["thelist"] = Array.new
      milestones <<milestone
  #otherwise, we just have a normal task
  else
     #Add task to current milestone list array
     milestones.last["thelist"] <<data[0]
  end
 }
}

#Reverse our milestone list, so that the to-do's show up oldest first
milestones.reverse

#Let's go through each milestone and add to basecamp
milestones.each{ |m|

#Get a copy of our todo list
tasklist = m['thelist']

#Nil out key so we dont send to basecamp API
m['thelist'] = nil

#Create a milestone with our project id
#Uaing the data from our milestone hash
session.create_milestone(project.id,m)

puts "Milestone created."

#Grab an instance of the milestone we just created
themile = session.milestones(project.id).last

#create a to-do list for milestone
tmplist = Hash.new
tmplist["tracked"] = false;

#Use milestone title prefixed with M-LISt for list title
tmplist["name"] = "M-LIST: " + m['title']
tmplist["description"] = "Task list for milestone."
tmplist["milestone-id"] = themile.id

#Create the list with Basecamp API
session.create_list(project.id,tmplist)

puts "Milestone list created."

thelist = session.lists(project.id,true).last

#Create items underneath our list
tasklist.each{|task| session.create_item(thelist.id,task)}
}

puts "All done!"

Upload the ruby script to your hosting provider (I use dreamhost) or place in the directory on your local box.

Make sure you have your csv file from MS project and the basecamp library from 37 signals in the same directory and the xml-simple gem installed.

So, we simple change the variables for the basecamp URL, username, pass, filename and run the ruby script using our favorite editor and prepare to run the script.

For the love of god, please make sure you try this on a test account before you do it for the first time on your actual account, the results could be unexpected.

ruby import_basecamp.rb

If all goes well you will see the script do it's thing like this.

Milestone list created.
Creating Item for list_id: 1483337
Creating Item for list_id: 1483337
Creating Item for list_id: 1483337
Creating Item for list_id: 1483337
.
.
.
All done!

If you get 404 errors, please check to make sure you have the correct URL, that you have a project created in your account, and that you have activated the basecamp API in the Account tab of your basecamp account.

This is a very simple example, depending on how complex your MS Project Plan you may want to omit the task part all together, or modify how you name tasks/milestones. Either way it sure beats entering it all by hand.

11 Comments

Filed under ruby, tutorial

11 Comments

  • Hien
    January 18th, 2007 at 5:44 am

    Hi

    Thanks for posting the code. I tried it and i get this error.

    Creating data structure...
    import_msproject.rb:51: undefined method `[]' for nil:NilClass (NoMethodError)
    from import_msproject.rb:31:in `each_line'
    from import_msproject.rb:31
    from import_msproject.rb:30:in `open'
    from import_msproject.rb:30

    Not sure what's going on, i'm a non ruby guy.

    Thanks

  • Tim
    January 18th, 2007 at 9:16 am

    Hey,
    I think the problem is the script is expecting that your project will be set up like:

    Milestone
    Task
    Task
    Task
    Milestone
    Task
    Task
    Milestone
    task
    .
    .
    .

    I suspect you start off with a non milestone task! So on line 51 when it tries to add your task to the milestone that milestone is empty, you can fix it by adding a fake milestone line at the top of your CSV file so the first tasks go under a milestone.

    Does that make sense?

  • Hien
    January 18th, 2007 at 3:40 pm

    Sorta makes sense. I have the ruby skills of a 2 year old.

    Here's the first few lines from my csv file, i think the export out of project isn't completely like your file.

    Name,Baseline_Finish,Milestone
    Begin,Tue 12/2/2006,Yes
    Analize,Tue 12/2/2006,No
    Initial Site Visit,NA,No
    Feasability Study,Mon 12/8/2006,No
    Buyer Profiling Surveys,Mon 11/3/2006,No

    Wondering if that format screws it up.

  • wayne
    March 5th, 2007 at 4:11 pm

    Hi,

    This is very interesting, and thanks.

    But I'm getting this error -
    >ruby import_msproject.rb
    ./basecamp.rb:358:in `request': Bad Request (400) (RuntimeError)
    from ./basecamp.rb:373:in `records'
    from ./basecamp.rb:105:in `projects'
    from import_msproject.rb:23
    >Exit code: 1

    Any ideas?

    Cheers,
    Wayne

  • alex
    April 13th, 2007 at 3:24 pm

    hi nice site.

  • Josh Zapin
    July 17th, 2007 at 4:53 pm

    I love the idea. Looks like it works great. Here's one question: What happens if you make a change in project plan? Does this script update the BaseCamp site?

  • Tim
    July 17th, 2007 at 5:35 pm

    Josh,
    No it will not do updates, is really just to get you started on a large one. No synching and all that.

  • Josh Zapin
    July 18th, 2007 at 7:15 pm

    Just got an error:

    import_msproject.rb:70: warning: Object#id will be deprecated; use Object#object_id
    ./basecamp.rb:358:in `request': Moved Temporarily (302) (RuntimeError)
    from ./basecamp.rb:356:in `request'
    from ./basecamp.rb:373:in `records'
    from ./basecamp.rb:319:in `create_milestones'
    from ./basecamp.rb:312:in `create_milestone'
    from import_msproject.rb:70
    from import_msproject.rb:60:in `each'
    from import_msproject.rb:60

    Thoughts?

  • Jim
    January 18th, 2008 at 10:14 pm

    I am getting the same error as josh. Any help?

  • Jim
    January 18th, 2008 at 11:01 pm

    ok.. figured that one out. make sure you create your project in basecamp. pebkac

    now I am getting a 404

  • Rudolf
    May 30th, 2008 at 9:18 am

    He laughs best who laughs last,

Leave a Reply