3

My organization is publishing data tables, with these two design requirements:

  • Must be a PDF to preserve our brand style and formatting, for people who want to print the tables themselves to use as a reference
  • Must be easy to copy and paste the data into Excel (or other spreadsheet software), for people who want to do their own analysis of the data.

The problem is, with every combination of settings for exporting tagged PDFs in InDesign that I've tried, the data pastes into Excel all in one column, losing the structure.

We're aware that problems getting data out of PDFs is a common frustration for people and a common design flaw in many published reports - and we'd love to be part of the solution, not part of the problem. How can we set up a PDF table so that it copies cleanly into spreadsheet software?

I'm sure there must be some option I've overlooked. If not, solutions based on plugins, code or non-InDesign software are also welcome, so long as it doesn't require extra work or software by the user who is downloading the PDF.

(A curious extra detail is, Adobe Acrobat Pro has an option, "Open table as spreadsheet", which works perfectly fine so long as the PDF is exported with tags enabled - but we can't rely on our readers having Acrobat. Since this feature works, there must be enough data about the table structure in the PDF... but for some reason it just isn't being used with these settings)

karel
  • 13,706
user56reinstatemonica8
  • 5,494

4 Answers4

2

Why can't you offer the table as a spreadsheet as a separate download?

Put a link in the original document which reads, "If you would like this data as a spreadsheet for your own analysis, click here to download it." The link will take the user to a webpage with all the spreadsheets. The XLS (or CSV, or whatever file format you want to use) will only need the minimum of formatting, like your company name and contact information, and the PDF's branding will be preserved.

It is possible in Acrobat Pro to export the text as a Word document, which has a table, which can then be pasted into Excel, but I assume you want as few steps as possible. I say you're pursuing the means at the expense of the ends. If you want your users to have a spreadsheet, give them one.

2

Off the cuff, I'd say the simplest solution would be to include the document and the spreadsheet(s) in a PDF portfolio. Acrobat has had that capability since v8, iirc. The only issue you might run into is folks who use something other than Reader to open the PDF, if the 3rd party solution doesn't understand portfolios, but Reader is the only fully reliable way to read a PDF in any case (as a graphic designer, I run into a problem almost every time I send a PDF proof to a client who uses Mac Preview or some other "PDF viewer" that doesn't actually implement the full PDF spec).

Alan Gilbertson
  • 324
0

I just faced this issue myself and resolved it.

Here's another simple solution:

  1. Copy paste from PDF to Microsoft Word.
  2. Copy paste from Microsoft Word to Excel
Iancovici
  • 579
0

In the end I went with PDF attachments as per the mtone's suggestion in a comment on Alan G's answer. Here's how to add an attachment to a PDF (quick summary: in Acrobat X Pro, it's just a case of clicking the paperclip icon on the right, and then adding the file from that window and saving).

Here's the results of my basic testing, comparing the two options:

Attachments

  • In Acrobat and Reader
    • Opens showing the PDF in the normal way, with the 'Attachments' panel to the left of the page open by default, showing the attachment (very small) with a file-type specific icon (also very small). The filename is cut very short, so to be obvious to average users what's going on, a very short self-explanatory file name (e.g. "DATA.xls") is a good idea.
  • In non-Adobe (e.g. Chrome, Mac Preview)
    • Opens the PDF as normal. There's no evidence that there is an attachment nor any obvious way to access it if a user was expecting it. That said, there's also no harm done compared with having no attachment, other than increased file size.

PDF Portfolios

  • In Acrobat and Reader
    • A very different interface is shown. You've got a few choices of (in my opinion rather cheesy) 2006-style "wooshy" interfaces for clicking through the contents of the portfolio, where each item needs to be double-clicked on to be seen in anything other than a preview. They're all pretty distracting and not very intuitive to someone expecting a normal PDF. The main PDF's first page is previewed in the centre. If it's more than one page, an average user might not realise that they can get more pages by double-clicking to open it - people familiar with 'PDF page turner' interfaces might think that the things they see are all the pages they can get.
  • In older versions of Reader / Acrobat (7 and earlier)
    • According to this thread, portfolios are treated as PDFs with attachments, but a "bullish" message is displayed asking the user to upgrade. There's discussion of many cases where this is undesirable and causes wasted time and distraction.
  • In non-Adobe (e.g. Chrome, Mac Preview)
    • A big ugly message telling the user to get Adobe Reader, and no way I can see for the user to just see the original PDF if this isn't an option (e.g. if they're on a controlled network, or, if they don't want to install ~500 MBs (!!) of software just to look at one PDF...).

In my case, attachments are definitely the better way to go - our PDFs are deliberately simple (for home consumption not professional printing) so cross-client compatibility is important and not giving more work or annoyance to our end users is very important. Also, the key 'feature' portfolios have over attachments (a swooshy interface) feels in our case more like an obtrusion than a benefit.

If you go down the portfolio road, check Matthew Oglethorpe's comment in the above linked thread on PDF Portfolio backward compatibility. Apparently, if you replace the Cover page for the portfolio in the "vertical Icon bar (left column, 3 buttons on my set up), click Pages icon(topmost)" under View > Portfolio > Cover page, you can remove the nag screen for people in older versions of Acrobat. Might only work in Acrobat 9, not tested.

Looking at the way that, on a Mac, this ugly nag screen is used as the file icon on the desktop, I think the above method might also work for replacing the nag screen for non-Adobe users with something useful. Again, not tested.

user56reinstatemonica8
  • 5,494