Batch-Import Excel Data into PDF Forms

A while ago I documented for AcrobatUsers.com how to manually import an Excel data record into a PDF form. You can find this information here: Can I import data from an Excel spreadsheet to a fillable PDF Form?

This is very useful if you only have to deal with one or a few records that you need to import into PDF forms, but what if we are talking about 10s or 100s of records? It gets a bit boring to click on the same buttons again and again. There must be a way to automate this…

And, there is. The following gives you an idea about how to do this using JavaScript.

Anything I said about importing data manually in the link above is still true, so get familiar with the manual process and verify that you can actually import one data record from your text file into your PDF form. If that does not work, trying to automate this step will also fail.

The key to importing data from an Excel file is that you need to export the data as a “tab delimited text file” – just like described in the AcrobatUsers.com question I linked to above. Once you have such a file, you can use the Acrobat JavaScript method Doc.importTextData() to import one record at a time (just like we did manually before). Take a look at the documentation for this method: Doc.importTextData

There is a problem with this page from the documentation: The error codes use the wrong sign: All positive values are supposed to be negative and vice versa.

To import a whole spread sheet of data, we need to call this method for each record, and then save the file under a new name, an then move to the next record. This can be done e.g. in an Action. You can use the following script in an Action (or a custom command in Acrobat DC):

[Update: I’ve fixed the code below – I had some of the error codes mixed up.]

There are two lines that actually do something: The line that is marked with ‘imports the next record’ is the one line that reads the record with the index “idx” from the file with the fielname “fileName”. And, the line with “saves the file” will save the open file under a new filename. You can get creative and use elements from the form to craete your new filename.

The only thing that’s a bit complex is the file and directory names in this script: Acrobat’s JavaScript uses “device independent paths”. What I’ve used in this script are paths on a Mac, if you are running Windows, the paths may look like this:

var fileName = "/c/temp/data.txt"; var outputDir = "/c/temp/output/";

A path of e.g. “C:\temp” gets converted to “/c/temp”. You can read up on device independent paths in the PDF specification.

This should get you started. If you have questions, as usual, post them in the comments.

231 Responses to Batch-Import Excel Data into PDF Forms

Hello, I wanted to say that I tried to make it work, but I couldn’t. Is there anything else I need to change or adapt on the code apart from the Windows way of writings paths? Thank you!

David, you cannot use the “Windows way of writing paths” when referring to files in Acrobat’s JavaScript environment: Acrobat uses “device independent paths”, and that requires that you use the forward slash, and you also encode the drive letter as the first folder: /c/somedirectory/somefile.pdf If you need more information about these device independent paths, you can look this up in the PDF specification.

Hi there, I’ve managed to create the action in Adobe Acrobat DC and have taken care of the anomalies of the Windows backslash issue but it just fills the current form with data of the first row and the output folder turns up empty. What could I be doing wrong? Thanks for your time.

Khalid, unfortunately, there is not just one thing that could be wrong. Check the Javascript console for error messages. Do you see any errors? If so, correct the error and chances are that you are one step closer to a working solution. If you are not getting any errors, then debug your JavaScript in the debugger that’s built into Acrobat. Unfortunately, explaining how to use that debugger would be a bit too much for the scope of this blog comment. Here is a link to an article that talks about debugging Acrobat JavaScript in general: https://acrobatusers.com/tutorials/how-to-debug-your-script

Hi Karl,
I have PDf file (unfortunately I cannot provide it due our corporate policy) which is fillable. So there are fields I can edit (product price new, product price old, product name)
I have values for these fields in excel. Rather then manully type the values (shop staff do it now) I would like to automate the procedure. So Import the values from excel to PDF.
I found some suggestions how to do this – make excel Tab Delimited Text and then in Adobe Tools>Forms>More Form Options and click on Import but this is not working for me, No Forms option in my Acrobat Reader (XI).
Trying to get JavaScript Console, also no success (Ctrl+J is not working for my and my Acrobat Reader XI) Any suggestions how to achieve this (without need to buy some software) Many many thanks

Just trying to figure out what I am doing wrong here… My understanding is that, using Windows, I really only need to change data file from
“/Users/username/tmp/data.txt”
to whatever my information file path is using “/C/Users…”
and the destination file path from “/Users/username/tmp/”
to whatever my destination file path this. Then I can just change the two “Text1” & “Text2” fields to whichever I choose, correct? And this should run, correct?

I was able to get this script to run, but the problem I am having is that on my .txt not every cell is populated with text or numbers. So when it goes to save the next file and runs the script to fill the form, if the new line from the .txt doesn’t fill a spot filled out on the form it leaves what was previously on the form from the last line of .txt.
Is there a way to clear the form before each iteration of the javascript is run?
I know the cheat way is to just put a hyphen or such in the blank spot on the excel and .txt file but I would rather leave the spots blank.

John, you can run the JavaScript command this.resetForm(); to reset the whole form back to it’s defaults.

This is just what I’ve been looking for (for hours)!
But I’m having some trouble.
So, I made the action and set the paths to look at “/c/temp/”.
When I open my PDF form and run the action, it populates the form with the 1st row of data in the data file, but errors out with User Cancelled File Select (error 3 from importTextdata). If I comment out the error checking code then the file is actually saved with the correct data (and correct file name from that data), but then terminates with no error (all green checkmarks). I didn’t see anything in the debugger, but I’m not too sure how it works. Am I missing something?
Another question I have is after the saveas, the “currently” open file is now the newly saved one. Does the code in the action still have state from being started from the original form? That is, does idx get set to 0 again? I assume it should increment until all rows from the data file are read or an error occurs and the code always acts on the currently open form.
This almost seems like success if I could just get it to read all data!

Let me answer my own question. The solution was to “pay attention to the internal field names”, which of course must match your data file column headers exactly. I fixed my data file and it works perfectly now. Thanks! BTW: Since we’re talking about data files, you don’t have to specify all form fields, which is useful if you have signatures or calculated fields that can’t really be populated from external data. You can pre-populate forms with an export from a SharePoint list for instance and build a bunch of PDF forms for further processing. Just scratching the surface here, I think… I wonder if you can use web service based data sources in a PDF action?)

Hi Karl,
I have tried to follow the instructions, but still coming up with the first line only. There doesn’t seem to be any errors. I’ve cleared the form first also in the event that it may have caused any issue.
This is the first time ever using Java for anything, so I am stumped.
I have about 200 records that I want to import with a lot of fields and I don’t want to do this manually.
Any help would be appreciated.

Susie, without seeing the script you are using, the form and the data you are trying to import, it’s impossible to say what’s wrong. I can however tell you how I would approach a problem like this: First, I would start out with a very simple form and try to get the process working with e.g. three fields (Does my example work of you? You can download all the files that I used.) If that works, but your actual form does not work, then I would fill in information into the form and export that data as a text file and then compare that with the records you are using. Another thing you can try is to reorder the data in your text file – maybe there is something wrong with the second record. And, as always, if you don’t know what your script is doing exactly, and how far it gets, or why it stops, add debug statements to your script that print out information about what’s going on to the console. If this still does not give you a better idea about what’s going on, you may want to consider paying for either some training or consulting. If that’s something you want to consider, feel free to get in touch with me via email.

Hi Karl, I found you thru this website “https://answers.acrobatusers.com/Can-I-import-data-Excel-spreadsheet-fillable-PDF-Form-q113686.aspx#postComments299748″. And, I tried to import data into PDF fillable forms with the test file tab delimited text file, but the message pops up like ” Some data in the text file was not imported successfully”. I would like to send my files if you allow. Thank you,

Sung, my email address is on my “About” page (http://khkonsulting.com/about). I cannot promise that I will get to your files right away, it may take a couple of days until I find some time to take a look.

I apologize, this is my first experience with scripts and java. my text file is set up correctly, I am just hung up on the script to get it to produce new pdfs for all records. It does the first one, but then gives me warning 3 about user cancelling files select. Please help me with this script, I am not sure what I am doing wrong. this is what I have:
var fileName = “/M/HR Misc/ACA/for upload to pdf.txt”;
var outputDir = “/M/HR Misc/ACA/f1095c form new/”; var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(outputDir + this.getField(“Full Name”).value + “.pdf”); idx++;
>
>

Jessica, you are working with JavaScript, not with Java – similar name, but completely different programming environment. This is important if you e.g. google for a solution to a problem. If you search for Java solutions, you will not be able to use anything you find. In most cases, problems with this import function are caused by your data file. You have to make sure that – the column headers are identical to the field names
– the data file has one column for every field in the form
– the data file does not have any extra information besides data for every field Without the data files, it’s impossible to say what’s going on.

I want to import the tab-delimited text file into pdf forms using adobe acrobat pro X. But it does not allow me to choose the text file, and only gives three types of files
xml, xml data package and form flow 99 data files. How to pick tab-delimited files? Thanks

Ernan, when you select Tools>Forms>Manage Form Data>Import Data in Acrobat X Pro, you should be able to select “Text File” as one of the options – at least for AcroForms. Is it possible that you are dealing with an XFA (or LiveCycle Designer) form? These forms limit you in what formats you can import.

Can anyone tell me where the error is in this code? var nomTXT = “/C/Users/usuario/Desktop/EPES_Guillermo-2016-04-21/EPES Sevilla/Protocolos/Caudalimetro/112CAUDALIMETRO”; var nomCarpetaDestino = “/C/Users/usuario/Desktop/EPES_Guillermo-2016-04-21/EPES Sevilla/Protocolos/Caudalimetro/nueva”; var err = 0;
var idx = 0; while (err == 0) err = this.importTextData(nomTXT, idx); // imports the next record if (err == -1)
app.alert(“Error: No puedo abrir el archivo”);
else if (err == -2)
app.alert(“Error: No puedo cargar los datos”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(nomCarpetaDestino + this.getField(“CODIGO INVENTARIO”).value + “_” + this.getField(“FECHA”).value +”_”+ “PR” + .pdf”);
// saves the file idx++;
>
>

Hello again, i found a mistake in my previous code, the quotes were ” ” instead of ‘ ‘ . but the code still says that can not open the file, i’ve checked the path and i can’t find the error, it’s exactly where the file is and with the javascript structure.

Hi Karl, I’m using LiveCycle Designer form as i need to do some select behavior ont he form.
Is there any way to make this work with those types of forms?
Also, I need to be able to save(or export) the PDF’s as “Reader Extended PDF” with “Enable More Tools”. Anything I can add to the JS to make these happen? Thanks!

Hobin, the method I presented only works with AcroForms, not with LiveCycle XFA forms. Reader-enabling can only be done using the File>Save As menu item, you cannot accomplish this via JavaScript.

Hello Karl; Can I import values from the excel sheet to a table designed corresponding to those values in the spreadsheet? Best,

Karl I have put the following code in an action. /* Put script title here */
//IMPORT TEXT INTO OMRF BENEFICIARY FORM // specify the filename of the data file
var fileName = “/c/Users/tjohnson/Desktop/OMRF369MakeTable.txt”; // the tab delimited text file containing the data
var outputDir = “/c/Users/tjohnson/Desktop/forms/”; // make sure this ends with a ‘/’ var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); // imports the next record if (err == 1)
app.alert(“Error: Cannot Open File”);
else if (err == 2)
app.alert(“Error: Cannot Load Data”);
else if (err == -1)
app.alert(“Warning: Missing Data”);
else if (err == -2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == -3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(outputDir + this.getField(“First”).value + “_” + this.getField(“Last”).value + “.pdf”); // saves the file
idx++;
>
> It fills the first name from the file into the form–however–it does not save the form and also does not advance to the next records. Can you please point me in the right direction? Thank you so much for your help!

This is what comes up in the console–I am not good at debugging with the console–so not sure what it all means–it is pointing to the file name line. Can I send you a picture of the console? I can’t copy paste it in this box.

I finally figured it out–I had the wrong path to my data file. Thank you so much for the code! Tamera

I get a return code 3 and when referring to the Acrobat JavaScript Scripting reference it states that code is “Error: Invalid Row”. If I change the saveas err==0 to err==3 it creates one pdf but does not continue with the rest of the records. If I import manually it states some data was not imported successfully but upon reviewing everything expected actually imported. Wondering what I should look at next to get this process working as intended.

Hi Karl, I’ve been struggling through this and I am now stuck. Here is where my code is:
// specify the filename of the data file
var fileName = “/c/Users/username/Desktop/Data.txt”; // the tab delimited text file containing the data
var outputDir = “/c/Users/username/Desktop/TestFill/”; // make sure this ends with a ‘/’ var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(“/c/Users/username/Desktop/Data.txt”, idx); // imports the next record if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(“/c/Users/username/Desktop/TestFill/” + this.getField(“Name”).value + “.pdf”); // saves the file
idx++;
>
> I run it and it only fills the first record and does not save it or go on to the next record. In JSDebugger I get the following message:
“UnsupportedValueError: Value is unsupported. ===> Parameter cPath.
Doc.saveAs:21:Batch undefined:Exec” My Data.txt is set up with a column “Department”, “Manager” and “Name”. The form has fillable section for each of those fields. The sections are named correctly. Any help you can offer would be greatly appreciated. Thank you, Ed

This is great! Got it to work but wanted to know if it was possible to add .readonly = true; somewhere in the document? This would make each individual export non editable. I tried adding after “.pdf”).readonly = true; but that broke the next data import. Any ideas?

Andrew, you can set each field to readonly in your form before you import the data. This way, all fields will be read-only as far as the user is concerned, but you still can modify the field contents via the data import (or programatically).

Ed, I assume the error message is referring to the “saveAs” call and not the one where you import data. Is it possible that your “TestFill” directory does not exist, or your Name field is empty?

So I changed my save directory to the desktop. I know this directory exists and the problem persists. The Name field in my data file contains the correct information. It is not empty. I was messing around with “saveAS” and I changed it to this: this.saveAs(outputDir + “Name”.value + “.pdf”); // saves the file
idx++;
>
>
I changed the “+ this.getField(“Name”).value” section to “+ “Name”.value” just to see what would happen. When I run this, it actually crates a new PDF named “undefined” but the document only has the name of the last record in my Data.txt file filled out. This leads me to believe that the issue is with the saveAS portion? Although I’m not sure what the problem might be.

Ed, ‘“Name”.value’ will cause the “undefined” value – there is no “value” property in the String object. If you want to test with a static filename, try this:

this.saveAs(outputDir + “Name”+ “.pdf”); // this will use 'Name.pdf' as output filename

I get the same result. A pdf named “Name” that only contain the name of the last record in my data file. I am at a loss.

Ed, without your data file, the PDF file in question and your script, it’s impossible to say what the problem is. To debug this, you may want to add some debug output statements along the way to see where and why the program stops.

Unfortunately I can’t send those documents to you because of privacy issues. This is a project that was given to me even though I’ve never worked with JS before. I appreciate the help and I’ll struggle my way through it. It’s a good way to learn. Thank you!

Hi Karl, I seem to be having a similar problem that others are having. I keep receiving the “Warning: User Cancelled File Select” message. I did get at one time a single pdf to save (only one time). I saw that someone else mentioned you could get it to work with more fields in the pdf then headings on the txt file, so I don’t think that’s the problem. I double checked the Headings in my txt file to make certain they matched up with the fields in the PDF that I wanted filled. I believe I have the fields set up in the save as line to name the individual pdfs by separate names. I was hoping you could take a look at my script and maybe you can see what I keep missing. I appreciate any insight you might have. Thanks /* Batch Import */
// specify the filename of the data file
var fileName = “/Users/ethanl/Desktop/Contacts.txt”; // the tab delimited text file containing the data
var outputDir = “/Users/ethanl/Desktop/Filled Contracts/”; // make sure this ends with a ‘/’
this.resetForm()
var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); // imports the next record if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(outputDir + this.getField(“COUNTY”).value + “_” + this.getField(“COMPANY”).value + “.pdf”); // saves the file
idx++; >
>

Kurt, unfortunately it’s not just the script, I would also need access to the PDF file and the data file to figure out what’s going on.

Hi Karl, If it helps I went ahead and emailed you a copy of the pdf and a dummy txt form to you, since you’ve mentioned to other people sending them in. If you get a chance to look at it, that would be great, but if not, I understand that you’ve got a lot on your plate already. I thanks you for giving me the time you’ve given me so far. Kurt

Hi Karl, One of my Co-Workers was looking at what I was doing and tried a few things, and was able to get it to work. Thanks for supplying the script and the help Kurt

The problem was in the txt file. I didn’t create it it originally, and I was looking at in a document viewer, so I didn’t see the problem. My co-worker opened it in excel and found there was an extra empty column between a couple of data columns. When he removed that and we reran the script, it worked. We’re going to generate a couple of new txt files and test it again when those are ready so we can try it on items we would be using it on.

Hello Karl, I have some kind of security issue when i run the script. I get the following message:
NotAllowedError: Security settings prevent access to this property or method.
Doc.importTextData:8:Field btnGenerer:Mouse Up Using Acrobat, i changed the preferences (edit->preferences->javascript->enable menu items javascript execution privileges) but this is not enough, i run into the same problem. How can i fix this? Thanks for the script, good starting point!

Karl,
I received error 3, like other users, and I couldn’t discover the problem. Finally, I ignored the error, by changing the code from (err == 0) to (err == 0 || err == 3) in two places. Then the code ran successfully and saved new PDF files with all the data. I wish I knew what error 3 was and why I received it. But I seem to be okay ignoring it. What do you think? The error codes use the wrong sign in the Acrobat DC SDK Documentation, you say. But the numbers are different, too. You say error 3 means “Warning: User Cancelled File Select,” which is error -1 in the documentation. While trying the code, I found a few requirements that it might help users to know:
* The PDF form must be a Reader-extended PDF so that it can be saved. That is, click File > Save As Other > Reader Extended PDF > Enable More Tools (includes form fill-in and save).
* Dates must be formatted the same in the tab-delimited text file as in the PDF form fields. I chose the format m/d/yy.
* The field used for the file name must a field in the PDF form (not only in the text file). Also, you suggested to a user to add this.resetForm(); to the code. That suggestion is so important it belongs in the sample code, I think. I added it on a new line after the “while” line. Thanks for generously sharing this helpful code.

I had to learn about folder level scripts and trusted functions. After i better understood those concepts i was able to run the script. The script below copies a file called “master.pdf” and each copy of the file is populated using the file “liste.txt”. The following script is located in the following folder:
“C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\Javascripts” var Trusted_Copie=app.trustedFunction(function() app.beginPriv();
var fileName = “./liste.txt”;
var outputDir = “./”;
var otherDoc=app.openDoc(“master.pdf”,this); var err = 0;
var idx = 0;
while (err == 0) otherDoc.resetForm();
err=otherDoc.importTextData(fileName,idx);
if (err == -1)
app.alert(“Warning: User Cancelled File Select”);
else if (err == -2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == -3)
this.closeDoc(); //fermeture de “programme copie.pdf”
else if (err == 1)
app.alert(“Error: Cannot Open File”);
else if (err == 2)
app.alert(“Error: Cannot Load Data”);
else if (err == 3)
app.alert(“Error: Invalid Row”);
else if (err == 0) otherDoc.saveAs(outputDir +otherDoc.getField(“Nom du fichier”).value+”.pdf”);
idx++;
>
>
otherDoc.closeDoc(true); //fermeture de “master.pdf” (fichier vide en raison de la commande resetForm())
app.endPriv();
>); I call this function from a pdf file that has one button. The button calls the script on the mouse up event this way: Trusted_Copie(); Thanks for reply. Greetings from Quebec city.

How do I save the script as a button that I can execute?
How do I change this.saveAs(outputDir + this.getField(“Text1”) so that it pronmpts for file name?

How do I save the above script as a button in Acrobat that I can execute?
I am thinking of saving it as a js file in Acrobat javascript folder but do not know how to add it to the Add-on Tools menu for 3rd party javascript

Karl, Thank you for managing this string, and your help. I have ZERO experience with programming, but i have been tasked with auto-batching a thousand fillable forms. No one here knows how to do this, either. Here is my code, to date. the console says there is a syntax error, in the output line. Any help you can offer is very appreciated. Pete /* Batch Import */
var fileName = “/c/Users/pwd/ Documents/Non-Use data 10-12-2016.txt”;
var outputDir = “/c/Users/pwd/Desktop/”;
var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); // imports the next record
if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(outputDir + this.getField(“WDID”).value + “_” + this.getField(“Structure_Name”).value + “.pdf”); // saves the file
idx++;
>
>

here is the error. var outputDir = “/c/Users/pwd/Desktop/”;
SyntaxError: illegal character
1:Console:Exec
undefined

Karl, This iteration got me past the syntax error, but now it is telling me “error: cannot open file” Thanks, again, Pete var fileName = ‘/c/Users/pwd/ Documents/Non-Use data 10-12-2016.txt’;
var outputDir = ‘/c/Users/pwd/Desktop/’;
var err = 0;
var idx = 0; while (err === 0) err = this.importTextData(fileName, idx); // imports the next record
if (err == -1) app.alert(‘Error: Cannot Open File’);
>
else if (err == -2) app.alert(‘Error: Cannot Load Data’);
>
else if (err == 1) app.alert(‘Warning: Missing Data’);
>
else if (err == 2) app.alert(‘Warning: User Cancelled Row Select’);
>
else if (err == 3) app.alert(‘Warning: User Cancelled File Select’);
>
else if (err === 0) var batch = new batch ();
saveAs(this.getField(WDID).value , this.getField(WDID).value +’_’+ this.getField(Structure_Name).value+’.pdf’);
>
>

Pete, it looks like you are using the wrong kind of single quote around your two paths. Use either the normal double-quotes (as you do towards the end of your script), or use the single quote that is on the same key (that is on a US keyboard) as the double quote.

Karl, Thank you for responding. I have made that change, but now the debugger is telling me: Warning: User Cancelled File Select. Not sure what to do now. Thank you, Thank you, Thank you,
Pete

Pete, that’s usually a sign of a wrong path to the text file. Make sure that the path is correct. One way to do that is by calling importTextData() without any arguments. Acrobat will then prompt you to select the text file and select one record from it. Does that correctly import the data into your document?

Hey, Sorry to keep bugging you. Thought I had it, but I don’t. Yes that single field came through, just fine. but the error persists. Out of ideas. I have triple checked the field names and the directories. Just not sure what I’m doing wrong. Does it require that all the fields in the form have a corresponding field column in the spreadsheet? Beyond that, I’m lost. Would it be at all possible to send you the form and the spreadsheet, so you could take a look?

Pete, yes, the number of columns in the text file and the column names has to match exactly the number of fields and field names in your PDF file.

Karl, OK, I removed everything from both the Form and the Dataset, except the two fields that are for the naming scheme. Same error. Thanks

Karl, Just wanted to let you know, that I got it to work! It was in the field names. Thank you so much for all your help. Have a great rest of your day,
Pete

Karl,
Thank you so much for this information. I was able to get my file to import data manually but unable to get the action to work . This is my code:
// specify the filename of the data file
var fileName = “/Users/cianacloud/desktop/test.txt”; // the tab delimited text file containing the data
var outputDir = “/Users/cianacloud/desktop/specsheets”; // make sure this ends with a ‘/’ var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); // imports the next record if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(outputDir + this.getField(“Code”).value + “_” + “.pdf”); // saves the file
idx++;
>
> This is what the debugger is telling me:
SyntaxError: missing ( before formal parameters
1:
NotAllowedError: Security settings prevent access to this property or method.
Doc.importTextData:8:Doc undefined:Close ReferenceError: text is not defined
8:Doc:Did Save
ReferenceError: text is not defined
8:Document-Actions:Document Did Save
NotAllowedError: Security settings prevent access to this property or method.
Doc.importTextData:8:Document-Actions:Document Did Save

Ciana, the first thing that sticks out is that your path does not end with a ‘/’ as indicated by the comment. The security error is very likely due to the context from which you call this code. You can only call it from a privileged context. There are other error messages that do not seem to be related to this script, so check all your other scripts that may be running for errors.

Hi Karl,
I was able to get your script to work and successfully complete the task. But when I check on the page, at the last field of every page, it shown the field name. The last field on every page should have an empty cell. When I manually import the data, this error did not occur.
Can you think of anything that I might did wrong? Thanks. Below is the script that I used: // specify the filename of the data file
var fileName = “/c/data/data.txt”; // the tab delimited text file containing the data
var outputDir = “/c/data/”; // make sure this ends with a ‘/’ var err = 0;
var idx = 0; this.resetForm(); while (err == 0) err = this.importTextData(fileName, idx); // imports the next record if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(outputDir + this.getField(“LName”).value + “_” + this.getField(“FName”).value + “.pdf”); // saves the file
idx++;
>
>

Hello Karl, Your blog continues to blow me away in terms of helpful information for Acrobat Scripting. Thank you! I am working on a document where I am importing hundreds of rows of data into the form and saving the PDF. This is no problem. I have tested up to a record count of 750 rows, and my script will chug through the records without issue. The main problem has been coming in when I want to *flatten* the PDF form before saving it. So what I’ve been doing is saving it in one directory, then opening the freshly-saved document, flattening it, and then saving it somewhere new: $this.saveAs( <
cPath: outputSaveDir + outputDocName + “.pdf”,
bPromptToOverwrite: true
>); // Open the file, flatten it, save it, close it.
var flattenedDoc = app.openDoc( <
cPath: outputSaveDir + outputDocName + “.pdf”,
bHidden: true,
>);
flattenedDoc.flattenPages();
flattenedDoc.saveAs( <
cPath: flattenedDocsDir + flattenedDoc.documentFileName + ” [FLATTENED].pdf”,
bPromptToOverwrite: true
>); // Close all open docs every iteration
var d = app.activeDocs;
for(var openDocCounter in d) <
d[openDocCounter].closeDoc(true);
> Astonishingly, this all works…
Until it gets to record 51. It will only run through 50 iterations of the loop before it says: NotAllowedError: Security settings prevent access to this property or method.
App.openDoc:35:Console undefined:Exec It seems this can only be done 50 times? Is there any way around this? To view the full JS I am feeding Acrobat, please see: https://gist.github.com/ethanbeyer/1f058aeb7979f94d5f8bbb2f9ce8690c Thank you so much for your time!

Thanks, Karl! I’m thinking you’re right – it’s definitely the same issue. Frustrated that Adobe changes things like this, doesn’t document it, and then provides a really off-the-wall workaround, but hey – what can you do? Thanks again.

Jen, yes that can be done, but you have to work around one problem: All form fields with the same name in a PDF document will contain the same data. This means that if you add a new page with form fields, these fields will show the same content as the fields you’ve had on your first page in the document, and when you change the information on the just added page, the information on the first page changes as well (and this will be the same regardless of how many pages you’ve already added, all will contain the same information). This can be dealt with by e.g. using templates with form fields that change their names when the template gets spawned. You just have to figure out what the field names are on the last page added, and modify them. You can also flatten your document after you add the information is added, this way you will not have any interactive form fields in the document anymore.

Karl, thanks for this bit of code, it’s been a huge help. Everything is working great. One problem: i need to import boolean data, as the form in question uses multiple checkboxes. What entries need to be made on the excel spreadsheet to fill these checkboxes? 0,1? Yes, No? Null? Checked, Unchecked? Also this form uses some sets of check boxes that are mutually exclusive. Does this change how the input or output is configured? Thank you again!

Alexander, the default values for a checkbox are “Off” and “Yes” – you can override the selected value, but not the deselected value. The best way to figure out what exactly your form is exporting is to save a form with sample data to a tab delimited text file, and then analyze the file. For a checkbox group (checkboxes share the same name, but different export values), you will need to use the export values for the individual options. When you set the group to “Off”, none of the checkboxes should be selected.

Karl, First off, thank you for posting this code as I know nothing about javascript and didn’t know where to start. It all works perfectly from what I can tell, but I do have one question. Well two, one easy, one hard, or maybe they’re both easy or hard? Anyway, First question: is there a way to get this tool to fire from regular Acrobat Reader or do you have to have a Pro license to execute it? Second question: is there a way to have the user define the location of the text document as opposed to having it hardcoded? I did comment out the variable fileName and left this.importTextData() blank, which prompted me to navigate to the .txt file and select a record, but then it didnt’ do anything from there, just kept looping back to that same dialog and asking me to select a record, which basically defeats the purpose. Any thoughts on that would be much appreciated, but it works for now and that’s good enough for now, so I thank you.

Chris, you need Adobe Acrobat for this, it will not work with the free Adobe Reader. You don’t need “Pro”, if you have “Standard” it will work as well. You can use the app.browseForDoc() method to let the user select the file: http://help.adobe.com/en_US/acrobat/acrobat_dc_sdk/2015/HTMLHelp/index.html#t=Acro12_MasterBook%2FJS_API_AcroJS%2Fapp_methods.htm%23TOC_browseForDocbc-7&rhtocid=_6_1_8_6_1_6

Hey Karl,
Do I need to edit the following code?: err = this.importTextData(fileName, idx); // imports the next record I am running the code in JavaScript and it keep getting “Warning: User Cancelled File Select”

Coy, check your data file. These problems are usually caused by problems with the data: Too many columns, not enough columns, not the right columns… The error codes are often misleading. Try to import the data manually and see if you get an error message or any other indication of what’s wrong.

Greetings Karl, I’ve read through all of the above context and cannot figure out what my issue is. I’m using Adobe X Pro and Excel 2010. The script I’m using: /* Address Change */ var fileName = “/c/Users/dmanley/Desktop/PDF Test Folder/Address Test File.txt”;
var outputDir = “/c/Users/dmanley/Desktop/PDF Test Folder/ Change of Address Form (copy)/”;
var err = 0;
var idx = 0; while (err == 0) err = this.importTextData(fileName,idx); if (err == 1)
app.alert(“Error: Cannot Open File”);
else if (err == 2)
app.alert(“Error: Cannot Load Data”);
else if (err == -1)
app.alert(“Warning: Missing Data”);
else if (err == -2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == -3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs“/c/Users/dmanley/Desktop/PDF Test Folder/Change of Address Form (copy)/”; + this.getField(“Location”).value + “_” + this.getField(“Location”).value + “.pdf”);
>
> And I get a SyntaxError: illegal character 3: at line 4. Also I get an Error: Cannot Open File when I Run the script. After I Run the script it looks like this: // specify the filename of the data file
var fileName = “/Users/username/tmp/data.txt”; // the tab delimited text file containing the data
var outputDir = “/Users/username/tmp/”; // make sure this ends with a ‘/’ var err = 0;
var idx = 0; while (err == 0) err = this.importTextData(fileName, idx); // imports the next record if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(outputDir + this.getField(“Text1”).value + “_” + this.getField(“Text2”).value + “.pdf”); // saves the file
idx++;
>
> Not sure why the fileName and outputDir revert to tmp.

Dennis, when you post a script, it converts the straight quotes ” to smart quotes, which will get rejected by the JavaScript interpreter. The first thing I would check is that all quotes are actually using straight quotes. There is an extra “>” at the end of your code, and there is a problem with the “saveAs()” syntax in your first code sample. You should change it to this:
this.saveAs("/c/Users/dmanley/Desktop/PDF Test Folder/Change of Address Form (copy)/" + this.getField("Location").value + "_" + this.getField("Location").value + ".pdf");
The WordPress software may break this into multiple lines, the whole thing should be in one line (unless you know how to break JavaScript statements into multiple lines).

Thank you Karl. After setting this up using your code above, the one line and correcting the “>”, I have an engine.

Hi Karl, I am trying to use the js and it works for one record, then nothing…no error just stops. Does it matter the order of the columns in the text file? Does each cell have to have digit? (if so why would it not run until it reached the blank cell then error or stop? Where is the clear form input supposed to be? Obviously my js runs to the point of saving the file in the right directory with the right file name then stops…. thanks, Steve

Steve, the order of the columns is not important, but you have to make sure that you have the same number of columns as you have fields, and that the columns are named the same as the fields. What I often do is fill out a form, and then manually export the data as a text file and compare that to the data in my data file that fails to import correctly. The input file is a “tab separated text file”, this means that individual values are separated by a tab character. This allows you to have an empty field: Just don’t have anything between the tab character that brought you to the column in question and the tab character that jumps to the next field. Do you get any errors on the JavaScript console? Errors are not just reported via the popup message, but also (and otherwise quietly) printed to the JavaScript console. You can check these messages by bringing up the console dialog via Ctrl-J or Cmd-J.

Batch Form Importing Acrobat DC Hi I’m trying to import info from excel to a pdf filler I’m making a 1095c. // specify the filename of the data file
var fileName = “/Server3/Home/d.rabinowicz/Desktop/1095c/0.txt”; // the tab delimited text file containing the data
var outputDir = “/Server3/Home/d.rabinowicz/Desktop/1095c/pdffiles/”; // make sure this ends with a ‘/’ var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); // imports the next record if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(outputDir + this.getField(“1 Employee Name”).value + “.pdf”); // saves the file
idx++;
>
>
I just need to get around this “Warning: User Cancelled File Select” thing.

Dovid, this problem can usually be fixed by making sure that you have ‘clean’ data. This means that you have an identical number of data columns in your data file as you have fields in your PDF file, and that the column names use the same names as the fields. Look for empty data columns, or mismatching names.

Hi Karl, I found my error it as in the text file, but now I have a different issue its only importing the one name and its not saving it or going to the next name on my list i have 200 names on my list.

Dovid, check the JavaScript console for errors. If it’s stopping while processing the first record, chances are that you are dealing with an exception. You can bring up the JS console using Ctrl-J or Cmd-J

Thank you Karl but I mean multiple folders like row one goes to row one’s folder then row two to row two’s folder and so on. Instead of be saved in one directory. Thank you,
John Benson

John, you can certainly do that as well, but you would have to figure out where to get the folder name from. One option would be to have a hidden field in the PDF form that you use for that purpose, and when you load the data into the PDF file, that field would get set to the folder you want to use. You then just have to get that information from the form and assemble the output path.

hi,Karl.I have tried the JS code to import my text data to pdf and it does work! the only thing is my data is Chinese so when it import to pdf, it caused some garbled text.I just wondering that do you know how to fix this problem.

Huang, unfortunately I don’t have any experience with Chinese and forms. I would fill out a copy of the form, and then export the data to a text file, analyze the text file to find out what encoding is used for the text, then try to import the same – unmodified – text file again into a blank copy of the form to make sure that Acrobat is able to handle the import. If that’s the case, then you need to find out what encoding Excel uses to export your text file and then come up with a mapping between the two encodings.

Hi Karl,
Many thanks for all informations.
I’m lost… :/
I would like merging data (.txt tab) into a pdf form (created with livecycle at best or Acrobat) without user action (only open the PDF) and without putting the .js in a “special” Acrobat folder.
I use the importTextData javascript function, but with the file parameter it doesn’t work because of security ! This action on a button without file parameter work well, but asking the user to select the data file 🙁
Many thank by advance for your help,
Regards,
Bada

Bada, as you found out, you cannot do this from within a PDF document. There always needs to be some level of user interaction.

Karl I have about 2,118 Business records I am dealing with and I ran the script. Everything worked perfect. However I have 15 records with business names that start with a Number and it only auto filled those 15 records. My questions, how come its not populating for the businesses that begin with a letter?

Chris, without seeing the form, it’s impossible to say what’s going on. Are you getting any error on the JavaScript console?

Karl, this is the script I used. I’m not sure how to rung the JavaScript Console after doing CTRL+J // Copy of 2016 K-1 Final_Merge.txt var fileName =”C:\Users\chris\Desktop\K1 2016 Project\Copy of 2016 K-1 Final_Merge.txt”; var outputDir = “C:\Users\chris\Desktop\K1 2016 Project\Completed K1s/”; var err = 0;
var idx = 0; while (err == 0) err = this.importTextData(“Copy of 2016 K-1 Final_Merge.txt”,idx); // imports the next record if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(“Completed K1s” + this.getField(“Part II Box G Line 1”).value + “_” + this.getField(“Part II Box F”).value + “.pdf”); // saves the file
idx++;
>
>

Chris, your first problem is that you are not using the device independent paths that Acrobat requires. You need to convert your paths so that they look like this:
var fileName ="/C/Users/chris/Desktop/K1 2016 Project/Copy of 2016 K-1 Final_Merge.txt";
I don’t know what you mean by “how to rung the JavaScript Console”.

Karl,
I can’t seem to figure out why I’m getting “SyntaxError: umterminated string literal 2: at line 3. Here is what i’m using // specify the filename of the data file
var fileName = “/Users/cathleenmcdonald/Desktop/JHO-111-Merged/Copy of jho addresses-NEW.txt;
var outputDir = “/Users/cathleenmcdonald/Desktop/merged/”;
var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); // imports the next record if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(outputDir + this.getField(“Text1”).value + “_” + this.getField(“Text2”).value + “.pdf”); // saves the file
idx++;
>
>

Cathleen, you need to end the string with a ” character. Check the end of the line that starts with var fileName – it needs to end with .txt"; . It’s possible that you also broke that line into two, which you need to fix as well.

This script works for me and is incredibly useful. But the one feature I can’t figure out are checkbox fields. They appear as one of the variables in the .txt file, however, I cannot figure out what the cell needs to indicate to cause the box to be checked or unchecked. I attempted exporting the pdf file to txt with the boxes checked and unchecked to see if that would give me the answer, but it seems to only preserve what the original PDF file has selected. Maybe it’s impossible. Upon export, the file does give “on” “Off” “Yes” as entries. Thanks for this posting.

Mikey, a checkbox always reports “Off” for the unchecked state. The default for the checked state is “Yes”, but that can be changed. You will need to know what the different checkboxes are using for the on state.

Hello Karl,
I have lots of data fields that I don’t wish to autopopulate. I keep getting error 3 and per your other responses, I think it is due to more fields in the PDF than in the txt file. Why does this have to match? Is there a way around it so that it will populate AND save? I’ve gotten it to the point where it will populate the fields that I want, but it won’t save. Thank you

Mark, as to the ‘why’, you need to ask Adobe. All I can add to the discussion is that the fields and the columns need to match when you use the import function. You can add hidden dummy fields just so that you have a correct match. Another option is to read the text file directly, and then parse in JavaScript and extract only those columns you want to use. That is a lot more involved, and if you need help with that, I would have to direct you to my consulting services. If the function fills in all the fields, but does not save, then there is an exception before the save function is called. You will have to find out exactly what that exception is and why it occurs, then fix that problem and the save function should get called again.

Hi Karl, first of all thanks for all the usefull info. I’m getting the following error:
SyntaxError: missing, before statement3: at line 4 When using the code: /* Import Data */
var fileName = “C:\Users\81247976\Documents\Documents\Contracts 2017\Auto Del\Data.txt”;
var outputDir = “C:\Users\81247976\Documents\Documents\Contracts 2017\Auto Del\”; var err = 0;
var idx = 0; while (err == 0) err = this.importTextData(fileName, idx); if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
else if (err == 1)
app.alert(“Warning: Missing Data”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 0) this.saveAs(“C:\Users\81247976\Documents\Documents\Contracts 2017\Auto Del\” + this.getField(“Klant N° (BAT)”).value + “_” +
this.getField(“Naam winkel”).value + “.pdf”);
idx++;
>
> Any idea how I can solve this?

Steven, you need to use “platform independent paths” for the file names you are using in your script. See here for more information: https://acrobatusers.com/tutorials/file-paths-acrobat-javascript

Hello Karl,
Ok, so here is the script. It DOES populate the appropriate fields but gives me that #3 error and does not save. For clarification, I have more fields in my PDF than my txt file, but all fields in my txt file are in the PDF. /* Autofill OPPE Records */
// specify the filename of the data file
var fileName = “/C/Userx/xxx/xxx/xxx/NeuroTestDatafile.txt”;
var outputDir = “/C/Userx/xxxxx/xxx/xxx/OPPEAutoPopTest/”;
var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); // imports the next record
if (err == -1)
app.alert(“Error: Cannot Open File”); else if (err == -2)
app.alert(“Error: Cannot Load Data”); else if (err == 1)
app.alert(“Warning: Missing Data”); else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”); else if (err == 3)
app.alert(“Warning: User Cancelled File Select”); else if (err == 0)
>
>

>
> Mark A Montgomery says:

Also, I am using Acrobat Pro XI and this is what the js console shows:
ReferenceError: Percent is not defined
1:Field:Validate
UnsupportedValueError: Value is unsupported. ===> Parameter cPath.
Doc.saveAs:23:Batch undefined:Exec

Karl Heinz Kremer says:

Mark, there are two different issues: The #3 error is due to a data mismatch between your data file and the PDF. This was actually wrong in my code, and I just corrected that. The error you’ve listed now is different: For some reason the saveAs function is not happy with the path you are providing. You could add the following line just before saveAs() to see what is actually saved:

app.alert(outputDir + this.getField("Provider").value + ".pdf");

This should then display the path that the saveAs() method is using as it’s file name. Look for anything odd.

Mark A Montgomery says:

Karl, this.saveAs(outputDir + this.getField(“Text1”).value + “_” + this.getField(“Text2”).value + “.pdf”); // saves the file idx++; It looks like the idx++; is missing after the “.pdf”); which it is not but it doesn’t show the ‘enter’ that separates it from the ‘saves the file’. This is my new script and it is important to point out a couple things that us novices need to be careful of.
1) that the this.resetForm needs to go after the idx++; in order for it to populate the form first. I tried it in 3 separate locations and it broke the js. 2) when copying and pasting your script, the idx++; actually flows with the “saves the file” unless there is an enter after “file”. This script just saved my office about 150 hrs per year. Thank you so much, Karl. (I’ll post this on your consulting blog too.) Here is the full code. /* Autofill OPPE Records */
// specify the filename of the data file
var fileName = “/c/Users/XXX/Desktop/Temp Files/NeuroTestDatafile.txt”;
var outputDir = “/c/Users/XXX/Desktop/Temp Files/OPPEAutoPopTest/”; var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); // imports the next record if (err == -1)
app.alert(“Error: Cannot Open File”); else if (err == -2)
app.alert(“Error: Cannot Load Data”); else if (err == 1)
app.alert(“Warning: User Cancelled File Select”); else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”); else if (err == 3)
app.alert(“Warning: Missing Data”); else if (err == 0) this.saveAs(outputDir + this.getField(“Provider”).value + “.pdf”);idx++; // saves the file idx++;
this.resetForm();
>
>

Todd Williams says:

Greeting, and thank you for this amazing post and for all the questions and answers from all participants! It has been very useful. Is there a way to modify this approach so that instead of saving each time a new record is read in, can it just create a new page in the existing pdf, thus resulting in a 200 page pdf, assuming the form is a single page, rather than 200 single page pdfs? Thank you in advance for help that can be provided!

Karl Heinz Kremer says:

Todd, yes, that can be done. I would use a page template and then instantiate a new template for each record, and after filling it, flatten the new page (otherwise the information you assign to one field will be shared across all fields on all pages that share the same name). When you spawn the page template, just make sure that the fields are not getting renamed.

Rebecca Pine says:

I’m using this script and keep getting this error:
UnsupportedValueError: Value is unsupported. ===> Parameter cPath.
Doc.saveAs:27:Batch undefined:Exec My data file is saved in the proper format. /* Autofill OPPE Records */
// specify the filename of the data file
var fileName = “/c/Incident Intake Form/Incident Intake Form.txt”;// the tab delimited text file containing the data
var outputDir = “/c/Incident Intake Form/”; // make sure this ends with a ‘/’ var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); // imports the next record if (err == -1)
app.alert(“Error: Cannot Open File”); else if (err == -2)
app.alert(“Error: Cannot Load Data”); else if (err == 1)
app.alert(“Warning: User Cancelled File Select”); else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”); else if (err == 3)
app.alert(“Warning: Missing Data”); else if (err == 0) this.saveAs(outputDir + this.getField(“PAYROLL ID”).value + “_” + this.getField(“EMPLOYEE”).value + “.pdf”);idx++ // saves the file idx++;
this.resetForm();
>
>

Karl Heinz Kremer says:

Rebecca, from what context are you calling this script? This error is often associated with a filename that contains characters that are either not allowed in filenames, or have a specific meaning. One thing you can do is add this line before the this.saveAs() line:

app.alert(outputDir + this.getField(“PAYROLL ID”).value + “_” + this.getField(“EMPLOYEE”).value + “.pdf”);

This will display a dialog box with the path that your script will try to use to save the file. Is there anything odd about this filename?

Rebecca Pine says: i figured it out. i can’t use the name field because there is a comma. Thank you so much. Karl Heinz Kremer says:

Rebecca, great! You can clean up the string using Javascript and e.g. replacing anything that is not allowed with either nothing, or an underscore. This way, it does not matter what the user types in the form fields, you will always be able to save the file.

Henry says:

I just wanted to extend my thanks for your work on this. I had a couple of bumps (related to my data sheet) but got it working and it’s amazing! Thank you again.

Karl Heinz Kremer says: Henry, glad that it worked out. Pilid says:

I am running the below script, while calling it through a button on mouse up, It throws me an error as below NotAllowedError: Security settings prevent access to this property or method.
Doc.importTextData:6:AcroForm:Button1:Annot1:MouseUp:Action1 Please help var fileName = “C:\Users\Dils\Downloads\TestDocument tabdelimited.txt”;
var outputDir = “C:\Users\Dils\Downloads\Tested\”;
var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx);
if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
// else if (err == -3)
// We are not reporting this error because it does
// indicate the end of our data table: We’ve exhausted
// all rows in the data file and therefore are done with
// processing the file. Time to exit this loop.
// app.alert(“Error: Invalid Row”);
else if (err == 1)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: Missing Data”);
else if (err == 0) this.saveAs(outputDir + this.getField(“FIRST_NAME”).value + “_” + this.getField(“LAST_NAME”).value + “.pdf”);
idx++;
>
>

Karl Heinz Kremer says:

Pilid, there are at least two problems in your approach: First and most importantly, you cannot run the import function from a button. You can see that when you lookup the documentation for Doc.importTextData(): http://help.adobe.com/en_US/acrobat/acrobat_dc_sdk/2015/HTMLHelp/index.html#t=Acro12_MasterBook%2FJS_API_AcroJS%2FDoc_methods.htm%23TOC_importTextDatabc-71&rhtocid=_6_1_8_23_1_70 – it clearly states that “If cPath is specified, this method can only be executed during batch and console events.”. Also, the syntax for your paths is wrong. You need to use the device independent format that I used in my script (again, see the documentation for more information).

Pilid says: Could you please help me, how to use the script ? From where to Run ? Karl Heinz Kremer says:

You can only run it from a batch or a console event. That means for example you can run as an “Action”, or as a “Custom Command” in Acrobat DC. You can also create a trusted function (see here for more information: https://acrobatusers.com/tutorials/using_trusted_functions ), and call the actual import function in a privileged context. As far as the path goes, you need to convert your Windows path into a device independent path. “C:\Directory\file.ext” will become “/C/Directory/file.ext”. It is possible that Acrobat is throwing the “not allowed” error only because you have the path wrong, so that should be the first thing to fix. The two lines that define the path variables need to look like this:
var fileName = “/C/Users/Dils/Downloads/TestDocument tabdelimited.txt”;
var outputDir = “/C/Users/Dils/Downloads/Tested/”;
However, your initial comment suggests that you are calling the process from a “mouse up” event, which suggests a non-privileged context.

Pilid says: Thank you. its working now. Thank you so much for the help. Pilid says:

Hello Karl, I am Glad that the script works very well, I am able to create the multiple pdfs after populating the data from the txt file. however, If there any possibility that we can password protect each sheet automatically ? I want to pull the password from the txt file and protect the pdf. Is this possible ? Your answer solution will be highly appreciated.

Karl Heinz Kremer says:

Pilid, no, that is not possible. You can assign a password via JavaScript, but for that to work you have to create a security policy (which contains the password). You can then apply such a security policy, but that would only apply the password that you’ve previously used to create the security policy. JavaScript cannot assign passwords on it’s on. So, if you are OK with using the same password for all documents you want to protect, you can create one security policy and then apply that using the Doc.encryptUsingPolicy() method.

Alexandra Koller says:

Hi Karl! Your posts on your website are fantastic and I feel super close to figuring out this batch export from excel to PDF – but I am still out of my element. I’ve been asked to help merge exported data from excel to a PDF certificate for a client of ours. I followed your article and created a custom command. I copied and pasted your command, with your tweaks for my Windows version: // Cert_Spreadsheet_excel
var fileName = “/c/temp/data.txt”; // the tab delimited text file containing the data
var outputDir = “/c/temp/output/”; // make sure this ends with a ‘/’ var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); // imports the next record
if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
// else if (err == -3)
// We are not reporting this error because it does
// indicate the end of our data table: We’ve exhausted
// all rows in the data file and therefore are done with
// processing the file. Time to exit this loop.
// app.alert(“Error: Invalid Row”);
else if (err == 1)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: Missing Data”);
else if (err == 0) this.saveAs(outputDir + this.getField(“Text1”).value + “_” + this.getField(“Text2”).value + “.pdf”); // saves the file
idx++;
>
> However, when I went to the next step, to execute our custom command from the Action Wizard user interface, a box popped up and I got an “Error: Cannot Open File.” Am I supposed to be editing the custom command to fit the file name? How do I know how to properly label/code the file name? Right now the file is on my desktop saved as a tab delimited text file, and I have a windows operating system. With my adobe PDF certificate only having six fields to fill from Excel, do I need a custom command? Should I just do an Action in adobe? I’m so unfamiliar with how to work Adobe, let alone coding. Any guidance you can provide would be so so helpful and appreciated. Thanks so much in advance!

Karl Heinz Kremer says:

Alexandra, converting your Windows path to a “device independent path” is pretty simple. Let’s say your file is on your desktop, and Windows Explorer shows the following path when you click into the path field: “C:\Users\UserName\Desktop\data.txt” (the UserName will of course be your specific user name on your computer). You can then just replace every “\” with a “/” and replace the drive letter at the beginning with “/C”, which will give you “/C/Users/UserName/Desktop/data.txt” – that should be all that’s necessary to get past this error message. As far as using an Action vs. a Custom Command goes, an Action is something that you usually create when you want to run the same processing steps on more than one file. In the past (before Acrobat DC), there were no custom commands, so even if you wanted to run it just on one file, you had to create an Action. In Acrobat DC, when I know that I always just want to process one file, I create a custom command, if I need to process more than one file in one “batch”, I create an Action. Hope that helps.

Meredith says:

Hi Karl,
Thanks for putting this together. I do not have much programming experience, but this seemed pretty straight forward. I used your code above and updated for my files. However, the data is only coming in for the first record and then I get the missing data error. I am wondering if its because some of my cells in the data are blank. Here is my code in case there is something I am missing:
// specify the filename of the data file
var fileName = “/c/Users/millme/Desktop/test list.txt”; // the tab delimited text file containing the data
var outputDir = “/c/Users/millme/Desktop/”; // make sure this ends with a ‘/’ var err = 0;
var idx = 0;
while (err == 0) err = this.importTextData(fileName, idx); // imports the next record
if (err == -1)
app.alert(“Error: Cannot Open File”);
else if (err == -2)
app.alert(“Error: Cannot Load Data”);
// else if (err == -3)
// We are not reporting this error because it does
// indicate the end of our data table: We’ve exhausted
// all rows in the data file and therefore are done with
// processing the file. Time to exit this loop.
// app.alert(“Error: Invalid Row”);
else if (err == 1)
app.alert(“Warning: User Cancelled File Select”);
else if (err == 2)
app.alert(“Warning: User Cancelled Row Select”);
else if (err == 3)
app.alert(“Warning: Missing Data”);
else if (err == 0) this.saveAs(outputDir + this.getField(“Client ID”).value + “.pdf”); // saves the file
idx++;
>
>

Nathan says:

Karl, I keep getting the Warning:Missing Data error even though I have saved a new file with all the column headers and filled the data in with a space of “off” for radio buttons. I understand that its meant ot have a column for every data field.

Nathan says:

Hi Karl, I have been working on the above code all day now and i have it failing at Warning Missing data. I have been right through and checked all the columns to the enth degree and cant get ti to work. Any tips on where to go? I was also hoping i could make this a button in the actualy PDF also, is that possible?

Karl Heinz Kremer says:

Nathan, this error is caused by a mismatch between columns in the data file and the fields – either something is missing, or not named correctly. You cannot call this function from a button without installing a folder level script first. See the documentation for more information – especially the part about a privileged context: http://help.adobe.com/en_US/acrobat/acrobat_dc_sdk/2015/HTMLHelp/index.html#t=Acro12_MasterBook%2FJS_API_AcroJS%2FDoc_methods.htm%23TOC_importTextDatabc-71&rhtocid=_6_1_8_23_1_70

Karl Heinz Kremer says:

Meredith, this is usually caused by a mismatch between the columns in your data file and the fields in the form. You need the same amount of columns as you have fields, and the column names need to match the field names exactly.

Nathan says:

Hi Karl, Yeah, I found out that i was referncing the wrong column header when saving each file. Thanks for this, its a great help. I now have an issue where I am using Adobe Livecycle Designer ES4 and now I cannot use this for any of the forms i build as the option to import using a txt file is removed. I also get the error of “ns1:datanode” in the txt file. Any advice on this?

Marco says:

Hi Karl,
this script is fantastic!
Only one thing: should be possible send every file created to an email address specified into data.txt file? Thanks indeed! Ciao

Karl Heinz Kremer says:

Marco, take a look at this function in the API documentation: http://help.adobe.com/en_US/acrobat/acrobat_dc_sdk/2015/HTMLHelp/index.html#t=Acro12_MasterBook%2FJS_API_AcroJS%2FDoc_methods.htm%23TOC_mailDocbc-74&rhtocid=_6_1_8_23_1_73 All you need to do is extract the data for the email address form a form field (which can be hidden), and then call this function. That should do the trick.

Karl Heinz Kremer says:

Nathan, LiveCycle Designer (and XFA forms) is a completely different beast, and most things that are very simple to do with AcroForms are a lot more complex (at least when it comes to automating these forms in Acrobat). If you need help with an XFA form, you may want to work with somebody who has done such work before. If you are interested in my professional services, feel free to get in touch via email with me – my email address is on the About page.

Aquoibon says:

Using Acrobat Pro 10.1, I never managed to get your script running.
Do you think it would be possible to get a script and a PDF file that work? Thank you.