Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2229
  • Last Modified:

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
0
RobertJones53
Asked:
RobertJones53
  • 7
  • 3
  • 2
1 Solution
 
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now