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

[Timber1234]

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.

[try67]

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);
}

[Timber1234]

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]

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. 

[try67]

Copy the code from my message.

Go to Acrobat and press Ctrl+J to open the JS Console window.

Paste the code into the lower part of the window, instead of the text that's already there.

Select all of the code using the mouse or keyboard.

Press Ctrl+Enter to execute the code.

It will print out the names of values of all the fields in the file in a two column structure, which you could then copy and paste directly into a blank Excel spreadsheet.

PS. You can add this line to the start of the code to clear the console before it outputs the values, so it's easier to copy it later on:

console.clear();

[Timber1234]

I have attached a screenshot of what happened after I pushed ctrl+Enter in the console, did I do this right? after i did CTRL+Enter the information starting at "Syntax Error " showed up.

[try67]

I'm not seeing any screenshot. You can't attach it to an email reply, if that's what you're doing.

[Thom Parker]

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. 

[Timber1234]

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]

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

[Bernd Alheit]

In Acrobat at Tools search for "export data":

[Thom Parker]

Exactly what I already said,

Thank you Bernd for posting the screen shot. 

[radzmar]

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.