CSV Templates

Introduction

This section will go through all the tags that are available for CSV template, together with the appropriate data selection query. In this documentation, curly braces are used as delimiters for the tags. Please check general template for how to change the delimiters delimiters. The files should be in .csv format.

Tag Overview

The following tables shows the available tags in csv template. The three dot in the format column shows what is variable, they should be either replaced by cursor name or column name.

info

Please note that the column names are case sensitive, you can use double quotes to force the casing.

Tags can't start with a number and should start with an alphabetical character (a-z,A-Z)

Tag Name	Format	Tag	Short Description
Normal Substitution	{...}	{normal}	Normal Substitution, the data from the given column will be replaced.
Loop Tags	{#...} {/...}	{#data_loop} … {/data_loop}	Tag that loops over the given cursor name and repeats everything in between the tags for each record by creating new row(s).
Inline Loop Tags	{:...} {/...}	{:data_loop} … {/data_loop}	Tag that loops over the given cursor name and repeats everything in between the tags for each record by creating new row(s).

tip

Special Tags: Other special tags can be used. Please refer to the general template section: special-tags

Normal Substitution

Available From: v1.0

These kind of tags are the simplest tag to use. These tags are enclosed in curly braces and can include variables that will be replaced with actual data when the output is generated.

Example

The data source below was created using the database available in the sample data of AOP. The database contains numerous tables and views with raw data that can be used for reference.

Date Source

Hereby example data source for different options.

SQL

select 'file1' as "filename",
       cursor (
           select c.cust_first_name as "cust_first_name",
                  c.cust_last_name  as "cust_last_name"
           from aop_sample_customers c
           where c.customer_id = 1
       )       as "data"
from dual;

PL/SQL returning SQL

declare l_return clob;

begin l_return := q '[
        select
            ' file1 ' as "filename",
            cursor (
                select
                    c.cust_first_name as "cust_first_name",
                    c.cust_last_name as "cust_last_name"
                    from aop_sample_customers c
                    where c.customer_id = 1
            ) as "data"
        from dual
        ]';

return l_return;

end;

PL/SQL returning JSON

declare l_cursor sys_refcursor;
l_return clob;
begin apex_json.initialize_clob_output(dbms_lob.call, true, 2);
open l_cursor for
select
  'file1' as "filename",
  cursor (
    select
      c.cust_first_name as "cust_first_name",
      c.cust_last_name as "cust_last_name"
    from
      aop_sample_customers c
    where
      c.customer_id = 1
  ) as "data"
from
  dual;
apex_json.write(l_cursor);
l_return := apex_json.get_clob_output;

/*
 l_return can also be a varchar2:
 l_return := q'[
  [
    {
    "filename": "file1",
    "data": [
          {
          "cust_first_name": "John",
          "cust_last_name": "Dulles"
          }
      ]
    }
  ]
 ]';
 */
return l_return;

end;

JSON

[
  {
    "filename": "file1",
    "data": [
      {
        "cust_first_name": "John",
        "cust_last_name": "Dulles"
      }
    ]
  }
]

Template

The template should contain the name of the columns that were provided in the query above. For example we have the template with following content:

"First Name","Last Name"
{cust_first_name},{cust_last_name}

 template.csv  

Output

When the above data source together with the given template is passed to AOP, the output will be as follows.

"First Name","Last Name"
John,Dulles

 output.csv  

Simple Angular Like Expressions

Available From: v1.0

Simple angular like expressions are also supported that can perform simple mathematical operations. A full list of Angular expressions can be found at https://docs.angularjs.org/guide/expression.

AOP(APEX Office Print) gives support to different operations such as Mathematical operations, String operations, Number/BigNumber operations, Conditional and Comparison operations, Array operations, Number formatting and Date formatting.

REFERENCE

For more details see Simple Angular Expression

Loop Tags

Available From v1.0

AOP allows you to loop through a record by using the loop tag, which has two parts: the start of the loop and the end of the loop. The start of the loop is denoted by a # symbol followed by the record name enclosed in delimiters, such as {#record1}. The end of the loop is denoted by a / symbol followed by the same record name enclosed in delimiters, such as {/record1}.

Ex:

info

{#record}start

Contents of loop and other

end{/record}

Example

The data source below was created using the database available in the sample data of AOP. The database contains numerous tables and views with raw data that can be used for reference.

Data Source

Hereby examples of data source.

SQL

select 'file1' as "filename",
       cursor (
           select cursor (
               select product_name as "product_name",
                      list_price   as "price"
               from aop_sample_product_info
           ) as "products"
           from dual
       )       as "data"
from dual;

PL/SQL returning SQL

declare
    l_return clob;
begin
    l_return := q'[
        select 'file1' as "filename",
        cursor (
           select cursor (
               select product_name as "product_name",
                      list_price   as "price"
               from aop_sample_product_info
           ) as "products"
           from dual
        )       as "data"
        from dual;
        ]';
    return l_return;
end;

PL/SQL returning JSON

declare
  l_cursor sys_refcursor;
  l_return clob;
begin
  apex_json.initialize_clob_output(dbms_lob.call, true, 2) ;
  open l_cursor for
  select 'file1' as "filename",
       cursor (
           select cursor (
               select product_name as "product_name",
                      list_price   as "price"
               from aop_sample_product_info
           ) as "products"
           from dual
       )       as "data"
  from dual;
  apex_json.write(l_cursor) ;
  l_return := apex_json.get_clob_output;
  return l_return;
end;

JSON

[
  {
    "filename": "file1",
    "data": [
      {
        "products": [
          {
            "product_name": "Business Shirt",
            "price": 50
          },
          {
            "product_name": "Trousers",
            "price": 80
          },
          {
            "product_name": "Jacket",
            "price": 150
          },
          {
            "product_name": "Blouse",
            "price": 60
          },
          {
            "product_name": "Skirt",
            "price": 80
          },
          {
            "product_name": "Ladies Shoes",
            "price": 120
          },
          {
            "product_name": "Belt",
            "price": 30
          },
          {
            "product_name": "Bag",
            "price": 125
          },
          {
            "product_name": "Mens Shoes",
            "price": 110
          },
          {
            "product_name": "Wallet",
            "price": 50
          }
        ]
      }
    ]
  }
]

Template

The template should contain the name of the columns that were provided in the query above. For example we have the template with following content:

"Product Name","Price"
{#products}{product_name},{price}{/products}

 template.csv  

Output

When the above data source together with the given template is passed to AOP, the output will be as follows.

"Product Name","Price"
Business Shirt,50
Trousers,80
Jacket,150
Blouse,60
Skirt,80
Ladies Shoes,120
Belt,30
Bag,125
Mens Shoes,110
Wallet,50

 output.csv  

Grouping Rows of Records

Available From: v19.3

It is possible to group records or arrays into n number of elements by appending |group:n at the start and end labels of the loop.The current group can be looped over by {#group} {/group} looping tags.

Given a record 'product' with rows of data, lets create a group of 2 each.

{#product|group:2}
{#group}
This group will have two product.
{/group}
{/product|group:2}

Reference

For more details on grouping records please visit Grouping Rows of Records

Looping n Times

Available From: v20.2

With this filter one can loop n times for record or a column. You should use #n where n is number of times followed by |loop which becomes {#n|loop} for start {/n|loop} for end. Note that you should use | followed by loop keyword and loop is not a record.

Consider we have a record name product then, the syntax for looping 3 times is as follows:

{#3|loop}
    {#product}
        Product name is {product_name}
    {/product}
{/3|loop}

Reference

For more details on looping multiple times please visit Looping n Times

Filtering

Available From: v3.0

It is possible to filter out the rows from a record based on a condition using filter method. The syntax of filter method is
{#recordName|filter:operand1:operator:operand2} thus creating a record named filtered.

The supported operators are: '<', '>', '<=', '>=', '==', '===', '!=', '!==' .

Considering a record name product with various column including unit_price, the implementation of filtering by unit_price is:

{#product|filter:"unit_price":">":"100"}
{#filtered}
{product_name} {quantity} {unit_price}
{/filtered}
{/product|filter:"unit_price":">":"100"}

Reference

For more details on filtering out the rows from a record please visit Filtering

Sorting

It is possible to sort the rows of record by its value or property. Along with the property, type of sorting should also be defined as "asc" for ascending and "desc" for descending. A record named sorted is created allowing you to iterate through the rows of this record.

Syntax is: {recordName|sort:"columnName":"asc" or "dec"}.

Lets say, you have a record named product with column unit_price.

{#product|sort:"unit_price":"asc"}
{#sorted}
{product_name} {quantity} {unit_price}
{/sorted}
{/product|sort:"unit_price":"asc"}

Reference

For more details on sorting out the rows from a record please visit Sorting

Distinct

It is possible get a distinct column values from a record using AOP. Syntax is: {recordName|distinct:"column_name":"asc" or "dec"}

For a record name product with unit_price column, we can get distinct unit_price as:

{#product|distinct:"unit_price":"asc"}
{unit_price}
{/product|distinct:"unit_price":"asc"}

Reference

For more details on distinct of column values please visit Distinct

Breaking into Groups

Available From: v20.2

It is possible to break a record into groups of records based on the value of column of the record.

Syntax is : {#recordName|break:"columnName"}.

If you have a record named products with column named category inside it, then implementation of breaking into group would be:

{#products|break:"category"}
CATEGORY: {break[0].category}
The items in this category are:
{#break}
    • {category}    {product_name}  {unit_price} {quantity}
{/break}
{/products|break:"category"}

This will break the products record into multiple records, each having same value for column CATEGORY and can iterate through the particular group using loop tag.

Reference

For more details on breaking a record into groups please visit Breaking into Group

Inline Loop Tags

Available From v1.0

AOP allows you to loop horizontally through a record by using the inline loop tag, which has two parts: the start of the inline loop and the end of the loop. The start of the loop is denoted by a : symbol followed by the record name enclosed in delimiters, such as {:record1}. The end of the loop is denoted by a / symbol followed by the same record name enclosed in delimiters, such as {/record1}.

Ex:

info

{:record}start

Contents of loop and other

end{/record}

Example

The data source below was created using the database available in the sample data of AOP. The database contains numerous tables and views with raw data that can be used for reference.

Data Source

Hereby examples of data source.

SQL

select 'file1' as "filename",
       cursor (
           select cursor (
               select product_name as "product_name",
                      list_price   as "price"
               from aop_sample_product_info
           ) as "products"
           from dual
       )       as "data"
from dual;

PL/SQL returning SQL

declare
    l_return clob;
begin
    l_return := q'[
        select 'file1' as "filename",
        cursor (
           select cursor (
               select product_name as "product_name",
                      list_price   as "price"
               from aop_sample_product_info
           ) as "products"
           from dual
        )       as "data"
        from dual;
        ]';
    return l_return;
end;

PL/SQL returning JSON

declare
  l_cursor sys_refcursor;
  l_return clob;
begin
  apex_json.initialize_clob_output(dbms_lob.call, true, 2) ;
  open l_cursor for
  select 'file1' as "filename",
       cursor (
           select cursor (
               select product_name as "product_name",
                      list_price   as "price"
               from aop_sample_product_info
           ) as "products"
           from dual
       )       as "data"
  from dual;
  apex_json.write(l_cursor) ;
  l_return := apex_json.get_clob_output;
  return l_return;
end;

JSON

[
  {
    "filename": "file1",
    "data": [
      {
        "products": [
          {
            "product_name": "Business Shirt",
            "price": 50
          },
          {
            "product_name": "Trousers",
            "price": 80
          },
          {
            "product_name": "Jacket",
            "price": 150
          },
          {
            "product_name": "Blouse",
            "price": 60
          },
          {
            "product_name": "Skirt",
            "price": 80
          },
          {
            "product_name": "Ladies Shoes",
            "price": 120
          },
          {
            "product_name": "Belt",
            "price": 30
          },
          {
            "product_name": "Bag",
            "price": 125
          },
          {
            "product_name": "Mens Shoes",
            "price": 110
          },
          {
            "product_name": "Wallet",
            "price": 50
          }
        ]
      }
    ]
  }
]

Template

In the template below iteration is performed over the names of the keys and values. For more details on special looping codition visit here.

{:products[0]|keys}{.},{/}
{#products}{:products[current_child_index]|values}{.},{/}{/}

 template.csv  

Output

When the above data source together with the given template is passed to AOP, the output will be as follows.

product_name,price,
Business Shirt,50,
Trousers,80,
Jacket,150,
Blouse,60,
Skirt,80,
Ladies Shoes,120,
Belt,30,
Bag,125,
Mens Shoes,110,
Wallet,50,

 output.csv