How to extract data out of a PDF

This article explains three tools for extracting data tables from PDFs: The open-source tool Tabula and the commercial tools smallpdf and cometdocs. 

The Problem

Often, our data doesn't come in a neat Excel sheet or CSV file, but is buried as a table in a PDF, like in this report by the United Nations: 

If we then try to copy and paste the numbers into a spreadsheet, the columns and/or rows won't translate: 

There are many tools out there that try to solve this problem. Every PDF table is a bit different (some are over-designed, some use weird text formats), so if one solution doesn't work for your specific PDF, you can try another one. The first tool we'll show you for extracting data tables from PDFs is Tabula:

Solution 1: Tabula

Tabula is a small open-source software that you can download on Windows or Mac. Once you've installed it and clicked on the tool icon, it will open in your web browser (e.g. Firefox or Chrome). But don't worry: All your data will be processed on your computer. That makes Tabula great for sensitive data. 

That's what you will see when you open Tabula:

Click on "Browse" and then "Import" to open the PDF with the data table you want to extract. 

After importing your PDF, you can now tell Tabula where the table(s) are on your page(s). To do so, click and drag to select the table, then click on "Preview & Export Extracted Data" to see how Tabula has interpreted your selection: 

Have a close look at this preview of your data. Sometimes some characters of text are missing, or only half of your numbers are right. If your data doesn't look as intended, you have two options: 

1. In the sidebar, toggle between "Stream" and "Lattice." Stream looks for whitespace between columns, while Lattice looks for boundary lines between columns. Choosing Lattice instead of Stream or the other way around can make a huge difference. 

2. Revise and experiment with your selection. Often, it works well to draw the selection box very close to the data, even inside the table. It doesn't work as well to select the table with some white space around it. If your preview doesn't get better, try selecting just a subset of your data table.

Once your data looks good, you can export it as a CSV, TSV or JSON, or just copy and paste the table into your spreadsheet. It's very likely that you still need to clean up the data a bit. Here, we still need remove the spaces in the numbers. After doing so, we can copy and paste the result in Datawrapper.

Solution 2: smallpdf

If you can't or don't want to install software on your computer and your documents are not sensitive,  Smallpdf can be a good solution. It's a PDF converting software that you can use for free online (twice an hour). You can also download it as a free trial. 

To use the online version, go to  https://smallpdf.com/pdf-to-excel and drag your PDF file in the big green box:

Wait for a second, and your file will be ready for download. Since smallpdf did not ask us to make a selection, like Tabula did, every page that is uploaded will be converted. 

We get an Excel file with two tabs in it: In the first one is the text that was next to the table in the original PDF; in the second one is the table in its original design.  Smallpdf did a better job at converting the numbers than Tabula. However, we can see that it messed up the multiple-line headers a bit:

Solution 3: Cometdocs

If you want to convert more than two PDFs online at once, and you don't have a problem with signing up for a service, cometdocs can be a good alternative. It lets you convert five PDFs per week. When you go to their website (cometdocs.com), you will see an "Upload" button. Click on it to choose your PDF:

Once your PDF is uploaded, it will appear in the white box. Below it, you'll see four tabs. Click on "convert" and drag your PDF there. Then choose your desired output format (Excel, in our case). To start converting your PDF, you now need to sign up:

After a minute or two, you'll receive an email with a link to the converted file. Our Excel file will have all the information on one sheet:

Like smallpdf, cometdocs converts the whole page(s) to a single Excel file. We will need to do some cleanup here and delete some rows and columns. 

Other tools

There are many more tools that exist out there to extract data from PDFs. So if none of these options works for you, it's worth looking around a bit — for example, fans of the command line might be interested in tools like pdftk. If you find a better (free and/or open-source) tool than the ones we explained here, do let us know!