[Guide] Bulk fill out a form from Excel data (Adobe Acrobat DC Pro required)
Problem:
I have PDF form (which has a labeled form) that I need to fill out based on data I have in an excel sheet. The output should be one filled out instance of the PDF form per each row of the Excel data sheet. Additionally, I want to print all of them at the end easily.
Bonus:
This works with languages (e.g. Hebrew) where there is a bug where even the filled out fields in a form (e.g. in Hebrew) do not appear unless you click on them or add something to them in English.
Solution:
This requires the use of Adobe Acrobat DC Pro due to the usage of the “Actions” feature!
- 1) Create or ensure that the PDF form has actual form fields (e.g. use the Prepare form function in Adobe Acrobat DC Pro) and that the form fields are labelled (preferably appropriately).
- 2) Ensure that the Excel sheet you want to fill out the forms from has only one proper table with headers (to distinguish the the data entries) and one data entry per table row.
- 3) Download BulkPDF (home page; github page) - a free and open source program that does the bulk of the work here. (kudos to some German fellow!)
- 4) Run it. Load the spreadsheet - make sure it detects the fields. Load the PDF in the next screen. Match the Value (via the “Select” button in the “Edit” column) to the Excel field. On the next screen select your generated naming preferences for each filled out form and the directory BulkPDF will save them in. Click Finish and let it run.
At this point this might be enough for you. Yet what if you wanted all these in one PDF and wanted to print it?
[Optional]
I had an issue with some entries in Hebrew not displaying in the outputted forms until I clicked on them or added some Egnlish. The other thing that also fixed the problem is to go in the “Prepare form” function in Adobe Acrobat DC Pro and give the field yet again a transparent background. So I scripted this solution and used actions to apply it to all the generated PDFs!
- a) Open the “Actions Wizard” under “Tools” in Adobe Acrobat DC Pro.
- b) Select “New Action”.
- c) Add (to a pre-existing “Open” action) “Execute JavaScript” under “More Tools”.
- d) Un-tick the “Prompt User” checkbox in the added “Execute JavaScript” action (so it won’t prompt to run the code on each PDF)
-
e) Click “Specify settings” in the added “Execute JavaScript” action and add the following code (it iterates over all fields int he form and makes the background transparent as per my aforementioned manual solution just automated):
for (var i = 0; i <= this.numFields; i++){ var fNa = this.getField(this.getNthFieldName(i)); if (fNa.type == “text”) { fNa.fillColor = color.transparent; } }
-
f) Click “OK”. Add a “Save” action under “Save & Export”. Click Save.
-
g) On the right, select the newly created action and give it all the outputted PDFs from BulkPDF. Click start and wait for it to finish!
[End optional]
-
5) At this point if you try to merge the PDFs using Adobe it won’t let you as the form field labels have the same names and will ask you to make a portfolio which isn’t a single PDF and you can’t really print it all in one shot. The solution is to flatten all of our PDFs loosing any form related labels and converting the the filled in form data to regular text. How to do it on all the docs? Simple - JavaScript and Actions! Follow the Bonus step written out above but instead of that piece of code insert the following:
flattenPages(); -
6) Now you can merge/combine the PDFs using Acrobat Pro (or whatever else) and have a single PDF with the filled out data form Excel that you can print or send to someone!
This took my till 2AM to figure out not wanting to deal with Python at that point. BulkPDF is something Adobe Acrobat Pro should have built in… And the darn bug with the foreign language text that I spent 2 hours finding a workaround for!
EDIT: trying to fix formatting. The four-space-indent-for-code thing does not work =(.
Submitted February 14, 2018 at 10:27AM by Hakkensha
via reddit https://www.reddit.com/r/pdf/comments/7xl1e3/guide_bulk_fill_out_a_form_from_excel_data_adobe/?utm_source=ifttt
from Medical Release Form https://medicalreleaseform.tumblr.com/post/170880950431
via Medical release form
from Tumblr https://jeemiahmelville.tumblr.com/post/170881179732
No comments:
Post a Comment