Sunday, November 17, 2013

How to Export Your Outlook Inbox to CSV for Data Analysis

So one of my colleagues at work showed me this cool script he wrote in Visual Basic to pull all the data from Outlook for analysis.

Cool, I thought - I'd like to do that, but don't want to muck about in VB.

Well, I was surprised to discover that Outlook has the ability to export email to CSV built in! Follow the simple steps below (here demonstrated in Outlook 2010) and you can analyze your emails yourself and do some cool quantified self type analysis

How to Export Outlook Email to CSV (from Outlook)

1. Open Outlook and click File then Options to bring up the options dialog:


2. Selected Advanced, then click the Export button:


3. Click Export to a file and then the next button:


4. Selected Comma Separated Values (Windows) and click next.


5. Unless you want to export a different folder, select Inbox and click next.


6. Browse to a folder and/or type a filename for your export.


7.  Choose Map Custom Fields... if you want to customize which fields to export. Otherwise click the Finish button.


8. Sit tight while Outlook does its thing.



You should now have a CSV file of your inbox data!

How to Export Outlook Email to CSV (from Access)

This is all very well and good, but unfortunately exporting to CSV from Outlook does not provide the option for date and time as fields to be included, which makes it useless if you'd like to do time series (or other temporal) analysis.

To get the date and time data you can pull data from Outlook into Access and then export it as noted in this metafilter thread.

Import from Outlook into Access
1. Fire up Access and create a new database. Select External Data, More.. and then Outlook Folder.


2. Select Import the source data into a new table in the current database and click OK


3. Select the email account and folder you'd like to import and click Next 


4. Change the field settings if you'd like. Otherwise accept the defaults by clicking Next


5. Let Access add the primary key or not (you don't need it). Click Next 


6. Click Finish and wait. When the process is done you should have a new table called 'Inbox'.



Export Data from Access to a CSV
1. Make sure the Inbox table is selected and click External Data then Text File.


2. Pick or type a filename and click OK


3. Selected Delimited and click Next


4. Select Comma as the delimiter and tick the box which says Include Field Names on First Row. Click next.


5. Pick or type a filename and click Finish


You should now have your Inbox data exported as CSV (including time / date data!) and ready for analysis. Of course you can repeat this process and append to the Access database folder by folder to analyze all the mail you have in Outlook.

9 comments:

  1. In access, after selecting external data, Outlook Folder, Import the source data into a new table in the current database, and pressing OK, I get the following error message: Window title: Import Exchange/Outlook Wizard. Error message: Object or class does not support the set of events

    ReplyDelete
    Replies
    1. I too get this error. Am using Outlook 2007 and Access 2007. Suggestions?

      Delete
  2. is there a way to run this in batch for every user's inbox and run it in the background?

    ReplyDelete
  3. When importing the data from outlook the categories aren't showing. Is this possible?

    ReplyDelete
  4. CSV export works great. Thanks!. Found you thru Google. I'll check out your other posts.

    ReplyDelete
  5. Will this also include email data from within the email signatures?

    ReplyDelete
  6. Hello guys, I have a problem with Outlook 2010.

    I need to export all of data from emails (addresses, sender's name, etc) from my Inbox folder, here is the problem, inside Inbox folder I have a TON OF SUBFOLDERS and Export option is not working, this option just gives me email that are directly in Inbox folder.

    How can I export all emails, including form subfolders, to a CVS file?

    Thanks

    ReplyDelete
    Replies
    1. Yeah, I think you're going to probably have to write some VB.
      http://www.dreamincode.net/forums/topic/208946-export-outlook-folder-content-to-csv-or-database-with-vba/

      Delete
  7. To whoever created this article, many thanks for your help! I really appreciate it! :)

    ReplyDelete