Import Map Files

The import map file defines how data in a file can be imported into the project database. The map file system is very flexible and thus has many configuration options.

Overview

The following types of file are imported using an import map file:

• Excel Table (*.xls;*.xlsx;*.xlsm;*.xlsb)

• Access Database Table (*.mdb)

• DBF Format Database (*.dbf)

• Elecdes Project (*.prj)

Importing an Elecdes Project is special because it always defaults to using the same import map file: "EDS Project Import Map.dbf". This map file has been supplied pre-configured to import the various DBF files from an Elecdes project. It follows the format described below and can be modified to suit any special requirements.

Importing Excel Instrument Datasheets does not use import map files. Instead the standard datasheet templates are used to identify the data to import from the datasheet files.

Catalog Map Files

Import map files are also used for mapping the columns in the EDS catalog files to the columns in the ratings tables in the project database. They are used when you create or re-select a component from the EDS catalog, not for importing data from foreign data sources. These map files have filenames that end with "... catalog map.dbf". Catalog map files use only simple mapping from one column to another, without any filtering.

Import Map File Location

Import map files can be found in the <EDS>\IMP_IM\ImportMapFiles directory for imperial units or the <EDS>\MET_IM\ImportMapFiles directory for metric units.

Building Complex Import Map Files

When you are creating import map files you should plan to test the import on a temporary database that you can discard, then fine-tune the import map files, then repeat the import until the data is imported as intended.

It is easiest to build the import map file by adding a small number of mappings then testing the results before adding further mappings: first the instrument tags, then add the ratings, then add the terminals, then add terminal strips, cables etc, then add connections and so-on. Repeat this until you have one or more import map files with which you can import your entire data set.

Import Logs

Every import performed that uses an import map file will produce a unique import log file. The log file is saved in the same folder as the import data source, and has a filename in the format <source_name>.Import.<mapfile_name>.<timestamp>.log.

Log files are viewable in any text editor, and contain detailed information to assist with map file development and debugging, as well as providing an audit trail. Details include:

• Import time.
• Import source.
• Destination database and primary table.
• Map file used.
• Table structure analysis for Excel sheet import sources.
• For each record in the import source:
  • Logs the identifier of that record (eg its row number or index).
  • For each import map section evaluated against the record in the import source, logs:
    • The map section name.
    • The main action taken as a result of the section evaluation (created a record, updated a record, skipped creating/updating a record).
    • The status of the section evaluation/action (info/warning/error).
    • Details of the action (such as the component type and name and record table/IDX for created/updated records, or the reason/condition that caused the section to skip creating/updating a record).
    • Any user messages as a result of evaluation of LOGMSG(...) formula functions in the section.
• Summary information, including:
  • A list of map sections that requested non-existent source data fields/variables during the import (and the names of those fields/variables).
  • The number of source records that resulted in at least one database record being created or found.

Import Map File Fields

The primary contents of the import map file specify that data from the fields named in FIELD will be imported into the fields named in DESTFIELD. The other columns in the import map file provide only filters, conditions or descriptions for that primary mapping.

DESTFIELD	The name of the field in the chosen tag table of the project database into which the data will be imported (the destination for the imported data). Can alternatively define: A Ratings mapping (to populate fields in the ratings record for the component, rather than the tag record), by prefixing the field name with 'R1:'. A Relational mapping (to specify or set a parent link, or set any other type of link between records), by starting with a link type and colon (eg 'P1:'). A Data filtering (conditional) mapping (to prevent the map section from being processed, depending on the source data), by starting with a comparator (eg '!='). A Multi-part Identifier (to find/check for an existing record based on than just parent and a single naming/identifying column), by specifying a relational column alias, and also specifying 'Identifier' in CONDITIONS. Can be derived from source data using a formula, with the following considerations: Ratings mapping: In order to be detected as such, must start with 'R1:' before any formula hash pairs. Relational mapping: In order to be detected as such, must contain a colon (':') outside of the formula hash pairs (eg L#STR(100+[subloopnum])#:Tagname, rather than #CONCAT("L", STR(100+[subloopnum]), ":Tagname")#). Data filtering (conditional) mapping: In order to be detected as such, must start with a comparator (eg '!=') before any formula hash pairs. Multi-part Identifier: In order to be detected as such, must start and end with angle brackets ('<' and '>') outside of the formula hash pairs (eg <#IDALIAS#>), and also specify 'Identifier' in CONDITIONS. May be left blank if: FIELD is populated with a formula. 'Identifier' is specified in CONDITIONS, and FIELD is populated with a variable name that had a record identifier saved to it via the SETRECORD formula function in a earlier section.
FIELD	The name of the field in the table that is being imported (the source of the data). Can alternatively specify: A formula to use or manipulate one or more fields from the source table. An assignment for the data to place into the field named in DESTFIELD. A "lookup" to get an alternate value to place into the field named in DESTFIELD that is based on the data found in the field named in the lookup. That the current table name, file name, sheet name, or value from an absolute cell reference should be fetched from the source. May be left blank if: 'Identifier' is specified in CONDITIONS, and DESTFIELD contains relational column alias for a Multi-part Identifier.
COMP_TYPE	The type of component to create (or the types of existing components permissible to find and update) for each record that is imported. For relational columns, the COMP_TYPE filters the type(s) of component that can be linked to the main component created/found from each record that is imported. Can be derived from source data using a formula.
ITEM_NUM	A section number used to identify which entries of the import map file apply to which component when multiple components will be created from each record that is imported. Can instead contain "Include" followed by a name to mark a section that is to be included into other sections.
ITEM_DESC	A brief description of the component or link that is created by the section from the import map file. e.g. "JB T-Strip" or "Connection for wire". This field is optional. You can enter any text that you feel is appropriate.
TABLE	A filter for the name of the file, table or view that is to be imported. Specifies that only files, tables or views whose name matches the pattern defined in TABLE will use that entry of the import map file. This field is optional. If it is empty then all imported files, tables or views can use the map file entry.
SHEET	A filter for the name of the Excel sheet that is to be imported. Specifies that only sheets whose name matches the pattern defined in SHEET will use that entry of the import map file. This field is optional, and only applies when the source is an Excel file. If it is empty then all imported sheets can use the map file entry. Note: To restrict a map entry to a particular Excel filename (as well as, or instead of a sheet name), use a filter in the TABLE field.
DESTTABLE	The name of the table in the project database that is the destination for the imported data. This field is optional. If it is empty then the data will be imported into the table that is chosen at the beginning of the import procedure. DESTTABLE is most likely to be used when one section of the map file must import to a different table than the other sections. e.g. Importing an I/O channel to the "TerminalGroup_Terminal Groups" table. Can be derived from source data using a formula.
CONDITIONS	One or more of a specific list of conditions (comma separated) that affect the behaviour of a mapping or whole map section. If there are no conditions required then this field should be left empty.
NOTES	Any notes or description that you wish to enter in the import map file. This field is optional.

Mapping Types

Note: By default, any blank result from the evaluation of FIELD (for any of the below mapping types) will not overwrite data in an existing database record, unless the mapping also specifies the WriteBlanks condition.

Column Mapping

In the simplest case the map file merely defines the 'mapping' of data between columns in the import table and columns in the chosen tag table of the project database.

Fields in the table to import appear in the "FIELD" column, and fields in the tag table of the project database appear in the "DESTFIELD" column. In the example, data from the "Desc" column in the import table will be imported into the "Description" column of the tag table in the project database.

Each row of the map file is a single mapping between the two fields named on that row.

Note: The FIELD may also refer to a previously set variable from a formula mapping.

Formula Mapping

Import Map files support the use of Formulae in the FIELD column. Formulae allow data from one or more columns in the import table to be manipulated and combined to produce a final value for the DESTFIELD.

Formulae can also be used to trigger side-effects that affect how subsequent formulae, sections, or import table records are processed. For this purpose, a formula mapping will be evaluated even if its DESTFIELD is blank.

See Formula Evaluator for a description of the syntax required for formulae, and Function Reference for a list of functions that can be used to manipulate data from the import table.

Source Data Parameters

In the context of formula evaluation for import mapping, source data expressions must refer to fields in the table being imported, a fixed reference, a previously set variable, or a relational column whose first relational code is a variable name that resolves to a string (in the format "<IDX>,<Tablename>") that references an existing database record (typically the database record for an earlier section, saved via SETRECORD).

The field names referenced are not case sensitive.

Formula Side-Effects

In addition to producing a value for a mapping, formulae can be used to alter import behaviour outside of a mapping, by:

• Setting variables that can be retrieved by subsequent formulae in the same or latter sections (while processing the same import source record), or by all formulae in all sections while processing subsequent import table records.

• Altering control flow to skip sections, or repeat one or more sections multiple times per import table record (typically in conjuction with the use of variables).

When a formula function with side-effects is evaluated as part of formula evaluation (which occurs top-to-bottom for all mappings in a section), the side-effects either occur immediately (for SET, GETSET, UNSET), or are deferred until the section finishes processing (for SETRECORD, REPEATSECTIONS, SETNEXTSECTION).

If a section contains a data filtering (conditional) mapping that does not evaluate to true, any formulae following that conditional mapping (in the same section) will not be evaluated, and thus will not produce side-effects.

If a section contains a MustBeNew condition, and that condition is not met, then any variables specified in evaluated SETRECORD functions (in the same section) will be set to an empty string, rather than to the existing record.

If a section contains a FormulaEvaluationOnly condition, then formula mappings and data filtering (conditional) mappings in the section will be evaluated as usual, but the map file will not be used to create or find a component (and thus does not need any destination column mappings or component identification aspects).

Assignment Mappings

It is possible to define the data explicitly to be created from the map file. Instead of having a field name in the "FIELD" column, the column can contain the data to insert preceded by the '=' symbol. This is called an assignment mapping.

Data from an assignment mapping will be exactly the same for every component created from that section of the import map file.

An assignment mapping is often used to set the ratings table name (by setting R1_Table), see Ratings Data, below.

In the example map file fragment, the assignment mapping will cause the text "FLOW" to be written into the "Function" column of all of the records created using the mappings from the import table.

Fixed References

Sometimes the value you need to use is not in the table itself, but in the metadata of the table, such as the table/sheet name, or the file name. When importing from Excel, you may also have arbitrary data in the first row, before the column headings.

Fixed references allow you to retrieve these values, which are then treated as assignments when entered into FIELD as a column mapping. They can also be used as a field name or source data parameter inside a formula.

Reference Name	Notes
$TABLE	Retrieves the table name (MDB imports), or filename without extension (DBF and Excel imports) of the current import data source.
$SHEET	Retrieves the sheet name of the current import data source (Excel only).
$A$1	Extracts the value from cell A1, or any other cell reference (Excel only).

Lookup Mapping

You may wish to insert into the destination table one of a number of values that are not in the table to import, but that you can choose based on the data in the table to import. For each value found in the table to import, you will "look up" that value in a list to find the corresponding value that you want to insert into your destination table. A lookup provides a mapping from one value to another.

A lookup mapping is the recommended method to set the ratings table name (setting R1_Table), see Ratings Data, below.

Map file:

Lookup table (e.g. TypeCodeToRatingTable.dbf):

A lookup mapping replaces the contents of the "FIELD" column in the map file, must always start with the word "Lookup", and has a strict format of keywords that indicate what each part means. The format of a lookup is as follows:

Lookup <Field name> in <Lookup table column to search> from <Lookup table DBF name> return <Lookup table column to return>

Each part enclosed in <...> is a parameter that may differ between lookup mappings. The words between these parameters ("Lookup", "in", "and", "from" and "return") are the special keywords that must be used.

• <Field name> is the name of the field from the table to import that contains the value that must be found in the lookup table.

• <Lookup table column to search> is the name of the column from the lookup table in which the data from <Field name> must be found.

  The "in" keyword and parameter can be omitted if the column is named "CODES".

• You can use more than one column from the table to import to identify the value that you want to create. Further "<Field name> in <Lookup table column to search>" pairs can be used following the keyword "and". For example:

  Lookup "Type" in "CODES" and "ClassName" in "CLASSES" from ....

• <Lookup table DBF name> is the name of a DBF file that is a lookup table, namely a table with two columns of paired values. The two columns are named in the "in" and "return" parameters.

• <Lookup table column to return> is the name of the column from the lookup table that contains the corresponding value to return from the found row.

  The "return" keyword and parameter can be omitted if the column is named "VALUE".

In the example, an instrument will be created and will be named by the contents of the "Tag" field of the table to import. The name of the ratings table for that instrument is set by the lookup mapping in the second row of the map file. The lookup will fetch the data from the "Type" column of the table to import, which would contain an ISA instrument type code (e.g. PIT, FE, etc). The fetched type code is searched for in the "CODES" column of the lookup table file "TypeCodeToRatingTable.dbf". If the type code is found in the "CODES" column then the contents of the "VALUE" column of the lookup table file are put into the "R1_Table" column of the tag table in the project database.

For example, if the "Type" column of the table to import contains the value "FS" then this will be found on row 13 of the lookup table "TypeCodeToRatingTable.dbf", so "Instrument_Flow_Switches" will be set into the "R1_Table" column of the tag record in the project database.

A lookup mapping can also be used as part of formula, by using the MAPLOOKUP formula function. The functionality is the same, however, note the difference in syntax.

Component Types

It is important to define the component type for sections that are importing components into the Tags table, as tag records always require a component type to be set.

There are three ways to specify a component type:

• Directly via the "COMP_TYPE" column in the import map file.

  For example: Device

  

• Indirectly via the "COMP_TYPE" column in the import map file, by using a formula that derives a value from the source record data.

  For example: #CompType#

  For example: #IF(EQ([InstType], ""), "Device", "Instrument")#

• Via a "Component_Type" data mapping in the import map file.

  Put the column name "Component_Type" in the DESTFIELD column of the import map file and an appropriate value either imported, assigned or looked up from the FIELD column. This method, using a lookup mapping, is used when linking P&ID tables to Instrument Manager.

The component type only needs to be specified on one line of each import section. The line that imports the name of the component is usually a good choice.

The component type must be one of (or a comma-separated list of multiple of) the built-in types or user types.

• The component type text must be exactly correct as it must match the type in the program. This includes spaces between words, e.g. "TERMINAL STRIP".
• The built-in types are: AREA, ENCLOSURE, INSTRUMENT, DEVICE, PLC CARD, TERMINAL STRIP, TERMINAL, TERMINAL GROUP, CABLE, CORE, and WIRE.
• The user types in a default installation are: TANK, VALVE, LINE and DOCUMENT. The component type text for user types can be found in the TYPE_STRING of the user type configuration.

If a comma-separated list of component types is provided, the program will first attempt to find an existing component of any of those types (in the order specified), and if there was no match, create a component using the first type specified.

The only time a component type may be omitted is if the map section is importing a non-Tag component (such as a diagram), or if the map section is not permitted to create a new component (ie the MustExist condition is specified).

Component Type Filter

The "COMP_TYPE" column specifies a filter when specified on the line for a relational column, see Relational Columns below. Used this way the component type specifies the type(s) of component that can be found for the relational column and thus linked to the imported component. For a filter for a relational column you can specify more than one component type in a comma separated list, e.g. "DEVICE, TERMINAL STRIP".

"COMP_TYPE" can be omitted for a relational column, if you are sure that the desired component can be found without filtering by component type (ie if there is only one component matching the value for the identifying column [eg Tagname] and the parent component [if specified], or the related record is directly specified).

Map File Divided into Sections

Rows in the map file can be divided into sections. The configuration information in each of these sections defines the mappings for a single component from each record in the import table. By having more than one section it is possible to import more than one component from each of the records in the import table.

The numbers in the "ITEM_NUM" column define the sections. All mappings with the same item number are considered to be in the same section and apply to importing the same item. Mappings with a different item number are considered to be in a different section and apply to importing a different item. Sections are processed in the order in which they appear in the map file regardless of the actual item number used for each section. This is important for importing containment relationships, see Relational Columns below.

Section processing order can be manipulated (typically to repeat one or more sections, in conjuction with the use of variables), by using import-specific control flow formula functions in formulae in the FIELD column.

In the example there are two sections, 1 and 2. These will import a device and a terminal using the mappings from the import table.

Multiple Sections versus Multiple Import Map Files

An alternative to separate sections all in one import map file is to use separate import map files and perform an import from the same source tables with each separate import map file. In a simple case both methods will produce the same results. However, the results will be different if the condition Must be New is specified for any of the mappings. See Conditions for Mappings below.

Using multiple sections differs from using multiple import map files because Instrument Manager considers that a component created during the import is "new" in the project database until the import operation is completed, not only during the section of the import map file that created it.

• During one import operation using a single import map file, a "new" component is considered new until that entire import is finished, even if that component was created by a previous section of the import map file.

• During separate imports using separate import map files, a component will no longer be "new" during the later import map files.

The order in which source records and sections is processed differs when comparing multiple sections versus multiple map files.

• When using a map file that contains multiple sections, all map sections are applied to the current source record before moving to the next source record.

• When using separate map files, all source records are processed using each map file in turn (since each map file is used in a separate import operation that you must run).

If you are importing a complex data set then you can expect to use a combination of separate sections and separate import map files.

Data Filtering for Sections

The sections of the map file can have a data filter associated with them to determine whether or not the section should be used for a particular record in the import table. The data filter can be applied to any field in the import table, or the result of a formula (see formula mapping).

The data filters in the example are those with '==' or '!=' in the "DESTFIELD" column. The part following the '==' (equality) or '!=' (inequality) is a pattern that will be compared to the data in the column in the import table which is specified in the "FIELD" column.

If the data in the column named in "FIELD" does not match the filter, then for the current record in the table being imported: remaining mappings in the section will not be evaluated; the section will not create/update a record in the project database.

The supported filters are:

• '=' or '==' for "equal to"

• '!=' or '<>' for "not equal to"

• '<' for "less than"

• '<=' for "less than or equal to"

• '>' for "greater than'

• '>=' for "greater than or equal to"

• '*' will match any number of letters or digits

• '?' will match any single letter or digit

• '@' will match one or more letters

• '#' will match one or more digits

The pattern can be enclosed in quotation marks, for example: =="FT*".

Multiple filters can be specified in one entry by separating each by a semicolon, for example the filter !="TBC";!="N/A" means that the map file section will only be used for records where the data in the column named in "FIELD" is not "TBC" and is not "N/A".

In the example, the data in the TAG field of the import table, is compared against "FT*". If the tag starts with FT then section 1 will be used, importing an instrument. Otherwise section 2 will be used, importing a device.

Filters can contain multiple patterns, wildcards, and comparisons. See Specmatch for more details about configuring filters.

Included Map Files

You can add a directive to a map file that causes the content of another specified map file to be inserted into the main map file at the position of the directive, prior to evaluation. These map file inclusions can be nested.

Doing this can be useful when the development of a single map file with multiple sections becomes too unwieldy, due to a large number of complex sections (as can be the case when source data is very abstract or irregular, and requires significant filtering, processing, or generative component creation).

To specify a map file inclusion directive in a map file, specify IncludeMapFile <mapfilename> in the DESTFIELD of an otherwise empty record in the main map file. For example: IncludeMapFile ImportCables.dbf

The program will search for <mapfilename> in the following locations:

1. As an absolute path.
2. Relative to the path of the main map file.
3. In the "ImportMapFiles" subdirectory of MET_IM/IMP_IM directory for the current client configuration.
4. In the USER_SYMB folder.
5. In the "ImportMapFiles" subdirectory of MET_IM/IMP_IM directory configured for the current installation.

If found, the records/mappings from the found file will be copied into a temporary copy of the main map file (in memory) at the location of the directive, with any additional IncludeMapFile directives recursively processed and included as well.

If an IncludeMapFile directive specifies a value in the ITEM_NUM column, this value will be treated as a prefix to apply to the ITEM_NUM's of all the mappings in the included file, when they are copied into the temporary copy of the main file. This allows one map file to be included multiple times, with each inclusion producing distinct sections (assuming unique prefixes were specified in ITEM_NUM), which together with the use of variables in formulae mappings in both the main map file and the included map file, allow the multiple inclusions of the same map file to do different things.

To aid in development and debugging, if any IncludeMapFile directives are present in a map file, the program will save the temporary copy of the main map file (in memory, with the inclusions) as a DBF file with the extension .WithIncludedMapFiles.dbf, in the same folder as the main map file.

Warning: The developer of a map file must ensure that the resultant section names in ITEM_NUM in the combined map file (after inclusions) are unique, otherwise the map file will not function as intended.

Warning: IncludeMapFile directives with ITEM_NUM prefixes are not currently compatible with the definition of Include sections (for Included Mappings) in the map files being included. Either move the Include sections into the main (top level) map file, or use variables in formulae mappings as an alternative to Included Mappings for reducing repetition of mappings.

Included Mappings

You can create a map file section that is to be included into the mappings of another section. These inclusions can be nested such that a section to be included, itself includes another include-section.

Use this method to group together mappings that would otherwise be repeated identically in one or more sections. The overall size of the map file can be dramatically reduced in cases where you have a number of nearly-identical sections except that each has a different data filter or different parent hierarchy or connection relationship.

Where an include-section is included into another section, a relational prefix can be applied to all of the data mappings specified in the included section. This is specified in the DESTRELPFX column of the map file. However, if you are trying to simplify the specification of parent hierarchy or connection/links, you should consider directly specifying related records, rather than using Included Mappings.

To define a map section that is to be included, set the ITEM_NUM column to a text value starting with the word "Include" followed by any unique name for the section that you choose, e.g. "Include terminal".

Warning: Definitions of map sections to be included are not currently supported in map files that are going to be included into other map files with an ITEM_NUM prefix.

To include an include-section into the mappings of another section, set the DESTFIELD column to the full name of the include section, including the word "Include".

In the example above, sections 100 and 101 both include a section named "Include device" that maps a device and its enclosure name. For section 101, the included mappings will be prefixed by C2: so, for example, the final enclosure mapping will be C2:P1:Tagname.

Relational Columns

In order to import linking information, "DESTFIELD" can contain relational columns, which can be used to define the parent or container relationships and electrical connection relationships between the imported components. These relational column mappings do not import data. Instead they create links in the database between two components.

In the example, the mappings beginning with "P1:" are the relational mappings.

Related components are identified by component name (by default), which is always in the "Tagname" column in the project database. The relationship in the import map file must therefore usually be "Tagname" with the required combination of "P1:", "C1:" and "C2:" relationship prefixes.

In the special case that you must identify a component by another column, use the "Identifier" condition to specify that the mapping (to a column that is not Tagname) identfies existing components. You may need to use something different to the component name if you are importing another database, importing its primary key value into a column in Instrument Manager and using that primary key value to identify existing components that have been imported previously.

For each of the three sections in the example map file, a separate component will be imported. The P1:s in the "DESTFIELD" column indicate parent or container items. Two P1:s in a row, "P1:P1:", indicates a grandparent, or two layers of containment. For example, a panel is usually two layers away from a terminal because the panel will contain a terminal strip, device or instrument that then contains the terminal.

Components that are to be containers must be imported before the components they are to contain. This is accomplished by placing the section for the mappings that will import the container components higher up the map file (towards the start) or by importing the container components in a previous import operation.

In the example, each component is contained by the component imported immediately prior to it. The terminal (item 3) is contained in the instrument (item 2), which is in turn contained in the enclosure (item 1).

A relational column requires that the correct component type is specified, see Component Type Filters above. If a related component could be one of a number of types, then those types should be listed together in a comma separated list, e.g. "DEVICE, TERMINAL STRIP".

If the component relationship you wish to create a link for is to a component already found/created by an earlier section in the map file, you may find it easier/simpler to directly specify the related record (using variables), than to specify the relationship using multiple relational mappings to define the complete relational hierarchy.

Component relationships can also be cleared in conjuction with the WriteBlanks condition.

See also: Relational Columns.

Relational Filtering

Relational mappings can have extra information specified directly in the map file to ensure the correct components become linked. This is useful when certain data is assumed rather than specified in the imported data. Note that this is not an assignment of a value into a related component. Instead, it is specifying that a related component must have a specific value, most likely a specific Tagname.

In the example, a terminal is contained in a junction box. The terminal is actually in a terminal strip named "TB" that is inside the junction box, but the strip name "TB" is assumed because it's the same terminal strip name in all of the junction boxes. Therefore the second line of the map file specifies that the terminal should be linked to a terminal strip named "TB".

Complete Relational Hierarchy

It is usually important to identify all levels of hierarchy. Consider that you are importing a record that defines a terminal that is part of a terminal strip named non-specifically as "TB" in a junction box named "JB3". It is not enough to specify only the immediate container of the terminal with a relational column "P1:Tagname" with the field containing the text "TB" from the imported data. If there are many terminal strips named "TB" and you specify only this immediate container, then Instrument Manager would simply find the first terminal strip named "TB" and import your terminal into that strip.

To correctly identify the strip you would also need to specify the panel that contains the strip with a relational column "P1:P1:Tagname" with the field that contains the junction box name "JB3" from the imported data. Instrument Manager will then look for a terminal strip named "TB" in an enclosure named "JB3" and import the terminal into that strip.

However, if you know that the name of the component is unique then you can use the "AnyParent" condition as described below.

Connection Mapping

Connections exist in the conductor records: wires and cable cores / conductors. Each conductor can have two connections, which are linked via the C1 and C2 relational columns, which represent the connection at either end of the single segment of the conductor. You can import connections by specifying a relational column mapping for C1 or C2.

In the example a cable-core/conductor is imported from the field named CORE2. It is contained in the cable that is named in the field CABLE2.

The core/conductor is connected to a terminal that is named in the field TERM1. The connection is created in the project database as a link in the C2 relationship of the core/conductor by specifying "C2:" in DESTFIELD. The container of the terminal is a terminal strip that is named in the field TBLOCK1 and the terminal strip is contained in a junction box that is named in the field JB1.

The relational column mappings that start with "C2:" will ensure that the correct terminal, including its complete relational hierarchy, will be found and connected to the core/conductor.

The core/conductor is also connected to another terminal, this one being named in the field TERM2. The connection is created in the project database as a link in the C1 relationship of the core/conductor by specifying "C1:" in DESTFIELD. The container of the terminal is an instrument that is named in the field TBLOCK2 and the instrument has no named container - so any instrument with the correct name will suffice. It would be expected therefore that the instrument name was unique.

• C1:Tagname will ensure that a terminal is found with the same name as the text from the field TERM2.

• C1:P1:Tagname will ensure that the terminal is contained in an instrument with the same name as the text from the field TBLOCK2.

The records for the terminals, instrument, terminal strip and junction box must all be present in the project database to resolve this relationship. They can however have been created by a previous import map section of the same import map file processed during the same import operation which is yet to be completed.

See also: Relational Columns.

Directly Specifying Related Records

You can avoid having to specify a complete relational hierarchy for a relation, by directly specifying the related record. Directly specifying related records can simplify your import map file and make it easier to read and understand, especially in the typical usage when variables are used.

To directly specify a related record, enter the relation code prefix (including the colon) in DESTFIELD (eg "P1:"), but do not include a column name. Then, in FIELD, enter a variable name, source data name, or formula, that will evaluate to a string in the format "<IDX>,<Tablename>" (eg "123,Tag_Tags").

Typically FIELD will specify a variable name that has been used to capture the IDX and Tablename of the database record found or created by an earlier section, via the use of SETRECORD formula function in that earlier section.

See the SETRECORD documentation for an example of capturing the database record for one section and then using it to directly specify a related record in a subsequent section.

Ratings Data

For ratings to be imported for a component, the destination table for the ratings data must be specified. This can be done using an assignment mapping, specifically setting the data for the "R1_TABLE" column. Alternatively you can add the ratings table name directly to the data to be imported, or Instrument Manager can determine the correct ratings table from the name of an instrument. These other two options are described below.

Given that a rating table name for an Instrument Manager database is not usually going to exist in non-Instrument Manager data that is to be imported, the best method to determine the rating table name is to use a Lookup Mapping, which is described above.

Destination fields that are in the ratings record are specified by the "R1:" prefix in the "DESTFIELD" column of the import map file.

You can assign a fixed value to a field in a ratings record, see Assignment Mappings above.

Instrument Manager will create a new record in the specified ratings table to contain the ratings for the new component that is imported. The ratings data itself is imported into the ratings record instead of the tag record for all destination fields with an "R1:" prefix.

The example mappings will create an enclosure component and link it to a new rating record in the Enclosure_Enclosures table. The Desc and Width fields in the import table will be read into the Description and Width fields of the new ratings record in the Enclosure_Enclosures table. The Status field of the new ratings record will be assigned the value "Imported".

Another method to set the ratings table is to modify the data files that are to be imported and add a column that specifies the Instrument Manager ratings table for each component to be created. This is particularly useful for instruments which have numerous different ratings tables (other types, such as enclosures, usually have only the one ratings table so an assignment mapping is sufficient). See also Ratings Table Determined from Tagname below and Lookup Mapping above.

If the mapping for the rating table name cannot determine a valid rating table name then the rating record for the component will be created in the default table for that component type. For example, for instruments this would be Instrument_Instruments.

See also: Relational Columns.

Instrument Ratings Table Determined from Tagname

Instrument names can be specific to their type, e.g. "PT" is used for a "Pressure Transmitter" instrument. If you do not specify any mapping for the destination field "R1_TABLE" for an instrument, then Instrument Manager will attempt to use the name of the instrument to choose a ratings table.

Each instrument rating table in the default project database has an associated "Nameseq block". When you import a component, Instrument Manager will attempt to find the best match from the component name to a name sequence. If a ratings table specifies the sequence that Instrument Manager chooses as the best match, then the ratings record will be created in that table.

For example, the instrument name "PT123" would best match the name sequence "InstMgr-Instrument_Pressure_Transmitters" in the default name sequence files, Names IEC or Names ANSI. That sequence has BLOCK1 = "in_pt". In the default Instrument Manager project database, the table "Instrument_Pressure_Transmitters" specifies "in_pt" as its "Nameseq Block".

See also: Name Sequencing.

Table Filtering

Each row in the map file can have an associated filter to ensure that it is used for import tables only of a specific name or names. The table filter is stored in the "TABLE" column in the map file. It is specified as a file name pattern to be compared to the import table name (MDB) or filename (DBF, Excel). Table filters are evaluated by Specmatch.

In the example, if the import table starts with "Dev" then the data from the "DEVFUNC" field will be imported into the "Function" field. If the import table starts with "Inst", the data will come from the "DESCFUNC" field.

An alternative to table filtering is to ensure that you choose only the correct tables to be imported with a specific import map file, and use different import map files for different tables to be imported.

The map file for the Elecdes Project import, "EDS Project Import Map.dbf", provides an example of table filtering. The Elecdes Project import will import numerous different DBF format files in one operation using a single import map file. Each imported file requires its own import map file section, so they are filtered by the TABLE column in the import map file.

Excel Sheet Filtering

All sheets in an Excel file will attempt to be imported using the import map file. Therefore, if an Excel file has multiple sheets, and not all are applicable to all sections of the map file, then it is necessary to filter the map file by Excel sheet.

Excel sheets are filtered using a Specmatch filter in the SHEET column of the map file. You should specify an appropriate sheet filter for all entries in the import map file.

SHEET filters can be used in conjunction with TABLE filters (which filter by file name of the chosen Excel files).

Conditions For Mappings

You can specify one or more (comma separated) conditions for mappings in the import map file, that affect the behaviour of the mapping or map section they are specified in.

Identification Conditions

These conditions control how a component described by a map section is identified, so that it can be checked for existence, prior to Existence Conditions being evaluated.

By default, components are identified by name, which is the "Tagname" column (or "Filename" for Output Diagrams) in the default project database. A component is considered to exist if there is a record with the same name and the same container hierarchy (as described in Complete Relational Hierarchy above). The Identifier condition enables you to identify components by a previously imported ID or primary key or some other column (or relational column alias) that is not Tagname.

AnyParent

This condition specifies that the imported component will match any existing component based solely on the name of the component without any regard to the parenting of that existing component - i.e. the existing component can have any parent, or no parent.

This condition should always be specified on a mapping that specifies a name of a component. This condition applies only to the specific mapping on which it appears, not to the entire section of the map file. If this condition is specified on the mapping that will set the tagname of the component, then it means that the main component that is being imported can have any parent. If this condition is specified on the mapping that specifies the name of the parent component (e.g. P1:Tagname) then it means that the parent of the main component can have any parent.

Use this condition if you know that all components of this type have unique tagnames and thus you can dispense with specifying the mappings to identify the parenting of the component. This is possibly the case for instruments in that, no matter what area an instrument exists in, it will always have a unique name.

Do not use this condition if there may be numerous existing components with the same tagname but in different parent or container components. For example you are unlikely to be able to specify this condition for importing terminal strips if they are all generically named "X1" for the first strip in each different junction box.

AnyParent is ignored if you provide a parent relationship mapping.

AnyNonBlankParent

Equivalent to specifying both AnyParent and NoBlanks.

SetParent

This condition specifies that the parent for the imported component will be set to the component specified by the relational columns in the map section. The primary component will match any existing component based solely on the name of the component without any regard to the current parenting of the existing component, the same as for the AnyParent condition.

This condition should always be specified on a mapping that specifies the name of the primary component that will have its parent set or changed, not to the mapping for the parent.

Identifier

By default, existing components in the Instrument Manager database are identified by their Tagname and the Tagname of any parent relationship. The "Identifier" condition tells Instrument Manager to identify existing components for this map section by some other column than Tagname, such as a unique ID value copied from the source database, or a relational column alias that contains a formula that builds a unique multipart ID (for example concatenating a wire name with its termination names).

Use this condition if you are importing from another relational database with an identifier column (usually also the primary key) that uniquely identifies each row and you have previously imported that identifier into the Instrument Manager database. For example, if you have imported conductor and terminal components by name while also importing their ID column value then, in a separate import operation, you can use the ID values to identify the components to import electrical connections from the source database.

Use this condition if you have created/updated a component in an earlier section, and now want to update that component again in a latter section. You will need to use the SETRECORD formula function in the earlier section to save the component record identifier to a variable, and then in a mapping the latter section (with Identifier specified), specify the variable name in FIELD, and leave DESTFIELD blank (or specify 'IDX' in DESTFIELD).

If you use this condition with a relational column alias that contains a formula that builds a unique multipart ID, you may leave FIELD blank, and you must ensure that the map section populates any constituent relationships/links (eg C1/C2) that are used by the relational column alias to produce the multipart ID.

Identifying existing components by an ID value also means that two or more components with the same tagname can be uniquely identified in secondary import operations.

Identifier implies AnyParent if you do not provide a parent relationship. This means that a unique ID value can identify an existing component without regard to any parent relationship that it may have but, if your ID is not unique, you can still specify the parent relationship.

FormulaEvaluationOnly

This condition specifies that the map section should only have its formulae evaluated, and does not identify or attempt to create or update a component/record.

As such, this condition is only useful to help structure map files that make heavy use of formula functions to set/get variables, and alter map section control flow.

Existence Conditions

These conditions force or prevent a map file section from creating/updating a component, depending on whether the identified component already existed in the database.

NOTE: Components that are created in the same import operation but from previous sections in the same import map file are still considered to be new until that import operation is completed. See Multiple Sections versus Multiple Import Map Files above.

AlwaysCreate

This condition specifies that a new component must always be created from the import map file section, even if there is an existing component with the same name (and in the same containing component if appropriate).

Exercise caution with this condition because it will always create new components, so you would only want to run the import operation once on any particular project database. Compare this to the MustBeNew condition, which will simply fail to create any components if the data is imported a second time into the same database.

This condition can be useful for importing wires when you know that there will be existing wires with the same name that cannot otherwise be differentiated. Thus you must force the import to create a new wire because otherwise it would find an existing unrelated wire and import the data into that existing wire.

CanExist

This condition tells Instrument Manager that a component can be imported (an entire section of an import map file may be used) even if the component already exists in the database.

Since this condition applies to an entire section of the import map file, you need specify this condition on only one record of an import section. The line that imports the name of the component is usually a good choice.

Use this condition to suppress the question about overwriting existing components.

MustExist

This condition allows you to specify that an entire section of an import map file may only be used if the component already exists in the database.

Since this condition applies to an entire section of the import map file, you need specify this condition on only one record of an import section. The line that imports the name of the component is usually a good choice.

Use this condition when you want to add data to only existing components. You can use one "MustExist" section and one "MustBeNew" section in the import map file where each section specifies some different mapping for existing or for new components.

MustBeNew

This condition allows you to specify that an entire section of an import map file may only be used if the component does not currently exist in the database.

Since this condition applies to an entire section of the import map file, you need specify this condition on only one record of an import section. The line that imports the name of the component is usually a good choice.

Use this condition when you want to import only new components and skip all existing components (thus protecting any data you may have modified in the project database since they were imported). You can use one "MustExist" section and one "MustBeNew" section in the import map file where each section specifies some different mapping for existing or for new components.

NOTE: Components that are created in the same import operation but from previous sections in the same import map file are still considered to be new until that import operation is completed. See Multiple Sections versus Multiple Import Map Files above.

MustExistForRemainder

This condition is similar to "MustExist" except all remaining sections are also skipped if the component does not exist in the project database.

Use this condition if you only want to import components related to an initial component if that initial component already exists in the database. For example, if "MustExistForRemainder" was specified on an import map file section for a cable, then following import map file sections for the cores or conductors of that cable would be skipped if the cable did not exist.

MustBeNewForRemainder

This condition is similar to "MustBeNew" except all remaining sections are also skipped if the component already exists in the project database.

Use this condition if you only want to import components related to an initial component if that initial component does not yet exist in the database. For example, if "MustBeNewForRemainder" was specified on an import map file section for a device, then following import map file sections for the terminals of that device would be skipped if the device already existed.

Action Conditions

When a map section has created or found a component, these conditions trigger additional actions to occur after the section finishes processing, or after the whole import finishes processing.

CatSelect

This condition applies to a whole map section, and causes a Catalog (re)selection to be performed on the current component as soon as the map section finishes processing (rather than at the end of the import process).

Subsequent map sections will be able to find or make links to any child component records created by the catalog (re)selection, as if they were created by their own map section(s).

Delete

This condition specifies that the existing component identified by the map section must be deleted. Implies MustExist (ie, if no existing component is found for the map section, nothing is imported).

If the found component has a rating record, this will also be deleted automatically.

If the found component is a Device, Instrument, Terminal Strip, PLC/DCS Card, or Cable, then any child components (Terminals, Terminal Groups, Cores) will also be deleted (ie, DeleteChildren implied).

Since this condition applies to an entire section of the import map file, you need specify this condition on only one record of an import section. The line that specifies the name or identifier of the component is usually a good choice.

DeleteChildren

This condition specifies that any children of an existing component identified by the map section must be deleted. If no existing component is found, this condition has no effect, and the section will be processed as if this condition was not specified.

Child deletion is nested/recursive, so if specified on an Area component, any direct or indirect child Areas, Enclosures, Devices, Terminals etc will be deleted (along with any rating records for those).

Child deletion will occur prior to any child creation that may occur due to use of the CatSelect condition.

Since this condition applies to an entire section of the import map file, you need specify this condition on only one record of an import section. The line that specifies the name or identifier of the component is usually a good choice.

GenerateDiagram

This condition applies to a whole map section (one that creates or updates an Output Diagram record), and forces the identified Output Diagram to be generated at the end of the import.

Alternatively, the options in the import wizard can be used to force the generation of all imported Output diagrams, without needing to specify GenerateDiagram in the map file.

Individual Mapping Modifier Conditions

These conditions are applicable to individual mappings in a section, and typically alter the default behaviour for reading source data and/or writing to destination record(s).

NoBlanks

This condition will prevent the map section from importing a component if the source data (ie the result of the expression in FIELD) was blank.

Using this condition is equivalent to having a second mapping that has the same FIELD, with a DESTFIELD of !="" (ie a Data filtering (conditional) mapping that will stop processing the map section if FIELD evaluates to blank).

NoDataChange, NoRatingChange, NoRelationChange

These conditions allow you to prevent a single mapping from overwriting an already populated (ie non-blank) tag or ratings column (e.g. a DESTFIELD of "Function" or "R1:Catalog Number") or relational link (e.g. a DESTFIELD of "C1:...") of an existing component found by the map section. If the column or link is not populated in the record of the existing component (or if the map section creates a new component), the mapping will populate it as usual (assuming the source FIELD does not evaluate to a blank value itself).

If specified on a mapping with a DESTFIELD of "R1_TABLE", any of these conditions will prevent an existing ratings record from being deleted and recreated in a different ratings table.

Note: All three conditions (NoDataChange, NoRatingChange, NoRelationChange) have the same effect, irrespective of the type of mapping they are specified on. The three different conditions are provided for clarity in the map file, so you can use NoDataChange on Data mappings, NoRatingChange on Ratings mappings, and NoRelationChange on Relational mappings.

WriteBlanks

This condition allows a blank value from the source data (ie the result of the expression in FIELD) to overwrite an existing value in the column specified by DESTFIELD (contrary to the default behaviour which is to avoid overwriting existing data with blanks).

This condition can also be used on base-level relational mappings (eg "C1:Tagname" in DESTFIELD) to clear a related record link if the result of the expression in FIELD is blank (or 0, in the case of directly specified related records).

Note: For WriteBlanks to clear a "P1:" relational mapping, SetParent must also be specified on a mapping that specifies the name of the primary component.

Map File Editor

Map files are DBF format database tables and can be edited using any DBF format compatible editor. The EDS Database Editor can be started from within Instrument Manager to edit map files.

1. Select "Edit a mapping file" from the "Tools" menu in Instrument Manager.

2. A file selection box will be displayed.

3. Select the map file to be edited and click [Open].

4. Database Editor be started and will open the selected database file.

   The map file can be edited with the full functionality of Database Editor.