• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Import Data from a PDF to Excel

You are here: Home / Power Query / Import Data from a PDF to Excel
Import Data from a PDF to Excel
August 13, 2020 by Mynda Treacy

Office 365/Microsoft 365 users can now import data from a PDF to Excel using Power Query*.

It’s super handy for importing data tables inside PDF documents because Excel locates them for you ready to import. And if the data is a little messy you can use the Power Query transform tools to clean it before loading it into the Excel file.

*Only available to Office/Microsoft 365 subscribers, find out how to get the latest updates.

Tip: If you don’t have Office/Microsoft 365 then this same functionality is available in Power BI Desktop.

Watch the Video

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and PDF and follow along. Note: This is a .zip file please ensure your browser doesn't change the file extension on download.

Import Data from a PDF to Excel Steps

Step 1: Get Data from PDF - It’s as easy as going to the Data tab of the ribbon > Get Data > From File > From PDF:

Import Data from a PDF to Excel

Step 2: Locate the PDF File you want to import to Excel - Browse to the location the PDF is saved > click Import:

Import pdf file to Excel

Step 3: Select Tables and Pages in PDF - The Power Query Navigator window opens with a list of pages and tables Excel has identified in the PDF. You can select a table or page to preview in the pane on the right, as shown below with the table on page 7 displayed:

power query pdf navigator

Step 4: Import Data from PDF or Clean - From here you can click the ‘Load’ button to import data directly to the Excel worksheet or to the Data Model. However, you’re most likely to want to do some more cleaning of the data first, in which case click the ‘Transform Data’ button to open the Power Query Editor:

power query editor import pdf to excel

Notice Power Query has correctly identified the column labels and placed them in the header row. If it doesn’t do this automatically, click on the drop down in the top left of the table > Use First Row as Headers:

power query promote headers

Not all tables will import as easily. For example, Table2 in this PDF has column headers split over multiple rows. Watch the video to see how to fix them so they’re in a single row and can form the column labels.

When you’re done with tidying up the data, give the query a name in the Properties pane, then go to the Home tab > Close & Load to finalise importing data from the PDF to Excel.

Import Multiple Tables/Pages from PDF

If you have tables that run over multiple pages, for example credit card or bank statements, Power Query will automatically combine then into a single table.

You can also import consecutive pages with the StartPage and EndPage optional parameters like so:

= Pdf.Tables(File.Contents("C:\Sample.pdf"), [StartPage=3, EndPage=5])

More on the Pdf.Tables function here.

Import Multiple PDFs From Folder

If you have multiple PDFs containing data or tables with the same structure, you can use the Power Query Get Data From Folder connector to get them and import the data into a single table.

Import Data from a PDF to Excel

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power Query
Previous Post:act v plan dashboardPlan v Actual Excel Dashboard
Next Post:Display All Matches from Search in Userform ListBoxDisplay All Matches from Search in Userform ListBox

Reader Interactions

Comments

  1. Newton L Nickel

    September 29, 2022 at 1:08 am

    Import Multiple Tables/Pages from PDF
    Dear Minda: Thanks for your great job. Congratulations for all your team!
    Is there a way to import several reports from many pages (1, 3, 6, 8, 13, 33 or more pages). I’ve tried From Folder and I got success only for one page. For the bigger one, I’ve tried = Pdf.Tables(File.Contents(“C:\Sample.pdf”), [StartPage=1, EndPage=33]). It’s Ok only for a 33 page. I would like to import everything. A page count solution?

    Reply
    • Mynda Treacy

      September 29, 2022 at 10:17 am

      Hi Newton,

      Please see this Q&A on our help desk that deals with importing multiple PDFs from a folder containing multiple sheets with Power Query.

      Mynda

      Reply
  2. Jane

    August 2, 2022 at 11:22 am

    Hi, How do I extract specific value of each workbook from multiple excel workbooks

    Reply
    • Mynda Treacy

      August 2, 2022 at 11:32 am

      You could get all the workbooks using Get data > From Folder, then filter the results until you have the values you want.

      Reply
  3. Donna DD

    September 8, 2021 at 2:37 am

    The “Data from PDF” submenu only appears in certain limited versions of Excel.
    The article never lists them.
    We have all the latest updates installed… and we have NEVER seen that “from PDF” menu.

    Reply
    • Mynda Treacy

      September 8, 2021 at 9:05 am

      Hi Donna,

      Get data from PDF is available if you have a Microsoft 365 license. What 365 license do you have that you’re not seeing it in?

      Mynda

      Reply
      • Dominic

        September 14, 2021 at 8:50 pm

        Hello,
        similar problem – I cannot see this.
        I’m running Excel for Mac version 16.52 on a Microsoft 365 Subscription.
        (In my Microsoft account it says “Microsoft 365 Family”, for what it’s worth)

        Is this a Mac limitation? Or a license limitation, do yo think?

        Thank you!

        Reply
        • Mynda Treacy

          September 14, 2021 at 9:29 pm

          Hi Dominic,

          Mac doesn’t support Power Query yet, sorry. It’s in development, but a long way from being able to get data from a PDF.

          Mynda

          Reply
          • Dominic

            September 18, 2021 at 11:51 pm

            Thanks Mynda.
            🙁

          • Dominic

            September 18, 2021 at 11:54 pm

            Thanks for that info, Mynda. Disappointing, but much better than wasting ages trying to find it!

    • Shan

      March 10, 2022 at 12:23 pm

      I’m using Excel 365 ProPlus, and that option to get data from pdf is not available. All updates installed. Not sure why though.

      Reply
      • Mynda Treacy

        March 10, 2022 at 4:50 pm

        There are different update channels, some slower than others. You might be on a semi-annual channel that hasn’t got the PDF connector yet and because it only updates twice per year, it appears to be up to date. You can find out about the different channels here.

        Reply
  4. Rohit

    June 8, 2021 at 9:02 pm

    Good post.it was very helpful.
    I have searched many site to convert online.

    Reply
    • Mynda Treacy

      June 8, 2021 at 9:10 pm

      Glad it was helpful, Rohit!

      Reply
  5. Johan van Rensburg

    May 13, 2021 at 9:32 pm

    I import a off file into excell but can’t do calculations. Am I missing a step/steps to properly convert it to an excel file. If all works do I save it as an excel file?
    Please help

    Reply
    • Mynda Treacy

      May 13, 2021 at 10:15 pm

      Not sure what you mean, Johan. Please post your question and sample Excel file on our forum where we can help you further: https://www.myonlinetraininghub.com/excel-forum

      Reply
  6. Dinesh

    March 30, 2021 at 7:33 am

    same proble for me, i already updated before using this option. please make a video about this, thank you

    Reply
    • Mynda Treacy

      March 30, 2021 at 8:42 am

      Maybe you need to update your .Net framework as per this page which clearly says PDF connector is available for 365 users.

      Reply
  7. Dinesh

    March 30, 2021 at 7:27 am

    i want to import pdf tables to excel and i watched your video but i didt see pdf option under files options, and im using 365 subscribtions. so how to enable pdf option. thank you

    Reply
  8. Gerry Xie

    March 12, 2021 at 2:25 am

    my Excel version is 365 and we are active/paid subscribe, but I can’t find “from PDF” choice. what’s the problem?

    Reply
    • Mynda Treacy

      March 12, 2021 at 10:01 am

      Hi Gerry, I suspect you need to update Excel to get the From PDF functionality. You can do this via the File tab > Account.

      Reply
  9. Dipen Barua

    December 20, 2020 at 2:12 pm

    Nice tutorial. But I am facing a problem here. I don’t have a get data option in my toolbox. I don’t know what is the problem. Can you please tell me what is the problem? Thanks in advance.

    Reply
    • Catalin Bombea

      December 20, 2020 at 3:32 pm

      Hi,
      What excel version you have? As mentioned at the beginning of the article, it’s available for Office/Microsoft 365 versions, there is also a link with update details.
      Cheers,
      Catalin

      Reply
  10. Johan

    August 20, 2020 at 1:16 am

    Well done

    Reply
  11. Steve Olson

    August 19, 2020 at 11:58 pm

    Good post. Also showing how to transform headers was very helpful.

    Reply
    • Mynda Treacy

      August 20, 2020 at 8:36 am

      Glad you found it helpful, Steve!

      Reply
  12. Moise Garrett, MBA

    August 13, 2020 at 11:00 am

    awesome….love your website

    Reply
    • Mynda Treacy

      August 13, 2020 at 5:33 pm

      Thanks so much, Moise!

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.