Usage (with an existing .csv file in /tmp/todo.csv that has four columns: todo_text,context,priority,project):
~> things_todo.pl > Database.xml ~> cp -i Database.xml ~/Library/Application\ Support/Cultured\ Code/Things/Database.xml ~> open /Applications/Things.app/
#!/usr/bin/perl -w ########################################################################### # ID $Id: things_todo.pl,v 1.5 2009/01/24 12:25:09 me Exp me $ # # DESCRIPTION Imports a CSV list of ToDo items into a Things XML # database. Make sure: # i) You back up your existing Things Database.xml # ii) You aren't running Things when you run this (or when you copy it into the right directory) # iii) If you don't know where Things' Database.xml is (and how to back it up and then copy a new file in its place), you probably shouldn't run this script! # iv) This is all guess work. Use at your own risk! # # USAGE things_todo.pl input.csv # input.csv should be a valid CSV file with # "todo_text,context,priority,project" # priority is 'high', 'medium' or 'low'. If left blank, # default is low. Other fields are straight text. # # Written by Nathan Bailey, http://polnate.net/ (c) 2009 ########################################################################### use strict; use Digest::MD5 qw(md5_hex); use Data::Dumper; use Text::CSV_XS; my $csv_file = '/tmp/todo.csv'; # Input file (in CSV format) my (%all_todos, # List of all the ToDo items, indexed by idref %priorities, # all priorities (h,m,l) and associated items %projects, # all projects (user defined) and associated items %contexts, # all contexts (user defined tag) and associated items %next_actions, # all next actions (= all ToDo items assigned to a project) %projectref_index, # have we already created this project? %contextref_index, # have we already created this context? ); my $next_objectid = 143; # Unique key for each item (eg. z143, z144, etc.) #my $next_objectid = 914; # Unique key for each item (eg. z143, z144, etc.) my $current_obj_id = $next_objectid; # Remember the initial value my $proj_index = 0; # Sort projects in incremental order (from 0) my $csv = Text::CSV_XS->new(); # create a new object open(CSV,$csv_file) || die "Couldn't open $csv_file: $!"; while() { chomp; my $status = $csv->parse($_); # parse CSV line into fields die "$status: Couldn't parse CSV line '$_'?" unless $status; # you'll need to fix your CSV file! (alternatively, change this line to 'next;' to skip this line my ($todo_text, $context, $priority, $project) = $csv->fields(); # get the parsed fields ######################################################################## # If this ToDo's project doesn't exist yet, create it... if (!defined $projectref_index{$project}) { $projectref_index{$project} = "z$next_objectid"; # We've created it now $projects{"z$next_objectid"} = { 'focus' => 'z128', # focus = z111 (Inbox) for ToDos in Inbox, z106 (NextAction) for ToDos in Projects and z128 (ActivityLevel-1) for Projects 'focuslevel' => 1, # focuslevel = 0 for ToDos and 1 for Projects 'focustype' => 131072, # focustype = 1 for ToDos in Inbox and 131072 for Projects and ToDos in Projects 'title' => $project, # straight text field 'parent' => '', # parent = '' for ToDos in Inbox, 'idrefs="$proj_ref"' for ToDos in Projects 'index' => $proj_index++, # is incremental, to show order }; $next_objectid++; # each object needs its own unique idref } ######################################################################## # If this ToDo's context doesn't exist yet, create it... if (!defined $contextref_index{$context}) { $contextref_index{$context} = "z$next_objectid"; # We've created it now $contexts{"z$next_objectid"} = { 'title' => $context, # straight text field }; $next_objectid++; } ######################################################################## # (note: we need to do this _after_ anything else increments next_objectid, so we've got the same next_objectid) # Add this ToDo to the project's children push(@{$projects{$projectref_index{$project}}->{'children'}}, "z$next_objectid"); # Add this ToDo to the tag's "items tagged" push(@{$contexts{$contextref_index{$context}}->{'items_tagged'}}, "z$next_objectid"); # Add this ToDo to the next actions list (do all project-allocated ToDos really go here?) $next_actions{"z$next_objectid"}++; ######################################################################## # Add this ToDo to the appropriate priority list (everything in Things is double-referenced...) if ($priority eq 'high') { $priority = 'z131'; # High, as defined in XML below push(@{$priorities{'high'}}, "z$next_objectid"); } elsif ($priority eq 'medium') { $priority = 'z132'; # Medium, as defined in XML below push(@{$priorities{'medium'}}, "z$next_objectid"); } else { $priority = 'z114'; # Low, as defined in XML below push(@{$priorities{'low'}}, "z$next_objectid"); } ######################################################################## # Add this todo to hash # Clean up todo_text $todo_text =~ s//\\u3e00/g; # Need to escape > $todo_text =~ s/&/\u2600amp;/g; # Need to escape & ###$context = 'z102'; # Work - for debug only $all_todos{"z$next_objectid"} = { 'focus' => 'z106', # focus = z111 for ToDos in Inbox, z106 (NextAction) for ToDos in Projects, z128 (ActivityLevel-1) for Projects 'focuslevel' => 0, # focuslevel = 0 for ToDos, 1 for Projects 'focustype' => 131072, # focustype = 1 for ToDos in Inbox, 131072 for Projects and ToDos in Projects 'title' => $todo_text, # straight text field 'parent' => $projectref_index{$project}, # parent = '' for ToDos in Inbox, 'idrefs="$proj_ref"' for ToDos in Projects 'tags' => "$priority " . $contextref_index{$context}, 'children' => '', # children = '' for ToDos, $todo_refs for Projects 'index' => 0, # index = 0 (but actually, should be incremental to show order) ###'tags' => "$context $priority", # for debug only }; $next_objectid++; } close(CSV); #die Dumper \%all_todos, \%priorities, \%projects, \%contexts, \%next_actions, \%projectref_index, \%contextref_index; # Sort subroutine to sort idrefs numerically sub by_numeric_ref { my ($aa,$bb); $aa = $a; $aa =~ s/z//; $bb = $b; $bb =~ s/z//; $aa <=> $bb } # Generate tags for medium and high priority tasks my ($low_refs,$medium_refs,$high_refs); if (defined $priorities{'low'}) { $low_refs = ' idrefs="' . join(' ', @{$priorities{'low'}}) . '"'; } else { $low_refs = ''; } if (defined $priorities{'medium'}) { $medium_refs = ' idrefs="' . join(' ', @{$priorities{'medium'}}) . '"'; } else { $medium_refs = ''; } if (defined $priorities{'high'}) { $high_refs = ' idrefs="' . join(' ', @{$priorities{'high'}}) . '"'; } else { $high_refs = ''; } #die "Low=$low_refs, Med=$medium_refs, High=$high_refs"; # Print all the database (XML) before individual items print qq{ }; }; # This isn't their MD5, but hopefully it works anyway...? sub make_md5 { my $string = shift; my $md5 = md5_hex($string); my ($a, $b, $c, $d, $e) = $md5 =~ m/^(.{8})(.{4})(.{4})(.{4})(.{12})$/; $md5 = "$a-$b-$c-$d-$e"; $md5 =~ tr/a-z/A-Z/; return $md5; } # Print out all the todos, projects and contexts in order (I don't know if this is important, but since it's only a little harder to do it in order, we do...) foreach my $item_ref (sort by_numeric_ref (keys %all_todos, keys %projects, keys %contexts)) { my $md5 = make_md5($item_ref); my $index = ''; if (defined $contexts{$item_ref}) { ### my $shortcut = 'x'; # Do we need to create these properly? I hope not! :-} ### 134481920 7363F31F-A1D1-4B87-AE18-1504854FA334 $next_objectid BuildNumber 534 NSPersistenceFrameworkVersion 186 NSStoreModelVersionHashes Coworker sUkYhaNoBrCwKhR37oCWF3oMlJ+X/0EWvkqloP0feV8=Focus uULbh3iyp5BG3KMX6idGxJMAibcC6yucan0Z01ggiIM=Globals xg3+cjRZK5nzTQnzZc7QQs9Glge/AHW0kv95fcBSkgA=Milestone AshotdE+fvVYp6EK3DxEagqNUtde95dtva4bGCb5dP8=MobileSyncItem nhMBcaMSSTKgFnWBXIygedzMzRfTP3eGsr6TJi/DUiY=Note udFXIE3oFQrQbh2Ju4FvwWc878b9vep6DW6zVipQvEc=Record hcEtJCAXFUdb13ax22/07sD5EsZ+4UyMSQODnU0LyiU=Reminder 0VMtvPM/E/wh+TBTNHW5D5o9N4WXSeyTBjBHJHg89W0=SyncedCalendar uFecPv4qw/Ul/MnjxCEo207DnQN7DdkbvehReBug9BI=SyncedTask TXqtPJnhNwmeJWFf90FXFGdWKmcpjvlQRv34RXVaz30=Tag L9tlQ9OeHv0Nd5JzQ7T++N5lSPGZQs/yjZXVp2fatLI=Team 2/JrXCDtsSp8yG/A4FwlD5t8RHQacaJUMl+GylaRkmU=Thing 4+kUGDvoOAxogtkV5zqB5RB/mOBZDUXodM6PRiG18Ls=ToDo 2BUb3z36nOAXYIHVSH0DBTMyHjMcy8yGVmmGt5lUGVc=NSStoreModelVersionHashesVersion 3 NSStoreModelVersionIdentifiers OSMajorVersion 5 OSMinorVersion 6 $shortcut # after name="type" print qq{ }; } else { my ($focustype,$focuslevel,$title,$index,$parent,$focus,$children,$tags); if (defined $projects{$item_ref}) { $focustype = $projects{$item_ref}->{focustype}; $focuslevel = $projects{$item_ref}->{focuslevel}; $title = $projects{$item_ref}->{title}; $index = $projects{$item_ref}->{index}; $parent = ''; $focus = $projects{$item_ref}->{focus}; $children = ' idrefs="' . join(' ', @{$projects{$item_ref}->{children}}) . '"'; $tags = ''; } elsif (defined $all_todos{$item_ref}) { $focustype = $all_todos{$item_ref}->{focustype}; $focuslevel = $all_todos{$item_ref}->{focuslevel}; $title = $all_todos{$item_ref}->{title}; $index = 0; $parent = ''; if ($all_todos{$item_ref}->{parent} ne '') { $parent = ' idrefs="' . $all_todos{$item_ref}->{parent} . '"'; # For debugging: $title = '(Parent proj=' . $projects{$all_todos{$item_ref}->{parent}}->{title} . ') ' . $title; } $focus = $all_todos{$item_ref}->{focus}; $children = ''; $tags = ' idrefs="' . $all_todos{$item_ref}->{tags} . '"'; } else { die "Undefined ref? $item_ref"; # This shouldn't happen, but we check anyway :-) } ### Safe titles $title =~ s/\W//g; # for debugging only print qq{ }; } } print q{