Dynamic document generation using data from Excel

Dynamic document generation is the process of automatically creating a batch of documents that are personalized with dynamic data from a source. For example, a letter might be personalized to address each recipient by name. Creating documents with dynamic data would require a data source, like a list, spreadsheet, or database, JSON, or arrays. Merge fields are placeholders in predefined templates that are later replaced using data from one of the above-mentioned or any other supported data sources.

PDF4me has powerful connectors that can help you generate a large number of documents with dynamic content. This document automation can be achieved through simple integrations with Microsoft power tools such as Power Automate. As a Power-user, you can integrate PDF4me Connect features to manipulate and manage your business documents. The connectors support data sources like .xlsx, JSON, Sharepoint data lists, or arrays to populate data into templates.

Microsoft Excel spreadsheets are one of the most used data sources for dynamic document creation. Excel sheets are the most chosen tool for organizing dynamic data as they are easy to use and give great control over the data. Let us look at a few scenarios where dynamic document generation software can be highly effective in simplifying mass document generation.

• Businesses - Promotional mails, creating invoices, reports, quotations, mails, envelopes, etc.
• Banking & Finance - Generating statements, custom emails, receipts, etc.
• Health care - Generating reports, insurances, etc.
• Academics - Generating academic reports, certificates, grade reports, etc.

In a way, dynamic document generation and mail merge are used in almost all areas of work and businesses where documents are involved.

How to dynamically generate documents using Excel spreadsheets?

Let’s look at a use case where we want to use generate a large number of documents using data from an Excel spreadsheet and store it in Cloud storage with PDF4me Connect and Power Automate.

There are four PDF4me actions available to create dynamic documents in Power Automate -

• Mail Merge with Data File (List of Docs)
• Mail Merge with Data File (Single Doc)
• Mail Merge with Data String (List of Docs)
• Mail Merge with Data String (Single Doc)

Since we are using an Excel spreadsheet, we will be using actions with Data File as the data source.

How does it work?

All you need to do is map your Data file content and Template file content using the PDF4me action in Power Automate.

The above action reads your Excel file and performs replaces the merge fields according to your Word/PDF template. It can produce a large number of documents in a single call. Based on the input, it produces a Word output format for Microsoft Word templates and PDF file output for PDF templates.

Let’s take an example where you want to generate custom documents for all employees of your firm. A sample excel would look like this.

Your sample template should have the following merge fields to map to the data source which is your Excel spreadsheet.

After you have properly mapped the data file content and the template file data, you should be able to get an output as shown below.