SQL Table Transformer Macro for Confluence

Contents

Documentation

News and Updates

#Atlassian #Confluence #AtlassianConfluence #JIRA #SystemAnalytics #SQLTableTransformer #table_data_analysis

Summary

New features and bug fixing

Details

  • ​per SQL query configuration: support for arbitrary notes added. The notes can be defined for each SQL query and will be displayed along with the results of this SQL query. See: Per SQL Query Configuration "notes" property in the user's guide.

  • bug fixes and improvements

#Atlassian #Confluence #AtlassianConfluence #JIRA #SystemAnalytics #SQLTableTransformer #table_data_analysis

Summary

New features and bug fixing

Details

  • ​User macro "Split Table into Several" added.

  • Option "Documentation" added to "Bring Results As" param. New params for this option:

    • "Result Format Options" param with 2 options:

      • by default - display each row of the output table as the document section.

      • "Tables from Rows" - display each row of the output table as separate table.
        Note: new corresponding per SQL query option: “result_format_option”.

    • "Column Index to Use as Header" - title the sections using this column value.
      Note: new corresponding per SQL query option: “column_index_to_be_used_as_header”.

    • "Keep Header Column Within Section" - keep the header column within the section.
      Note: new corresponding per SQL query option: “keep_header_column_within_section”.

    • "Display Column Name Within Header" - display the column name within header too.
      Note: new corresponding per SQL query option: “display_column_name_within_header”.

    • "Insert Page Breaks" - used to print 1 table per page.

    • "Wrap Result with Table" - additionally wrap the result with table.

  • "Allow Inner Tables Selection" - allow/disallow inner tables selection when “Input Body Type” is set to “Confluence Wiki”. If enabled, inner tables will also be processed and can be accessed from the SQL, using corresponding table_# (that is, by the order of appearance).

  • "Output Layout" - aligns results to right/left if needed.

#Atlassian #Confluence #AtlassianConfluence #JIRA #SystemAnalytics #SQLTableTransformer #table_data_analysis

Summary

New features

Details

  • you can indicate the path to specific JSON array that you need to be selected in the input JSON input

  • you can indicate the CSV separator used in the input CSV data

  • you can indicate that the data located by the URL is compressed using zip and you want to extract and process only the specific file from the archive

  • new input parameter supported: "Case Sensitive Column Names" - enable it to use strict case sensitivity for column names (exactly as in the source data)

#Atlassian #Confluence #AtlassianConfluence #JIRA #SystemAnalytics #SQLTableTransformer #table_data_analysis

Summary

Critical bug fixed

Details

  • Fixed hanging plugin preview UI in some later versions of Confluence.

#Atlassian #Confluence #AtlassianConfluence #JIRA #SystemAnalytics #SQLTableTransformer #table_data_analysis

Summary

New features and bug fixing

Details

  • Support conversion of results to ordered/unordered HTML list (use the new option "CSV as List" of the parameter "Bring Results As" and "Ordered List" checkbox to toggle ordered/unordered list output).

  • Support a new option for per SQL query configuration parameter bring_result_as. Use "bring_result_as":"Nothing" to execute the SQL query, but don't display any results.


Flexible Transformation, Cross Table Analysis and Conversion

If you often use or create tables in your Confluence documents and you know at least some SQL basics - this macro is for you!

The macro will seamlessly allow running SQL queries against your tables like if the tables were part of a temporary database AND you don't need to setup a database for that!

Get the SQL-Powered Table Transformer macro on Atlassian Marketplace

SQL-Powered Table Transformer Macro for Confluence is a powerful tool for analytics, transformation and conversion of Conventional Tables* using SQL queries. To get the desired results the macro leverages SQL as a "universal" transformation operator that gets applied to one or more Conventional Table*. Just add your tables into the macro container, express your desired results through regular SQL queries and the macro will do the rest. The results will be returned and presented as one or more Confluence tables that are built on the resultsets, returned by the SQL responses.

A lot of very basic examples are presented in the SQL Table Transformer Macro for Confluence User Guide.

*Conventional Tables can be:

  • Confluence HTML tables that are usually created using "Insert Table" tool
  • Any other HTML tables (pure or within a HTML code), placed inside the macro container
  • Jira table source, placed inside the macro container (multiple Jira tables are supported too)
  • CSV data, placed inside the macro container (multiple CSV tables are supported too)
  • Any other temporary in-memory DB tables you would want to create in runtime using SQL (such as, downloaded from a local or remote URL, or from attachments)

List of Capabilities

  • analytics of one table, analytics of several tables together (cross-table analysis) using SQL

  • pre-rotation (rotation of input tables, i.e. prior to SQL execution)

  • post-rotation (rotation of output tables, i.e. after SQL execution)

  • transformation using arbitrary SQL, such as:

    • filtering

    • sorting (including multiple criteria)

    • column adding (such as computed)

    • column hiding

    • column renaming

    • columns reordering

    • merged table cells splitting

    • cells auto-merging (horizontal/vertical strategies available)

    • cell patterns replacement / translation / highlighting

    • aggregation

    • math / computation

    • table merging

    • table splitting

    • finding tables intersection and excerpt

    • user-defined functions and stored procedures

    • declaring arbitrary Java functions as source code that can be immediately used by SQL

  • dynamic data preparation for charts and reports

  • all combinations of the above capabilities (as long as supported by SQL and the H2 DB Engine)

  • nested macro (sequential) transformations

  • using the result tables within the other table filtering macros, such as "Pivot Table", "Table Filter" and "Charts from Table" that come with Table Filter and Charts Macro (by Stiltsoft)

  • conversion of tables (or SQL query resultset) to CSV, JSON or JIRA Table formats (i.e., bringing output results in Confluence table, csv, JIRA table or JSON formats)

  • conversion of table to SQL script

  • fetching remote tables by a URL or by a list of URLs for displaying in the document or further analysis. The following formats of the remote tables are supported (and can be mixed in the list of URLs): HTML, CSV, JIRA Table.

  • fetching tables from csv attachment files with an ability to fetch from:

  • a) arbitrary space and page;

  • b) multiple versions and perform immediate comparison.

  • creating table from JSON-formatted table object.

  • fetching one or more tables from remote databases (even from heterogeneous), execute common SQL over these tables as if they were in the same database.

  • fetching data from page attachments (arbitrary space, page; arbitrary versions).

  • un-spanning a table (splitting cells that are merged in the source table)

  • auto re-spanning table cells (merging cells in the output tables that have the same values)

  • etc.

How It Works

It is as simple as can be! Here is a primitive example:

  • install the SQL-Powered Table Transformer macro to you Confluence instance
  • add the macro container to a Confluence page (you can also put it inside your other favorite table macro, such as Table Filter or Charts by Stiltsoft, so, the outer macro could use the result from the SQL-Powered Table Transformer)
  • create or put one or more tables into the container (note, that you can put one or more tables from other Confluence pages by including those into the "SQL-Powered Table Transformer" container using Excerpt/Excerpt Include macros)
  • you are ready to transform your tables: open the macro in Edit mode and put this trivial SQL query into "SQL Query" input parameter field: SELECT * FROM table_1
  • to see the result - click refresh Preview or save the macro and the page: you should see your whole original table (the top in the macro container) brought back by the SQL query.
  • modify the "SQL Query" as you please to achieve the desired transform or analysis results (note, that you can provide several SQL queries and get more than 1 table as result).

For an overview diagram that illustrates how the whole thing works refer to the macro User's Guide, Technical Details.

Links

  1. Get the Macro from Atlassian Marketplace
  2. Submit an Issue or Suggest an Enhancement
  3. Get Updates via RSS channel

Issues

Key Summary T Created Status
Loading...
Refresh

How Tos

Overview

Assume you want to create a Confluence table that would be built on data, sourced from from some database. You can achieve this following the steps below.

Prerequisites

  1. Confluence (Server)

  2. SQL-Powered Table Transformer macro for Confluence installed

  3. Depending on the source DB used: Oracle, Postgres, Mysql or other jdbc driver installed on your Confluence server.

  4. Documentation: SQL Table Transformer Macro for Confluence User Guide

Step-by-Step Instructions (Oracle)

  • Add SQL-Powered Table Transformer macro container to a Confluence page

  • Edit the macro properties as follows:

  • Put ; into "Query Splitter" field.

  • Put the following into the "SQL Query" field:

Hint: you can use a Helper Snippet to quickly add the below piece of code - just start typing the following in the SQL query editor: link_
You’ll see a drop down list with the matching Helper Snippets. Select the Oracle snippet.

CREATE LINKED TABLE
    LINK(
        'oracle.jdbc.OracleDriver',
        'jdbc:oracle:thin:@<db01.mydb-host.com:1522>/<mydb_name>',
        '<mydb_user_name>',
        '<mydb_password>',
        '<mydb_table_name>'
    ) READONLY;
 
SELECT * FROM LINK LIMIT 20;
  • Replace the parameters within <> with your desired connection properties.

  • Save the macro configuration.

  • Now the macro will bring you some data of the linked table (in our example we limited it to 20 rows).

Step-by-Step Instructions (Postgres)

  • Add SQL-Powered Table Transformer macro container to a Confluence page

  • Edit the macro properties as follows:

  • Put ; into "Query Splitter" field.

  • Put the following into the "SQL Query" field:

Hint: you can use a Helper Snippet to quickly add the below piece of code - just start typing the following in the SQL query editor: link_
You’ll see a drop down list with the matching Helper Snippets. Select the Postgres snippet.

CREATE LINKED TABLE
    LINK(
        'org.postgresql.Driver',
        'jdbc:postgresql://<db02.mydb-host.com:5432>/<mydb_name>',
        '<mydb_user_name>',
        '<mydb_password>',
        '<mydb_table_name>'
    ) READONLY;
 
SELECT * FROM LINK LIMIT 20;
  • Replace the parameters within <> with your desired connection properties.

  • Save the macro configuration.

  • Now the macro will bring you some data of the linked table (in our example we limited it to 20 rows).

Other DBMS

Linking tables from other databases (such as MySQL, H2, etc.) can be achieved in similar ways (see examples above). If you use H2 as the source DB, then you don’t need to install the JDBC driver (since it comes with the SQL-Powered Table Transformer macro).

Some Additional General Notes

If DB gets updated, the tables generated by the macro will be also updated.

You can link more than one table from the database and use those in your SQL Query. You can even link one table from one database and another table from another database (even other type DB) and execute an SQL on those linked tables as if they were the same database.

Overview

Assume you have a series of status pages that each contain a table of data, such as project milestones. You'd like to have an additional page that pulls the rows from each of the tables on those individual pages and builds a master table that contains all of them in a single table, using something like Excerpt macro. To achieve this follow the steps below.

Prerequisites

  1. Confluence (Server)

  2. SQL-Powered Table Transformer macro for Confluence installed

  3. Documentation: SQL Table Transformer Macro for Confluence User Guide

Step-by-Step Instructions

Put each of the tables inside the Excerpt macro container. Note that Standard excerpt macro supports only 1 macro per page, so you’ll need to have one table per page.

On the summary page add the SQL-Powered Table Transformer macro container, then inside the container link all the tables from other pages using the Excerpt Include macro.

Modify the SQL Query in the SQL-Powered Table Transformer macro properties to pull data from all the needed tables. Table names will be TABLE_1, TABLE_2, etc. (according to the order you put them into the SQL-Powered Table Transformer). For example:

SELECT * FROM TABLE_1 UNION SELECT * FROM TABLE_2 UNION SELECT * FROM TABLE_3

Save the macro.

As result your should get a summary table that will contain all data from TABLE_1, TABLE_2 and TABLE_3

If the source tables get updated, the tables generated by the macro will be also updated.

Overview

Assume you have a table that you need to pull content from (if it matches certain criteria) and then place that content into another table on a different page (not a child or parent page). For example, you have a table with a column called 'status'. The status column contains the words green, yellow, or red. If the status is 'red', you'd like to pull the whole row of data that contains the 'red' status and place it into another table. To achieve this follow the steps below.

Prerequisites

  1. Confluence (Server)

  2. SQL-Powered Table Transformer macro for Confluence installed

  3. Documentation: SQL Table Transformer Macro for Confluence User Guide  

Step-by-Step Instructions

Put your source table inside the Excerpt macro container.

On your target page add the SQL-Powered Table Transformer macro container.

Then, inside the SQL-Powered Table Transformer macro container, link the source table using the Excerpt Include macro.

Modify the SQL Query in the SQL-Powered Table Transformer macro properties to pull the needed rows from the source table. Its name will be TABLE_1. For example:

SELECT * FROM TABLE_1 WHERE “status” LIKE ‘%red%’

Save the macro.

As a result you should see a new table with all the rows from the source table where status is red.

Overview

This howto provides step-by-step instructions on how to display a table, scraped from a URL, as a table on a Confluence page.

Let’s assume there is one or more HTML-formatted tables, located on some Web page at some URL. To scrape and display one of the HTML tables on your Confluence page as Confluence table, follow the steps below.

Prerequisites

  1. Confluence (Server)

  2. SQL-Powered Table Transformer macro for Confluence installed

  3. Documentation: Table Scraper from URL Macro

Step-by-Step Instructions

  1. Create an empty Confluence page

  2. Edit the page

  3. Include “Table Scraper from URL“ user macro into the page

  4. Click the macro container and select “Edit” (Edit macro dialog will appear)

  5. In the Edit macro:

    1. set the URL parameter to the desired URL with a HTML table.

    2. set the Tables Selector parameter to the desired CSS-selector. If set to default (table), then all tables within any <table></table> tags will be selected.

    3. set the Table Index Number parameter to the index of the desired table in those, selected by the Tables Selector (first table’s index is 1, 2nd table’s index is 2, etc.). You can indicate only one index.

  6. Set other parameters as you desire (refer to Table Scraper from URL Macro )

  7. Save the macro.

  8. Save the page.

  9. When the page loads it will have the table, built from the table, scraped from the URL.

Overview

This howto provides step-by-step instructions on how to display a CSV file from a URL as a table on a Confluence page.

Let’s assume there is a CSV data, located at some URL. To display the CSV data from this URL as a table on a Confluence page, follow the steps below.

Prerequisites

  1. Confluence (Server)

  2. SQL-Powered Table Transformer macro for Confluence installed

  3. Documentation: Table from CSV URL Macro

Step-by-Step Instructions

  1. Create an empty Confluence page

  2. Edit the page

  3. Include “Table from CSV URL“ user macro into the page

  4. Click the macro container and select “Edit” (Edit macro dialog will appear)

  5. In the Edit macro enter the CSV URL into the “CSV URL” parameter field.

  6. Set other parameters as you desire (refer to Table from CSV URL Macro)

  7. Save the macro.

  8. Save the page.

  9. When the page loads it will have the table, built from the CSV data, downloaded from the URL.

Overview

This howto provides step-by-step instructions on how to display a CSV file from a page attachments as a table on a Confluence page.

Let’s assume you a Confluence page with an attached CSV file. To display the CSV file as a table on a Confluence page, follow the steps below.

Prerequisites

  1. Confluence (Server)

  2. SQL-Powered Table Transformer macro for Confluence installed

  3. Documentation: Table from CSV URL Macro

Step-by-Step Instructions

  1. Create an empty Confluence page

  2. Attach a CSV file to the newly created Confluence page (let’s assume the file name is test.csv).

  3. Edit the page

  4. Include “Table from CSV URL“ user macro into the page

  5. Click the macro container and select “Edit” (Edit macro dialog will appear)

  6. In the Edit macro set the CSV URL parameter as follows: “:^test.csv” (without quotes). Note, that the format of this fields allows referring to attachments of not only the current page, but any other pages in your Confluence instance (full format is <space>:<page title>^<attachment>#<version>. By default the current version of the attachment will be used, however, you can instruct to use the specific version of the attachment. For example, if you need version 7 of the file, append #7 to the CSV URL.

  7. Set other parameters as you desire (refer to Table from CSV URL Macro)

  8. Save the macro.

  9. Save the page.

  10. When the page loads it will have the table, built from your attachment CSV file.

Problem Statement

Let’s assume you have some data that can be kept as a table in Confluence (for example, a list of various references). Now, you would want to include references from this table into various other pages and yet you want only the relevant references to appear on each of the pages.

As well, you would want to have an ability to update the table and be sure that all the documents would have the updated data.

Solution

You can easily achieve this using SQL-Powered Table Transformer plugin for Confluence, here is a recipe:

  1. Create a Source Page where you will put your Source Table with all the References

  2. Create the References Source Table on this page and place it into the Excerpt macro container.

  3. Save the Source Page

  4. Now, open a Target page where you want to put the relevant references from the Source Page.

  5. Insert SQL-Powered Table Transformer macro container into the Target Page

  6. Insert an “Excerpt Include” macro container into the SQL-Powered Table Transformer macro container.

  7. Edit the properties of the “Excerpt Include” container and configure it to include the Table from the Source Page, save the macro (make sure “Remove Surrounding Panel” option is checked).

  8. Now, edit the properties of the SQL-Powered Table Transformer macro container:
    1. put the following SQL into the “SQL Query” field:

    CREATE TABLE TABLE_T AS SELECT * FROM table_1 WHERE LABELS LIKE '%|Target-DOC-2|%';
    ALTER TABLE TABLE_T DROP COLUMN LABELS;
    SELECT * FROM TABLE_T

    2. put the “;” into the “Query Splitter” field
    3. save the macro container configuration
    Here is what you should now have on the Target Page

  9. Save the Target Page.

  10. When done, you should see in your Target Page a table with only reference [1]:

Let’s say you now want the reference [2] also to appear on your Target Page. To achieve this, you just need to edit the Source Page and append “Target-DOC-2|” into the Labels column of the reference [2], then save the page. From now on, reference [2] should also appear in the Target Page.

If you update your Source Page table, the data will be always updated on all the Target Pages too.

Surely, you could follow the same approach for any kind of data, for example, “Acronyms”, “Terms”, etc.

To convert CSV to Confluence table:

  1. Add SQL Table Transformer macro container to your Confluence page.

  2. Place the CSV data directly into the macro container body.

  3. Edit the macro input parameters: set Input Body Type to “Direct CSV”.

  4. To include all the data into the output table, leave “SQL Query” input parameter default value as is, i.e., “SELECT * FROM table_1”.

  5. Save the macro.

  6. Save the page.

  7. When the page loads you should see Confluence table, created from your CSV data.

To convert Jira Table Markdown to Confluence table:

  1. Add SQL Table Transformer macro container to your Confluence page.

  2. Place the Jira markdown text directly into the macro container body.
    Commonly used Jira table markdown is:
    ||Col 1||Col 2||Col 3||
    |Val 1|Val 2|Val 3|

  3. Edit the macro input parameters: set Input Body Type to “Direct JIRA Table”.

  4. To include all the data into the output table, leave “SQL Query” input parameter default value as is, i.e., “SELECT * FROM table_1”.

  5. Save the macro.

  6. Save the page.

  7. When the page loads you should see Confluence table, created from your JIRA Markdown text.