'%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 Tables into Excel Without Losing Blank Rows
Question details
The user wants to import a single-column PDF table into Excel while preserving empty rows to prevent subsequent data from shifting upward.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing table data from a PDF document into an Excel spreadsheet without altering the original row structure.
- Observed behavior
- Standard PDF import methods automatically remove empty rows, causing the remaining data to shift upward and lose its original alignment.
Ensure you are using a version of Excel that supports Power Query (Excel 2016 or newer) and have the source PDF document saved locally on your computer.
Use Power Query to Import and Transform PDF Data
Power Query gives you precise control over how PDF data is parsed, allowing you to intercept and preserve blank rows before they are dropped by Excel.
When Excel imports data directly, it tries to clean up the dataset by discarding empty rows. By routing the import through Power Query, you can replace 'null' values with a placeholder, securing the structure of your single-column table.
Open Excel, navigate to the 'Data' tab on the ribbon, click 'Get Data', select 'From File', and then choose 'From PDF'. Locate your file and click 'Import'.
In the Navigator window, select the specific table or page that contains your data. Instead of clicking 'Load', click 'Transform Data' to open the Power Query Editor.
In the Power Query Editor, locate the column where blank rows are missing. Blank rows typically appear as 'null'. Right-click the column header and select 'Replace Values'.
In the 'Value To Find' box, type 'null' (without quotes). In the 'Replace With' box, type a space or a distinct placeholder like '[BLANK]'. Click 'OK'.
Once the transformations are applied and the empty rows are secured, go to the 'Home' tab in Power Query and click 'Close & Load' to push the properly formatted data into your spreadsheet.
Easily Convert PDFs to Excel with WPS Office
If manual Power Query transformations are too complex, WPS Office offers a dedicated PDF-to-Excel conversion tool. It intelligently parses document layouts to retain original formatting, including blank rows, without needing advanced data query skills.
- 1. Open the PDF to Excel Tool: Launch WPS Office, navigate to the 'Tools' tab, and click on 'PDF to Excel'.
- 2. Import Your PDF: Click 'Add Files' in the conversion dialog box and select the PDF document containing your tables.
- 3. Customize and Convert: Select your desired output location and format (.xlsx), then click 'Convert'. The file will automatically open in WPS Spreadsheets with your blank rows perfectly preserved.
Frequently Asked Questions
Why does Excel automatically remove blank rows when importing PDFs?
Excel's standard data import tools are designed to clean and consolidate datasets. They often treat completely empty rows as useless null data and drop them to tidy up the table, which unfortunately causes single-column data to shift upward.
Can I restore the deleted blank rows after the data is already loaded into Excel?
No, if the data was imported and the blank rows were dropped during the load process, you cannot automatically restore them. You must either re-import the data using Power Query or manually insert new rows.
Is Power Query available in older versions of Excel?
Power Query is built-in natively under the 'Data' tab (Get & Transform) for Excel 2016 and newer versions. For Excel 2010 and 2013, it was available as a separate free add-in download from Microsoft.
How can I prevent PDF columns from merging during import?
If columns merge unexpectedly during import, use the Power Query Editor. Select the merged column, use the 'Split Column' feature under the 'Home' tab, and specify the delimiter (like a space or comma) used in your PDF data.
