Excel First

Excel Solutions

Use Excel to populate PDF Form Fields

Did you ever need to fill out multiple PDF forms faster and without mistakes?  I’m sure that, if you’ve done this manually, on regular basis, you agree that it is tedious and time consuming.

Update:

If you have Adobe Acrobat Pro installed OR a trial version (even if it expires and you don’t want to purchase a license), then you can use an Excel App to Fill Out PDF Forms automatically, directly from Excel. Read the article and download the tool from here: excel-app-to-fill-out-pdf-forms

If you prefer the manual method, then continue reading this page for an alternative method.

Use Excel to fill out PDF Form Fields:

PDF forms are very powerful and widely used. Unfortunately, many users are not aware of a simple but useful feature of Excel: Export XML Data. However, this is about to change, right? 🙂 This feature can be accessed from the Developer Tab, XML Group from Ribbon. (If the Developer tab is not visible in Excel’s Ribbon, you have to activate it first, from Excel Options, Customize Ribbon – check the Developer check box on the right side of the screen)

Even for users that are aware of this feature, setting the correct export structure is not so obvious, this is the very reason I chose to write this article, which, by the way, is also my first article about Excel.

Here is what you can do:

To be able to do what you just have seen in the above images, you have to successfully complete 3 steps:

  1. Prepare the Excel structure for export
  2. Extract the XML structure of the PDF Form
  3. Map the XML file to an Excel sheet.

Step 1: Prepare the Excel structure for export

Let’s start with Excel, as it’s probably the most familiar step for you. The data structure I prepared for this example is very simple, a defined table with Employees information in one sheet, and in another sheet, I’ve prepared a simple list, where data is populated from Employees table using very simple formulas, based on a drop-down selection. You can start by downloading the files used in this example:

Download the Excel sample file: Export-data-for-PDF.xlsx

Download the PDF Form sample file: Contact Information.pdf

The data structure requested by the PDF Form may vary; such as, if the PDF Form has simple fields, then you can set any structure you want, in a range of contiguous or non-contiguous cells. If the PDF Form has a table form, then you have to prepare data to be exported from a defined table.

 Step 2: Extract the XML structure of the PDF Form

This xml file will be imported into Excel and mapped with the workbook cells.

This step has nothing to do with Excel, you will need Adobe Acrobat Pro for this stage. If you do not have Acrobat Pro, and you do not intend to buy that software, you can use FoxitReader , it’s a free PDF Viewer software that you can use to import or export data from a PDF Form. Another free PDF Viewer that enables users to import – export form data in the Free version comes from: PDF XChange Viewer. However, this last PDF XChange Viewer can export form data in XML format, but can import only AcroForms data in FDF and xFDF format, not in xml format. This PDF form used for this example is not a simple AcroForm, it’s an XFA form created with LiveCycle Designer. AcroForms are the original PDF forms technology. If your form is created with Adobe Acrobat, you’re lucky, many 3rd party vendors support AcroForms.

To Extract the XML structure of the PDF Form using Adobe Acrobat Pro:

  • From Tools-Forms-Manage Form Data-Export Data, export form data in xml format.

To Extract the XML structure of the PDF Form using Foxit Reader:

  • From Form Tab-Export-To A New File, export form data in xml format.
PDF Form Fields Export PDF Form Data

Export PDF Form Data – Adobe Acrobat Menu

PDF Form Fields Foxit Reader

Export PDF Form Data – Foxit Reader

That was easy 🙂 Now, let’s see the last step:

Step 3: Map the XML file to Excel sheet

Here we are at the last step, I’m sure you’ll find it as easy as the previous steps…

First, you should delete the existing XML map from the downloaded file, this way you can practice the following steps. (press the XML Maps button, select the Map and click Delete.)

From the Develover tab in Excel, click on Source Button, this should open the XML Source panel. At the bottom of this panel, you should see the XML Maps button; click on it, the XML Maps dialog window is now open; choose Add and browse for that .xml file exported in step 2 (you will receive an error message like: “The specified xml source does not refer to a schema. Excel will create a schema based on the xml source data” – you may ignore this message, Excel expects an XSD validation schema, but it will accept your xml file, and it will automatically create a schema based on the structure found on xml file)

After adding the xml source, the mapping is very easy: simply drag xml fields to the corresponding worksheet cells. You have 3 ways to map a cell: drag the element to cell, right click the element and choose to Map Element, or select the cell and double click the element. The following video will illustrate this process (I’ll use all those 3 methods to map a cell, use the one you like):

At this point, the Excel file is ready to produce any number of xml files with employees data, in seconds… From the Developer Tab, use the export button to create the output xml files; select another employee from dropdown and press export button again, and so on. Of course, you can automate this process with a simple macro, if you want even faster results.

You can send the data in xml format to your recipients (the xml type is one of the most used data transportation formats), you don’t have to send very large PDF files, if they have the PDF form, they can load the data themselves using the Import Data command.

Enjoy and have fun! 🙂

Your opinion matters, if this article was helpful for you, or if you want to share other techniques, you can leave a reply.

51 Comments to Use Excel to populate PDF Form Fields

  1. tajmirEt says:

    What’s Going down i’m new to this, I stumbled upon this I’ve found It absolutely helpful and it has aided me out loads. I hope to give a contribution & help other customers like its helped me. Good job. tajmirEt

  2. Mynda says:

    A very thorough and easy to follow post, Catalin.

    Cheers,

    Mynda

  3. Richard Munsey says:

    Hi, When I export xml from pdf, I open in excel, source, xml map. field map only shows about 30 of the xml maps out of 112.

    Thank you

    • Hi Richard,
      Yes, this happens sometimes, but here is what you should try: don’t export blank form data, type some data into all fields from your form, then export again.
      Normally, it should not ignore fields with data. If you still have problems, you can send me the form, so I can take a look at it for you.
      Let me know if this solves the problem 🙂
      Cheers,
      Catalin

  4. Rhy says:

    Wonderful post, this was exactly what I was looking for!  However, I am having a problem importing the mapped XML file from Excel back into Adobe?  No data is showing up in the form fields after import?  When I open the XML file I can clearly see that the correct data is in the XML…  Any ideas?

    Thanks!

    Rhy

  5. Katie says:

    Is there anyway to do this where you have a single excel file and have its data populate multiple different pdf forms?

    • Hi Katie,
      Yes, it is possible. You can add multiple xml maps to excel, just like you did for a single pdf. All xml fields can be mapped to the same cells, and you will receive a message when trying to export data, to select the mapping you want to export. You will have to name your maps, in order to easily recognize what should be exported.
      Also, you can use a macro to export each existing mapping.
      There are many possibilities. The one presented in this article describes a way to automatize this process for users that does not have Acrobat Pro installed. (It is also possible to use a code that exports excel data in FDF format, and create the PDF automatically based on that FDF exported file and a PDF template)

      For those who have Acrobat Pro, it’s a completely different picture, they have total control over the process, it can be done 100% via code, no need to manually export the field structure. It takes a few hours of work to write the program, but I can fill an unlimited number of PDF forms much faster. If you have Acrobat and you want me to help you, just let me know.
      Cheers,
      Catalin

      • Erin Hague says:

        I am trying to do this using Acrobat Pro. Granted the version now is more updated than your article post, the information still applys. Can you forward instructions or any links that demonstrates how to go from excel to pdf without having to do it manually? Thanks so much!

        • Hi Erin,
          The acrobat pro version is irrelevant, all versions have import/export commands.
          Go to the top of this post, there is a link to a more automated solution.
          Cheers,
          Catalin

      • Josiah Timmerman says:

        Hi Catalin

        Thanks so much for this! Exactly what I need, except I am having one issue.

        I can add multiple xml maps, but I get an error if I try to apply separate maps fields to the same cell? It says that it would overlap an existing mapping.

        • For the same cell?
          You mean for the same pdf template? Only one mapping can be used for a pdf template.

        • If you need to make different mappings for the same pdf template, then the easiest way is to copy the pdf template.
          Instead of MyTemplate.pdf, you will also have MyTemplate copy1.pdf, MyTemplate copy2.pdf, and so on.
          Each copy will get a separate worksheet, it’s the only way to do it without modifying the code.

  6. Carlos Januszewski says:

    Thank You!

  7. zoedtdt says:

    creatingpdf form from excel online can be download on this very well website http://www.tagpdf.com/online/convert-excel-to-pdf/, this is a wonderful creatingpdf form from excel online, and There are only three steps to creatingpdf form from excel easily.

    • Thanks for sharing the link you use, there are many online excel to PDF form converters, this article is not about creating the form though… It’s about filling it with data.

  8. Pilar Keyes says:

    Hello Catalin,

    I have Adobe Acrobat Pro. I have a fillable PDF created using Live Cycle Designer.  It is a U.S. tax form (1042S) which can be found here.  https://www.pdffiller.com/en/project/76830301.htm?f_hash=b9649f&reload=true

    I ONLY have Excel 2011 for MAC.

    I would like to take my excel file and populate the fillable PDF with the many records in my excel.

    I do have the Designer tab in my excel but not the Source feature.

    When I try to use the adobe acrobat Tools – Forms – More Form Options – Import Data I get this error:

    Xml parsing error: syntax error (error code 2), line 1, column 1 of file /Macintosh HD/Users/pkeyes/Desktop/1042TabDelimitedTextforPDF.txt

    Any suggestions for me?

     

     

    • Hi Pilar,
      Unfortunately, Excel for Mac does not support XML tools, and other very useful excel tools, like Power Query for example. The file you indicated (“1042TabDelimitedTextforPDF.txt”) does not seem to be an xml file, it’s a tab delimited text.
      An xml file will start with a header like:
      <?xml version=”1.0″ encoding=”UTF-8″?>
      Then you will see the xml nodes and values:
      <topmostSubform>
      <c1_1>1</c1_1>
      <c1_2>2</c1_2>
      </topmostSubform>
      In your form, <c1_1> field tag corresponds to “1 Income code” field, <c1_2> to “2 Gross income”.
      You have to create the xml from code, and write all the values from code.
      In windows environment, I can even write directly from excel to PDF, using the Acrobat Pro library, but never tested the code on a Mac. Excel 2016 for Mac has more VBA limitations for manipulating files.
      I would go for writing the xml structure in a text file.
      Cheers,
      Catalin
      3

  9. MeleaB says:

    Catalin.

    This is great, and just what I was looking for.

    I need to extend this to take multiple rows of data to populate multiple forms. (Each row represents a different person who each need their own populated form.)

    You have given me a great starting point. I’ll research further to figure out how to do this with a VBA macro.

    Cheers!

  10. Amoo Mass says:

    This is great!!!

    Do you have any advice on using Excel to to select and populate a customized PDF template form and then save it as a new PDF file (with a custom file name) without ever leaving Excel?  If so, I am looking at ultimately creating a macro button that does it all in one step.

    • It can be done, but with Adobe Reader is more dfficult, if you have many fields, you have to create an FDF file from vba. The easiest way is with Adobe Acrobat Pro version, you have almost total control from Excel.
      I will post tutorials for these 2 versions, but this will be next year.
      Cheers,
      Catalin

  11. SylvainR says:

    Thank you so much!
    Works great on Windows 10 Pro (build 1809), with Excel 365 and Acrobat DC Pro.

  12. Stephen K Jones says:

    Thanks for a great introduction to the subject.

    One question – I have a pdf form into which I wish to import data from Excel. I exported the form’s xml with no problem. Here’s a snippet:

    01/13/20
    01/14/20
    01/15/20
    01/16/20

    When I export the xml from Excel, “xfdf:original” has been changed to “ns1:original” and Foxit reader will not import the data. Here’s a snippet of the Excel export:

    01/13/20
    01/14/20
    01/15/20

    If I open the Excel exported xml in Notepad++ and replace “ns1” with “xfdf”, Foxit imports the modified xml with no problem.

    Is there a way to force Excel to export the xml with “xfdf”?

    Many thanks!

  13. Stephen K Jones says:

    I see that important information was purged from my snippets before they were posted. Now I need to learn how to post snippets of xml code!

  14. Stephen K Jones says:

    Thanks! I’ll repost my comment.

  15. Stephen K Jones says:

    One question – I have a pdf form into which I wish to import data from Excel. I exported the form’s xml with no problem. Here’s a snippet:

    01/13/20
    01/14/20
    01/15/20
    01/16/20

    When I export the xml from Excel, “xfdf:original” has been changed to “ns1:original” and Foxit reader will not import the data. Here’s a snippet of the Excel export:

    01/13/20
    01/14/20
    01/15/20
    01/16/20

    If I open the Excel exported xml in Notepad++ and replace “ns1” with “xfdf”, Foxit imports the modified xml with no problem.

    Is there a way to force Excel to export the xml with “xfdf”?

    Many thanks!

    • Hi Stephen,
      If you are able to edit the pdf form, the best way is to remove the spaces from field names: from “Item a” to “Itema”. The exported xml will no longer have the xfdf:original attribute that displays the original field name. Spaces are invalid chars in xml tags, that’s why the pdf application removes them and adds the original name attribute.
      Another way is to manipulate the exported xml from PDF with visual basic to pass data. This way, you will fill the exact xml exported from the PDF application, should be no problem at import.

  16. Rod says:

    Also, you can use a macro to export each existing mapping.
    There are many possibilities. The one presented in this article describes a way to automatize this process for users that does not have Acrobat Pro installed. (It is also possible to use a code that exports excel data in FDF format, and create the PDF automatically based on that FDF exported file and a PDF template)

    MAY I HAVE THIS CODE PLEASE! 😬. Thank you soooooo much.

    • Hi Rod,
      The code is very simple, you should record a macro while manually exporting the map.
      Here is an example:
      ActiveWorkbook.XmlMaps(“Contacts”).Export Url:= FullFilePath,True
      There is a way to fill pdf’s more easily, fully automated, without acrobat, see the other topic, the link is provided on top of this article (read comments too).

  17. Nate Lasko says:

    Hello
    Is there any way to do this with hundreds of records that export to separate PDFs that can be printed and or saved and emailed?

    I am doing work for a client that has a spreadsheet with hundreds of entries that need to be exported to a fillable pdf

    Thanks for your help!

    • Hi Nate,
      At the beginning of this article there is a link to another post that describes a more automated solution. It will do what you need, but requires Adobe Acrobat Pro. The good news is that you can install a trial of Acrobat Pro, the excel solution will still work even trial period ends and you never buy a full Acrobat license.

  18. James Goodman says:

    I had the same problem and found this tool which I find is quite convenient: http://www.pdfmailmerger.com

    It doesn’t require any tech knowledge… I hope no one minds if I post this link here 🙂

  19. Pankaj says:

    Hello,

    I am using FoxitPhantom to create formname field When I export it as CSV, it shows the field name in it but when I export the form fields as .xml and in excel, when I try to map that .xml file, no form field is shown. Why it is ?
    Thanks in advance.

    • Hi Pankaj,
      How are you “exporting the form fields as .xml and in excel ?
      Did you add the xml via Developer Tab>Source>XML Maps>Add Mapping as described in this article-Step 3?

  20. Britt says:

    figured out that I don’t have this option on excel for mac, leaving a note so other mac users need to find an alternative.

  21. Miriam says:

    Hi Catalin, I was wondering how you created the excel spreadsheet to change all information from changing the name in the dropdown list

Leave a Reply

Your email address will not be published. Required fields are marked *

The Excel Learning Journey that I propose for you, is structured in such a way as to offer you the most practical way to learn Excel, free.

Start Your Free Excel Learning Journey!

Enter your email address to subscribe to this blog: