Fillable Form to Flat PDF to Fillable Form Yikes!

[JMagenta]

Hello Everyone,

I hope you are all doing well.

I have been searching for an answer to my problem for weeks now, and decided to finally ask my question. First, I have a 4-page fillable form document. It’s my template and contains all the form fields I need. When it’s a completed form it’s saved as flattened PDF. I’m trying to find a way to return that flattened PDF to a fillable form again so that I can export the data values into a spreadsheet. I want to eventually use the form fields as variables in a statistical program. I get the point and click way of doing this. Prepare form > Merge Data files into spreadsheet, ect., but I would like to use an Action in Adobe Pro that included a few “Execute JavaScript” steps.

I am brand new to JavaScript and automation within Adobe Pro but I’ve been experimenting with different code to achieve a solution. I’m willing to write all the code I need, but what I’m looking for is sample script to draw from.

I have an action that inserts pages before the target document(s). I scoured the internet for the following code (thank you to the maker of this You Tube video and Action Step: https://www.youtube.com/watch?v=bhbU3C7RIpE) that then grabs all the form fields from this template document, and places them on the target document:

for (var p=0; p<this.numPages; p++) {

      for (var f = 0; f < numFields; f++)

    {

        name = getNthFieldName(f);

        type = getField(getNthFieldName(f)).type;

        rect = getField(getNthFieldName(f)).rect;

        var newField = this.addField(name, type, p, rect);

    }

}

Then I have step to delete the template. And another step to save the new target document. However, the resulting document is flawed. The target document has all the form fields from the template document but replicated on each page, so that each page contains all the form fields from the template document. This is not what I want.

If placed properly, it’s my belief that the form fields can tell JavaScript where to extract the data. Is this true? And if so, what would that code look like?

Also, not all the PDF forms are typed, but instead handwritten entries, so I know I will need an OCR step in an alternate Acrobat Action as well. What would be the best solution here? I feel that what I’m wanting is possible somehow, am I completely wrong? Please help!

Thank you so much,

JMagenta

[try67]

- The easiest way to do it is just to take the original file and use the Replace Pages command to replace all the pages with those from the new version. That will keep all the existing fields in place.

- JS will not pick up the underlying text when you create a field over it. It can be done with a script, but it's a much more complicated task. You would need to use iterate over all the words in the page, comparing their locations on it with the area defined by the field's rect property.

- OCR in Acrobat is not going to pick up any hand-written text.

[JMagenta]

Thank you for your swift reply. I was afraid OCR wouldn't pick up any handwritten text. Thank you for that. Now that's a problem for another day. However, you're saying that if I just replace the pages of the target PDF with the template form it should pick up the fields? And what about the typed data in those fields, will it be available for export? 

[try67]

It will not pick up anything, but any existing fields will still be there. Try it...

And no, the text on the page will not become those fields' values.

[JMagenta]

I tried the replace pages solution. Although it did turn the target file into a fillable form, great, it did not capture the Text values "under" it. What I was left with was a fillable form, with static text "under" it. How can I capture the values of those fields, or make those fields recognize the text "under" it? 

[Thom Parker]

Collect and save the data before flattening. There are a number of ways to do this.  

But first, how is the form flattened?

[JMagenta]

There lies the problem. The form is being downloaded, filled out, then sent back as a PDF. That is, the form is being "printed" and saved as a PDF. Meaning it is no longer a fillable form when I get it. 

[Thom Parker]

Why? What is the point of flattening?

 You could have a submit button that emails you the data. If you wanted a flattened version it's trivial to create it yourself. Could be done with an automation script. 

Or, even if the user prints to PDF from Acrobat Reader, they still have the non-flattened form. So they could do both send you the flattened PDF and submit the data. 

[JMagenta]

I understand. It's just what I'm left with right now. I can change the data into a word document that is editable. However, I can't seem to get to what I'm looking to achieve. Which is variables with unique values that are ready to be processed. 

[Thom Parker]

Just as an experiment, so you can see how some of the ways data is moved about.

1) Add a button field to your form.

2)Open the properties for that button and select the "Actions" tab.

3) Select the "Submit a Form" action

4) Enter your email into the "Url" using this format "

mailto:mm@mycom.com

 5) Select one of the may data formats that can be used to export data from a PDF.  

FDF is good for reloading data in to the PDF, but not so good for loading into Excel.  

[JMagenta]

Thank you, I was able to see all the different ways this form could have been sent back. I weep for what could have been, lol. My question is, say I export the PDF to a Word Document. I now have the raw data again. What next? And how can I automate this process for several forms? How can I get an FDF, fill my template Form, then "Merge Data into Spreadsheet.."? Or something to that effect?

[Thom Parker]

If you know your users are going to be using Acrobat Reader, then you can use a script on the submit button to submit the data in a format that is good for Excel, such as simple XML.  

Read this article:

https://acrobatusers.com/tutorials/submitting-data/

And this:

https://www.pdfscripting.com/public/Form-Data-Handling.cfm

[JMagenta]

Yikes, been down an 'XML road' with Excel. It doesn't give me what I need or easily. A total mess, so when I figured out that Adobe Pro has this "Merge Data Files into Spreadsheet" function, and the data resembled a data table I was relieved. That said, I have read the recommended articles. I appreciate that, I'm always willing to learn. It just let me know that I may want to change these PDF's into FDF's somehow, at least then I can create a data table from several forms at a time.

[try67]

FDF is not an alternate format to PDF. It's just a way of exporting the data from PDF form fields (and comments) into a more manageable format.

[JMagenta]

Also, I like how Adobe Pro collects the form fields from several documents and turns them into "variables" with the different values from each different document. However, I realize you can achieve this using Prepare Form>Merge Data Files into Spreadsheet...Is there another way to go about this that I'm overlooking? Is there a way to turn the PDF into an FDF somehow? I'm grasping at straws. I really need to automate this process for future incoming documents of which there are hundreds. Alas, I'm left to hand enter the information into a spreadsheet. This is error prone and very time consuming...Hopefully, there's a solution that works. Thank you. 

[try67]

As I said, this can be done retroactively, but it's quite complex. I've done something similar with comments (see this paid-for tool I've created: https://www.try67.com/tool/acrobat-retroactively-copy-highlighted-text-into-comments) and could probably adjust it to work with fields, too. If you're interested feel free to contact me privately to discuss it further.

[JMagenta]

Thank you, and I appreciate the offer.

[Thom Parker]

Exporting to word does nothing for you. If you need the data, then use the data. Have the user either send the non-flattened form or just the data in one of the available formats.  If you have all the forms, the you can merge them into a spread sheet (which in Acrobat means a CSV file).  This is your second simplest method.  

I know this is confusing. There is a large variety of ways to move the data around. Nearly all require some custom programming because there is no good current method built into Acrobat for data handling. 

There is however a built in Acrobat feature that is close, which is the "Distribute Form" tool set.  This is the first simplest method. Check this out and if it works for you, you're good.  If not, you'll need to do some custom scripting. 

Something I've done from time to time is write Excel Macros that open Acrobat and extract data from a PDF and put it in a Spread sheet. The macro allows for some intelligence in  adding the data. For example, checking to make sure the same form data isn't added twice, and mapping the data names in the PDF to column names in the spreadsheet.  So this is yet another way to go.   

[JMagenta]

I understand. I just wanted to get my documents to FDF's so that they populate my Form, then from there I can use Prepare Form>Merge Data into Spreadsheet. Or is their an easier solution? I tried to export to XML format. If you try to use it with my statistical program you need a "map" and causes a whole host of headaches there. If you try to import into Excel directly you also need to create a "map" file in order to get the file to resemble a dataset. If you use the "Get Data" option in excel you have to "clean" things up each time to get what resembles a dataset. I've just found this way, using Adobe Acrobat, an "easier" way to get something that resembles a dataset. And I don't have access to the forms themselves, just the PDF's. So what can I do? 

[Thom Parker]

Ok, there are two ways to do this

1) Put a submit button on the form that emails the data to you in FDF format.

2) Have the user send you the un-flattened form (either manually or with a submit button). Save these files to a single folder. taking care to rename them accordingly so they don't overwrite each other.  Then use the Merge Form data feature. 

[JMagenta]

Thank you, that's the way to go moving forward for me! But what do I do with the forms I have? I can't have them resent. This is old information now. Not saved by the sender. How can I  process the PDF's I have? Is there no way?

[Thom Parker]

It can be done with a script, but it's not simple.  Here's the sequence.

1. Run a console script to get the names and positions of all the form fields and place them in a global variable. 

2. Use a batch process to run a script that adds square Annotations to each of the flattened forms at the locations collected in #1. If you have the commenting preferences setup appropiately, the text under the rectangle will automatically be copied into the contents property of the annot. 

3.  Collect data from annots and add to some storage location that was created at the begining of the process. A global variable will work. 

4. At the end of the process, run a console script convert the collected data into CSV format and then manually save it to a text file. or you could add it as an attachment to a PDF, then save it from there.

[JMagenta]

Thanks for your time and help. I think I have partial code for number 1. Not sure. I will give it a try and go from there. If I ever get this done it will help me, and hopefully someone else when I post it in the future. Hopefully, it's correct and works. I really don't know. 

[Thom Parker]

Good Luck.