Import Data/Populate fields in pdf from Excel

[TPDIII]

I'm not sure if I am using the correct terms, but I would like to set up text fields in a pdf that can import data from specific fields in an excel file.  I found the import data form in the properties of the text field, but it will only allow me to select pdf files.  Is it possible to import data an excel file to a text field?  I would like to do the following

1. create a excel table that includes kids last name, first name, and Grades (Q1, Q2, E1, S1 etc...)

2. I have a pdf form that already has text fields that correspond to the fields mentioned above.

3. Set up the pdf to import the data and generate a page for each student.

I specifically mentioned excel because everyone at my school has excel.  However, it would also work if I could design another pdf with a table that I could import data from.

[George_Johnson]

You cannot import directly from an Excel file, but you can export a file from Excel that contains records that can populate the fields of a PDF form.

You have to export to a tab-delimited text file. The first row of the file must be the tab delimited field names of the corresponding data in the subsequent rows. When you manually import the data, you're given the option of selecting a row of data to import.

You can also import programmatically via JavaScript. This allows you to loop through the records, perhaps adding a new page to display the data for each record. If you need more details, post again.

[TPDIII]

Yes, I definitely need more details.  Would I have to set anything up in excel, or when I save would I just select a tab delimited text file? 

Which would be the easiest approach in regards to the two you mentioned?

[George_Johnson]

Yes, you can maintain the file in Excel as a spreadsheet, and when needed, export to tab-delimited format. You can test this now, just be sure the first row contains the corresponding form field names. Once you've created the file, try to import by selecting: Forms > Manage Form Data > Import Data

and select "Text Files" and then the text file you exported from Excel. It should further prompt you to select a row of data to import.

To import such a text file programmatically, you;d use the importTextData doc method: http://livedocs.adobe.com/acrobat_sdk/9.1/Acrobat9_1_HTMLHelp/JS_API_AcroJS.88.502.html

[TPDIII]

I followed the link to the page to use java script to do this process which is the route I think I prefer to go.  I have some questions.

1. Will I have to name the text fields as the rows of the .txt file?

2. Since it is being done with java script can I get it to generate a page per entry? Is this feesable?

3. I'm assuming the script on the page has to be preceded and followed by other script, where can I go to get this?

Thanks!

[George_Johnson]

1. Yes, exactly the same (including case)

2. Yes, using the template spawning technique discussed earlier

3. I don't understand the question.

[TPDIII]

Something like this?

// Import the first row of data from "myData.txt".

this.importTextData("/c/desktop/myData.txt", 0)

if (typeof cnt == "undefined") cnt = 0;

this.importTextData("/c/desktop/textdata.txt", cnt++ % 4)

// Specify the name of the template

var template_name = "Student Information Sheet";

}

// Get a reference to the template

var t = getTemplate(template_name);

// Add a new page based on the template

if (t !== null) {

    t.spawn({

    nPage: numPages,      // Add the new page to end of document

    bOverlay: false,      // Create a new page, not an overlay

    bRename: true         // Rename the fields

    });

} else {

   app.alert("The template named \'" + template_name + " does not exist in this document.", 1);

}

But how would I tell the new page to take the second row from the chart, and will I have to click the button for each one of the fields in the data?

[TPDIII]

Okay,

    I have a tab-delimited txt file saved onto my desktop named 'DF Test.txt'.  It has the following fields: Name, Q1, Q2, E1, S1.  I created text fields and named them exactly like the fields in the .txt file and then created a button, selected Mouse up > java script and then added the following script.

// Import the first row of data from "DF Test.txt".

this.importTextData("/c/desktop/DF Test.txt", 0)

if (typeof cnt == "undefined") cnt = 0;

    this.importTextData("/c/desktop/DF Test.txt", cnt++ % 4)

When I click on the button nothing happens.  I don't get an error or anything.  I know I've done something wrong, just not what.

[George_Johnson]

There are a number of things wrong, but ignore that for the moment. What happens if you have just the following as the button's MouseUp script:

importTextData();

[TPDIII]

It opens up a menu to select a file.  When I select the file I can pick a row in the document and then the information in the fields went into the proper text field. 

Will the other script not work due to privileged vs non privileged events?  I tried adding the trusted function in, and I even turned on the debugger and now have a console that pops up with errors.  I tried

function myOtherTrustedFunction()

{

// Import the first row of data from "DF Test.txt".

this.importTextData("/c/desktop/DF Test.txt", 0)

if (typeof cnt == "undefined") cnt = 0;

    this.importTextData("/c/desktop/DF Test.txt", cnt++ % 4)

};

app.trustedFunction(myOtherTrustedFunction);

but got a bunch of errors...

[George_Johnson · Accepted solution]

That's a big part of it. In order to open a particular file with JavaScript, the code that does that actual opening of the file needs to be executed in a trusted context. What that means for you is that the code needs to be placed in a folder-level JavaScript file. Code in a button can then call the code in the JavaScript file and the data in the text file can be read into the form.

The other problem is the path you're using is unlikely to be correct for a file on a Mac. Are you running this on Windows?

The other code (that uses the cnt variable) appears copied from the sample in the documentation and doesn't apply to your situation.

[TPDIII]

Yes, I'm on a mac.  I have the file sitting on my desktop for easy access.  Yes, the code was copied from the page.  I changed what I thought I needed to change to make it apply to my situation.  I've never done anything like this before, except for the other scripts you helped me with, so this is basically greek to me.

I need to cut out the

if (typeof cnt == "undefined") cnt = 0;

    this.importTextData("/c/desktop/DF Test.txt", cnt++ % 4)

So I should have just

// Import the first row of data from "DF Test.txt".

this.importTextData("/c/desktop/DF Test.txt", 0)

"What that means for you is that the code needs to be placed in a folder-level JavaScript file"

Does this mean I would have to do something in a folder on each computer that uses this java script?  Or is there a fix in the script itself that would allow anyone using the form on any computer to use it without problems? 

Btw, does using '//' signify that I'm beginning a new set of instructions?

[George_Johnson]

Reading a file is not allowed from a script in a document. This is a security restriction. The way around this restriction is to install a JavaScript file on each machine that would need to do so. So yes, you would need to install the file on any machine that would require this functionality. It's really not as bad as it may sound.

Note also that anything to do with templates will not work with Reader, so if you need this to work with Reader, you'll be limited to reading one record at a time from the data file. This might not be a big deal, as the user would simply click the button once for each record, view/print, and manually move on to the next.

What I've been trying to do here is introduce you to the concepts and point you to the documentation where you can learn more. But I also realize there's a lot to learn, especially when starting from scratch.

If installing a JavaScript file on each machine sounds viable, then I'll help you move forward.

[TPDIII]

I will have to ask around and see how many machines have acrobat on them.

1. In regards to installing a java script on each machine, (and this may be a stupid question), but would doing that cause any security risks on the machines, or when you mention security issue is it simply something to do with the program having the proper permission to execute the script.  I'd hate to tick off the IT guys in my district.

2. "Note also that anything to do with templates will not work with Reader, so if you need this to work with Reader, you'll be limited to reading one record at a time from the data file."  So, if I set up a button to generate a blank page from a template it won't work in Reader?y

3. I greatly appreciate all the help you've given me so far.  I understand more than I did at the beginning, but, as you said, it is still a lot to learn. 

If generating a template won't work in Reader there may not be any reason for me to do the code the other way, so how I proceed will probably depend on that.

[George_Johnson]

1. The security issue is with Acrobat. Adobe correctly prohibits JavaScript from reading files on a machine unless given explicit permission to do so. Installing a JavaScript file that allows for it is the explicit permission. Now, it's possible to open up security holes that can allow malicious PDFs to take advantage of the code that you install this way, so it has to be correctly coded to minimize the chances of this.

2. Templates do not work with Reader. I should have stressed this sooner, but it was mentioned in the other topic that showed the code for spawning templates.

It's not entirely accurate to say that don't work with Reader, since it's possible to add a usage right to a document that will allow templates to work. Unfortunately, the particular usage right cannot be applied with Acrobat. Rather, LiveCycle Reader Extensions is required, and this isn't cheap. This limitation effectively makes them unusable with Reader.

[TPDIII]

Thanks for the help with the issue.  I've made a form that uses the importDataText() function you showed me.  Probably better to keep it simple anyway; more variables = more problems.