How to Export PDF Form Data to Excel Using Adobe Acrobat Pro

In this tutorial, I’ll guide you through the process of extracting data from PDF form fields in Adobe Acrobat Pro and exporting it to Excel. This method is particularly useful when you have multiple filled-in PDF forms that you want to analyze collectively. Click below to watch the video tutorial on how to export PDF form data to Excel and automate the process with Power Query.

Step 1: Open the PDF File in Adobe Acrobat Pro

1. Locate the folder containing your filled PDF forms on your desktop.

2. Right-click on the PDF file you want to work with.

3. Select "Open with" and choose "Adobe Acrobat."

Step 2: Prepare the Form for Data Export

1. Within Adobe Acrobat Pro, click on the "Prepare a Form" option.

2. Next to "Prepare a Form," find the three-dot menu and click on it.

3. Choose "Export Data" from the options.

Step 3: Save Data as a Text File (.txt)

1. Navigate to the folder where your PDF is located.

2. Create a new folder (e.g., "PDF Data") and open it.

3. Choose "Text File (.txt)" as the file type and click "Save."

Step 4: Open Excel and Load Data

1. Open Excel and start a new blank workbook.

2. Navigate to the "Data" tab and click on "Get Data From File."

3. Select "From Folder" and choose the folder where you saved the text file.

4. Combine and transform the data by following the prompts.

Note: By setting up the connection this way, Power Query is connecting to the text data files in that particular folder, so when the next file is saved in that folder, you can Refresh your data and it will automatically appear in your Excel sheet.

Step 5: Edit Data Using Power Query Editor

1. The Power Query Editor will open. Remove unnecessary columns and edit headers.

2. Make any additional adjustments based on your analysis needs.

3. Click "Close and Load" to load the data into Excel.

Step 6: Repeat for Multiple PDF Forms

1. Repeat the applicable steps above for each PDF form you want to analyze.

2. Ensure each text file is saved in the designated folder (e.g. “PDF Data”).

3. Refresh data from the Data tab in Excel to include the new form's information.

Alternative: Merge Data Files

1. If you have numerous PDF forms, go to the "Prepare Form" tab in Adobe Acrobat.

2. Click on options and select "Merge Data Files into Spreadsheet."

3. Add all relevant files and click "Export."

Step 7: Save Data as CSV in Excel

1. Save the merged file as a CSV file within a new folder.

2. Open the CSV file in Excel, save as an Excel Workbook, adjusting formatting as needed.

3. Optionally you can create a similar Power Query connection to this CSV file if needed if you will have multiple iterations of file extracts to combine.

Conclusion

By following these steps, you can efficiently extract and analyze data from multiple PDF forms using Adobe Acrobat Pro and Excel. This method is especially helpful for managing large sets of forms and streamlining the data extraction process.

Remember to subscribe to my Channel for more tutorials, and feel free to share this guide with others who might find it useful. Happy analyzing!