Intools Import Utility Guide

Intools Import Utility Guide

Overview

The InTools Import Utility is a useful tool for reducing the amount of manual data entry for an INTools project. Population of the backend database tables is accomplished by importing from Excel, Access, XML, SQLServer and others.

The Import Utility can import into “Tables” or “Modules”. The “Table” import method will be discussed first, followed by the “Module” import method.

Importing into Tables

Importing into tables is a bit more labor intensive than into modules but allows better control over the importation process. Importation must follow a specific sequence as detailed below.

General Table Import Procedure

The general procedure for table importation is:

·  Create a data source, e.g. Excel spreadsheet

·  Populate it with data to be imported

·  Create a named range including the column heading row

·  Run import utility

·  Create a data link

·  Open the link and cross reference the Excel columns to the database fields

·  Import the data

Creating a data source, e.g. Excel spreadsheet

Exporting the tables from the backend database is a good way to create the data source. This will pre-populated the seed file information into the spreadsheet for easy editing.

Hint: You can also create your own spreadsheets for import using the column names described in the following instructions.

Our InTools installation uses Microsoft SQL Server which has a tool called “Enterprise Manager”. Using “Enterprise Manager”, the user can view and export InTools tables.

Warning! Care should be exercised when using “Enterprise Manager” to ensure that database data is not changed.

Open “Enterprise Manager” using:

Start > All Programs > Microsoft SQL Server > Enterprise Manager

Right-click on the InTools database, e.g. CVXMAX, and choose:

All Tasks > Export Data

A DTS Import/Export Wizard will open. Just choose ‘Next’ on the first window to start the wizard, then choose next on the second window to choose the Intools database as the “Source”

(Data Source should be ‘Microsoft OLE DB Provider for SQL Server’, Server should be ‘EDGSQL02’, Use SQL Serve Authentication, User Name: CVX3599, password: CVX3599, Database ‘CVXMAX’.)

On the third window choose:

(Destination is ‘Microsoft Access’ and for the file name use a previously created blank Access Database.)

On the next window choose ‘Copy tables and views from the source database’.

On the next window select the tables to be exported.

On the next window choose ‘Run immediately’.

On the next window hit ‘Finish’ and the tables will be exported to your blank Access database.

Open the database in Access and export the table to a blank Excel spreadsheet. A tab will be created in your spreadsheet that will have the InTools data.

Save the exported spreadsheet in the “C:\Program Files\SmartPlant\Instrumentation\Import” directory.

Populate it with data to be imported

Modify the spreadsheet by removing index, plant, area, unit and extraneous columns. Add the additional data to the column(s) to be imported and save.

Create a named range including a column heading row

Select the column(s) to be imported along with the column heading row, create a named range and save the spreadsheet.

Run import utility

The Import Utility is run from the start menu :

Start > All Programs > Integraph SmartPlant Instrumentation > Import Utility

Create a data link

Create a new data link by right-clicking on the group name and choosing ‘New Link’.

Select Target of ‘Table’, Databse type of ‘ODBC’, SQL Data of ‘Excel Files=Microsoft Excel Driver’ and browse to the source spreadsheet created above.

Enter the named range you created above in the “Enter Table Name” popup, save the link and close the link creation wizard.

Open the link and cross reference the Excel columns to the database fields

Highlight the link you created on the right-side panel and choose “Open Link”. Drag and drop the Excel column names on the left into the InTools database fields on the right to cross reference.

Import the data

Hit the “Import” button to import the data. A log file is generated that can be used to troubleshoot import errors.

Instrument Index

Before an instrument index can be imported, several supporting tables must be populated.

Instrument Types

The minimum information necessary to place a tag in the InTools instrument index is the tag number, instrument type, instrument description and process description. The latter three items are imported per the following procedure:

Instrument Types are made up of a triad of data from two tables: “COMPONENT_ FUNCTION_TYPES” and “PROCESS_FUNCTION”.

The “PROCESS_FUNCTION” table needs to be loaded first.

Create the import data by exporting the InTools “PROCESS_FUNCTION” table.

Delete all columns except the one entitled ‘PROC_FUNC_NAME’.

Add any new process functions required (if any).

Create an named range with all rows of the ‘PROC_FUNC_NAME’ column including the heading.

Run Import Utility per the above instructions.

PROCESS_FUNCTION Table
PROC_FUNC_NAME

Now you have the support table ready for importation of the Component Types. Export the “COMPONENT_ FUNCTION_TYPES” table from INTools into Access and then into Excel. Delete all columns except ‘CMPNT_FUNC_TYPE_NAME’ and ‘CMPNT_FUNC_TYPE_DESC’. Add in a column named ‘PROC_FUNC_NAME’.

COMPONENT_ FUNCTION_TYPES Table
CMPNT_FUNC_TYPE_NAME
CMPNT_FUNC_TYPE_DESC
PROC_FUNC_NAME

Add in new component types by entering instrument type into ‘CMPNT_FUNC_TYPE_NAME’, e.g. PT. Add in an instrument description into ‘CMPNT_FUNC_TYPE_DESC’, e.g. Pressure Transmitter, FF. Add in a ‘PROC_FUNC_NAME’, e.g. Pressure.

Import the spreadsheet into InTools using the method described above.

Hint: If you add a tag number column to this spreadsheet and add tag numbers with the instrument types you just imported; you have the minimum amount of information needed to import a tag into the InTools “COMPONENT” Table (InTools Instrument Index).

Alternative Method: You can use the Facilities Instrument Index as a basis for InTools instrument types. Take their columns named ‘Prefix’ and ‘Instrument Category’ and copy to a new spreadsheet. Add a column for process function. Enter a process function for each item. Import this spreadsheet into Excel for preparation of importing additional columns of the Facilities Instrument Index.

P&ID

Prior to importing an instrument tag associated with a P&ID, you must import a list of P&ID’s that you will be referencing.

Export the “DRAWING” table from InTools into Excel. Delete all columns except the ‘DWG_TYPE_ID’ and ‘DWG_NAME’.

DRAWING Table
DWG_TYPE_ID
DWG_NAME

Enter your new P&ID drawing number into the ‘DWG_NAME’ column and enter 1 into the ‘DWG_TYPE_ID’. A ‘DWG_TYPE_ID’ value of 1 tells InTools it’s a P&ID. Import the spreadsheet into the “DRAWING” table and they will be available for entering new tags.

Hint: You can also create your own spreadsheet using the two column names listed. Exporting the existing table is not necessary but maybe helpful at first.

Equipment

Prior to importing an instrument tag associated with an equipment tag, e.g. ZZZ-4536, you must import in the referenced pieces of equipment.

Importing the Equipment List is accomplished via a spreadsheet with the following columns ‘EQUIP_NAME’, ‘EQUIP_DESC’, ‘EQUIP_TYPE_NAME’. To prepare the import spreadsheet you will need to export two tables from InTools: “EQUIPMENT” and “EQUIPMENT_TYPES”. Once exported, open the “EQUIPMENT” spreadsheet and delete all columns except ‘EQUIP_NAME’ and ‘EQUIP_DESC’; then add a column called ‘EQUIP_TYPE_NAME’.

EQUIPMENT Table
EQUIP_NAME
EQUIP_DESC
EQUIP_TYPE_NAME

Add your equipment tag numbers into the ‘EQUIP_NAME’ column and your equipment description into the ‘EQUIP_DESC’ column. Into the ‘EQUIP_TYPE_NAME’ add the closest match from the “EQUIPMENT_TYPES” spreadsheet. This spreadsheet can then be imported into InTools.

Manufacturer

Prior to importing a tag that refers to a specific manufacturer, the manufacturer needs to be in the “COMPONENT_MFR” table in InTools. Export this table to a spreadsheet and delete all columns except the one titled ‘CMPNT_MFR_NAME’. Add your list of manufacturers into this column and import to InTools.

COMPONENT_MFR Table
CMPNT_MFR_NAME

Model #

Prior to importing a tag that refers to a specific manufacturer, the manufacturer needs to be in “COMPONENT_MOD” table in InTools. Export this table to a spreadsheet and delete all columns except the ones titled ‘CMPNT_MFR_ID’ and ‘CMPNT_MOD_NAME’.

COMPONENT_MOD Table
CMPNT_MFR_ID
CMPNT_MOD_NAME

Add your list of paired manufacturer and model numbers into the ‘CMPNT_MFR_ID’ and ‘CMPNT_MOD_NAME’, respectively and import into InTools.

Lines

Prior to importing a tag that refers to a specific line, the line needs to be in the InTools “LINE” table. Export the “LINE” table to Excel delete all but the following columns:

LINE Table
LINE_NUM
LINE_SIZE
WALL_THICK
LINE_TYPE_ID
LINE_SCHED
LINE_INTERNAL_DIA
LINE_UOM
PIPE_MATERIAL
ANSI_DIN
DWG_ID
NORMAL_PRESS
PIPE_CLASS_ID

Fill in data into these columns using the line list.

Import into Excel using the following mapping between spreadsheet and Table:

Instrument Tags

With the supporting tables preloaded with data we can now import the Instrument Tags that will make up the Instrument Index.

Export the “COMPONENTS” table from InTools into Excel.

Delete all columns except the ones you want to import into. The minimum columns for a inserting a tag are:

COMPONENTS Table
CMPNT_NAME
CMPNT_FUNC_TYPE_NAME
CMPNT_FUNC_TYPE_DESC
PROC_FUNC_NAME
LINE_NUM
CMPNT_SERVICE
CMPNT_MFR_NAME
CMPNT_MOD_NAME
DWG_NAME
EQUIP_NAME
CMPNT_LOC_NAME
SPEC_CMPNT_FUNC
SPEC_CMPNT_MOUNTING
SPEC_ENCLOSURE_CLASS
INSTR_PD_FLG = 2

Fill in the data to be imported.

Import your completed spreadsheet into InTools.

Hint: The Facilities Instrument Index is another good source for a data source.

Process Data

Export the “PD_GENERAL” table from InTools into Excel.

Delete all columns except the ones shown below:

CASE_NAME = 1
PD_DWG_ID
CMPNT_NAME
PD_FLUID_STATE
PD_FLUID_NAME
PD_PRESS_MAX
PD_PRESS_NOR
PD_PRESS_UID
PD_TEMP_MAX
PD_TEMP_NOR
PD_TEMP_UID
PD_VISC_NOR
PD_VISC_UID
PD_PRESS_UFLG
PD_SPEC_GRAV_NOR
PD_GAS_SG_AS_MM
REV_NO

Fill in the data to be imported.

Import your completed spreadsheet into InTools.

Mapping details for the data link are as follows:

Importing into Modules

Specifications

Importation to all tables listed above should be completed before importing a specification.

It will be handy to generate a couple of reference printouts:

First generate the Datasheet with exposed database field names:

·  Open InTools.

·  Open “Specification Module”.

·  Open “Page Editor”.

·  Choose “Open Page”.

·  Choose the specification which you import will target.

·  Choose “Edit”.

·  Choose “View Names”

·  Print the form with the exposed field names.

Now generate the data source template for the import:

·  Open a completed InTools data sheet for the type you want to import.

·  Choose “Print”.

·  Choose “Yes” to preview printed report.

·  Choose “SaveAs”

·  Choose “Data Only” and “Include Headers”.

·  Save as an “XLS” type.

Modify the resulting spreadsheet as needed and import using the following mapping>

(The example is showing the import map of a pressure transmitter.)