Search code examples
excelpdfacrobatpdf-formfoxit

Automate PDF Forms Based on Excel Data

I have a list of approximately 1000 duplicate customer accounts, and tasked with filling out a form as documentation to merge these accounts into one each. This form is customarily handwritten, but available as an editable PDF. I have an Excel sheet with relevant information, and would like to automate this process to transfer the data to the form and print it.

I've seen some other questions similar to this, but the responses were for narrow cases that didn't apply to my dataset, or looked promising but had dead links as part of the explanation. This one seems close, but I don't have the prerequestite knowledge to implement it (dead links).

I've tried Acrobat and Foxit PDF Editor, exporting to XML, and trying to link the XML to my Excel data points, but either my data isn't formatted properly to automate, or the tutorial varied from my use case.

I can supply example files, but don't know the best way to host them for you. My data consist of ~1000 rows of:

Account#  Name     CreationDate
111111    JohnDoe  1/1/2000
111112    JohnDoe  1/1/2001

The PDF form wants data formatted as:

Oldest Account: NAME & ACCOUNT NUMBER
Newest Account: NAME & ACCOUNT NUMBER

All the Excel data has been verified as accurate, so I am just missing the step of automating the transfer to a single PDF form for each customer. I can likely easily change the Excel data to a single row per customer if it simplifies the process.

Any help towards this is appreciated! Foxit (or free alternative) is a preferred software, Adobe is difficult to work with due to licensing issues.

Solution

  • I successfully got it to work much simpler than any other answer I found online.

    Used FoxitPDF Editor (or Adobe Acrobat PRO) to create editable fields on a PDF document.

    Found BulkPDF Free version processed exactly what I needed without any manual mapping through Excel and without the watermark from it's Business version.

    Select the spreadsheet you're pulling data from (no empty columns between data or it won't find the next ones), and select the PDF writing data to. Match the PDF form fields to the Excel data, select a naming scheme, and fire away to automate.