PDFs are the never-ending bane of a data journalist’s existence. Unfortunately, public bodies really love sending data as PDFs.
As this is the case, you will often need to get the data out of the PDF and into a more usable format, such as a spreadsheet.
You could:
- Ask for the data not in a spreadsheet
- Copy and paste it into something more usable
- Copy and paste it and clean it up
- Use a PDF to Excel converter
- Use a converter that includes optical character recognition (OCR
- Just transcribe the data
Want more? Buy Getting Started with Data Journalism, a beginners and beyond guide to finding, cleaning analysing and visualising data in any size newsroom.
**Option 1**
Ask whether it is possible for those responsible for the data to send you a more usable version of the information. They may be able to send you the original spreadsheet or text document the information came in.
This is particularly relevant for FOI requests, where responses are often sent out as PDFs – very unhelpful when you’ve asked for lots of data that’s usually held in tables.
In the UK, Section 11 of the Freedom of Information Act allows requesters to specify what format that would like the response in, meaning you can ask for it as spreadsheet or CSV file. The public body “shall so far as reasonably practicable give effect to that preference”.
You must include this preference when you make the request. The public body doesn’t have to send you the information in another format if you ask only after it has sent a response (although some may still be willing to help).
If you’ve requested a particular format and the public body doesn’t send you that, it needs to explain why it wasn’t practical to send it like that.
If you don’t get the format you want, and there’s no or very poor reasoning as to why, you can appeal this. A Court of Appeal judgement backs the right of requesters to ask for specific formats.
This court judgement sits alongside amendments to Section 11 of the FOI Act in 2013, which added requirements for public bodies to publish information that is, or forms part of, a dataset in a reusable form, where the requester asks for this.
The requirements in these amendments, plus the court judgement, should help with getting data in a format that is easy to make use of.
**Option 2**
Basic copy and paste sometimes works, allowing you to copy the data as a formatted table into a Microsoft Word or Excel document.
For example, the data from the above FOI response from the Welsh Ambulance Service on ambulance response times copies and pastes into Microsoft Word with only a few minor formatting issues.
**Option 3**
Sometimes copying and pasting the data won’t result in a table being copied, or in some cases the data is only available within graphics in the PDF.
However, if the data is small and/or simple, it may be possible to quickly clean up the rows of text. Once you have copy and pasted it into a word or spreadsheet programme, you can then tidy it up, either by converting the data to tables using delimiters (such as a space), or by dragging columns back to the right place.
**Option 4**
Alternatively, use a converter to turn the PDF into a usable format. There are many free online PDF converters on the internet, of varying effectiveness. One tip is to upload your file to several and use the best conversion you get back.
Online converters can struggle with PDF files where the table data is combined with large quantities of text, and many of the free ones place limits on the number of pages or size of document you can convert.
In order to give an idea of how they work, this is an example of what they returned when a simple PDF file was uploaded to a number of different file converters.
The original PDF of ambulance data can be downloaded from here
A quick search should give you numerous options for converting your PDF. These are some of the available PDF to XLS converters (many also offer options to convert to other file types or to merge/split PDF files):
- Small PDF – max two free conversions per day
- I love PDF – limited document processing
- Adobe – has a limit on the number of files that can be converted in a certain amount of time without subscribing
- PDF 2 Go – limited file size and limited files per conversion
- PDF Online – no limits on file size or the number of uploads
- PDF to Excel – no limits on file size or the number of uploads
- Invest in Tech – appears to have a file size limit
- Zamzar – max 50Mb file size
- Nitro – requires email
- Soda PDF – has a file size limit
- Online 2 PDF – max 100Mb file, max 100 pages converted at a time
- PDF Tables – Max 25 pages converted
- Easy PDF – unlimited conversions
- PDF Burger – limited number of conversions per day
- PDF Bear
As you can see, a number of these free converters have limits on how you can use them, which may make them unsuitable for some tasks.
To use them, first upload your PDF file, following the instructions on screen. Many give you the option of uploading a file from your computer or via a URL, Google Drive or Dropbox.
With most, the spreadsheet file is available to download in less than a minute. Nitro’s email requirement means you have to go and get the file from your emails rather than instantly download it.
Different converters output the information in different ways. Which is most useful will likely depend on what you plan to do with the data.
Small PDF, I love PDF, Soda PDF, and PDF Bear puts each table in the PDF document (or what the software thinks is each table) in a different spreadsheet sheet. Zamzar and Nitro both put each page of the PDF on a separate sheet (Nitro also opened with a warning about unreadable content in the file).
Adobe, PDF 2 Go, PDF to Excel, Invest in Tech, Online 2 PDF, and Easy PDF all output the contents of the PDF in one sheet. Both PDF Online and PDF Burger give you the option of choosing between each table in individual sheets or having them all in one sheet.
PDF Tables only converts the first 25 pages of the document (not helpful as the PDF has 31 pages).
The results from some of these converters are the same as each other as they use the same underlying conversion software for their site.
Which type of conversion works better will depend on the PDF you are converting and what you are planning to do with the results.
Whichever conversion site you use, the data is likely to be messy when you get it back. You may get blank rows between sections, spaces between columns may not be read, so two separate columns may be combined as one or individual columns from the original PDF may be split across several merged columns in the spreadsheet. If this is the case, it may mean piecing tables back together, unmerging sections and deleting blank rows and columns.
**Option 5**
What if you are sent a PDF where the information is a scan of a document?
This happens frustratingly often – if the only version of the information that can be tracked down by FOI officers is a printout, or if there has been some redaction done to protect personal data and the easiest way to do it was by physically covering up and then rescanning the documents.
Getting data out of these types of files is hard – you cannot copy and paste or not all online converters work.
What some have, which you can try, is optical character recognition (OCR) – this converts images of text into actual text, which you can then edit or analyse in other programmes.
An example file to practice on
Among the converters, PDF 2 Go, PDF to Excel, Invest in Tech, Zamzar, and Online 2 PDF all include or have free options to use OCR.
PDF 2 Online has a limit of 30 pages – although it gives you the option of selecting which pages in the PDF you want to convert. Small PDF, I love PDF, and PDF Bear offer OCR as a premium paid for option.
What to do if none of these work?
Sometimes transcribing is an option – a very slow one, but it is possible.
Sometimes it may be the only way – court data in England and Wales is very difficult to get in a useable form – the data team at the Guardian had to extract the data on riot related court cases by hand when it was carrying out its Reading the Riots project in 2011 .
There is also the possibility of using code to extract data from PDFs.
Investigative journalism website ProPublica had to use a number of different techniques to extract data from PDFs as part of its Dollars for Docs investigation, which tracks financial ties between doctors and medical companies.
The techniques they used are outlined on their website:
Information correct as of March 2022
Want more? Buy Getting Started with Data Journalism, a beginners and beyond guide to finding, cleaning analysing and visualising data in any size newsroom.