MuleSoft Connector for Excel Services

Build 20.0.7587

Defining Custom Views

The CData MuleSoft Connector for Excel Services allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views and are useful in situations where you cannot directly control the query being issued to the driver e.g. when using the driver from a tool. The User Defined Views can be used to define predicates that are always applied no matter what. If additional predicates are specified in the query to the view, then they are combined with the query already defined as part of the view.

For example, a User Defined View called UserViews.RCustomers that only lists customers in a particular city might look like:

SELECT * FROM Customers WHERE City = 'Raleigh';
The query to the driver could be:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';
Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';
That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined as appropriate.

Defining Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json in the Location folder. The connector will automatically detect the views if a file called UserDefinedViews.json is found in the Location folder.

It is also possible to have multiple view definitions and control them using the UserDefinedViews connection property. If the UserDefinedViews property is specified, only the views defined in this file are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

	"MyView": {
		"query": "SELECT * FROM Account WHERE MyColumn = 'value'"
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"

Defining Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:


The view is created in the JSON configuration file and will now be discoverable.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead.

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view will be updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.


The view will be removed from the JSON configuration file and can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the name of the view from clashing with an actual entity in the data model. It is possible to change the name of the schema used for UserViews. This is done by setting the UserViewsSchemaName property.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587