Archiving Tweets in Bento
If you’re like me, you probably use Twitter more as a personal diary than as a means of oversharing to the world (though of course, that’s a by product that you obviously have to be aware of.) As a journal though, what good is it if you can’t easily pull up your tweets from months or even years ago? And if you’re at the mercy of a server, there will be the possibility of losing your tweets forever and that would just suck.
In the age of meeting niche needs through the Internet, it was easy to find a service that would make .xls files out of your Twitter feed. That service is Tweetake, easily found at http://www.tweetake.com.
At first, the script they used was a bit buggy, but it has improved over time. The only real unfortunate thing about it now, is that it can only archive tweets from no earlier than 2009. The good thing for me was I started using Tweetake early enough so that I managed to archive all my tweets from when I started “seriously” tweeting in 2008.
For me, a .xls file wasn’t enough. If it was going to come in a flat file database type, it may as well be in something that could be easily filtered and searched. The challenge was clear: get this into Bento! You figure it would be easy with Bento already being able to take in CSV files, however, the data for the date/time field was a string typed out like this: “Tue Apr 19 06:00:20 +0000 2011″ which unfortunately a spreadsheet could not automatically decipher, let alone Bento. I decided to utilize the functions in Numbers to basically convert the string into meaningful data that Bento could take in. Once I figured that out, I realized that the time also had to reflect the time zone I live in. The time in the field was set to GMT, but not only was it a matter of changing the time, but the date, month or even the year noted could be changed by the time shift of a few hours, and so I had to account for that too. Through trial and error, and learning about Numbers functions (helped by my programming background), I managed to create calculated fields that would determine the correct time, day, month and year values based on the original string value in the xls file.
So, long story short… I have made a Numbers document and a Bento template that can be used to archive your tweets generated as a .xls file by Tweetake. It can be downloaded as:
tweetaketobento.zip
All you need is Numbers ’09 and Bento 3.
Working Numbers File
Instructions
- Download your Tweetake .xls file from http://www.tweetake.com. You will need to allow Tweetake access to your Twitter account by logging in.
- Open the Tweettake .xls file in Numbers. There will be a lot of extraneous information in this file, but you’ll only need two columns of info.
- Copy the 2 columns labeled “Status Date” and “Status Text,” but exclude the top row with these labels.
- In the “tweettake to bento” Numbers document, paste the info in the appropriate columns in the Table labeled “Tweetake.” These columns (Column A and B) are colored in BLUE.
- In the “GMT Offset” Table in Cell B1, put the GMT Offset for the area where you live. (-10 is for Hawaii)
- If you’re not sure what the GMT offset is for your area, go to
http://wwp.greenwichmeantime.com/ - If you need to do more than 1 time zone, you’ll have to do import and export your tweets in separate batches. Simple, right?
- If you’re not sure what the GMT offset is for your area, go to
- Export the Numbers document as a .csv file (File > Export)
- A folder with two .csv files will be created as a result. The .csv file you’ll want is Tweetake to Bento-TweetTable.csv
- Open Bento and import the template Tweets.BentoTemplate (File > Import > Template)
- In Bento, import the Numbers generated .csv file (File > Import > File)
- Choose Tweetake to Bento-TweetTable.csv
- Choose Target: “Tweets” (Template that was just imported in Step 7)
- Set up the import with the following Headers -> Fields
- “STATUS TEXT” == “Tweet”
- “GV9 EOYear Adjusted Year” == “YEAR”
- “DATE/TIME” == “Date Time”
- You’re done! To archive newer tweets, repeat process again, with the exclusion of Step 7 as the template need only be imported once.
If you’ve tried this, let me know if it works and what you think! 
