# Extracting a PDF table **URL:** \ **Category:** Factual Questions\ **Created:** [February 22, 2019, 2:50pm UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016 "2019-02-22T14:50:43Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Folacin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/folacin/32/3195_2.png) [@Folacin](https://boards.straightdope.com/u/Folacin)\ **Post date:** [February 22, 2019, 2:50pm UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/1 "2019-02-22T14:50:43Z")
I have a PDF file that has a table in it something like this: ```auto name|value1|value2|value3|value4 a 1 1 1 b 1 1 1 1 c 1 1 d 1 1 1 ``` I want to get it into an Excel file so I can do some analysis on it (the original data is not accessible to me). COpy/pasting to Excel ended up with every field on a separate line, and missing cells not generating a line The Google told me to select and copy the table from the PDF and paste it as a table into Word, and then move that table to Excel. This also ended up with the empty cells in the original PDF table not generating cells in either Word or Excel. In other words, the copied table looks like this: ```auto name|value1|value2|value3|value4 a 1 1 1 b 1 1 1 1 c 1 1 d 1 1 1 ``` Since there is no way from the data to assume where the empty cells are, I can’t figure a way to create them with a script. Any ideas? 380 rows or so, so a lot to do manually. Ideally, there would be some way to copy the null cells (or insert something into the table but the PDF is not editable), but I can’t find anything. ---
**Author:** ![GaryM](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/garym/32/241_2.png) [@GaryM](https://boards.straightdope.com/u/GaryM)\ **Post date:** [February 22, 2019, 3:23pm UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/2 "2019-02-22T15:23:19Z")
Not in front of my PC right now, but have you tried Import as an Excell command? ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [February 22, 2019, 3:30pm UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/3 "2019-02-22T15:30:12Z")
Going directly to Excel in general won’t work. Instead of copy/paste into Word, try opening the PDF from Word. I’ve had decent luck getting it to convert into a table that way, then you can copy/paste from Word into Excel. ---
**Author:** ![Folacin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/folacin/32/3195_2.png) [@Folacin](https://boards.straightdope.com/u/Folacin)\ **Post date:** [February 22, 2019, 3:35pm UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/4 "2019-02-22T15:35:34Z")
I hadn’t seen that option - but it looks like that only works for txt/csv files - at any rate it didn’t recognize the PDF. Using the word ‘import’, Google did find an Adobe tool to do the work that has a free trial: [https://acrobat.adobe.com/us/en/acrobat/how-to/pdf-to-excel-xlsx-converter.html](https://acrobat.adobe.com/us/en/acrobat/how-to/pdf-to-excel-xlsx-converter.html) I may try that when I get home tonight. Reply to Gary, guess I should have quoted! ---
**Author:** ![Folacin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/folacin/32/3195_2.png) [@Folacin](https://boards.straightdope.com/u/Folacin)\ **Post date:** [February 22, 2019, 3:38pm UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/5 "2019-02-22T15:38:40Z")
> [@TroutMan](#): > > Going directly to Excel in general won’t work. Instead of copy/paste into Word, try opening the PDF from Word. I’ve had decent luck getting it to convert into a table that way, then you can copy/paste from Word into Excel. Well, don’t I feel silly - that worked and is somewhat obvious now that it has been pointed out. The Word table has all the columns, I can go from there. Thanks! ---
**Author:** ![bordelond](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bordelond/32/150_2.png) [@bordelond](https://boards.straightdope.com/u/bordelond)\ **Post date:** [February 22, 2019, 3:43pm UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/6 "2019-02-22T15:43:36Z")
In Adobe Acrobat (at least as early as 2010’s Version 9), you can text-select all the items in the PDF table and then right-click within the selection and choose **Open Table as Spreadsheet**. There are also options to **Copy as Table** and to **Save as Table** , the latter of which converts the items you selected into a Comma-Separated Values (CSV) file which can be read natively in Excel and other spreadsheet programs. All of that presumes that the PDF was created by exporting or saving from the original program into a PDF. Scanned-file PDFs will render the table “un-copyable”. ---
**Author:** ![GaryM](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/garym/32/241_2.png) [@GaryM](https://boards.straightdope.com/u/GaryM)\ **Post date:** [February 22, 2019, 3:46pm UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/7 "2019-02-22T15:46:04Z")
OK, I tried using Excell’s Import feature and all I got was the PDF formatting data with lot’s of special characters. So that’s didn’t work, at least with my file that was mostly text anyway. But as I own the Adobe Acrobat program I tried saving the file for inside Acrobat as an Excell file at that worked perfectly. However it did not carry over any formulas, but they didn’t exist in the PDF so I guess I’m not surprised. I’m willing to do the conversion for you if you’re comfortable with that. ---
**Author:** ![Folacin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/folacin/32/3195_2.png) [@Folacin](https://boards.straightdope.com/u/Folacin)\ **Post date:** [February 22, 2019, 4:12pm UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/8 "2019-02-22T16:12:58Z")
> [@GaryM](#): > > OK, I tried using Excell’s Import feature and all I got was the PDF formatting data with lot’s of special characters. So that’s didn’t work, at least with my file that was mostly text anyway. > > But as I own the Adobe Acrobat program I tried saving the file for inside Acrobat as an Excell file at that worked perfectly. However it did not carry over any formulas, but they didn’t exist in the PDF so I guess I’m not surprised. > > I’m willing to do the conversion for you if you’re comfortable with that. Thanks for the offer. As noted above, Troutman’s solution is working well enough, so I’m good. ---
**Author:** ![Tim\_T-Bonham.net](https://avatars.discourse-cdn.com/v4/letter/t/46a35a/32.png) [@Tim\_T-Bonham.net](https://boards.straightdope.com/u/Tim_T-Bonham.net)\ **Post date:** [February 23, 2019, 1:30am UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/9 "2019-02-23T01:30:48Z")
A last resort (but one that I had to do once): 1. Print the pdf. 2. Use a pencil to make a mark (period or zero) in each of the empty cells. 3. Scan it back in again, either to Word or as a PDF, but now things should be kept in the proper columns. Can be a lot of work, so only an absolute last resort. But if the table contains a lot of complicated numbers (where re-keying would create a lot of errors), you might need to do this. ---
**Author:** ![septimus](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/septimus/32/410_2.png) [@septimus](https://boards.straightdope.com/u/septimus)\ **Post date:** [February 23, 2019, 3:52am UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/10 "2019-02-23T03:52:53Z")
Among last-resort solutions, let me offer mine: Run the data copied from the pdf through a filter program like ```auto sed "ss s . sg" ``` where the number of spaces is set appropriately. _Sed_ is a rather simple program which was available _free-of-charge_ long ago, before Microsoft Corp. and Adobe Inc. even existed. _Sed_ and its siblings are very powerful: _too_ powerful, I’m afraid, for the Windo$e environment; they’d obviate the need for various $19.95 shrink-wrapped solutions. ---
**Author:** ![Dancer\_Flight](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dancer_flight/32/7601_2.png) [@Dancer\_Flight](https://boards.straightdope.com/u/Dancer_Flight)\ **Post date:** [February 23, 2019, 6:25pm UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/11 "2019-02-23T18:25:26Z")
I’ve had good luck extracting tabular data from PDFs using [tabula](https://tabula.technology/). I don’t recall if it handles empty cells particularly well. The interface (runs in a web browser) is a little clunky and it isn’t the fastest thing in the world, but it did well extracting a unified table from a multipage PDF for me in the past. Bonus: free to use and runs on linux, windows, and mac. Output is in CSV format. ---
**Author:** ![yabob](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/yabob/32/2821_2.png) [@yabob](https://boards.straightdope.com/u/yabob)\ **Post date:** [February 23, 2019, 7:05pm UTC](https://boards.straightdope.com/t/extracting-a-pdf-table/830016/12 "2019-02-23T19:05:53Z")
> [@Dancer\_Flight](#): > > I’ve had good luck extracting tabular data from PDFs using [tabula](https://tabula.technology/). I don’t recall if it handles empty cells particularly well. The interface (runs in a web browser) is a little clunky and it isn’t the fastest thing in the world, but it did well extracting a unified table from a multipage PDF for me in the past. Bonus: free to use and runs on linux, windows, and mac. Output is in CSV format. Seconded. I’ve been using this, and it works OK. The documents I’ve been extracting with it had empty cells and it managed.