Field conversion

To modify mapped data or to apply filtering (to reject certain records), create a new section using the name of the relevant line from the mapping section. For example, if you have the following mapping section:

[ImportFilterMap]

Test1_to_type = Test1 -> Type

and you want to convert the data from the Test1 column before importing it, somewhere in the file you need to have a section called [Test1_to_type]:

Test1_to_type]

followed by the necessary conversion rule.

The basic format of this conversion/filtering section is as follows:

[Relevant mapping name]

Filtering Rule 1 = External Pattern 1 -> Citect String 1

..

Filtering Rule m = External Pattern m

..

Filtering Rule n = External Pattern n -> Citect String n

The name used for Filtering Rule n has no intrinsic significance to CitectSCADA (except that it uses it as a key to locate the entry). The only restriction is that it needs to be unique within the section, so you can use whatever is convenient.

The value in External Pattern n is a combination of characters which CitectSCADA will look for in the external data source column. This pattern can be any combination of the following:

Any other characters need to literally match the same character.

If External Pattern n is found in the external column, Citect String n is written to the relevant column in CitectSCADA (as per the mapping).

In addition, two special characters can appear in the output data string:

Other characters are written literally to the output database.

CitectSCADA works through each filtering rule in the section, looking for a match. If a rule does not match, the next one is tried, then the next, and so on, until a match is found. If no match is found, the whole record is rejected; none of the data from any field is copied to CitectSCADA.

For example, to convert the string "FLOAT" in the external data source to "DIGITAL" in CitectSCADA, you could use the following entry:

[ImportFilterMap]

Test1_to_type = Test1 -> Type

..

[Test1_to_type]

Rule1 = FLOAT -> DIGITAL

..

For a more complex example, let us assume that the external data source has a column called "Tag" which is equivalent to the "Name" field in CitectSCADA. Let us also assume that the external database has no direct equivalent of CitectSCADA's "Type" field, yet CitectSCADA needs this field to be filled in. We need to use the "Tag" field to decide what goes into the "Type" field of the CitectSCADA database.

If the "Tag" column in the external data source has the value "I:060/07", we have determined that we write the string "DIGITAL" into CitectSCADA's "Type" field. In fact, if that field has "I:" followed by any octal value, followed by a slash "/", followed by any octal value, we want the string "DIGITAL" to appear in our "Type" field. How do we express this in the format file?

Firstly, there are two sets of relationships to consider, one connecting the "Tag" field in the external data source to the "Name" field in CitectSCADA, and the other connecting it to the "Type" field in CitectSCADA. So we need two "mappings" (entries) in the [ImportFilterMap] section:

[ImportFilterMap]

Tag_to_Name = Tag -> Name

Tag_to_Type = Tag -> Type

..

As we want the data in the "Tag" field to be copied directly into the "Name" field, we do this by not having a [Tag_to_Name] section anywhere in the format file.

But because we are not copying directly from the "Tag" field to the "Type" field, but are just using the data to decide what goes into the "Type" field, we need a [Tag_to_Type] section.

Recall the desired outcome: If the "tag" field has "I:" followed by any octal value, followed by a slash "/", followed by any octal value, we want the string "DIGITAL" to appear in our "Type" field.

We express this in the format file as follows:

[Tag_to_Type]

Rule1 = I:%e/%e -> DIGITAL

..

This will match "I:060/07" or "I:0453/02343445602" (and cause the string "DIGITAL" to be written to CitectSCADA's Type field), but will not match "I:060/98" or "I:054".

To give a few examples of how the wild-card characters (%s, * and ?) might be used, the pattern "HE%sLD" or "HE*LD" in the format file would match "HELLO WORLD" or "HE IS VERY BOLD" in the external data source. The pattern "HE???????LD" would match "HELLO WORLD" but not "HE IS BALD", as each question mark "?" needs to match exactly one character.

CitectSCADA will also handle multiple wildcard patterns, such as "%s/%s:%s".

For an example more useful than "Hello World", imagine that we need to copy the data straight across without modification, but we want to verify that no blank fields are copied across. The pattern "?%s" or "?*" will match any string that has at least one character, but will not match a blank.

Sometimes only part of the input stream is necessary in the output, or the input might need to be split up into different output columns. In these situations "tokens" are useful.

In this example of an export situation, the "Addr" field in the CitectSCADA database needs to be split among two fields in the external database: the "IOdev" (whose value is always "D" or "M"); and "IOaddr" (whose value is a decimal integer of no more than 3 digits). Values in the "Addr" field of the CitectSCADA database are strings such as "D62", "M546", etc.

This situation could be solved by concatenation, i.e. using one mapping to write to the IOdev field, and three other separate mappings to copy each digit separately into the IOaddr field of the external database. But this would be complex and in some situations would not work.

It is better to use a token to address the situation:

[ExportFilterMap]

..

Addr2IOdev = Addr -> IOdev

Addr2IOaddr = Addr -> IOaddr 

..

[Addr2IOdev]

D = D* -> D

M = M* -> M

AnythingElse = * -> 

..

[Addr2IOaddr]

Rule = ?{%d} -> $1

In the [Addr2IOaddr] section, the {%d} is the token string, and as it is the first (and only) token appearing in the rule, $1 is used to reference it on the output stream side. So if the "Addr" field of the CitectSCADA database contains "D483", "D" is written to the "IOdev" field of the external data sink, and "483" (the token) to the "IOaddr" field.

Here is another example illustrating the use of multiple tokens. Suppose we need to: convert period characters (.) to colons (:); remove the first two characters (which are blank); and remove any unnecessary characters from the data we are expecting; that is, convert "..BJ6452.78......" to "BJ6542:78". This can be achieved by using the following rule:

Rule = ??{*%d}.{%d}* -> $1:$2

At this point, we introduce another feature of the format file. If you use the following rule:

[Relevant mapping name]

Filtering Rule = External pattern

i.e., without "-> Citect String" included, CitectSCADA interprets this as "check that the string matches the External Pattern, if it does, copy it across unchanged".

If this rule is used:

Filtering Rule = External pattern ->

i.e., without "Citect String", it would mean: "If the string pattern matches then accept the record but copy a NULL string to the CitectSCADA database."

Using the above example again, we can add the restriction that any records with no data (i.e. a blank or NULL string) in the Tag field of the external data source will not be imported into CitectSCADA. We would add a [Tag_to_Name] section, and would have just one rule: that we accept everything except for a blank.

[Tag_to_Name]

RejectBlanks = ?*

..

Recall that CitectSCADA checks the pattern in each filter rule sequentially until a pattern that matches the string is found in the external data source. With this in mind, a huge range of conversions and filterings are possible by ordering the rules correctly and, in some cases, by making use of concatenation.

For instance, if certain string types need to be converted but others need to be copied unmodified to CitectSCADA, you could have a section with a set of rules at the top, followed by a final rule to let everything else through unmodified.

[Tag_to_Name]

Rule n = ...... -> ......

..

LetEverythingElseThru = %s

A single %s or *, without anything else, matches anything and everything, including blanks.

For an example of how to reject a particular string or pattern, let's suppose we want to reject any tags starting with "DFILE"(another real-life example). We would simply use the following:

[Tag_to_Name]

Rule1 = DFILE* -> !REJECT!

..

LetEverythingElseThru = %s

Clearly, it is pointless having the !REJECT! rule not followed by other rules concerning patterns that you do want to accept, as anything that does not match an input pattern is rejected. The logic behind the order that the rules appear can become particularly important when using a !REJECT! rule. You would typically have any reject rule(s) as the first rule(s) in the mapping. There would not be any point in putting a !REJECT! rule as the last rule in the mapping.

!REJECT! rules can also be useful where some text file generated by another system contains some sort of header lines that are not wanted, but the rest of the data is necessary.

See Also