SQL Word

MySQL to Documents - Word, PPTX, and PDF

Introduction

This blog details how to create documents in either Microsoft Word or Adobe PDF format from MySQL data. It discusses various popular approaches and their shortcomings.

The later part of the article focuses on document generation software. You can see how it empowers business users in generating documents from MySQL. It concludes with the advantages of using the document generation software route and how and it provides lower TCO (Total cost of ownership) over other approaches.


Document generation from MySQL

MySQL is a robust, scalable, integrated, secure yet simplified DBMS that can hold all of an organization’s sensitive, complex, and mission-critical data all together in a place. Business users create various documents from this data including contracts, invoices, statements, etc.

To generate Word and PDF documents, the software needs to connect to MySQL database, run queries to extract data, and use that data to generate DOCX/PDF documents.

There are multiple systems and approaches available. Each of them has its advantages and disadvantages. Here, we are looking for an approach that’s business user-friendly and involves no-code/less code. Let’s explore the popular choices.


Popular Approaches

PDF Document Generation:

Approach1: open-source PHP libraries

There are a lot of third-party open-source PHP libraries such as fpdf, tcpdf, or wkhtmltopdf that generate PDF documents MySQL database. While these are free, the below are their main disadvantages.

  1. Building the PDF layout is very tedious. It’s not easy to introduce a new template or make changes to an existing template. The more complex it becomes, the harder it gets to generate good PDFs, no matter which package you pick. Often, they don’t support your company styles/fonts.
  2. Every time a new template comes in, it has to be coded again by developers. If your organization uses a lot of templates, this approach becomes maintenance-heavy.

Approach2: puppeteer (headless chrome)

The logic was to fetch what was needed from the database, render an HTML file from a template, pass in the DB data, then get the puppeteer to load the page and render it to pdf. Then returning the pdf to the web app. Using puppeteer means that any CSS you use in chrome can be rendered to a pdf.

  1. Puppeteer hangs for large pdf generation. Hence, it’s not suitable for the generation of large PDF or large volumes of PDF generation.
  2. As in the above approach, each template has to be coded. Thus, it’s a time-consuming process. Moreover, it’s maintenance-heavy. Every time a template changes, underlying code needs to change as well.

Word Document Generation.

Out of the PDF and Word, surprisingly Word document creation is harder. Let’s assume you are given a Word document template with dynamic fields (spaces) like name, address, etc. to be filled in from the database.

To do this, through an ODBC connector, create DNS and use Word mail merge to create Word documents.

  1. Mail merge itself is tedious to use and offers very limited functionality. It’s useful only for creating simple DOCX documents.
  2. Business users find it hard to work with database connections. This is not for business users.
  3. It’s only available on your network and to a select few users. External users or external-facing systems can’t use this for Word documents generation.

In all the above approaches, developers have to develop templates and assist business users in document generation. How about giving the control back to them in template management through DIY document generation?


EDocGen Document Generation Software

What separates document generation software from other approaches is, it allows the generation of Word and PDFs from existing templates. Thus, business users can generate documents themselves without needing much help from the IT team.

It is an API-first software. Users can generate documents either through the system’s UI or you can integrate it with your apps.

If you’re looking to generate complex documents containing multiple tables, lists, nested lists then EDocGen is the answer. However, the generation of documents is very simple. It is a 2-step process.

Step1: Connect to the database

In the connection screen, select the SQL server from the drop-down. Then enter the connection URL in "username@hostname[:port]/DatabaseName" format and the password.


MySQL Database Connection


For simple documents, you can connect to the database using the connection string populate the query data to populate the PDF/Word/PPTX document templates. Business users can use the native queries for document generation.

Let’s see how it works.

Generation of document from MySQL

SELECT
    District AS 'State',
    Name AS 'City'
FROM City
WHERE CountryCode = 'US'
ORDER BY State;
                    

A MySQL document Store allows developers to create either traditional SQL relational applications or schema-less document databases. This eliminates the need for a separate NoSQL document database. You can store both relational data and JSON documents together within one database. For example, both data models can be queried in the same application and results can be in a table, tabular, or JSON format.

We can adjust the above query so that each city becomes an element in an array. To do this, we simply pass the Name column (the name of the city) to the JSON_ARRAYAGG() function. We also use a GROUP BY clause to group the results by State. These queries are useful for creating tables, nested tables, nested lists, etc.

SELECT
    State,
    JSON_ARRAYAGG(Name) AS 'Cities'
FROM City
WHERE CountryCode = 'US'
GROUP BY State;
                    

Step2: Generate Documents

Users can generate documents in various formats including PDF, PPTX, DOCX, TXT, etc. Select the document format from the drop-down and input a name for your documents. You can even prefix the name with one of the dynamic fields.

Enter the file name and prefix for individual documents before clicking the generate button. The output in ZIP format would contain generated documents.

MySQL PDF generation


If you’re generating PDFs, the system provides options to add watermark and password protection.

Step3: E-Sign/Email

This is an optional step. You can distribute the generated documents for e-Signature and Email.


MySQL Document distribution


Why EDocGen

It offers a complete solution for the automatic generation of complex documents that meet the laws and regulations. It makes it easy for business users to automatically fill in previously created templates with data from the MySQL database.


Schedule a demo to explore SQL to Word generation.


Popular Posts