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
Column Name |
Note |
Instrument ID |
Required Field |
Description |
Fields will be left blank if spreadsheet is blank. |
Calibration Frequency |
Must match predefined dropdown |
Classification |
Must match predefined dropdown** |
Manufacturer |
Must match predefined dropdown** |
Model Number |
Text field |
Serial Number |
Text field |
Department |
Must match predefined dropdown** |
PID Number |
Text field |
PID Revision Number |
Text field |
Document ID |
Must match predefined dropdown** |
Equipment ID |
The Equipment Record must exist or must be getting created in the current run of the utility (Equipment is defined in the “Equipment” worksheet). |
System ID |
The System Record must exist or must be getting created in the current run of the utility (System is defined in the “System” worksheet). |
Instrument Type |
Must match predefined dropdown** |
First Cal Date |
Date field |
Next Cal Date |
Will be calculated automatically once Last Cal Date and Calibration Frequency fields are populated |
Last Cal Date |
Date field |
Status |
Must match predefined dropdown |
Location |
Must match predefined dropdown** |
Building |
Location field cannot be blank. Must match predefined dropdown for the specified Location.** |
Warranty Exp Date |
Date field |
Responsible Technician |
Must be a valid technician for the target company. |
Failed Cal Alert Type |
Must match predefined dropdown |
Found Out of Cal Alert Type |
Must match predefined dropdown |
Exp Man Hours |
Numeric field |
Late Cal Compl Rpt Type |
Must match predefined dropdown |
As Found Failure Compl Rpt Type |
Must match predefined dropdown |
Failed Cal Compl Rpt Type |
Must match predefined dropdown |
Item Approval Policy |
Must match predefined dropdown |
Calibration Approval Policy |
Must match predefined dropdown |
Note |
Text field |
Loops Worksheet
Column Name |
Note |
Loop ID |
Required Field |
Description |
Fields will be left blank if spreadsheet is blank. |
Calibration Frequency |
Must match predefined dropdown |
Classification |
Must match predefined dropdown** |
Department |
Must match predefined dropdown** |
PID Number |
Text field |
PID Revision Number |
Text field |
Document ID |
Must match predefined dropdown** |
Equipment ID |
The Equipment Record must exist or must be getting created in the current run of the utility (Equipment is defined in the “Equipment” worksheet). |
System ID |
The System Record must exist or must be getting created in the current run of the utility (System is defined in the “System” worksheet). |
First Cal Date |
Date field |
Next Cal Date |
Will be calculated automatically once Last Cal Date and Calibration Frequency fields are populated |
Last Cal Date |
Date field |
Status |
Must match predefined dropdown |
Location |
Must match predefined dropdown** |
Building |
Location field cannot be blank. Must match predefined dropdown for the specified Location.** |
Responsible Technician |
Must be a valid technician for the target company. |
Failed Cal Alert Type |
Must match predefined dropdown |
Found Out of Cal Alert Type |
Must match predefined dropdown |
Exp Man Hours |
Numeric field |
Late Cal Compl Rpt Type |
Must match predefined dropdown |
As Found Failure Compl Rpt Type |
Must match predefined dropdown |
Failed Cal Compl Rpt Type |
Must match predefined dropdown |
Item Approval Policy |
Must match predefined dropdown |
Calibration Approval Policy |
Must match predefined dropdown |
Instrument IDs |
List of Instruments that belong to this Loop, separated by a semi-colon. Instrument must exist or must be getting created in the current run of the utility. |
Note |
Text field |
Test Instruments Worksheet
Column Name |
Note |
Test Instrument ID |
Required Field |
Description |
Fields will be left blank if spreadsheet is blank. |
Calibration Frequency |
Must match predefined dropdown |
Classification |
Must match predefined dropdown** |
Manufacturer |
Must match predefined dropdown** |
Model Number |
Text field |
Serial Number |
Text field |
Department |
Must match predefined dropdown** |
Date Sent for Cal |
Date field |
Date Returned from Cal |
Date field |
Document ID |
Must match predefined dropdown** |
First Cal Date |
Date field |
Next Cal Date |
Will be calculated automatically once Last Cal Date and Calibration Frequency fields are populated |
Last Cal Date |
Date field |
Status |
Must match predefined dropdown |
Location |
Must match predefined dropdown** |
Building |
Location field cannot be blank. Must match predefined dropdown for the specified Location.** |
Warranty Exp Date |
Date field |
Responsible Technician |
Must be a valid technician for the target company. |
Failed Cal Alert Type |
Must match predefined dropdown |
Found Out of Cal Alert Type |
Must match predefined dropdown |
Exp Man Hours |
Numeric field |
Late Cal Compl Rpt Type |
Must match predefined dropdown |
As Found Failure Compl Rpt Type |
Must match predefined dropdown |
Failed Cal Compl Rpt Type |
Must match predefined dropdown |
Item Approval Policy |
Must match predefined dropdown |
Calibration Approval Policy |
Must match predefined dropdown |
Instrument Types |
List of Instrument Types this Test Instrument can calibrate, separated by a semi-colon.** |
Note |
Text field |
Systems Worksheet
Column Name |
Note |
System ID |
Required Field |
Description |
Fields will be left blank if spreadsheet is blank. |
Calibration Frequency |
Must match predefined dropdown |
Classification |
Must match predefined dropdown** |
Manufacturer |
Must match predefined dropdown** |
Model Number |
Text field |
Serial Number |
Text field |
Department |
Must match predefined dropdown** |
PID Number |
Text field |
PID Revision Number |
Text field |
Document ID |
Must match predefined dropdown** |
Owning System ID |
The System Record must exist or must be getting created in the current run of the utility (System is defined in the “System” worksheet). |
System Type |
Must match predefined dropdown** |
First Cal Date |
Date field |
Next Cal Date |
Will be calculated automatically once Last Cal Date and Calibration Frequency fields are populated |
Last Cal Date |
Date field |
Status |
Must match predefined dropdown |
Location |
Must match predefined dropdown** |
Building |
Location field cannot be blank. Must match predefined dropdown for the specified Location.** |
Responsible Technician |
Must be a valid technician for the target company. |
Failed Cal Alert Type |
Must match predefined dropdown |
Found Out of Cal Alert Type |
Must match predefined dropdown |
Exp Man Hours |
Numeric field |
Late Cal Compl Rpt Type |
Must match predefined dropdown |
As Found Failure Compl Rpt Type |
Must match predefined dropdown |
Failed Cal Compl Rpt Type |
Must match predefined dropdown |
Item Approval Policy |
Must match predefined dropdown |
Calibration Approval Policy |
Must match predefined dropdown |
Note |
Text field |
Equipment Worksheet
Column Name |
Note |
Equipment ID |
Required Field |
Description |
Fields will be left blank if spreadsheet is blank. |
Classification |
Must match predefined dropdown** |
Manufacturer |
Must match predefined dropdown** |
Model Number |
Text field |
Serial Number |
Text field |
Department |
Must match predefined dropdown** |
PID Number |
Text field |
PID Revision Number |
Text field |
System ID |
The System Record must exist or must be getting created in the current run of the utility (System is defined in the “System” worksheet). |
Equipment Type |
Must match predefined dropdown** |
Status |
Must match predefined dropdown |
Location |
Must match predefined dropdown** |
Building |
Location field cannot be blank. Must match predefined dropdown for the specified Location.** |
Note |
Text field |
Test Groups Worksheet
Column Name |
Note |
Item Type |
Value must be “Instrument”, “Test Instrument”, “Loop” or “System” |
Item ID |
Must match an existing asset ID (e.g. Instrument ID) |
Group Name |
Text field |
Num Cal Pts |
Number of calibration points. Integer field. |
Input Type |
Input Signal Type. Must match existing Signal Type.** |
Input Resolution |
Integer value from 0 to 8 (e.g. 2 should be entered for a resolution of 0.00) |
Input Low |
Input Range Low Value |
Input High |
Input Range High Value |
Input Tolerance |
Numeric field |
Input Values |
Semicolon separated values. Spaces are allowed but will be ignored. |
Output Values |
Semicolon separated values. Spaces are allowed but will be ignored. This cell should only have values if Correlation to Output for the row is “Manual” |
Correlation to Output |
Choose from the drop down list. |
Output Type |
Output Signal Type. Must match existing Signal Type.** |
Output Resolution |
Integer value from 0 to 8 (e.g. 2 should be entered for a resolution of 0.00) |
Output Low |
Output Range Low Value |
Output High |
Output Range High Value |
Stated Accuracy |
Choose from the drop down list. |
Range Accuracy Pct |
Numeric field (e.g. if 1.2%, then enter 1.2. Do not enter the percentage sign) |
Reading Accuracy Pct |
Numeric field (e.g. if 1.2%, then enter 1.2. Do not enter the percentage sign) |
Plus Minus |
Numeric field |
Test Type |
“Manual Pass/Fail” is the only option at this time Note – A Group cannot have test points and a Test Type. If the Test Type column contains a value, only the Test Type will be created. |
MPF As Found Result |
“Pass”, “Fail” or “Unknown” |
MPF As Left Result |
“Pass”, “Fail”, “Unknown” or “Not Entered” |
MPF Test Performed |
“Yes” or “No” |
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