How to Merge Multiple PDF Form Data into Excel
Question details
The user needs a way to combine form data from multiple completed PDF files into a single, organized Excel spreadsheet.
- Product
- Adobe Acrobat / Microsoft Excel
- Device & OS
- not provided
- Scenario
- Exporting filled PDF forms to consolidate data into a database or spreadsheet.
- Observed behavior
- Exporting PDF form data to Excel produces images instead of organized text rows, and lacks a clear option to combine multiple completed forms together.
Gather all the completed PDF forms you wish to merge into a single, easily accessible folder on your computer.
Merge Data Files into Spreadsheet using Acrobat
Use Adobe Acrobat's built-in form-editing mode to extract data from multiple PDF forms and consolidate them into a structured CSV file.
This method extracts the actual text inputted into the PDF form fields rather than converting the visual layout, avoiding the issue of exporting image files.
When exporting, individual text exports are formatted as tab-delimited, but merging multiple files will create a CSV file that Excel can perfectly read.
Launch Adobe Acrobat and open one of your completed PDF forms. Navigate to the 'Prepare Form' tool from the right-hand pane to enter form-editing mode.
In the right-hand pane, click on 'More' (or the options menu represented by a gear/wrench icon) and select 'Merge Data Files into Spreadsheet' from the dropdown list.
A 'Export Data From Multiple Forms' dialog box will appear. Click 'Add Files', locate the folder containing your completed PDFs, select all the forms you want to combine, and click 'Open'.
Once all files are listed in the dialog box, click 'Export'. Choose a destination folder, name your file, and ensure it saves as a CSV (.csv) file.
Locate the saved CSV file and double-click to open it in Microsoft Excel. Your form data will now be neatly organized.
Batch Convert PDFs to Excel with WPS Office
If you are trying to convert complex PDF tables into Excel format without turning them into images, WPS Office features a robust PDF to Excel tool. It accurately extracts text and structures, supporting batch conversion for multiple files.
- 1. Open WPS Office: Launch WPS Office on your computer and go to the 'PDF' section.
- 2. Select PDF to Excel: Click on 'PDF to Excel' in the tools menu to open the conversion dialog.
- 3. Add Multiple PDFs: Click 'Add Files' and select all the PDF documents you want to convert into Excel spreadsheets.
- 4. Start Conversion: Choose your desired save location and click the 'Start' button. WPS Office will process the files and output editable Excel spreadsheets.
Frequently Asked Questions
Why does my PDF form export as an image when converted to Excel?
Standard PDF conversion sometimes captures the entire page as a visual snapshot if the document is flattened or if the software does not recognize interactive form fields. Using Acrobat's 'Merge Data Files' specifically targets the form field data instead of the visual layout.
Can I merge PDF forms that have different field names?
Yes, but the resulting CSV file will create a unique column header for every distinct field name it encounters across all files. This means rows for PDFs that do not contain a specific field will have blank cells in that column.
What is a CSV file and why is it used for merging form data?
CSV stands for Comma Separated Values. It is a universal plain-text format that stores tabular data. It is highly efficient for exporting raw form data because Microsoft Excel natively recognizes and formats CSV files into rows and columns.
