Using mail merge to make images in PowerPoint from an Excel file
I am trying to finish my dissertation. As part of this research I took on an ambitious little side project. I am using the opensource police shooting database from the Washington Post. This dataset includes all the fatal police shootings in the US from 2015–2019.
Unfortunately, this data does not include the outcomes faced by the police officers who did the shooting. We do not know if these officers faced any legal or civil consequences from these actions. Further, we do not know if the inclusion of video, either from the officer’s body or dash cam, or from any other source (such as cell phone video) altered these consequences. So, my goal here is to find out the consequences from each case (4800+) and do some basic analysis to determine if there are any patterns in the data.
The issue is that hiring someone to do this research is expensive, and slow. So far I have attempted to use M-Turk (which failed, because of graft), undergrad coders (who are really slow), and two contractors from the Philippines I hired from Upwork.com. I am now attempting a new method, using crowd sourced science.
I found this amazing website called Zooniverse.org. This site is the world’s largest and most popular platform for people-powered research. With the help of more than a million volunteers around the world this site enables research that would not be possible, or practical, otherwise. Mostly, they have people classify images, such as images of blackholes. I am attempting to use this site to get people to classify the outcome of an event.
But, Zooniverse is designed to present individual cases (they call them subjects) as images, and the data I have is in text form (in an excel file). So I need to convert this data into images that I can then serve to volunteers.
I need to change this:
Into this:
Thanks to the internet and George Mount’s excellent youtube video I was able to do just that:
Below is a step-by-step walkthrough
Step 1. Save the excel sheet as a .xlsx file
Step 2. Open a Word file. We will use this as a bridge between PowerPoint and Excel.
Step 3. In Word, go to Mailings, and choose Select Recipients. And use “Existing List”.
Step 4. Choose the excel sheet you saved in step 1.
Step 5. Make sure you select “The first row of data contains header”.
Step 6. Select “Insert Merge Field”
Step 7. Now choose the information you want to be displayed in your slides. For this data I needed the name, age, gender, race, date and city, state of each shooting casualty.
Step 8. Next, label each piece of data that will be displayed.
Step 9. Preview Results to check for errors.
Step 10. Now we need to change the heading type of each piece of information. Go to Home and then select different headings for each piece of information.
Step 11. Now complete the mail merge and save the new document. For this particular data set this word document will be pretty large (about 4800 pages). If you’re using a mac you may need to save this file as a .rtf.
Step 12. Now open PowerPoint.
Step 13. Insert New Slide from Outline.
Step 14. And select the Word (or .rtf) file you saved in Step 11.
Step 15. You will now have up to 500 slides with the information you wanted.
Step 16. If you have more than 500 data points, you will be to do some additional work. Most likely you saw the following error message:
Step 17. Go to the last slide to determine where your merge stopped.
Step 18. Go to your word (or .rtf file) and search for this record.
Step 19. Now erase all the data above this point. And save the file under a new name (I label A-Z). Note. This can take a very long time if you’re simply scrolling up. This video shows a faster way to delete pages.
Step 20. Repeat steps 13–19 until you no longer get the error message about your outline size.
Step 21. Save the power point.
Step 22. Export the slides as .jpegs.
And that’s it.
In another blog post I will walk through how to set up a Zooniverse.org project and collect data for this type of study.