DEV Community

AndySqlman
AndySqlman

Posted on • Updated on

How to automatically filling excel sheets with SQL query results

Q: I need to populate a table with the results of SQL queries like the one below every day. Is there a simpler way than copy-pasting?

Image description

A: Yes. Here's a simple way to do it.

Note: SQLMessenger2.0 installation is required before proceeding with the following steps.

  • First, modify the table template to look like this:

Image description

  • Mark the cells that need to be filled with SQL query results as "Data Cell". The format for a data cell marker is <%DataCellName%>.

  • Here, we can use formulas to generate data cell markers. For example, in the "State" (A3) cell in the figure above, we can use the formula ="<%"&A2&"%>" to generate the data cell marker. Then, copy the A3 cell to the B3-E3 cells to quickly generate the data cell markers for the B3 to E3 cells.
    Image description

  • After modifying the Excel template, create a task in SQLMessenger, and add an attachment template of type "Dynamic Attachment File" to the task.

Image description

Image description

  • Select "Customize Spreadsheet Template" for the Template Type, then click the "Select File" button to import the designed Excel template sheet.

  • After importing the template file, click the "New Query" button to add an SQL query to the template.

  • In the "Create SQL Query" wizard, select the data source and enter the SQL statement, following the wizard's prompts to proceed.

Image description

  • Set corresponding Data Cells for each SQL field that we want to display in the Excel table.

Image description

  • Add another query to fill in the Total row in the same way.

Image description

Image description

  • After configuring the SQL query statements, click the "Preview" button to preview the template execution results.

Image description

  • The following image shows the Excel sheet filled out after executing the template:

Image description

  • After completing the task configuration, click the "Deploy" button for the new task configuration to take effect.

  • Q&A:

Q: Can this system automatically send the filled-out table via email to colleagues?

A: Yes, SQLMessenger can automatically send the table as an email attachment or in the email body to specified recipients. It depends on your configuration. Setting Recipients for Tasks

Q: Can this task be scheduled to run automatically at specific times I request, such as every day at 8 AM or 2 PM?

A: Yes. You can configure "Task Schedules" for the task to enable it to run automatically at scheduled times. Using Task Schedules

Q: I would like to individually query personal reports (such as sales performance reports) for multiple colleagues and then send them via email to each. Can this be done?

A: Yes. You can use the "Information Distribute" feature to achieve point-to-point distribution of reports. Using Information Distribution Task

Q: Is it possible to convert SQL query results directly into an Excel spreadsheet without using a template?

A: Yes. You can use the "Simple Table" to do this. Using Simple Tables

Original Link:https://www.sqlmessenger.com/docreader.html?id=506

Top comments (0)