Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

Collecting data in an Excel spreadsheet

Avatar

Level 3

I have created a form that I want to host on an internal website. Now I want to collect the data entered into that form and record that data on an Excel spreadheet. Is there anywhere you can find instructions on how to do this? Thanks.

1 Accepted Solution

Avatar

Correct answer by
Level 4

Alright,

First step is to make sure your form is set up to properly export xml data. Things to check:

- All fields that will have data exported are given a specific name. You can do this by renaming everything in the hierarchy panel, or by setting the name on the binding tab.

- Subforms correspond to an element group in the exported xml file. For example, having a text field called "Name" with a value of "Scott" inside a subform called "People" would result in the following XML:

<People>

     <Name>Scott</Name>

</People>

- You can control which fields/subforms will not be included in the exported data by setting the data binding property on the subform/field to None.

     - Setting the data binding to none on a field will prevent it from showing up on the exported data.

     - Setting the data binding to none on a subform will exclude the subform (but not the child elements) from showing up on the xml.

    

     For example, imagine I have a subform called "FormInfo", a subform inside it called "FileNumbers" and two text fields inside that called "FileNumber1" (with a value of 123) and "FileNumber2" (with a value of 345). Now if the databinding on all of that is set to the default "Use Name (...)", the XML would look like this:

<FormInfo>

     <FileNumbers>

          <FileNumber1>123</FileNumber1>

          <FileNumber2>345</FileNumber2>

     </FileNumbers>

</FormInfo>

using the above example, if you set the data binding on the text field "FileNumber1" to None, you would end up with:

<FormInfo>

     <FileNumbers>

          <FileNumber2>345</FileNumber2>

     </FileNumbers>

</FormInfo>

Using the same example, if we set the data binding on the subform "FileNumbers" to None, you would end up with:

<FormInfo>

     <FileNumber1>123</FileNumber1>

     <FileNumber2>345</FileNumber2>

</FormInfo>

As you can see, the subform itself is no longer included, and any child elements (like the text fields) show up under the next bound subform, in this case "FormInfo".

If you have a repeating subform, the exported xml will have as many copies of the repeated element as required to include all information. Using the above example, if we made the subform "FileNumbers" repeatable, open the form and add two instances of it, then export the data (again... asuming that everything is using the default binding of "Use Name (...)") We would see the following:

<FormInfo>

     <FileNumbers>

          <FileNumber1>123</FileNumber1>

          <FileNumber2>345</FileNumber2>

     <FileNumbers>

     <FileNumbers>

          <FileNumber1>567</FileNumber1>

          <FileNumber2>789</FileNumber2>

     <FileNumbers>

<FormInfo>

AS such, I like to wrap my repeating subforms in a container subsorm to group the repeated section together. The above example doesn't illustate the usefulness of this, so instead consider a form that has a section that collects a list of locations, and a second section that collects a list of people. Without the container subforms, the xml might look like this:

<Form1>

     <Person>

          <Name>Scott</Name>

     </Peron>

     <Person>

          <Name>usuggestaname</Name>

     </Person>

     <Location>

          <Address>123 Any Street</Address>

     </Location>

     <Location>

          <Address>456 Other Ave</Address>

     </Location>

</Form1>

When you have a lot of repeatable subforms, I find it's easy to get lost. As I mentioned above, I like to wra repeatable sections in another subform, to group everything together. In the above exaple, I would wrap the repeated "Person" subform in a subform called "People"... and I would wrap the "Location" subform in a subform called "Locations". The would result in the following:

<Form1>

     <People>

          <Person>

                <Name>Scott</Name>

           </Peron>

          <Person>

               <Name>usuggestaname</Name>

          </Person>

     </People>

     <Locations>

          <Location>

               <Address>123 Any Street</Address>

          </Location>

          <Location>

               <Address>456 Other Ave</Address>

          </Location>

     </Locations>

</Form1>

This is not mandatory, but I find it helps make the XML more readable.

Alright, so the above tips should help you produce a form that has a well structured xml export. An alternative method would be to define the xml schema externally, bind the pdf to that specification, then map the fields in Livecycle. SOme people prefer this method, but I find that is feels like more work, when I can just format my form nicely and have it dictate the exported XML schema.

The next step is to export the xml file.

     - In your version of Acrobat, you go to Forms > Manage Form Data > Export. This will prompt you to provide a file name/save location for the xml file.

     - Unless I'm mistaken, you cannot export form data in Adobe Reader, even if you Reader Extend your form. I could be wrong, and I don't have reader installed to test this. If it is blocked, you can create a submit by email button, Reader Extend your form, and use that functionality to get the xml file. The submit by email button will open your defult mail client with the XML as an attachemtn. In Outlook, you can right click/Save as to grab this xml file, without ever sending it. This is just one possible workaround, if, in fact, you cannot export via Reader. You would have to test this yourself using reader.

From here, you can bind the xml file to an Excel Workbook. I don't have 2003, but this article goes into great detail about it. The simple version is that you add your xml file the the Excel Workbok via the XML Source pane (accessible via Data > XML > XML Source). This will open a heirarchial view of your xml schema. From here, it's as simple as dragging and droping an xml group to a blank sheet. FOr example, my person/location form heirarchy would look like:

Form 1

     - People

          - Person

               *Name

     - Locations

          - Location

               *Address

If I wanted to have a worksheet that listed all people, I would click and drag "People" from this XML heirarchy and drop it to a bank spot. This maps the fields. The final step is to trigger the import. this is done by clicking Data > XML > Import.

That should cover all of your bases. If you have any specific questions, or if I haven't answered something clearly, feel free to reply to this and I'll try to get back to you ASAP.

Also, the instructions for binding to your XML in excel 2003 were described from memory, as I haven't used 2003 in years. THe linked tutorial goes through everything in a lot of depth, but I'm fairly certain I got the basics correct here.

Best of luck!

- Scott

View solution in original post

9 Replies

Avatar

Level 4

I do this all the time. If you created the form properly, being mindful of subform binding and control names, you should be able to export the data in a form to an XML format. Then you create an excel file and set the xml source to the exported data file. You can even merge the data from multiple filled out versions of the same form into one xml file. If you tell me which version of acrobat/excel you're using I could provide more specific instructions.

- Scott

Avatar

Level 3

Thanks Scott - I'm using Livecycle Designer ES 8.2, Acrobat 9 Pro (people completing the form will be using Adobe Reader X, and Excel 2003. I haven't done anything like this before so probably need instructions from start to finish.


Thanks for your help.

Avatar

Correct answer by
Level 4

Alright,

First step is to make sure your form is set up to properly export xml data. Things to check:

- All fields that will have data exported are given a specific name. You can do this by renaming everything in the hierarchy panel, or by setting the name on the binding tab.

- Subforms correspond to an element group in the exported xml file. For example, having a text field called "Name" with a value of "Scott" inside a subform called "People" would result in the following XML:

<People>

     <Name>Scott</Name>

</People>

- You can control which fields/subforms will not be included in the exported data by setting the data binding property on the subform/field to None.

     - Setting the data binding to none on a field will prevent it from showing up on the exported data.

     - Setting the data binding to none on a subform will exclude the subform (but not the child elements) from showing up on the xml.

    

     For example, imagine I have a subform called "FormInfo", a subform inside it called "FileNumbers" and two text fields inside that called "FileNumber1" (with a value of 123) and "FileNumber2" (with a value of 345). Now if the databinding on all of that is set to the default "Use Name (...)", the XML would look like this:

<FormInfo>

     <FileNumbers>

          <FileNumber1>123</FileNumber1>

          <FileNumber2>345</FileNumber2>

     </FileNumbers>

</FormInfo>

using the above example, if you set the data binding on the text field "FileNumber1" to None, you would end up with:

<FormInfo>

     <FileNumbers>

          <FileNumber2>345</FileNumber2>

     </FileNumbers>

</FormInfo>

Using the same example, if we set the data binding on the subform "FileNumbers" to None, you would end up with:

<FormInfo>

     <FileNumber1>123</FileNumber1>

     <FileNumber2>345</FileNumber2>

</FormInfo>

As you can see, the subform itself is no longer included, and any child elements (like the text fields) show up under the next bound subform, in this case "FormInfo".

If you have a repeating subform, the exported xml will have as many copies of the repeated element as required to include all information. Using the above example, if we made the subform "FileNumbers" repeatable, open the form and add two instances of it, then export the data (again... asuming that everything is using the default binding of "Use Name (...)") We would see the following:

<FormInfo>

     <FileNumbers>

          <FileNumber1>123</FileNumber1>

          <FileNumber2>345</FileNumber2>

     <FileNumbers>

     <FileNumbers>

          <FileNumber1>567</FileNumber1>

          <FileNumber2>789</FileNumber2>

     <FileNumbers>

<FormInfo>

AS such, I like to wrap my repeating subforms in a container subsorm to group the repeated section together. The above example doesn't illustate the usefulness of this, so instead consider a form that has a section that collects a list of locations, and a second section that collects a list of people. Without the container subforms, the xml might look like this:

<Form1>

     <Person>

          <Name>Scott</Name>

     </Peron>

     <Person>

          <Name>usuggestaname</Name>

     </Person>

     <Location>

          <Address>123 Any Street</Address>

     </Location>

     <Location>

          <Address>456 Other Ave</Address>

     </Location>

</Form1>

When you have a lot of repeatable subforms, I find it's easy to get lost. As I mentioned above, I like to wra repeatable sections in another subform, to group everything together. In the above exaple, I would wrap the repeated "Person" subform in a subform called "People"... and I would wrap the "Location" subform in a subform called "Locations". The would result in the following:

<Form1>

     <People>

          <Person>

                <Name>Scott</Name>

           </Peron>

          <Person>

               <Name>usuggestaname</Name>

          </Person>

     </People>

     <Locations>

          <Location>

               <Address>123 Any Street</Address>

          </Location>

          <Location>

               <Address>456 Other Ave</Address>

          </Location>

     </Locations>

</Form1>

This is not mandatory, but I find it helps make the XML more readable.

Alright, so the above tips should help you produce a form that has a well structured xml export. An alternative method would be to define the xml schema externally, bind the pdf to that specification, then map the fields in Livecycle. SOme people prefer this method, but I find that is feels like more work, when I can just format my form nicely and have it dictate the exported XML schema.

The next step is to export the xml file.

     - In your version of Acrobat, you go to Forms > Manage Form Data > Export. This will prompt you to provide a file name/save location for the xml file.

     - Unless I'm mistaken, you cannot export form data in Adobe Reader, even if you Reader Extend your form. I could be wrong, and I don't have reader installed to test this. If it is blocked, you can create a submit by email button, Reader Extend your form, and use that functionality to get the xml file. The submit by email button will open your defult mail client with the XML as an attachemtn. In Outlook, you can right click/Save as to grab this xml file, without ever sending it. This is just one possible workaround, if, in fact, you cannot export via Reader. You would have to test this yourself using reader.

From here, you can bind the xml file to an Excel Workbook. I don't have 2003, but this article goes into great detail about it. The simple version is that you add your xml file the the Excel Workbok via the XML Source pane (accessible via Data > XML > XML Source). This will open a heirarchial view of your xml schema. From here, it's as simple as dragging and droping an xml group to a blank sheet. FOr example, my person/location form heirarchy would look like:

Form 1

     - People

          - Person

               *Name

     - Locations

          - Location

               *Address

If I wanted to have a worksheet that listed all people, I would click and drag "People" from this XML heirarchy and drop it to a bank spot. This maps the fields. The final step is to trigger the import. this is done by clicking Data > XML > Import.

That should cover all of your bases. If you have any specific questions, or if I haven't answered something clearly, feel free to reply to this and I'll try to get back to you ASAP.

Also, the instructions for binding to your XML in excel 2003 were described from memory, as I haven't used 2003 in years. THe linked tutorial goes through everything in a lot of depth, but I'm fairly certain I got the basics correct here.

Best of luck!

- Scott

Avatar

Level 3

Hi Scott, bit of a delay in getting back to this form. Thanks very much for your help!

Avatar

Level 3

Hi Scott,

Is there an issue with bringing date fields in to the Excel file. They don't seem able to be mapped like the other fields.


Thanks.

Avatar

Level 4

Hello again,

I just made a test pdf form, added a date field, filled the form, and exported to XML. I had no trouble importing the date values into excel. The only issue that I can think of is if the date fields in the form contain a value that isn't recognized as a date... but even then, after a warning notification, Excel should still import the invalid date as text.

If you can describe the exactly what you do and what happens, I might be able to help you work out what is happening.

- Scott

Avatar

Level 3

It seems to happen from the Livecycle end - when I am choosing the 'Binding' for each field the option for choosing the two date/time fields in my form doesn't appear under the 'Default Binding' panel. All other fields appear in this panel and are able to chosen.

Avatar

Level 1

Hey there, I read some previous answer & must say they are great replies. For collection of data through a website through online forms and get it on to a spreadsheet the simplest n only available tool is Collatebox http://www.collatebox.com/ .

I bet there cannot be an easy way than this read this http://forum.collatebox.com/discussion/60/online-forms

-Alex

Avatar

Level 1

Hi there,

I am using Adobe Acrobat Pro DC (19.010.20069) and I don't see any setting in the properties window for data binding.

I run into an issue where my schema is not binding to the Excel file and I can't import new form data.

Whenever I open an Excel spreadsheet, I open the Source tab and add a new map from the XML file of a filled-in Adobe form; I am able to see the schema in the Source map panel and save.

Then, I fill in new data into my Adobe form, save as xml and try to import that data into the map I created. It gives me a message that the specified XML source does not refer to a schema and prompts me to create a new schema based on the XML source data.

This gives me the impression that my map is not binding and I don't know how to bind to my PDF. Please help! Thanks!!