Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Upon requesting some daily/hourly sales data from a coworker who is responsible for such requests, I was given a series of PDF files. The point of sale program that is used, for some reason, answers requests for this type of information in the form of PDF files.

The issue:

The PDF files look to be in a format that should easily be copy and pasted into a spreadsheet. There are three columns that look to be neatly organized across two pages. When copy/pasting the first page, all three columns from the PDF's first page are dumped into a single column consisting of the Date followed by the Hours for the transactions on that day. The end of this Date/Time information is followed by all of the Total Sales values that should be attached a Date and Time of the transaction. (NOTE: There are no duplicated Dates in the Date column, ie, Multiple transactions for a day only have one yyyy/mm/dd listed for the first row but not the following rows.)

While it was a huge pain, it was possible to, in about four or five steps, get the single column of data broken out into three columns that matched the PDF.

The second page of the PDF file, when attempting to copy/paste into a spreadsheet, creates a single column with the first third of the cells being the Dates from the PDF, the second third of the cells being the Hours of the transactions and the final third of the cells being filled with the Total Sales.

After the copy/paste there is no way to figure out which Hours belong to which Dates or Total Sales due to the lack of the duplicated Dates in the Date column as mentioned above.

My PDF-fu is next to non-existent. I've just now started to work with PDF editors and some websites, so far, with absolutely nothing remotely coming anywhere near usable output. (Both methods have so far done nothing but product blank documents.)

Before I go back and pester my co-worker into figuring out a way to create a report in some other format than PDF, is there any method by which to take the data that looks to be formatted correctly in a PDF and copy/paste it into a spreadsheet that will look the same?

I appreciate any help that can be made available. The sales data isn't so sensitive that I couldn't part with a bit to let somebody actually see what it is that needs to be dealt with, just let me know. The PDF's are less than 100kb each so sending them shouldn't be a burden to any interested party.

share|improve this question
It's a problem inherent in the PDF format that it loses document structure and is difficult to turn back into something with the original (or a sensible) logical order. There's a profile called "Tagged PDF" which retains information about the logical structure; this is also found in PDF/A files (except PDF/A-1b, which is minimalist). You might check if the people creating the PDFs have the option to save it as Tagged PDF or PDF/A. That's likely to convert in a more sensible way. – gmcgath Dec 22 '12 at 18:39
Okay, so I haven't tried figuring out what options for Ubuntu are available regarding Optical Character Recognition (OCR) software and I will need to check with the other keyboard jockeys about creating reports as "Tagged PDF". But I did stumble on pdftotext that, at the very least, could create a text document with the same format. Getting the text document into a spreadsheet still resulted in some fuglyness but at least the row integrity of the data was intact. Thanks everybody! – Chuck Jan 12 '13 at 17:35

I have acrobat pro, but if you have X or XI this should also work, not sure about Reader. Select the data that is in column form you want to capture. Right-click and choose "Copy as Table." You can then paste it as a table in Word or in Excel.

share|improve this answer

I would use some OCR software to process the PDF files and extract all data, Abby Fine Reader can read and process pdf files directly.

share|improve this answer
up vote 1 down vote accepted

My current solution to my own problem was to use pdftotext.

I then used Ubuntu's terminal to navigate to the folder with the PDF's. Syntax was :

pdftotext fullnameofpdffiletoconverttotext.pdf *nameofcreatedtextfile* -layout

The without the -layout added the text output was no different than my attempts to directly copy and paste from the PDF. (Which was a completely unusable mess). Adding the -layout argument at the command line gave me a text file that mirrored the PDF.

Copy/pasting from the newly created text file into a spreadsheet and making sure the check box for "Space" is checked on the "Import Text" or "Paste Special" menu kept the integrity of the rows but added lots of columns and at some intervals offset the columns in a row by one or two extra columns. This was cleaned up by deleting the extra empty columns.

On further investigation, it was found that the -raw argument at the end of the command line instead of -layout worked much better. Syntax while in the PDF containing folder:

pdftotext fullnameofpdffiletoconverttotext.pdf *nameofcreatedtextfile* -raw

Pasting from the new text file into the spreadsheet by selecting the "Space" check box in the "Separator Options" resulted in a nice neat four columns where the only problem was due to my source data not duplicating Dates for every row where a transaction occurred on the same day.


NOTE: There was only text in my source PDF's. No graphics.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.