How to export rows of Excel data into different fillable PDF forms?

[Lucas5CD3]

Hello!

I'm trying to modernise my workplace a bit. I work in research and collect data manually with a form.

My handwriting isn't great so this has lead to problems in the past.

I've managed to convert this form into a fillable PDF, so I could use this file and fill it in on my laptop and then save it manually for each form, to print it at a later time. This is a little more efficient than writing everything by hand, but I would like to improve even more.

You see, in this method I still have to select each field with the mouse and then write it separately. I reckon it would be more efficient to write everything on one row in Excel, defining the same variables I have in the fillable PDF and then exporting it to a new PDF file.

Additionally: I would like to know if there's a way to stack the exportation, so if I have many rows, export these in one go and have all the required PDF's generated in a designated folder.

Is this possible to do in Excel and Acrobat? Or is perhaps Access a better program for my interests?

I read online that it should be possible, but I haven't managed to make it work. Also, how would I fill out checkboxes in the fillable PDF, importing data from Excel?

Depending on where I collect data I would like to make separate Excel files, in case one place only accounts for some of the variables the form requires. Again, to lessen unnecessary scrolling through the Excel file.

There are a lot more aspects I would like to improve upon, but this on its own would make my work a lot more pleasant!

If anyone could help me, it would be great!

[try67]

Yes, it's possible. The basic process would be to create an Excel file with all the names of the fields in the first row and their corresponding values in the second, third, etc. You would then need to save the file as a tab-delimited text file and then you'll be able to import one row at a time. If you want to import multiple rows, either into a single file or by generating multiple merged files, it's a process called Mail Merge. You can do it via some of the PDFMaker plugin versions for Office, or by using a script, like this (paid-for) one I've developed: https://www.try67.com/tool/acrobat-mail-merge-and-email-pdf-files

[Lucas5CD3]

Thank you, I will try this later! Do the variables in the row need to be placed in the same order as one would progress through the fillable PDF file with the TAB-key (so from top to bottom, left to right), or can they be in different orders? Or is there a different way you can assign a cell to a fillable field in the PDF form? And is there a way to check check boxes?

[try67]

- The order doesn't matter.

- The value matches the name of the field in the same column. I recommend you export the data from an existing file and then you'll see how it is set up. You can move entire columns around and it will still work when you import it back it.

- The default value of a check box is Off. The on-value can be anything. By default it is Yes. If you specify Off the check-box will be un-ticked when you import the data. If you specify its on-value it will be ticked.

[Lucas5CD3]

I can't seem to make it work. I did as you said and defined all the variables in the first row, to match the names of the fields in the PDF, fields defined and edited with Adobe Acrobat. Then I made a test file with fake data and saved the Excel as a .txt file and went in Acrobat to "Import Data" (under "Tools" > "Forms" > "More form options" > "Manage form data" > Import data"). When I click the txt file it shows a preview which seems to be adequate. In the preview window I seem to be able to change the order of the columns, for whatever reason. However, when I go on with the importing, it says "Some data in the text file was not imported successfully", after which nothing is imported.

Do you know what is wrong in my process? In Excel I used "yes" as the term to define which check boxes are marked. The order is unimportant you said, if the name of the variable is the same. I triple checked if all the terms match, regardless, I thought it would fill out the variables with matching names, regardless if other variables don't have the correct name. Also, not all variables are filled out, but I don't think this would be an issue, or would it?

Thank you for your time, I'm sorry if I'm not very understanding. I'm not too handy with all this.

[try67]

You have to make sure that the values you entered for the check-boxes match their actual export values, including upper/loser-case. If the export value is "Yes" and you entered "yes", it will not work.

And it's possible that the error caused it to stop the import operation entirely, so only some fields are filled-in.

[Lucas5CD3]

How do I verify which export value toggles the check boxes? I've tried both types of "yes"es and the situation remains the same.

[try67]

In Prepare Form mode right-click the check-box field, select Properties, and then look under the Options tab:

[Lucas5CD3]

Hello,

That's good to know! Sorry for my slowness. I checked, it seems to be this setting for each check box. However, I still cannot manage to import the data. Surely I'm missing something obvious, but I can't figure it out.

[try67]

Can you share the PDF and text file you're using with us?

[Lucas5CD3 · Accepted solution]

I think I managed it! I indeed made a silly mistake. Thank you very much for your continued efforts! Next I'm going to try and do the multiple document outputs. I need to test it all a bit, still.

[Amber3288932036a8]

I am trying to do this same thing.  Pull Excel information onto PDF forms.  Can you share how you did it??

[Lucas5CD3]

I just wound up using a mail merge program. Works like a charm 🙂

[Amber3288932036a8]

Thank you for the information, I am considering the add on Power PDF Filler.  I fill out forms all day and I have to put the information in a excel spreadsheet too.  I looked into the mail merge and I couldnt figure it out.  I have adobe editor and the microsoft office.  Is Mail Merger an addin option ??

[Lucas5CD3]

Hi, sorry for the late reply. I made fillable fields in the PDF form, each field with a unique codename. Then in Excel the top of every row had that same designated name. For toggling check boxes the value to enter is "Yes" with capital Y. The program I use is called Automailmerge from Evermap  https://evermap.com/automailmerge.asp works like a charm for my purposes.