Filling the form fields of an Adobe PDF with data from an Excell spreadsheet.

I have the full version of Adobe Acrobat 5.0 on my PC and want to populate certain fields of a PDF form (U.S. Copyright Office Registration Form) with data from an MS-Excell spreadsheet (Title, Author, Year of Birth, Copyright Claimant, Address etc...). How do I transfer the data from the spreadsheet into the correct fields I need filled on the PDF?

Do you need to see the PDF form I am referring to? You can download   Form TX    at    http://www.copyright.gov/forms/

Robert Jones
TREN
Portland,OR
RobertJones53Asked:
Who is Participating?
 
Karl Heinz KremerCommented:
Take the following JavaScript and save it as C:\Program Files\Adobe\Acrobat 7.0\Acrobat\Javascripts\formtx.js. This will add a new menu item under the "Document" menu. Load your formtx.pdf file and then execute this JavaScript (after makeing the necessary changes to adjust it to your environment).


function processFormTX()
{

      // open the connection to the database
      try  
      {
            con = ADBC.newConnection("FORMTX");      // <-- change "FORMTX" to your data source name
            if (con == null) throw "Error connecting to data source";
            statement = con.newStatement();
            if (statement == null) throw "Error executing newStatement";
      }
      catch(e)
      {
            app.alert(e);
            return;
      }


      // process every record in the table

      try
      {
            if (statement.execute("Select * from formtx.csv"))      // <-- change formtx.csv to your table name
                  throw "Could not execute hte requested SQL command";
      }
      catch(e)
      {
            app.alert(e);
            return;
      }

      try
      {
            var i = 0;
            while (true)
            {
                  if (statement.nextRow())
                        throw "EOT";

                  row = statement.getRow();

                  // change the "TITLE", "AUTHOR2A", ... names and use the ones
                  // used in your database
                  this.getField("title").value = row.TITLE.value;
                  this.getField("author2a").value = row.AUTHOR2A.value;
                  this.getField("yearborn2a").value = row.YEARBORN2A.value;
                  this.getField("claimant").value = row.CLAIMANT.value;
                  this.getField("authorized").value = row.AUTHORIZED.value;
                  this.getField("mailingname").value = row.MAILINGNAME.value;
                  this.getField("cityzip").value = row.CITYZIP.value;

                  // this will write the new files to c:\temp
                  var fileName = "/c/temp/formtx_" + i++ + ".pdf";
                  this.saveAs(fileName);
            }
      }
      catch(e)
      {

      }
}




app.addMenuItem({
      cName: "Process FormTX Data",
      cParent: "Document",
      cExec: "processFormTX();",
      cEnable: "event.rc = (event.target != null);",
      nPos: 0
});
// end of JavaScript

0
 
nicholassolutionsCommented:
There's no way to just make the data from the Excel sheet populate the PDF. You can, however, copy and paste from the excel sheet to the PDF form, which should be pretty quick since the copyright form is short.

Just download it http://www.copyright.gov/forms/formtx.pdf
and open in Acrobat. When you move your mouse over the form fields, you should see it change to an "I-bar" cursosr like you have in MS word, etc. Just click in the field, and paste-in the data from Excel.
0
 
Karl Heinz KremerCommented:
nicholassolutions, this is not correct!

You can populate form fields in a PDF file automatically from a number of sources, Excel being one: You can create a VBA program in Excel that uses Acrobat's automation interface to fill inform fields. This would however be easier with Acrobat 6 or 7 Professional because it has a much improved JSObject interface.

Robert, is this just one form that you have to fill out, or is this a reoccurring job? If it's just a one time deal, it's not worth the effort, but if you need to do this again and again, how good are your VBA skills?

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
RobertJones53Author Commented:
Karl,

I have to do this task around 300-400 times per year and I do it in clusters of about 30. Being able to import data from a database would save an enormous amount of time.

My VBA skills? I don't even know what a VBA is. No programming knowledge at all.

Robert
0
 
Karl Heinz KremerCommented:
VBA is "Visual Basic for Applications", the extension language that's used by Excel, Word, ...

Does it have to be Excel? Can you upgrade to Acrobat 6 or 7? I would suggest to use MS Access and Acrobat 6 or 7 Pro, and then use Acrobat JavaScript and ADBC to get access to the data in the database. This way, I can walk you through the steps required. I don't use VBA myself, I dabble a little in plain VB, so if you are stuck with Excel and Acrobat 5 you need somebody with a strong VBA background to help you (and me).

If Access is not an option, a simple CSV file (text file) would also be possible.

We are doing all this on Windows I assume?
0
 
RobertJones53Author Commented:
I could put this in Access and will seek an upgrade to Adobe 7 later today or tomorrow. Can I upgrade to Adobe 7 at the Adobe site?
0
 
nicholassolutionsCommented:
Ah, somewhat stupidly I guess I assumed this was just a few forms that needed to get filled out. Sorry about that. Another option if you don't want to spend the money to upgrade is to use AutoIT scripting language (www.hiddensoft.com).
0
 
Karl Heinz KremerCommented:
I think you can buy the upgrade online (just make sure that you specify that you are upgrading from Acrobat 5 - they have two different upgrade options (same price however): One from Acrobat 6, and then one for older versions). For JavaScript development you would need the Professional version, Standard does not have the development environment (but it can run the program once it's finished).

Here is how this will work in general: You create the data in a database (either Access, MSSQL, Oracle, text file, ...), and make this database available as an ODBC data source (this is standard Windows "stuff"). Acrobat has an "ADBC" object, which is basically Adobe's implementation of an ODBC interface in JavaScript. This means that you can access the database, the tables and the individual data records in your data source from within Acrobat. All we need now is a program that opens the database, reads one data record at a time, opens the template PDF file (the form) and then fills in the form fields. Then the file is saved under a new name (you may want to add a field to the database that can be used to generate a file name if you don't already have this - e.g. a record number, a name or something similar). Once we are done with the whole table, the program will end and you will have all teh generated PDF files in one directory. If you don't anticipate that the forms need to be edited again, you can even "flatten" the forms, which means that the form field data is converted to static PDF content.

I'll provide some sample code later. I've answered a few questions here before about how to interface to a database with Acrobat's JavaScript.
0
 
Karl Heinz KremerCommented:
This question has some information about how to do this with JavaScript: http://experts-exchange.com/Web/Graphics/Adobe_Acrobat/Q_21367539.html

Do you know how to create an ODBC data source? If not, I can walk you through it.
I assume your PDF file already has form fields in it. With Acrobat, you can find out what the names of these form fields are. If you can give me two or three field names from the PDF file and the associated database field names, I can give you a a sample script that you can then extend to all fields.
0
 
Karl Heinz KremerCommented:
How are you doing with this problem?
0
 
RobertJones53Author Commented:
I could use some help setting up an ODBC data source. I guess that would be in MS Access? My PDF form is the Copyright Registration Form TX which you can download at http://www.copyright.gov/forms/formtx.pdf

I need to fill in the fields named: title, author2a, yearborn2a, claimant, authorized,
mailing name, street, cityzip.  

They are numbered on the form as 1, 2, 4, 8 and 9.
0
 
Karl Heinz KremerCommented:
I have to answer this in two parts: One now, and one tonight when I get home.

You create the ODBC data source within the Windows control panel:

Use the "Start" menu and select "Settings>Control Panel>Administrative Tools>Data Sources (ODBC)"
This will bring up the "ODBC Data Source Administrator". You can either create a user data source (can only be used by the user who created it), or a system wide data source (which can be used by everybody on the system). Select either the "User DSN" or the "System DSN" tab and click on the "Add" button. Select the driver you want to use. For MS Access you need the "Microsoft Access Driver (*.mdb)", other choices are e.g. dBase, Excel, and Text (which is pretty interesting because it allows you to use a CSV file and treat it as a database). I've never used the Excel driver, which means that I have no idea about how it works. You could probably use it as well, but you would be on your own to figure out how it works. It's possible that you see multiple entries in this table for every driver. I also have the Spanish and German versions to select from.

On the next dialog, you specify a name (this is the name that you will then use in your JavaScript program), a description and the actual database to use. For Access, you would browse to the .MDB file.

Click on "OK" and you are done.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.