'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Import PDF Data into Excel 2019 Without 'Get Data from PDF'
Question details
The user needs to import data from a PDF file into Excel 2019, but the standard 'Get Data from PDF' option is missing from the interface.
- Product
- Microsoft Excel 2019 Home and Student
- Device & OS
- not provided
- Scenario
- Importing table data from a PDF document into an Excel spreadsheet when the direct import button is unavailable.
- Observed behavior
- The 'Get Data from PDF' option is missing or removed in Excel 2019 Home and Student after an Office update.
Ensure you know the exact file path of your target PDF document and verify that your Office suite is updated to the latest build to support modern Power Query functions.
Use Power Query and a Dummy Text File to Import PDF Data
Since the direct PDF import button is missing, you can trick Power Query by importing an empty text file and manually modifying the source code to read your PDF.
This workaround leverages the underlying Power Query engine in Excel 2019, which still supports PDF extraction even if the user interface button is hidden in the Home and Student edition.
In the same folder as your target PDF (e.g., 'Z:\test.pdf'), right-click and create an empty text file. Give it a recognizable name (e.g., 'test.txt').
Open your Excel workbook, navigate to the 'Data' tab, select 'From File' > 'From Text/CSV', and choose the dummy text file you just created.
Instead of loading the data directly, click on 'Transform Data' to open the Power Query Editor interface.
In the Power Query formula bar at the top, replace the existing code with a PDF extraction expression. For example: =Pdf.Tables(File.Contents("Z:\test.pdf"), [Implementation="1.3"]). Replace 'Z:\test.pdf' with the actual path to your PDF.
Press Enter to apply the formula. Power Query will read the PDF and display available tables. Select the table you want to import, then click 'Close & Load' in the Home tab to bring the data into your worksheet.
Convert PDF to Excel Using a Dedicated Converter
If manipulating Power Query code is too complex, you can easily bypass the issue by converting the PDF directly into an Excel (.xlsx) file.
Seamlessly Convert PDF to Spreadsheet with WPS Office
If you frequently need to pull data from PDFs and find Microsoft Excel's tools restrictive, WPS Office offers a built-in, user-friendly PDF toolkit. You can easily extract tables and convert PDF documents directly into spreadsheets without complex Power Query workarounds.
- 1. Open WPS Office: Launch the WPS Office desktop application and navigate to the 'Tools' tab on the home screen.
- 2. Select PDF to Excel: Click on 'PDF to Excel' from the dedicated PDF toolkit menu.
- 3. Add your PDF file: Drag and drop the target PDF file into the converter window, or click 'Add Files' to browse your computer.
- 4. Convert and edit: Click 'Start' to begin the conversion. Once finished, the file will automatically open as a fully editable spreadsheet.
Frequently Asked Questions
Why did 'Get Data from PDF' disappear in Excel 2019?
The 'Get Data from PDF' feature is not natively supported in the Home and Student edition of Excel 2019 through the standard user interface. It often disappears after specific Office updates that align user interface features with your specific license version.
Can I use the Power Query PDF workaround in older versions of Excel?
This workaround depends entirely on the version of the Power Query engine installed on your device. While it works in updated Excel 2019 builds, older standalone versions like Excel 2016 or 2013 may not support the Pdf.Tables function natively.
Is there a way to simply copy and paste PDF data into Excel?
Yes, you can manually select and copy text from a PDF reader and paste it into Excel. However, formatting is usually lost, and the data may paste into a single column. You will likely need to use the 'Text to Columns' feature under the Data tab to organize the pasted text back into a proper table structure.
