Electrical Design Software | Elecdes Design Suite by Scada Systems Ltd

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.

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 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 instead contain a filter for the data to import from the field named in FIELD.

FIELD

The name of the field in the table that is being imported.

The source of the data.

Can instead contain a formula to concatenate multiple fields from the imported table.

Can instead contain an assignment for the data to place into the field named in DESTFIELD.

Can instead specify 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.

COMP_TYPE

The type of component to create for each record that is imported.

For relational columns, the COMP_TYPE filters the type of the component that can be linked to the main component created from each record that is imported.

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.

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 file or table name filter that specifies only tables 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 tables can use the map file entry.

SYNCHTABLE

The name of the P&ID table that should be checked for changes to ensure that linked P&ID components are synchronised.

This column is only used in map files that are used for linking P&ID tables from SQL Server, not regular importing. The value in SYNCTABLE will differ from the value in TABLE when the mapping is linking components from a "view" rather than a "table", as a view cannot be checked for changes.

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.

CONDITIONS

One of a specific list of conditions that must be met for the entry from the import map file to be applied.

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

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.

Concatenating Imported Data

Instead of the name of a single field from the table to import, the "FIELD" column can contain a formula that specifies how to create a composite of fixed text and one or more fields from the table to import. This composite value is inserted into the field of the project database table specified in the "DESTFIELD" column. The name of each field that you want to fetch from the table to import should be enclosed in # characters. Fixed text can be inserted before, between and after the #-marked field names.

In the example, the data from the "Type" field of the import table will be followed by a hyphen, then followed by the data from the "Loop" field of the import table. This composite value will be inserted into the "Tagname" column of the tag table in the project database.

For example, if the "Type" field contains "PIT" and the "Loop" field contains "120" then the value inserted into the "Tagname" column of the tag table in the project database would be "PIT-120".

Component Types

It is important to define the component type for the component that a section is importing. It is unlikely that the data in an imported table will contain this data, therefore it should be defined in the map file. This is usually done using the "COMP_TYPE" column in the import map file.

It is also possible to set the component type like any other destination column. 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 the built-in types that is originally part of Instrument Manager or the component type text defined for one of the user types. The text for a user type is specified in the INI file that defines the user type, see User Defined Component 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 default supported types are:

AREA, ENCLOSURE, INSTRUMENT, DEVICE, PLC CARD, TERMINAL STRIP, TERMINAL, TERMINAL GROUP, CABLE, CORE, WIRE, TANK, VALVE and LINE.

In the example the component created using the mapping in the import table will be of the type "Device".

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".

Multiple 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.

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 conditions are specified for any of the mappings. See Conditional Mappings below.

Using multiple sections differs from using multiple import map files because Instrument Manager considers that a component "exists" in the project database only after the import operation is completed, not after one section of the import map file is completed.

  • During one import operation using a single import map file, a component does not "exist" 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 may already exist from one of the previous import operations.

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.

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 the entire section will be skipped for this record from the imported table.

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.

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.

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.

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.

Related components are always identified by component name, which is always in the "Tagname" column in the default project database. Therefore the only relationship that you can have in an import map file is "Tagname" with the required combination of "P1:", "C1:" and "C2:" prefixes.

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.

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.

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".

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).

See also: Formulae.

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: Formulae.

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: Formulae.

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. This is similar to the patterns used for file pattern matching in Windows.

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.

Conditional Mappings

You can specify a number of conditions for mappings in the import map file to either prevent or to force the use of a particular mapping or map file section. Each of the conditions specifies a rule for overwriting data when the project database already contains components that could also be imported; when there are "existing" components.

Components are always identified by component name, which is always in the "Tagname" column in the default project database. A component is considered to exist if there is a record with the same Tagname and the same container hierarchy (as described in Complete Relational Hierarchy above).

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

NoRatingChange

This condition allows you to specify two ways in which the ratings must not change if the component exists. This condition applies only to a single mapping in the import map file section.

If "NoRatingChange" is specified on the import map file entry that will set the ratings table name, e.g. an assignment mapping for "R1_TABLE", then that single mapping will not be used if the component exists and already has a ratings record. Other import map file entries for importing ratings data may still be used. Use this condition if you know that some of your components have already had a ratings record created and that this import mapping is only for components that do not yet have a ratings record.

If "NoRatingChange" is specified on an import map file entry that will import a field of ratings data, e.g. "R1:Description", then that single mapping will not be used if the component exists and it already has a value in that rating. Use this condition if the ratings of some of your components already contain data that you do not want overwritten but that others are still blank and will be set during this import.

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.

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.

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.

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.

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.