Skip to main content
Participating Frequently
February 21, 2020
Question

Please Help! Can't export form data into Excel (Lifecycle form)

  • February 21, 2020
  • 4 replies
  • 1787 views

Hello Everyone,

 

I am trying to automate a process where we manually go through this form that appears to have been created by Lifecycle and I keep running into problems, I have tried saving & opening the file using Word, Excel, XML none of it seems to work, it all looks horrible when I get the end product if I save as and open and I cant use the data. Whatever settings are on here are so tight I cant even get the thing to Print to PDF it just hangs.

 

Since this form is publicly available I am providing a link below to it.

 

https://www.dla.mil/LinkClick.aspx?fileticket=6k7fDPHE1dg%3d&tabid=26135&portalid=104&mid=95012

 

I am somewhat comfortable with Macros and VBA but I am not sure if I am supposed to dl diff libraries or something, still very basic user. But any ideas on how to get the data from there assuming the form was filled in? BTW if it matters I am on Office 2016 and Acrobat Pro DC.

This topic has been closed for replies.

4 replies

radzmar
Community Expert
Community Expert
February 22, 2020

Bernd is right. With the export data tool, you can easily export the data of your form into a XML file. That XML than can be opened with Excel for example. Depending on the XML structure excel will show the data either in one or multiple rows. 

Bernd Alheit
Community Expert
Community Expert
February 22, 2020

In Acrobat at Tools search for "export data":

Thom Parker
Community Expert
Community Expert
February 23, 2020

Exactly what I already said,

Thank you Bernd for posting the screen shot. 

Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often
Thom Parker
Community Expert
Community Expert
February 21, 2020

So what you need to do is Export the data. In Acrobat Pro there is an "Export Data" option in Prepare forms mode. You do not want to put this PDF in Prepare Form mode, but if you use the tool search to find the Export data option you can just click on it from there. 

Data Export can also be exported with a script from the console window.

 

xfa.host.exportData("",false);

 

This will save the data in XML format.  Unfortunately it doesn't work in Reader.

 

The data in this file has repeated sections, so it's hierarchical, and in multiple layers. Importing it into Excel will not be easy. 

Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often
Participating Frequently
February 21, 2020

Thanks for replying, I may not be understanding oyu but I have tried using the "export data" function under file >>>Export and tried various options.

 

I also attempted to do it via the Prepare forms before and it gave me errors as well. I mentioned ot the other responder that I need to figure out what this console is you are both talking about.

 

I did save it to excel then try to open it but the data would of required a ridiculous amount of massaging to be useful.

 

Thom Parker
Community Expert
Community Expert
February 21, 2020

What do you mean you got errors? I tried it exporting the data both ways and the XML data was perfect. No errors, and it is arranged in exactly the way the form was designed. 

 

So what is it you want out of the data? Perhaps you need to hire a consultant. 

 

You'll find a tutorial here on the console window:

https://www.pdfscripting.com/public/Free_Videos.cfm#JSIntro

 

Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often
try67
Community Expert
Community Expert
February 21, 2020

LCD forms are very difficult to work with, as you've seen.

I think you can use this simple code to export the values of the fields to the console window, and from there copy them to Excel (since they are tab-delimited):

 

for (var i=0; i<this.numFields; i++) {
	var f = this.getField(this.getNthFieldName(i));
	if (f==null) continue;
	console.println(f.name+"\t"+f.valueAsString);
}
Participating Frequently
February 21, 2020

Thank you,  I have to figure out what you mean by Console and then ill give your solution a try! would I be able to expect the data to come out into "excellable" format where its like one column is the Feild name and the next is the name of data entered in that box (assuming the form is filled out of course).

Thom Parker
Community Expert
Community Expert
February 21, 2020

No, it won't print in excel format as far as columns. That code prints a vertical list of (Name <tab> Value). And by name I mean the full hierarchy name. But it can be pasted into excel. 

However as I explained in my previous post, you're data is hierarchical, with repeated sections, and there are a lot of fields, 222, so this would be a very wide form. But you could modify the script to create columns. 

Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often