SQL to Word and PDF - Docs from Databases

SQL Word

Documents are the universal truth across organizations of all sizes. Proposals, contracts, invoices, and compliance documents are all part of business operations. Enterprises use approved document templates for each of these document types. Whereas, databases hold the data that goes into these document templates for business documents production.

Re-key data into templates wastes time and resources. Hence, businesses look to automate document creation from SQL files. An ideal solution would populate the SQL Server/Oracle/MySQL data into Word/PDF document templates for Docx and PDF documents generation. It also should allow bulk/on-demand document creation. Smartly choosing the right approach can save you thousands of dollars and countless hours.

SQL Server WORD Population

SQL to Word with EDocGen

It is designed for business users to generate business documents without IT support. Generate documents from SQL Server in a simple 2-step process.

1. Connect to your database from the system.
2. Generate documents using custom SQL queries.
3. Optionally, you can choose to send generated documents to individual recipients.

Step1: Connect to the database

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

In the query, you can use native SELECT statements. Below are the sample MSWord template and the associated query for populating it. The system populates the table, image, and dynamic text fields in the template from database fields.

SELECT
  invoice.inv_no,
  `logo`,
  `date`,
  `trms_pymnt`,
  `po_ref`,
  `name`,
  `address`,
  `contact`,
  `Email`,
  `Phone`,
  `myhtml`,
  para,
  CONCAT(
    '[',
    GROUP_CONCAT(
      CONCAT(
        '{"amnt":"',
        amnt,
        '", "descrptn":"',
        descrptn,
        '"}'
      )
    ),
    ']'
  ) IT
FROM
  `invoice`,
  `inv_item`
WHERE
  invoice.inv_no = inv_item.inv_no
Group BY
  invoice.inv_no;

The system supports all types of queries including SQL joins.

Business documents are complex. Most of them would need the population of tables, images, HTML, Blob, etc. The system supports all these scenarios.

The system supports the creation of tables, loops, and lists from DB data. You can also create nested tables up to 6 levels. For nested tables, fetching data in JSON and XML formats through SQL queries is advisable. Take the example of "Order table".

Below is the query that populates this table. It fetches the data in XML format.

SELECT Cust.CustomerID,     
       OrderHeader.SalesOrderID,   
       OrderHeader.Status,  
       Cust.CustomerName  
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader  
WHERE Cust.CustomerID = OrderHeader.CustomerID  
ORDER BY Cust.CustomerID  
FOR XML AUTO, ELEMENTS  	      

Similarly, the below query fetches the data in JSON format.

SELECT CustomerID,  
      CustomerName,  
      (SELECT SalesOrderID, Status  
       FROM salesOrderHeader AS D  
       WHERE H.CustomerID = D.CustomerID
       FOR JSON PATH) AS D  
FROM Customer AS H  
FOR JSON PATH  

You can store the images in Base64 format or external image URLs. Include these columns in your SELECT query for the population.

You can store them as Blobs and include them in your SELECT statements.

The system supports the population of HTML, graphs&charts.

After entering all mandatory fields, click on the "Connect" button to check the connection. In the next step, the system populates query data into your template and generates the documents.

Step2: Generate Documents

The system supports the filling of dynamic text, tables, paragraphs, hyperlinks, blobs, and images from SQL data into document templates. Please refer to how-to generate for more details.

You don't have to do any data transformation and can populate data as-is. You have the option to map the template's dynamic fields to the query columns. If both names match, the system automatically maps them for you. Thus, database changes don't affect document creation.

Step3: Document Distribution

There are various distribution channels available, including downloading, sending them for printing, emailing, eSigning or syncing them with cloud storage platforms such as OneDrive, SharePoint, and S3 bucket.

You have an option to send generated documents individually by mail as an attachment. Select the email column in the query, input your mail subject & body, and click the send button. The system auto-sends these emails from your business email.

n addition to the web app, EDocGen also offers REST API for SQL to Word file format generation. There is comprehensive developer documentation available. By integrating with API,

1. Document generation can be automated (on-demand document generation) without requiring user interaction
2. Create thousands of documents in one go (bulk document generation) right from the SQL Server database

You can use

both PDF and Word templates and generate either PDF or DOCX files.

Automation

If you use the same template and query for every document generation cycle, entering the information is time-consuming and tedious. The system provides options to automate these steps.

To create automation, follow the above steps. After that, the generation of Word documents is as simple as clicking a button.

The system additionally provides choices for scheduling the generation.

There is also an option to automatically create printer-friendly PCL5 and PostScript files and upload them to an FTP server.

Complex Documents Generation

Templates vary in complexity. Often, you find templates with multiple tables, nested tables, lists, etc. Below is the sample template.

You can generate documents from it with the same above approach. You only need to change the SQL queries to retrieve the data in XML/JSON format.

If you don't want to enter connection details for security reasons, you may download the SQL Server data in XML/JSON format and use it for document generation. Please refer to XML to Word and JSON to Word that detail how to go about the XML and JSON population.

Schedule a demo to explore SQL to Word generation.