We’ve talked about batch importing data from an Excel document into a PDF form before: http://khkonsulting.com/2015/10/batch-import-excel-data-into-pdf-forms/
Back then, the idea was that we would have a spreadsheet with rows of data, and every row would be imported into a new copy of the document (e.g. a mail merge type application).
What if we want to import rows of data into one document? Let’s assume, we have a table in our document with 20 rows, and we have a spreadsheet that has the same 20 rows, and we want to import that data. If we would use the method from above, the whole form data would have to be rewritten into a spreadsheet with just one row of data. Let’s do a simple example with a small table:
| Firstname | Lastname |
|---|---|
| John | Doe |
| Jane | Doe |
The data in the PDF file would be organized like this:
| Firstname_1 | Lastname_1 |
| Firstname_2 | Lastname_2 |
Our datafile would then look like this:
Firstname_1 Lastname_1 Firstname_2 Lastname_2 John Doe Jane Doe
The blank spaces between the entries in the list above are TAB characters – remember, we need a tab separated text file for this to work.
This is simple to do for a small files, but if you are dealing with 100 records of 10 fields each, we are talking about a pretty extensive row of data. It can certainly be done, especially if a VBA macro is used, but it’s not what I would do.
The good news is that this can still be done using JavaScript. The trick is to work with two documents: We of course start out with the document that we want to populate with data, but in addition to that, we are also using a temporary document that has one set of fields for each record we are going to read. This way, we can read one record at a time, and then copy that record into our final form. This temporary form will be created on the fly using JavaScript. And, because we will never actually look at that form, we don’t have to worry about placing the form fields in any meaningful way. In my example further down, I am placing all fields right on top of each other.
Let’s assume we want to create a sign-in sheet for an event, and we want to print the full name of each participant and the participant’s email address on the form:
You can download the documents from these links:
The following script can for example be used as an Action, or a custom command in Acrobat DC:
/* Import list data from tab delimited text file */
var dataFile = "/Users/username/data.txt"; // !!! CHANGE THIS !!!
// Create a temporary document and add the three text fields
// that correspond with the three columns in our data file.
var tmpDoc = app.newDoc();
tmpDoc.addField("Firstname", "text", 0, [0, 0, 100, 100]);
tmpDoc.addField("Lastname", "text", 0, [0, 0, 100, 100]);
tmpDoc.addField("Email", "text", 0, [0, 0, 100, 100]);
// Iterate over the data file and import the corresponding record
// from the data file and then copy that data to the corresponding
// data row.
var err = 0;
var idx = 0;
while (err == 0) {
err = tmpDoc.importTextData(dataFile, 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) {
// collect the data and add it to the "real" form
var name = tmpDoc.getField("Lastname").value + ",\n" +
tmpDoc.getField("Firstname").value;
var email = tmpDoc.getField("Email").value;
this.getField("Name" + (idx + 1)).value = name; // we need to adjust the index by one
this.getField("Email" + (idx + 1)).value = email;
if (idx == 19) {
// we can only process 20 records on each sheet
err = -99;
}
}
idx++;
}
// cleanup
tmpDoc.closeDoc(true);
Here is the sample file I’ve used as Excel Spreadsheet and as tab delimited text file (this is random data thanks to the “Fake Name Generator“.
There are a few potential problems when you are using this approach: First of all, the same limitations that apply to manually importing data apply here as well: You need to make sure that each column in your data file is represented by a field in your temporary file, and that the names match. There cannot be any extra fields either. And, in this particular case, you will have to make sure that you only import up to the same number of records that your document can actually handle. My document uses 20 records, so I am checking for that in my script. If you need continuation pages, you can certainly do that, it would make the script a bit more complex.
If you want to adapt this approach for your own solution, make sure that you add all required fields to your temporary document. In the example above, I am only using text fields, but the same technique can be used for other field types as well.
Let me know if this works for you.
Hello!
I don´t know what i’m doing wrong, i think a followed all the steps and Adobe says: Error: Cannot open file.
I have changed the path to “/Users/usuario/Desktop/Pruebas de Adobe/data.txt”
Guillermo, without having access to your system, it’s impossible to say what’s wrong. Is this on a Mac? If not, you need something like this: “/c/Users/usuario/Desktop/…” – The driver letter needs to be part of the path.
I have accomplished the merge with a single row of data however, my excel data is 400 rows long and I like a single PDF sheet for each row printed. I understand that the JavaScript needs to be added however, I am using Adobe Pro X and I’m not exactly sure where in Adobe Pro I will be including the JavaScript. Also, when I am prompted to select a row to merge, will this still be necessary and will it prompt itself to continue to the last row of data. Please forgive my lack of understand and your assistance is greatly appreciate.
Sharon, Adobe Acrobat X Pro does not have custom commands, so you would need a menu item to launch the script, or use an Action (created via the Action Wizard) that will execute your script. For a menu item, you would need to modify the script, but it should work without modifications as an Action. You may want to play around with just the script and the data from this blog post until you have it working, and then move to your own form and data.
var tmpDoc = app.newDoc();
What is app here..
Sumit, “app” is the application object from Acrobat’s JavaScript API. In order to understand how this is used, you will need to become familiar with the API documentation. Take a look here: http://help.adobe.com/en_US/acrobat/acrobat_dc_sdk/2015/HTMLHelp/index.html#t=Acro12_MasterBook%2FJS_Dev_Overview%2FJS_Dev_Overview.htm
app is undefined when i ran the script.
This is issue might be because i am not able to install AcroXIPIWizInstaller and Visual Studio App Wizard provided in the tools folder of adobe SDK.
It is showing an error that i can be installed on VS2008 / VS2013 and not support provided for VS2012(i am using 2012).
Can you provide some other way to achieve the same functionality?
Thanks in advance!!
You are mixing two different things: “app” is an object in the JavaScript API, Visual Studio and the plug-in wizard are required to create plug-ins for Acrobat. These are two completely different APIs, and you cannot mix and match features and objects between the two. What exactly are you trying to do, what is your script, and what is the error message you are receiving?
I have a data table returned from the code and i want to show that data in the Acrobat PDF file in table form. When i use the above script, i am getting error on line
app.newDoc() i.e. app is not defined.
Hi Karl,
Could you please guide me how i can achieve this.
Thanks in advance!!
What version of Acrobat are you using to run this script? In which context are you running the script?
Karl: (or anyone who can help)
Is it possible to import data from a CSV or Tab Delimited Text File and have it populate a Form 941 and Form Schedule B for the 941 from the IRS? The forms are in the same format for the duration of a year. If I’m trying to use Virtual Basic/Macros is there a way I could write a script to populate these fields? I have 50+ Companies I have to fill this form out. The data is already in individual CSV files for each company….However I have to manually copy and paste it into every field for over 50 companies! So time consuming. There must be a way if the data is already there.
Ted, these forms are LiveCycle Designer (or XFA) forms, and all bets are off regarding what you can import and how. I ran into a number of problems with government XFA forms over the years. If it is possible, take a look here for some guidance: https://answers.acrobatusers.com/Write-field-form-VB-q285867.aspx
Thank you very much for your tuto! I had this error when the js is executed:
NotAllowedError: La configuración de seguridad impide el acceso a esta propiedad o método.
App.newDoc:5:Document-Level:test
tinchox5, most problems are usually related to a mismatch in the columns in the data file vs. the fields in the document. The number has to be the same, and the column headers need to match the field names in the form.
Hi Karl,
Thank you for your reply. I am still having a question that seeks some suggestions.
In the link of “Batch-Import list data into pdf form”, I can understand that in the code part, it is to create a temporary document to store the data from txt file. But I can not find how to import that existed pdf “sign-in sheet” and how to generate that pdf form to be ready to download. I mean, how to get this generated pdf “sign-in sheet”, I am a little bit confused because I am still a beginner for javascript currently.
Really appreciate for your help.
Changjian
Changjian, when you look at the code, you will see the “this” keyword: This indicates that the code is operating on the currently open PDF file in Acrobat. So, you would open your form, and then run the script.
Hi Karl –
Thanks so much for making this post – it’s exactly what I need to get something that takes to take a team of 3 people 2 days to manually key in (and hopefully, if I solve my issue) that could take them 2 minutes instead.
My issue – I built a form based on this post, and tested it a few weeks ago in preparation for a deadline I have coming up, and with my test data everything worked fine – but then I built the form out more, to include fields that users will provide input on, based on the data I planned on importing that I’ll be providing to the users.
I keep getting the ‘missing data’ error now that I’m attempting to load actual data – and I think it’s because I have the fields that are meant to be blank in the form and therfore have no data in the dataset that’s feeding them.
What’s the best way to script in something that basically says ‘fill all the fields in that you have, and skip the fields that don’t exist in the dataset, and then move on to the next line of data for the next form’?
Ankit, if you want to use the built-in function to import data, then you have to make sure that every field in the PDF file is reflected in your data file. You cannot have missing columns. If you need a solution that works with a mismatch between fields and data, I would use e.g. a VBA program to remote control Acrobat and add only fill fields that need data. That is a completely different approach and not as simple as what I’ve demonstrated in this blog post.
Can you point me to any good resources on explaining how I can start to learn/see if I can figure something out that does this over the weekend? (is that a completely absurd idea? not sure.)
Also – If I enter all the fields as columns in my data – is it possible to just ‘write’ a blank value for the ones that I’d like to keep empty?