Advanced Data Tutorial: Converting PDFs To Spreadsheets

As we’ve all discovered, many government agencies prefer releasing records in portable document format, or PDF. Sometimes that’s helpful, for example with narrative text files. But not so much for data.

This tutorial will show you one free way to convert PDFs with tabular data into spreadsheets. The data I’ll use comes from a PDF I converted recently: The number of sworn police officers for the top 50 municipalities in the United States. Here’s what the file looks like:

You can’t copy/paste this text into a spreadsheet, unfortunately, and you don’t want to waste time or risk a correction by typing the data manually. So let’s convert it.

To start, we’ll use a free app called XPDF, which you can download here (tar.gz file). There’s a helpful tutorial for installing the app on your Mac here).

Once you have the app installed, you can convert PDFs. We’ll start with our cops data, which we want to get into a two-column spreadsheet. So move the file into your XPDF folder, and then cd into it with the Terminal. Here I move from mstiles to desktop to xpdf with cd command:

Use the ls command to list the files inside your XPDF folder. See localcops.pdf is there:

Now we run this command to convert the PDF. Here we’re calling the PDFtoText app and performing the layout function on the file:

Once done, you should get a text file in your XPDF folder that looks like this:

Delete the non-tabular text (lines 1-3) and then fire up Microsoft Excel. Once you open a blank spreadsheet, go to File > Import… to pull in the text file.

After selecting the file, you’ll see an import wizard. Here we don’t have a delimiter, like a comma or tab, to separate the columns, so we have to create one ourselves by selecting “Fixed Width”:

The wizard draws the line based on its assumptions about your data (see below), but you can move it with the mouse. This line will edit your data, so be careful not to cut the name of a long police agency. In this case, “Port Authority of New York & New Jersey Police” is lengthy:

Run through the wizard and you should see this:

As you know, some PDFs are more complicated, so this isn’t always the perfect tool. Sometimes you might have multiple tables in a PDF, so you’ll need to split the document beforehand and run the command on each page. In other cases, you’ll have to delete headers and footers in a text editor. Sometimes agencies will save large spreadsheets into multi-page PDF pages, which requires using this tool a page at a time so the fixed-width columns match up.

Let us know if you hit a snag, and good luck.

Comments are closed.