MuleSoft Connector for Excel Services

Build 20.0.7587

Schema Discovery

You can invoke the List and List Columns operations to retrieve schema information, as described in the following sections.

You can also query the available System Tables to access all of the available connector metadata, which additionally includes information on data source capabilities and batch operation statistics.

Retrieving the Table Listing

Use the List operation to list the tables.

Visual Editor

To configure the operation in the Visual Editor, select the connector inside your flow and specify the following connector properties.

  • Connector Configuration: Select a connection you created. See Establishing a Connection for more information.
  • Operation: Choose List.

XML Editor

The XML below pulls the table listing into your flow:

<cdata-excelservices:list-tables config-ref="CData_ExcelServices_Configuration" doc:name="CData ExcelServices"/>

Metadata Returned

The List operation returns a dataset with the following columns:

Column NameData TypeDescription
TABLE_CATStringThe table catalog.
TABLE_SCHEMStringThe table schema.
TABLE_NAMEStringThe table name.
TABLE_TYPEStringThe table type.
REMARKSStringThe table description.

Retrieving Column Metadata

Use the ListColumns operation to retrieve column information. You can restrict the results by table name.

Visual Editor

To configure the operation in the Visual Editor, select the connector inside your flow and specify the following connector properties.

  • Connector Configuration: Select a connection you created. See Establishing a Connection for more information.
  • Operation: Choose List Columns.
  • SQL query: Specify the table name or use a MEL expression to do so.

XML Editor

The XML below lists the columns for the Account table, based on the table specified by the #[flowVars.table] variable.

<cdata-excelservices:list-columns config-ref="CData_ExcelServices_Configuration" doc:name="CData ExcelServices" table="#[flowVars.table]"/>

Metadata Returned

The List Columns operation returns a dataset with the following columns:

Column NameData TypeDescription
TABLE_CATStringThe database name.
TABLE_SCHEMStringThe table schema.
TABLE_NAMEStringThe table name.
COLUMN_NAMEStringThe column name.
DATA_TYPEintThe data type identified by the value of a constant defined in java.sql.Types.
TYPE_NAMEStringThe data type name used by the connector.
COLUMN_SIZEintThe length in characters of the column or the numeric precision.
BUFFER_LENGTHintThe buffer length.
DECIMAL_DIGITSintThe column scale or number of digits to the right of the decimal point.
NUM_PREC_RADIXintThe radix, or base.
NULLABLEintWhether the column can contain null as defined by the following JDBC DatabaseMetaData constants: columnNoNulls (0) or columnNullable (1).
REMARKSStringThe column description.
COLUMN_DEFStringThe default value for the column.
SQL_DATA_TYPEintReserved by the specification.
SQL_DATETIME_SUBintReserved by the specification.
CHAR_OCTET_LENGTHintThe maximum length of binary and character-based columns.
ORDINAL_POSITIONintThe column index, starting at 1.
IS_NULLABLEStringWhether a null value is allowed: YES or NO.
SCOPE_CATALOGStringThe table catalog that is the scope of a reference attribute.
SCOPE_SCHEMAStringThe table schema that is the scope of a reference attribute.
SCOPE_TABLEStringThe table name that is the scope of a reference attribute.
SOURCE_DATA_TYPEintThe source type of a distinct type. Or, a user-generated Ref type. If DATA_TYPE is not DISTINCT, this value is null. If a user-generated Ref, this value is null.
IS_AUTOINCREMENTStringWhether the column value is assigned by Excel Services in fixed increments.
IS_GENERATEDCOLUMNStringWhether the column is generated: YES or NO.
ISREADONLYbooleanWhether the column is read-only.
ISKEYbooleanWhether the column is a key.

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