Read commands

Read commands read from different data sources and formats and pump records into the pipeline, as shown in the following diagram. Multiple Read commands may be used within a pipeline which pump records into the next FilterCommand.

• Read file can be supplied with multiple formats with where conditions to determine which format is being read.

• Tabular sources support a common set of configurations to define the action to be taken when records have missing fields or have extra fields and specifies a header range such as rows 3:5.

• EIWM is currently restricted to read objects and datasets (not arbitrary associations since those need translation to an attribute).

Reading records

Reading records is accomplished through the Read Command, which reads data from a source Endpoint (for example, a database or file of some format) and emits records into the pipeline.

Multiple readers may be used within a single pipeline configuration. In this case, a reader will output results to the next filter that is, the next non-read command, so the following reads the first 1000 rows from, for example, yam1.csv, the first 500 objects from yam*_null.xml, and write up to 1500 rows to the output.txt file:

Example - Multiple Readers

<pipeline>

<read output-limit="1000">

<file folder="input" file="yam1.csv">

<text template="csv" />

</file>

</read>

<read output-limit="500">

<file folder="input\eiwm" file="yam*_null.xml">

<eiwm />

</file>

</read>

<write>

<file folder="output" file="output.txt">

<text template="tab" />

</file>

</write>

</pipeline>

Read Endpoints

This section describes the endpoints from which data may be read.

Range

The range endpoint emits an inclusive range of numbers between minimum and maximum values, either in an ascending or in a descending order, depending on whether the minimum or maximum range is higher.

The following table lists the range endpoint parameters.

The following table lists the range record attributes.

• Read from 1 through 10 with step 2 will yield 1, 3, 5, 7, 9.

• Read from -1 through -10 with step -3 will yield -1, -4, -7, -10.

• Read from -10 through -5 with step 1 will yield -10, -9, -8, -7, -6, -5. Read from -1 through 10 with step 1 would be a mistake.

Initiate a Pipeline using Range Template One

Either of these will emit one item in the pipeline, so it may be used to artificially initiate processing.

Example:

<read template="one" />

<read>

<range template="one" />

</read>

This endpoint may be used with the following projections to generate data from a loaded information standard (for example, to list all enumerations).

File

The file endpoint will read 0-n files of one or more formats and emit records into the pipeline.

The following table lists Read File Endpoint Parameters:

Read several file formats

The following example detects which reader to use from a few choices – if none of the readers is selected, a file is skipped. A special skip format may be used to indicate skipping certain files.

Example:

<read>

<file

folder="{{ $[source-data] }}\data load\*"

file="*.*"

report-progress="true">

<skip where="{{ $[file.extension] in ['exe', 'dll'] }}" />

<text template="tab" where="{{ $[file.extension] in ['txt', 'tab'] }}" />

<text template="csv" where="{{ $[file.extension] in ['csv'] }}" />

<excel header-row="1" where="{{ $[file.extension] in ['xlsx'] }}" />

<eiwm where="{{ $[file.extension] in ['xml'] and $[file.name] contains '_' }}" />

</file>

</read>

Record output is determined by the format reader. Reading a table of results from Excel, for example, will include all headers. To limit the attributes to a subset, a subsequent projection filter would be used.

Directory

The directory endpoint reads file or folder records from a directory.

Most settings are evaluated in Execute phase, for example, Recurse is evaluated for every folder read, meaning that if the expression includes the folder path/name, this rule may vary per folder processed.

The following table lists read directory endpoint parameters.

The following table lists the read directory record attributes.

SQL Server

The SQL server executes an SQL query to retrieve records.

The following table lists read SQL server endpoint parameters.

Records output are determined by the SQL query.

OleDB

The OleDB endpoint executes an SQL query against an OleDB source and emits records.

The following table lists read OleDB endpoint parameters.

Records output are determined by the SQL query.

OleDB is not on the known roadmap to be supported within .NET core, so currently there is no anticipated support for LINUX. Hence, it might be nice to drop this. Specific Excel and SQL readers (already existing) and Oracle reader will be needed, though most access now is via REST or WSDL api.

Class library

The ClassLibrary endpoint provides the ability to interrogate a currently loaded class library and output information (including extensions) to a structured, tabular form for simple processing.

The following table lists read class library endpoint parameters.

The following table lists the read class library record attributes.

Read class library attributes

Example:

<read>

<class-library artefact-type="Attribute" />

</read>

<where condition="{{ @ID.getAttributeExtension(':validationType') equals 'association' }}" />

<select>

<fields>

<field name="AttributeID" value="{{ @ID }}" />

<field

name="AssociationType"

value="{{ @ID.getAttributeExtension(':validationRule').slice('::', 1).toLower() }}" />

<field

name="AssociationClass"

value="{{ @ID.getAttributeExtension(':validationRule').slice('::', -1).toUpper() }}" />

<field

name="HAVD"

value="{{ @ID.getAttributeExtension('cvx:Engineering_Portal/HAVD') }}" />

</fields>

</select>

Configuration

Provides the reflective ability to interrogate loaded templated configurations and output some simple information about them.

The following table lists read configuration endpoint parameters.

The following table lists the read configuration record attributes.

A key scenario for this is the ability to process an arbitrary number of information interfaces defined in ISM from a standard configuration, so that new interfaces may be added via ISM.

Read template configurations for read and select commands

Example:

<read>

<configuration type="read, select" />

</read>

Waypoint

The waypoint endpoint reads data from a Waypoint and emits records.

A waypoint is used to aggregate and track deltas of updates and handle partial loads as listed in the following table.

Read a waypoint

Example:

<read>

<waypoint folder="{{ $[waypoint-root] }}\master\operational" />

</read>

All read formats

All read formats may specify common parameters as listed in the following table.

A special skip format is available which indicates to skip processing.

Tabular read formats

Tabular formats include Excel, Delimited Text, HTML Table and have a common set of configuration options in addition to format specific options as listed in the following table.

Excel

Read excel format reads an OpenXML XLSX file format or legacy BIFF format.

The excel read format uses all tabular format settings as well as Excel-specific configuration as listed in the following table.

The following table lists the read Excel format variables for metadata pinning.

Read an Excel file with multiple header rows

The following would read an Excel file with headers on rows 2 and 3, thus ignoring row 1 and expecting content to start at row 4:

Example:

<read>

<file folder="input" file="02_TAG_REG.xlsx" report-progress="true">

<excel header-row="2:3" />

</file>

</read>

Read several Excel files (ignoring certain sheets)

The following can read several Excel files, excluding certain sheets:

Example:

<read>

<file folder="input" file="*.xlsx" report-progress="true">

<excel header-row="1" include-sheet="{{ $[sheet.Name] not contains ['ignore', 'me'] }}" />

</file>

</read>

The following table lists the Excel data reader formats.

HTML table

The HTML table format reads 1-n HTML tables and emit records.

This format exists because systems have been encountered which export XLS files, however, it turns out these are HTML Table text files, sometimes of a very large size and poorly formatted as listed in the following table.

The following table lists read HTML Table format scenarios.

Text delimited

Reads a text delimited file such as CSV or TAB delimited. By default, this applies strict rules as per RFC4180 to protect against the fact that many CSV formats are not well encoded and open to mis-reading.

A set of strict rules are applied to detect potential problems but may be lifted at discretion as listed in the following table.

The following table lists the Read text format templates.

Read two sources into a single output

Example:

The following sample XML reads two files and combine them into one output file:

<pipeline>

<read>

<file folder="input" file="input.csv">

<text template="csv" />

</file>

</read>

<read>

<file folder="input" file="input.txt">

<text template="tab" />

</file>

</read>

<write>

<file folder="output" file="output.txt">

<text template="tab" />

</file>

</write>

</pipeline>

BUXML

Reads a BUXML format file as used by AVEVA NET Gatekeeper and AVEVA Change Manager as listed in the following table.

The following table lists the read BUXML format variables for pinning metadata.

EIWM (Engineering Information Warehouse Model)

Reads an EIWM format file as used by AIM – only reads root EIWM Objects and Datasets as listed in the following table.

The EIWM reader automatically pins the meta q, $[this.name], $[this.class].