19

How to Export Twitter Updates to Excel

Posted by reyjunco on October 21, 2009 in How-to, Research |

I found myself in need of an efficient way to download entire Twitter timelines to Excel in order to conduct a thematic analysis of Twitter topics for our research. I decided to share what I discovered. Here’s how to do it:
1. Go to http://twitter.com/statuses/user_timeline/userid.xml?count=x

2. Replace “userid” with your Twitter username

3. You’ll also need to replace the “x” at the end with your total number of tweets which can be found on your home page. This will download all of the tweets in your user timeline.

4. If you’d like to download your friend timeline (i.e., not just all of your tweets, but the tweets of everyone you are following), then replace “user_timeline” with “friends_timeline.”

5. A dialogue box will appear that will ask for your Twitter username and password. Once you’ve entered your username and password, you will have a page of output in xml format. Save this file by going to File->Save As in your browser. I recommend using FIrefox because Safari doesn’t do a good job of saving xml files.

Mac Users:
Here’s the bad news, you will need to use Excel in windows in order to import this file. I was unable to find a way to get Mac Excel to import the xml file correctly. If you know how, please post in the comments.

6. Open a worksheet in Excel and click on Data->XML->Import then choose the file you saved during the previous step.

7. Excel will show a dialog box that reads “The specified XML source does not refer to a schema. Excel will create a schema based on the XML source data.” Click “OK”

8. Excel will then produce another dialogue box that asks where you want to put the data. Select “XML list in existing worksheet” and click “OK.”

9. Voila! You will now have an Excel file that contains all of your tweets. Because Twitter is by no means a static medium, you may want to download tweets from time to time. In order to download new Tweets since your last download, find the id# of the last Tweet you downloaded (should be the third column in your Excel file) and input it into the ID field of this url:

http://twitter.com/statuses/user_timeline/userid.xml?since_id=ID

Update 10-26-09
: Currently, Twitter will only return 200 tweets at a time. If you want to download more than 200 tweets, you can download them incrementally by “page” with the link: http://twitter.com/statuses/user_timeline/userid.xml?page=x (the most recent updates are on page “1”). This application downloads your user timeline automatically: http://johannburkard.de/blog/programming/java/backup-twitter-tweets-with-twitterbackup.html

Tags: , , ,

  • http://www.linkedin.com/in/edborasky znmeb

    Not only are the pages at most 200 tweets long, tweets which are retweets that you made using the Twitter 'retweet' button will not be returned. In addition, a maximum of 16 pages will be returned.

  • http://blog.reyjunco.com/ Rey Junco

    The 16 page max must be new: http://apiwiki.twitter.com/Things-Every-Develop

    There are three different commands to get retweets (retweeted by me, retweeted to me, and retweets of me). I'm glad that I didn't have to deal with these during my data collection.

    The moral of the story– collect your data early and often.

  • WaspyRedhead

    This is very helpful information, thank you! Do you know if you are able to export information such as the usage of hashtags, or keywords? I’m a graduate student in public administration and I working on my thesis research proposal on a topic related to the use of social media in policy formation. I would like to create my own data set based on twitter participation, but I’m anxious about being able to mine enough data. Any advice you have would be most appreciated.

  • http://www.jmatthicks.com JMattHicks

    This is some pretty cool information. I haven’t figured out why I’d use it yet, but I’m sure there’s an awesome purpose and some great creative ways to use this. Thanks for sharing!

  • http://www.jmatthicks.com JMattHicks

    This is some pretty cool information. I haven’t figured out why I’d use it yet, but I’m sure there’s some great creative ways that I could. Thanks for sharing!

  • reyjunco

    @WaspyRedhead I would check out http://140kit.com/ – it’s an open source Twitter analytics platform built by a Twitter researcher for Twitter researchers. Twapper Keeper (http://twapperkeeper.com/index.php) is good tool for archiving tweets. Drop me an email – happy to help.

  • RyanCraft

    I actually found a 3rd party application that does this for you…has some cool features. Check out tweetake.com if interested.

  • Shawneen Gonzalez

    Thanks for this post. I was able to get up to 22 pages of tweets in xml format.  Just thought you might want to be aware of that. 

  • Fatima Rahiman

    Hi there
    Thanks for this helpful info!
    Is there away of importing #tags though ? 

  • Thummalapalli Stalin

    how to get
     tweets of particular topic like iphone 4 vs HTC mobile

  • http://twitter.com/Nojauta 会 Aleksandré

    Thank you so much!

  • http://twitter.com/Cuenta_test Tester

    Hi Rey! Im trying to download my timeline ( the tweets of everyone I’m following) but the dialogue box where I should write my Twitter username and password doesn’t appear. This is the error:
    Could not authenticate you./statuses/friends_timeline/santiroch.xml?since_id=147442752152535000
    I have tried with chrome, firefox, internet explorer but it doesn’t work. Please, could you help me with this? Thanks!

  • http://twitter.com/allauremkt Laura Gaulke

    Wow!! Way cool! Thanks for sharing. You rock. I am amazed.

  • pedsync

    Thanks for sharing this info. way useful..

  • pedsync

    Hi, this used to work extremely well for me for so many months.. But i could not use it over the last 2 weeks. it gives 404 error.. Did Twitter plugged this out or something? Any idea guys?

  • http://www.kasperkamperman.com/ Kasper Kamperman

    New url is: http://api.twitter.com/1/statuses/user_timeline/userid.xml?count=100

  • Wa2nderma1

    It work.. like this.. thanks

  • http://twitter.com/MoonyaAbd F. Mounia Abdaoui

    even this,, it does nt work with me.. could u please help :)

  • Chris

    Not working for me – is this still available?

Creative Commons License
Unless otherwise specified, all content on this blog is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.

This site is using the Junco Child-Theme, v2.0.2, on top of
the Parent-Theme Desk Mess Mirrored, v2.0.4, from BuyNowShop.com.