Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Collecting data in an Excel spreadsheet

Avatar

Avatar
Validate 10
Level 1
usuggestaname
Level 1

Like

1 like

Total Posts

55 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 1
View profile

Avatar
Validate 10
Level 1
usuggestaname
Level 1

Like

1 like

Total Posts

55 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 1
View profile
usuggestaname
Level 1

15-08-2012

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.

View Entire Topic

Avatar

Avatar
Validate 1
Level 2
past-tense
Level 2

Likes

9 likes

Total Posts

85 posts

Correct Reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Applaud 5
View profile

Avatar
Validate 1
Level 2
past-tense
Level 2

Likes

9 likes

Total Posts

85 posts

Correct Reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Applaud 5
View profile
past-tense
Level 2

16-08-2012

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