Adobe Acrobat

2009-03-16 13:20:09

#1

thomp

Registered: Feb 15 2006

Posts: 4411

Well, yes you can import data from an excel file into this form. However, the key thing you have to know to do this is the names of the form fields. The data in the excel file has to have the exact same names as the fields in the form. Fill out the form and then export the form data to a ".txt" format. This is a tab delimited file that is exactly what you'll need to import data into the file. You'll find all the field names here.

Once you've renamed all the fields in the Excel file. Export the data to a Tab delimited text file. Then import it into Acrobat.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Thom Parker
The source for PDF Scripting Info
www.pdfscripting.com
Very Important - How to Debug Your Script

2009-03-16 13:32:42

#2

ebbo1983

Registered: Mar 16 2009

Posts: 93

excellent, thanks for the fast response this is playing on my mind all day since I was assigned this project.

Another concern of mine is that the HMRC form cannot be saved - you can only print it after its been filled, if I can manage to fill the form using your advice - is it the case I will not be able to save the file? this would render the project quite useless.

2009-03-16 20:33:13

#3

thomp

Registered: Feb 15 2006

Posts: 4411

If the form does not have Reader Save Rights, then no, you can't save it. Although you should be able to export data out of the file which is just a good. Export the data to FDF files, then, when you double click one these files it should open up the filled form in Acrobat/Reader.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Thom Parker
The source for PDF Scripting Info
www.pdfscripting.com
Very Important - How to Debug Your Script

2009-03-17 04:59:38

#4

ebbo1983

Registered: Mar 16 2009

Posts: 93

Ok Great, I have filled the form and exported the data to a tag delimited text file, I can now see the names of the form fields.

I can also import the txt file to fill the form. So this concept is working.

I am now stuck on the excel part - creating a matching tag delimited text file.

I understand that I need to take certain cells and somehow "match" them to the corresponding field names in the exported PDF text file. In my screenshot the cell with "Name of Deceased A N other" needs to be somehow linked to IHT411_01 which is the corresponding field on the PDF form. I am struggling with this, at the moment I am just concentrating on getting the name of deceased into the name of deceased field on the PDF form. Once I can get this working I could probably figure out the majority myself.

Please could anyone shed some light on what I need to do?

[img]http://ebbo.nxserve.net/images/In_excel.JPG[/img]

2009-03-17 07:45:38

#5

gkaiseril

Registered: Feb 23 2006

Posts: 4307

You can run the following script from the JavaScript console and obtain the field names:

// Enumerate through all of the fields in the document.for (var i = 0; i < this.numFields; i++)console.println("Field[" + i + "] = " + this.getNthFieldName(i));

Or you can add the following script to your Acrobat/Reader's appliction folder and add a menu item to create of report of field names and some of the field's properties:

function DocumentFieldNames() {// see if we have no fields report and exitif(this.numfields == 0) {app.alert(this.path + ' file has no fields./nReport terminated.', 0, 3);return;} // end no field names/* Report of form fields in a PDF file */// declare the variablesrep = new Report(); // declare new reportrep.color = ["RGB",0,.5,0.5];rep.writeText("PDF Document and Form Field Information Report"); // report titlerep.writeText(""); // blank linerep.divide(); // divider linerep.indent();rep.color = color.red; // get some file informationrep.writeText("File Name: " + this.path);rep.indent();rep.color = color.black;rep.writeText("Title: " + this.info.title);rep.writeText("Author: " + this.info.author);rep.writeText("Subject: " + this.info.subject);rep.writeText("Keywords: " + this.info.keywords);rep.writeText("Creation Date: " + this.info.creationDate);rep.writeText("Modification Date: " + this.info.ModDate);rep.divide();rep.writeText("File Size: " + this.filesize);rep.writeText("Page Count: " + this.numPages);rep.writeText("Field Count: " + this.numFields);rep.indent();rep.divide();if (this.numFields !=0){for (i = 0; i < this.numFields; i++){// Enumerate through all of the fields in the document.var cFieldName = this.getNthFieldName(i); // get a field namevar cField = this.getField(cFieldName); // get fieldrep.writeText(i+1 + " Field Name: " + cField.name + ", Field Type: " + cField.type);if(cField.type == "text") rep.writeText("Character Limit: "+ cField.charLimit);if (cField.type != "button" | cField.type != "signature") rep.writeText(" Default Value: " + cField.defaultValue);var FieldDisplay = ''; // field display attributeif (cField.display == display.visible) FieldDisplay = "Visible";if (cField.display == display.hidden) FieldDisplay = "Hidden";if (cField.display == display.noPrint) FieldDisplay = "Visible No Print";if (cField.display == display.noView) FieldDisplay = "Print Not Visible";if (FieldDisplay == "") FieldDisplay = "Unknown";rep.writeText("Display attribute: " + FieldDisplay + ", Read Only Attribute: " + cField.readonly);rep.writeText("Short Description: " + cField.userName);rep.divide();} // end for} // end if numFields != 0rep.writeText(" ");rep.outdent();rep.outdent();rep.outdent(); rep.writeText("");rep.color = ["RGB", 0, 0.5, 0.5];rep.divide();rep.indent();rep.size = 1.1;rep.writeText("Date: " +  new Date()); // document date & timerep.open("Document Info Report"); // open the completed reportreturn;} // end function DocumentFieldNames // add the menu itemvar MenuParent = "Tools"; // default parent menu locationif (app.viewerVersion > 5) MenuParent = "Advanced"; // for version 6 & above use Advancedapp.addMenuItem({ cParent: MenuParent,cUser: "Report of Field Names", //menu display namecName: "DocumentFieldNames", // JS menu namecExec: "DocumentFieldNames()", // action to takecEnable: "event.rc = (app.doc != null)" // enable for open doc only});

George Kaiser

2009-03-17 10:53:49

#6

thomp

Registered: Feb 15 2006

Posts: 4411

George has gone to town. This is all very good info, getting a list of field names. However, you need to be able to match fields in the form to columns in the Excel file. For this purpose, the first row in the Excel WorkSheet has to be the column names. If you import the text file, that you exported from Acrobat, into excel, you will have your base excel file, with properly named columns. The problem now is filling it out the excel file since the form is all cryptic names. If you have an automated method for doing this you're in good shape. If not, then maybe you should consider a different path.

There is another way to approach this that will allow you more flexibility in naming the excel columns in a friendly way. And that's to use the ADBC object. This is Acrobat's database connection interface. It works through ODBC. So your excel file has to be ODBC registered. And the data block in the excel file has to be named, so it appears as a table to ODBC.
With this methodology you would read a row data from the excel file and use a name mapping object to match the filed name in PDF to the column name in the Excel file.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Thom Parker
The source for PDF Scripting Info
www.pdfscripting.com
Very Important - How to Debug Your Script

2009-03-24 06:03:49

#7

ebbo1983

Registered: Mar 16 2009

Posts: 93

Sorry to bring back an old topic, but its still work in progress:P

I am having a problem, I have updated the excel file and saved it as a tab delimited txt. When I import it it all works except the very last "total interest" field which should automatically fill out based on the calculations above. I am not sure why it does not do it, if I update the field above manually then click back into the total box it does update. Can some1 have a look into this for me.

Here are the two files:
http://ebbo.nxserve.net/images/My_test_beta2.pdf
http://ebbo.nxserve.net/images/complete_test.txt

2009-03-24 09:05:35

#8

gkaiseril

Registered: Feb 23 2006

Posts: 4307

Do not inlcude calculated fields in the file being imported.

George Kaiser

2009-03-24 10:10:20

#9

ebbo1983

Registered: Mar 16 2009

Posts: 93

I dont understand, If you open the text file in excel, the cell in question IHT411_04b has no value at all. which is exactly the same as cell IHT411_04a.

Both pass empty values to the form, yet the IHT411_04a value gets calculated on the form but the IHT411_04b does not. Im sure it has got something to do with the TAB not being closed properly or something alond these lines. Any further suggestions?

EDIT: added a picture in hope it makes it easir to understand what I am talking about

[img]http://ebbo.nxserve.net/images/problem.jpg[/img]

2009-03-24 10:20:43

#10

gkaiseril

Registered: Feb 23 2006

Posts: 4307

Have no column for the calculated values.

If you look at the JavsScript debugging console, you will see all the errors the inclusion of the calculated fields are causing. An empty value is still a value whethe null or zero.

George Kaiser

2009-03-24 11:59:45

#11

ebbo1983

Registered: Mar 16 2009

Posts: 93

thanks alot, I took the two columns out and it worked.

2010-07-08 13:58:02

#12

harringg

Registered: Jul 17 2009

Posts: 21

thomp wrote:

Once you've renamed all the fields in the Excel file. Export the data to a Tab delimited text file. Then import it into Acrobat.

Could you elaborate on the proper format? I've created an AcroForm, (Acrobat 9 Pro/Windows 7), exported as .txt (to automatically create the properly labeled headers), opened that in Excel 2007, modified some of the content fields (row 2, header is in row 1 of course) and I can't get it to re-import to the form. I'm exporting from Excel as MS-DOS .txt (Save As).

I've tried other text-only export formats and none of them will import.

Taking the original .txt file and immediately re-importing it works fine, so it's got to be lost during the Excel process.

Any advice would be appreciated.

As a side note, are there any recommended XML tools for working with the XML files exported from AcroForms? I've used Notepad, but for the person submitting/preparing data (to re-import), it's not user friendly.

Thanks

2010-07-08 14:11:58

#13

gkaiseril

Registered: Feb 23 2006

Posts: 4307

Thom is saying the Excel file has to be saved as a text file using the tab delimited format option.
In Excel go to '[b]File[/b]' then select the '[b]Save as...[/b]' option, now assign a location and file name, select the '[b]Save as Type:[/b]' select the option that reads "[b]Text (tab delimited) (*.txt)[/b]" and then save the text file.

Not MS-Dos Text, MacIntosh Text, Unicode Text, CSV, etc.

The columns in the Excel file have to have the same heading as the form field name.

There is an XML text editor available from Microsoft, but you will need to understand the XML tags and format. But it does add some syntax checking.

George Kaiser

2010-07-08 14:16:45

#14

thomp

Registered: Feb 15 2006

Posts: 4411

Here's an article that explains some of the top level issues of connecting to Excel:
http://www.pdfscripting.com/public/department48.cfm

Implementation details are provided in the articles at the bottom of the page, for pdfscripting.com members.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Then most important JavaScript Development tool in Acrobat
[url=http://www.pdfscripting.com/public/34.cfm#JSIntro][b]The Console Window (Video tutorial)[/b][/url]
[url=http://www.acrobatusers.com/tutorials/2006/javascript_console][b]The Console Window(article)[/b][/url]

Thom Parker
The source for PDF Scripting Info
www.pdfscripting.com
Very Important - How to Debug Your Script

2010-07-08 15:27:37

#15

harringg

Registered: Jul 17 2009

Posts: 21

gkaiseril wrote:

In Excel go to '[b]File[/b]' then select the '[b]Save as...[/b]' option, now assign a location and file name, select the '[b]Save as Type:[/b]' select the option that reads "[b]Text (tab delimited) (*.txt)[/b]" and then save the text file.

I'm sure I tried that one too, but will try again when at the office tomorrow.

gkaiseril wrote:

Not MS-Dos Text, MacIntosh Text, Unicode Text, CSV, etc.

I had seen that offered as an option in another post on these forums, so tried that. That was to make sure I was doing forum research before posting. :-)

gkaiseril wrote:

The columns in the Excel file have to have the same heading as the form field name.

I'm using the .txt file exported directly from the AcroForm (BTW, AcroForm means the Form Fields in Acrobat Pro, right? Just want to make sure I'm using the right lingo), so they should be. I import data (plain text, exported from Excel) routinely to Access and that's how I set it up the initial spreadsheets, using the fields from the source, in this case Access. I figured the same would be true using the AcroForm exported .txt file. I'm wondering if maybe the initial import to Excel is putting a stray column and that’s what's causing issues on re-import.

gkaiseril wrote:

There is an XML text editor available from Microsoft, but you will need to understand the XML tags and format. But it does add some syntax checking.

Thanks. I was just looking at that as an alternative since I was having issues with the text importing. I'm sure I'll get it sorted though. I do work with HTML/XHTML, but the end user of these forms don't, so if we can get the .txt import sorted, we'll be fine.

Thanks!

2010-07-08 18:43:26

#16

harringg

Registered: Jul 17 2009

Posts: 21

I just did the same thing using Acrobat 9 Pro (Macintosh) and Excel 2004 (Macintosh), saved as Text (tab delimited) .txt, re-imported to Acrobat and it worked flawlessly. I even added a header name in Excel (row 1) and a value (row 2), THEN added a new text field in the PDF and it imported fine, so it works.

I'll have to review to see if something got 'mangled' with the headers on import with the Windows one. I can't imagine that exporting to Text (tab delimited) .txt is any different on the Windows side of Excel than it is on the Macintosh side.

This is a basic concept and I work with Excel to modify/transform data for import to Access all the time, so it shouldn't be this tough.

2010-07-09 04:33:59

#17

harringg

Registered: Jul 17 2009

Posts: 21

Acrobat 9 Pro Windows (9.3.2)

Open 'empty' PDF document (no form fields).
Add three Text Fields
Forms>Add/Edit Fields>Add new field (Text Field)
Add new field (Text Field):Text1
Add new field (Text Field):Text2
Add new field (Text Field):Text3

Close Form Editor
Add Test1, Test2, Test3 to Text1, Text2, Text3
Forms>Manage Form Data>Export Data (as .txt)
Open the Form in Excel and it looks like this:
Text1 Text2 Text3
Test1 Test2 Test3

Change to:
Text1 Text2 Text3
Test4 Test5 Test6

Excel>Save As>Text (Tab Delimited) (.txt) ToImport.txtAcrobat>Forms>Clear Form
Acrobat>Forms>Manage Form Data>Import (Error: Could Not Load Data From The Text File)What am I doing wrong? :-(

2010-07-09 05:38:41

#18

harringg

Registered: Jul 17 2009

Posts: 21

Got it! I had the spreadsheet open. Closing the spreadsheet allowed me to import the file. I keep Excel files 'open' when working with them and importing to Access. Also, when testing with the Macintosh version, the spreadsheet was open (and import worked). Must be a Windows issue that was causing problems.

I didn't think this was a complicated process. :-)

2010-07-09 09:11:34

#19

thomp

Registered: Feb 15 2006

Posts: 4411

Had you read the article I pointed to in my last post you would have seen this issue discussed.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Then most important JavaScript Development tool in Acrobat
[url=http://www.pdfscripting.com/public/34.cfm#JSIntro][b]The Console Window (Video tutorial)[/b][/url]
[url=http://www.acrobatusers.com/tutorials/2006/javascript_console][b]The Console Window(article)[/b][/url]

Thom Parker
The source for PDF Scripting Info
www.pdfscripting.com
Very Important - How to Debug Your Script

2010-07-09 10:34:20

#20

harringg

Registered: Jul 17 2009

Posts: 21

thomp wrote:

Had you read the article I pointed to in my last post you would have seen this issue discussed.

I read your post too fast. My oversight. I saw this "Implementation details are provided in the articles at the bottom of the page, for pdfscripting.com members." and steer clear of (any) site that wants money for an answer to a question in this big world of the Internet and Google. :-)

Thanks for the guidance. Appreciate the help offered.

2010-07-09 10:48:42

#21

thomp

Registered: Feb 15 2006

Posts: 4411

Programming is of course about details. You have to pay attention or you end up spending a lot of time chasing your own tail.

Thom Parker
The source for PDF Scripting Info
www.pdfscripting.com
Very Important - How to Debug Your Script

2010-09-01 18:04:16

#22

fhpharmacy

Registered: May 23 2010

Posts: 2

You are all great! In an hour or so of reading you have helped me tremendously.
Thanks to you all
Nick@fhpharmacy

2011-11-18 03:48:44

#23

filgueira

Registered: Nov 18 2011

Posts: 1

Hy, it's abaout the same situation. I just do that using acrobat? Or cand i dod using reader?
It's becouse in reader X the import tabs isn't active.

These forums are now Read Only. If you have an Acrobat question, ask questions and get help from one of our experts.

Populate a PDF Form using Excel Data - get me started!