Prime Technologies, Inc.

Section 1 – Installing the Data Load Utility

Client System Requirements

The Client machine requirements for the Data Load Utility are the same as the ProCalV5 workstation.

ProCalV5 Client or Administrator Workstation must be installed on the Client.

Procedures

Make sure that you have already installed the appropriate ProCalV5 database components onto your server and client.

Place the ProCalV5 Data Load Utility disk into your CD-ROM drive or if you downloaded this executable from Prime Technologies, navigate to the folder. Run setup.exe and perform the installation.

If the setup program does not automatically launch, go to the CD ROM and run the setup.exe program.

This installation ONLY installs the PCDataLoad.exe program. The installation program will, by default, install the program into the C:\Program Files\Prime Technologies\ProCalV5 directory. If this is not where you installed the ProCalV5 client program you must change the installation directory to your ProCalV5 directory.

Section 2 – Overview of the Data Load Utility

Overview

The ProCalV5 Data Load Utility (DL Utility) allows you to create Instrument, Equipment, Loop, System and Test Instrument records from a predefined Microsoft Excel® file (the Source File). The program will also allow for the addition of multiple Test Point Groups for existing assets. While the Source File structure is predefined, the DL Utility allows some flexibility for defining User Defined Variables from site to site. This feature will be detailed later in the manual.

Each run of the program inserts asset records into a specified ProCalV5 Company. This program can be run multiple times for a specific Company. For example, you may want to run this program once for each Department at each Company.

The input records defined in the Source File will be used to set the field values for the added records. However, the DL Utility will allow you to choose a default value for many fields. You can choose those values during the initiation phase of each run of the utility. If a default value is chosen, then for all Source File records that have a blank value for that field, the default value will be used.

For example, if you would like to set the Calibration Frequency to “Annually” for all your Instruments except for the pressure transmitters, set the “Calibration Frequency” field in the Source File records for the temperature transmitters to a specific value (e.g. Quarterly) and leave all the other Source File records’ “Calibration Frequency” field blank. Then, during the run of the DL Utility, set the “Calibration Frequency” default value to “Annually”. Each Source File record with a blank value in its “Calibration Frequency” field (all the records but the temperature transmitters) will be set to “Annually” in ProCalV5.

A log file with the results of each run will be created and saved to the designated location. The log file will show the start time, the end time, which assets and Test Point Groups were created and any errors encountered during the run. The name of the Log file is displayed on the initiation screen of the DL Utility.

The program may be run in a “Test Run” mode. Running the program in this mode will process the source file and create a log file of what would happen if the program were run in production mode. Running in “Test Run” mode allows you to check the source file for errors.

The user will be able to indicate (by use of a check box) that missing list values will be added to the company if they are in the spreadsheet and do not exist in the company. The following lists may have values added to them during a load:

• Classifications
• Departments
• Documents
• Instrument Types
• Manufacturers
• System Types
• Equipment Types
• Locations
• Buildings
• Signal Types
• UDV List values

Internationalization

The program will be internationalized to display the screen labels, error messages and log file entries in the current user’s preferred locale. Note that the program will use the system’s default locale until the user logs into the system.

The program will also be internationalized to allow the user running the program to indicate what locale should be used when processing the source file. An excel file will be created for each currently available languages (French, Italian, German, Spanish and Portuguese).

Section 3 – Source File Definition

Worksheets and Columns

The Excel® Source File structure is defined in the table below. Several Excel Worksheets exist in the Excel file for each asset type and for Test Point Groups. A Template of the Excel® file is included on the install disk and is copied into the installation folder. The columns specified in the table below MUST be defined in the source file. The physical order of the fields in the source file is not significant.

Instruments Worksheet

Loops Worksheet

Test Instruments Worksheet

Systems Worksheet

Equipment Worksheet

Test Groups Worksheet

User Defined Variables

In addition to the fields listed above, the DL Utility allows you to populate User Defined Variable fields. The Source File spreadsheet column must be defined as it appears in ProCalV5. No mapping table is needed to define what fields in the Source File goes to what User Defined Field in ProCalV5. As long as the fields are named the same, the mapping is done automatically. Below is a list of the different types of User Defined Variables:

Text – Any alpha numeric text entered on the spreadsheet will also be entered into the corresponding field on the new Instrument record.

Decimal – Any number value will be entered into the corresponding field. If text is entered, the Log will show an error.

Integer – Any number value will be entered into the corresponding field. If text or a decimal is entered, the Log will show an error.

Date – For this field to be populated, the spreadsheet must use a date format. If the date is not in a correct format, the log will show an error.

List – The DL Utility will populate this field with an existing List value. If the List value does not already exist in the database, the Utility will create a new List value in ProCalV5 and populate the corresponding field.

File – Any alpha numeric text entered will populate the corresponding field, regardless if it is a valid link.

Signature – Any alpha numeric text entered will populate the corresponding field and populate the checkbox with a checkmark. If this column exists on the spreadsheet and is not populated, no action will be taken in ProCalV5.

User Defined Variables can be populated on any asset type.

Items in this section marked with ** will not need to have predefined dropdown values if “Auto Create Missing List Values” is checked.

Section 4 – Processing Rules

General Processing Rules

If any error is detected then the record being processed will not be created.

If a Department is either specified in an asset’s worksheet or defaults from the Main screen in the Utility and the Department has an assigned Approval Policy, those Policies will be populated on the Master Record.

A Test Point Group’s Control Limits will be set to the default setting from the Company screen, Additional Info tab.

If the “Next Cal Date” column is blank on an asset row then the utility will attempt to calculate the asset’s “Next Calibration Date” based on the “Calibration Frequency” and “Last Cal Date” column values.

All asset records will be added as “Approved’. No Change Control record will be created. If the asset has an Item Approval Policy, all signatures will be signed by the user running the utility.

When the user clicks on the “Start Conversion” button, the utility will ensure that all the columns in the Worksheet are there. If they are not, an error will be displayed in a pop up message stating which Worksheets are invalid and which fields are missing.

The utility checks against the “Required Fields” for each asset type. If a field is required and it is not populated by the column value or a default value then an error is generated. Additionally, if a User Defined Variable (UDV) is a Required Field then the worksheet must also include a column for that UDV. The UDV column name that is missing will be preceded by “UDV” in the error message‘.

For example, if all the Worksheets are invalid, the following message will be displayed in the pop up message (fld1, fld2, etc are just placeholders. The real column names will be listed):

The following Worksheets are invalid:

Instruments (Missing fld1, fld2, UDV fld1)

Loops (Missing fld3)

Test Instruments (Missing fld4)

Equipment (Missing fld1)

Systems (Missing fld3)

Test Groups (Missing fld3)

List Processing

Some values in the spreadsheet are associated with ProCalV5 lists (e.g. Manufacturers). All list values must exist in the associated list prior to loading the spreadsheet. The following are the lists that are processed by the utility.

Approval Policies

Classifications

Compliance Report Types

Departments

Documents

Equipment (for Loop and Instrument assignment)

Equipment Types

Frequencies

Instrument Types

Instruments (for Loop assignment)

Locations/Buildings

Maintenance Types (for Calibration Alerts)

Manufacturers

Signal Types

Statuses

System Types

Systems (for asset hierarchy assignments)

Technicians

Test Groups Processing

The utility will allow you to create multiple Test Point Groups for an existing asset. Each row in the “Test Groups” worksheet will produce one Test Point Group for the item designated in the “Item Type” and “Item ID” columns.

Set the “Num Cal Pts” column to designate the number of test points you want for the group.

Set the specific test point inputs in the “Input Values” column. Use a semi-colon (“;”) to separate the values. The number of values in this column must match the value in the “Num Cal Pts” column. If you would like the utility to set the inputs automatically, leave the “Input Values” column blank and the test points inputs will be evenly distributed across the Input Range.

If the “Correlation to Output column” is “Manual” you should fill in the “Output Values”. These are your expected output values for each test point. The number of values in this column must match the number of values in the “Input Values” column. If your correlation is anything but “Manual” then you must leave this column blank. The utility will calculate the correct expected output values for each test point.

Resolutions for the input and output fields will first look to the spreadsheet that is being processed, and if that is blank, then will use the Company’s default values.

Set other fields in the Test Point Group

Input Signal = Calculated value based on Input Range and Number of Test Points.

Output Signal = Calculated from Input Signal using Linear Correlation logic.

Low Limit = Calculated value based on Stated Accuracy, Accuracy Pct and Plus/Minus. Calculated the same as ProCalV5 does it.

High Limit = Calculated value based on Stated Accuracy, Accuracy Pct and Plus/Minus. Calculated the same as ProCalV5 does it.

Low Control Limit = Calculated only if set.

High Control Limit = Calculated only if set.

 
Setting Default Values on the Data Load Utility Main Screen

These values on the Main screen will only populate records with corresponding fields on the spreadsheet that are left blank. If the spreadsheet contains a value, the value in the Main screen will not overwrite it. Below is a list of the values:

Status – If the Status column’s row is blank on the spreadsheet, the default value will be used.

Classification – If the Classification column’s row is blank on the spreadsheet, the default value will be used.

Location - If the Location column’s row is blank on the spreadsheet, the default value will be used.

Building – The Location must contain a value before a Building can be selected on the Main screen. If the Location is left blank on the spreadsheet and the Building field is populated, an Error will be generated.

Department – If the Department column’s row is blank on the spreadsheet, the default value will be used. See “General Processing Rules”, above, for information on Approval Policies tied to a specific Department.

Calibration Frequency - If the Calibration Frequency column’s row is blank on the spreadsheet, the default value will be used.

Stated Accuracy – If the Test Group’s Stated Accuracy column is blank on the spreadsheet, the default value will be used.

Range Acc Pct – If the Test Group’s “Range Acc Pct” column is blank on the spreadsheet then the default value will be used. This value only applies if the Stated Accuracy is “Percent of Range” or “Percent of Reading Range”.

Reading Acc Pct – If the Test Group’s “Reading Acc Pct” column is blank on the spreadsheet then the default value will be used. This value only applies if the Stated Accuracy is “Percent of Reading” or “Percent of Reading Range”.

Plus / Minus – If the Plus / Minus column’s row is blank on the spreadsheet, the default value will be used.

Document Default Values – The “Expiration Date” and “Revision Number” are for future functionality and can be ignored.

Section 5 – Running the Data Load Utility

Before running the Utility you will need to populate the Excel® spreadsheet with the appropriate information. Once the information is entered on the spreadsheet you must save your work before running the Utility.

To start the Utility either navigate to where you installed the Utility, or if you created a shortcut, double click on the icon. The main screen will be invoked.

To Logon you will use the same User ID and Password already setup in the ProCalV5 database. Only System Administrators can log onto the DL Utility.

A file open dialog will appear. Find your Source File and click on the “Open” button.

Now you have to choose the Company into which you want to add data. Simply choose the company from the drop down list.

After you choose a company you can choose default values for many fields. These values are used to populate your fields for any records in your Source File where those fields are blank. These fields will contain only selections that already exist in the target Company. See “Setting Default Values on the Data Load Utility Main Screen”, above, for details.

You should also decide if you want to run in “Test Mode” or “Production Mode”. For “Test Mode”, leave the “Run as Test” check box checked. This will process the Source File without actually updating your database. A full log will be generated, recording any errors found in the Source File. Running in “Test Mode” allows you to check the Source File before committing the file to your system.

When you finish your selections, simply click the “Start Conversion” button. Your conversion will commence, with its progress displayed in the memo field (center of screen), the slide bar (bottom left of screen) and the counts (bottom right side of the screen).

At the completion of the conversion you will get a pop up screen telling you that you are finished and the location of the log file. Click Ok and then close the DL Utility by clicking on the Close button.

Note – To run multiple Data Loads, you will have to close and then reopen the utility for each one.

Section 6 – Log File

You should check your log file to investigate any errors you may have. The log file has this basic format:

Header

Specifies Conversion Start Date and Time, Type of Run (Test Run or Production), the Target Company Name, any default values that may have been selected, the Source File path and file name, the Log File path and file name and the DL Utility version number.

Asset and Error Information

This section lists the assets and Test Point Groups that are added and any errors that are encountered during the conversion. See Appendix A for possible error messages.

Footer and Summary

This section specifies the Conversion Completion Date and Time, Total Input Records Processed, Total Asset and Test Point Records Created and the Total Errors encountered and the length of time it took to complete the load.

Example Log File

Conversion Started on 30-Apr-2015 10:19:02

Loading Assets for Company: Prime Technologies from Source File: C:\ProCalData\DataLoadList1.xlsx

Log File: C:\ProCalData\Logs\Conversion_Log_30APR2015_101155.log

Conversion Run is "Test Run"

Program Version = 5.7.0.5

Default Values

   Status: In Service

   Classification: Critical

   Location: Loc 1

   Building: Bldg 1.2

   Department: Manufacturing

   Calibration Frequency: Quarterly

   Stated Accuracy: Pct of Range

   Range Acc %:  1.500000%

   Reading Acc %:  0.000000%

   Plus/Minus:      0.0000

    Document Expiration Date: 04/30/2015

    Document Revision Number: CURRENT

Migrating Systems....

Processing Row 2 System SYS-EMPTY

System SYS-EMPTY Processed Successfully

Processing Row: 27 TP Group for Instrument 12079

   ERROR: Instrument 12079 does not exist

TP Group for Instrument 12079 Not Processed

Closing source file...

Conversion Completed on 30-Apr-2015 10:19:22

Summary...

52 Total Records Processed

4 System Records Would Have Been Created

2 Equipment Records Would Have Been Created

2 Instrument Records Would Have Been Created

2 Loop Records Would Have Been Created

1 Test Instrument Records Would Have Been Created

4 Test Point Group Records Would Have Been Created

148 Errors encountered during the process

Total Conversion Time: 00:00:20