PCS PaP and CT import user manual

Content

Introduction

This user manual is mentioned to be a user guide for the users that are importing catalogue paths (CTs and PaPs) in PCS system. The document covers:

  • The format of the Excel file that should be obeyed
  • PCS front-end behaviour when the file is uploaded/imported
  • Errors and warnings that can occur

Catalogue Excel file format

In the Excel file the following sheets should be defined:

  • parametre_matrix sheet
  • ENEE_Codes sheet
  • CXY_sections sheet
  • Distances sheet
  • Origin-Destination sheets

The order of the sheets must be the same as defined here. Hidden sheets are not allowed so before import, you should check if there are any hidden sheets. All of these sheets will be described in more details in the sections below.

Parametre matrix sheet

This is the first sheet in the excel file and should be named “parametre_matrix”. The data that are read from here are activity types and parameter matrix.

The first activity type code should be defined in D10 cell and the description for this code should be defined in E10 cell. Other activity types should be defined in the rows below like shown in the following picture:

Activity types

Once the activity types are defined here, they can be used in the origin-destination sheets.
The parametre matrix should be defined in the format show in the following figure:

Parametre matrix

The header for the parametre matrix should be defined in row 17 and row 18. The name of the first parameter in the parametre matrix should be defined in E17 cell and the first header row should contain following parameters:

  • max train length (m)
  • max train weight (t)
  • max length of set of carriages (m)
  • max weight of set of carriages (t)
  • max Profile
  • reference Loco
  • highest planned speed
  • MinBrakedWeightPercent
  • other

The order of the parameters should be obeyed. The second header row (row 18 in the excel file) can have the following values: "y", "yes", "n" or "no".

  • The "yes" (or "y") for one parameter means that, once the catalogue is imported in new dossier the user input for this parameter in the new dossier will be validated against the value of this parameter from the catalogue dossier
  • The "no" (or "n") for one parameter means that, once the catalogue is imported in new dossier the user input for this parameter in the new dossier will NOT be validated against the value of this parameter from that catalogue dossier

In the excel file all parameters can be left empty but later, if there are mandatory parameters, they must be entered and the PaP can be published. The first definition of parametre matrix should start from cell C19 and one row should have following values:

  • The name of the agency (this should be valid PCS agency name)
  • Valid parameter reference code (this code should be unique)
  • Value for "max train length (m)" parameter which must be number with max. 5 digits
  • Value for "max train weight (t)" parameter which must be number with max. 5 digits
  • Value for “max length of set of carriages” parameter which must be number with max. 5 digits
  • Value for “max weight of set of carriages” parameter which must be number with max. 5 digits
  • Value for “max.Profile” which should be text in following format “P/C XX/YY” (for ex. “P/C 70/400”)
  • Value for “reference Loco” which can be any text with max. 12 characters. This "reference Loco" value should be the same  as the "Loco type number" that is published by the IMs
  • Value for “highest planned speed” which should be number with max. 5 digits
  • Value for “MinBrakedWeightPercent” which should be number with max. 2 digitsValue for “other” which can be any text

Each definition will generate one Parameter Set Code Template in PCS. After the import finishes, users can view/edit the Parameter Set Code Templates in the Template Manager.

ENEE Reference Codes sheet

This is the second sheet in the excel file and should be named “ENEE_Reference_Codes”. Here you should define all location names that will be used in catalogue dossiers. Part of this sheet is show here:

ENEE Reference Codes sheet

This sheet contains four columns in following order:

  • Location name in template (the name of the location that will be used in the template)
  • Location name from ENEE code
  • ENEE-code from location
  • Reference code (Primary Location Code) for the location

When defining stops for new country, together with the country name the country code should be provided.

When defining locations following rules should be obeyed:

  1. The locations that will be used in “Cxy_sections, Distances and origin-destination sheets” MUST be defined here in the “Location name in template” and that name should be used through entire Excel.
  2. The ENEE code must be valid
  3. The reference code must be valid
  4. There should be at least one code (reference or ENEE or both but reference code has priority over ENEE code)
  5. The empty rows between two countries should not be more than 3 empty rows.

The location that is used in Excel file can be resolved to PCS location in several different ways:

  1. If the reference code for location is present, then PCS tries uniquely to resolve the location:
    1. First, only from reference code
    2. Next, from reference code and ENEE id combination
    3. And finally, from reference code and country code (which should be provided together with country name) combination
  2. If the reference code is NOT present or no location was resolved with the 1 case, then PCS tries again uniquely to resolve the location:
    1. First, only from ENEE id
    2. Next, from ENEE id and location name from ENEE code
    3. And finally, from ENEE id and location name in template

If the operation can’t be uniquely resolved, then it will be displayed as warning (or data error) so that the user can enter the correct one.

CXY sections sheet

This is the third sheet in the excel file and should be name “C(corridorName)_sections”, for example C02_sections stands for corridor 02. The corridor for which the excel file should be imported is resolved from this sheet name so the number must be valid corridor. In this sheet, you should define the origin-destination pairs of the catalogues that will be imported. There are two different ways of defining origin-destination pairs:

The first way to define origin-destination pair is show here.

Define origin-destination pairs (extended way)

The first row should contain following columns:

  • Direction (N-S or S-N or E-W or W-E)
  • Start
  • End

The origin-destination pairs are defined first for the first direction and then for the opposite direction. Following rules applies here:

  • The location name must be defined in the ENEE_Codes sheet
  • The number of origin-destination sheets must correspond to the sum of origin-destination pairs in all directions defined here
  • The name of the origin location in one origin-destination pair defined here must be the same with the name of the origin location defined in cell I3 in corresponding origin-destination sheet
  • The name of the destination location in one origin-destination pair defined here must be the same with the name of the destination location defined in cell J3 in corresponding origin-destination sheet
  • There must be no empty space (no empty cells) in between the pairs (anywhere)

The second way to define origin-destination pair is show here:

Define origin-destination pairs (short way)

The first row should contain following columns:

  • Direction (N-S or S-N or E-W or W-E)
  • Start
  • End

When only one direction is enters, PCS will automatically resolve the pairs for the opposite direction. Following rules applies here:

  • The location name must be defined in the ENEE_Codes sheet
  • The number of origin-destination sheets must correspond to the number of origin-destination pairs defined here multiplied by 2 (for the opposite direction)
  • The name of the origin location in one origin-destination pair defined here must be the same with the name of the origin location defined in cell I3 in corresponding origin-destination sheet
  • The name of the destination location in one origin-destination pair defined here must be the same with the name of the destination location defined in cell J3 in corresponding origin-destination sheet
  • There must be no empty space (no empty cells) in between the pairs (anywhere)

The origin destination pairs should be in correct order to make complete route. This order is taken into account by PCS system afterward, to displaying logical results when user is searching for Pre constructed products.

Distances sheet

This is the fourth sheet in the excel file and should be named “distances”. Here you should define distances between locations show here:

Distances

Following rules applied here:

  • The header should consist of three columns: “Location1”, “Location2” and “Distance (km)”
  • The location point names must be defined in ENEE_Codes sheet
  • The distance must be number

Origin-destination sheet

This sheet should represent one origin-destination pair that is defined in Cxy_sections sheet. The format of this sheet is shown here.

Origin-Destination sheet for Breclav - Hegyeshalom

The rules that should be obeyed here are the following:

  • Each operation point on a sheet must belong to the same responsible IM
  • The origin-destination direction should be defined in A1 cell (N-S or S-N or E-W or W-E)
  • The origin and destination must be placed in I3/J3 cells
  • The name of the origin and destination locations must correspond to the pair defined in Cxy_sections sheet and the direction must be the same
  • The name of the origin defined in I3 must be the same with the name of the first location in the catalogue path timetable
  • The name of the destination defined in J3 must be the same with the name of the last location in the catalogue path timetable
  • The definition of catalogue paths timetable must start from B3 and must have the following headers
    • First header:
      • the first seven cells must be weekdays
      • “PaP ID” (merged two rows)
      • Dossier Type (merged two rows)
      • “path” (next six cell merged)
      • origin location name etc.
    • Second header:
      • the first seven cells must be weekdays
      • the next cell is merged cell which is part of “PaP ID” cell
      • “no” – path number
      • "note"
      • “param” – this parameter code must be defined in parametre_matrix sheet
      • “SJT” – Standard Journey Time
      • “MNS” – Max number of stops
      • “TST”- Total Stopping Time in minutes
      • “arr” – arrival time
      • “st” – stop type codes. The stop type codes must be defined in parametre_matrix sheet
      • “pr” – protected border. In case of Flex dossier type, this indicates that the operation point is protected.
      • “dep” – departure time
  • The definition of one catalogue timetable should have the following values
    • weekdays when the train is running. The train must run at least one day
    • value for “PaP ID” which should be unique. If this value is not present, the path number will be set as PaP ID
    • value for “Dossier Type” (Fix or Flex) (not relevant for CT import) If this value is not present, the PaP will be imported as Fix PaP
    • value for “Involved RFC” (number of the RFC) (not relevant for CT import) Please, never put there your own corridor number.
    • value for “no”
    • value for “note”
    • value for “param” which must be existing parameter set code defined in parametre_matrix sheet.
    • value for “SJT” (“HH:mm”, “HHmm”, “HH.mm” or “HH mm”) (not relevant for CT import)
    • value for “MNS” (nr) (not relevant for CT import)
    • value for “TST” (minutes) (not relevant for CT import)
    • valid arrival time (“HH:mm”, “HHmm”, “HH.mm” or “HH mm”)
    • value for “st” which must be valid stop types defined in parametre_matrix sheet. If the user like to set more stop type to a location, he should separate them with a comma ","
    • departure time

PCS import front-end

PCS Excel import is located under "Pre-constructed Products" as "Excel importer".

When the file is uploaded, PCS checks for 2 types of errors and warning:

  • Format errors, when the uploaded file does not satisfy the predefined rules. These errors must be fixed directly in the file. After that, the file must be uploaded again. Wrong reference loco in the parameter matrix results also this kind of error. Please check the second image below that shows an error for missing loco types. In this case, either the C-OSS Managers fix the import template and enter the correct the loco type or the IM has to publish the missing loco type in PCS.

 

  • Data errors, when PCS cannot resolve some entity from the Excel (e.g. operation point, activity type). These errors also must be fixed but PCS offers “Fix action” in the front-end, so no need of uploading the file again.
  • Warnings, similar to data errors but with possibility to be ignored and completed after the import process finishes (directly in the catalogue dossiers). However, PCS again offers “Fix action” in the front-end.

Import process

Once all errors and warnings are resolved (or ignored), PCS shows a “Report” section, describing what will happen if the import is run.

If the report complies with your objectives, the just click the “Import” button to start it.
The Import now runs in the background and has 3 different statuses. Each status displays different message on front-end:

  • “The import process is waiting to be executed…” – means that the import is registered by PCS and is waiting for its turn to be executed 
  • “The import process is running and is expected to finish at: <time>” 

  • “Import process successfully finished on: <date>” 

PCS will show the result of the import and will classify the actions with three types:

  • Inserted
  • Updated
  • Skipped

PCS will keep the last import report for the user’s agency. If another user, from the same agency, triggers new import, the saved one will be overridden.

Multiple imports

Users can do multiple imports and PCS will try to match the existing PCS catalog dossiers with the values in the file. Afterwards, a report will be shown.

When doing a Re-Import, PCS provides 5 options:

  • “Re-Import Parameter Set Codes” – Overrides the existing Parameter Set Code Templates in PCS, with the values from the Excel file.
  • “Re-Import Validation Rules” – Overrides the existing Validation Rules (checks) for the Train Parameters in PCS
  • “Re-Import Distances” – Overrides the exiting distances values in PCS
  • “Re-Import Activity Types for Path Sections” – Overrides the activity types for all path sections in the file
  • “Import Notes for Path Sections” – Overrides the notes for all path sections in the file

For all new values in the excel file (parameter set codes, distances, new path sections…), the options are ignored and the values are always imported in PCS. Thus, the options are only influencing the existing values.

There are 3 reasons why one dossier can be skipped: the dossier is published, closed or when re-importing RNE-C, the user agency is NOT the leading one.

Important: Once the catalogues are imported in PCS, all of them will be promoted to “Path Elaboration” phase and the traffic lights of the IMs will be set to: Processing. Later, the dossiers must be: Accepted, so that they can be published.

Excel format and data errors

As we said before, the errors and warnings must be fixed (or warnings can be ignored). PCS will guide you with the fixing process by providing a: description of the error, excel sheet and cell where the problem is located.

These are all possible error and warning messages that PCS can show:

  • parametre_matrix sheet is missing
  • Sections sheet (Cxy_sections) is missing
  • Minimum number of sheets allowed = {0}
  • Number of timetable sheets (total {1}) must be equal with the number of sections defined (total {0})
  • No sheets for {0} pair declared in Cxy_sections sheet
  • The cell content type should be {0}
  • Input file is missing
  • Row is empty
  • ENEE_Reference_Codes sheet is missing
  • Distances sheet is missing
  • Your agency is not responsible for RFC {0}
  • Activity type without description
  • First parameter name should be placed in E17
  • Invalid validity check flag. Valid values are: 'y', 'yes', 'n', 'no'
  • First agency name should be placed in C19
  • Invalid parameter reference code
  • Unknown parameter reference code. Valid values are: {0}
  • Parameter reference code is not defined
  • Invalid parameter value
  • Invalid parameter length (max. {0})
  • Invalid validity check flag. Only 'n' or 'no' is accepted
  • Missing value for mandatory parameter {0}
  • Default direction is not specified
  • Invalid direction on Cxy_sections sheet
  • Missing origin
  • Missing destination
  • Invalid direction
  • Origin name {0} must correspond on the names given in Cxy_sections sheet
  • Destination name {0} must correspond on the names given in Cxy_sections sheet
  • Timetable should start from B4
  • Invalid timetable header cell content. Expected value = {0}
  • Invalid calendar availability flag. Valid values are: empty cell, '-' and 'x'
  • Empty calendars are not allowed
  • Invalid path number. Only numbers are allowed
  • Invalid time format. Supported time formats are: 'HH:mm', 'HHmm', 'HH.mm' and 'HH mm'
  • Invalid activity type
  • Activity type {0} cannot be resolved
  • Missing origin in timetable sheet
  • Missing destination in timetable sheet
  • The section {0}-{1} or {1}-{0} is not defined in Cxy_sections sheet
  • Invalid distances header
  • Missing location name
  • Missing location’s distance
  • Mandatory PaP ID must be set
  • Origin and first path section name does not match
  • Destination and last path section name does not match
  • Direction is missing
  • Invalid length of PaP ID. The PaP ID can be max. 12 characters
  • Agency name {0} can't be resolved uniquely
  • Location {0} from agency {1}, can't be resolved
  • Location {0} can't be resolved in Distances sheet
  • Agency {0} can't be resolved
  • Location {0} with eneeId {2} (id = {1}) is inactive. Please contact PCS support team
  • Responsible agency for the location {0} is {1}. The import file uses agency {2} from other country which is not correct
  • Multiple locations exists with name {0} and responsible agency {1}
  • Multiple locations exists with name {0} in Distances sheet
  • Mandatory location {0} from agency {1}, can't be resolved
  • Mandatory location {0} can't be resolved in Distances sheet
  • Mandatory location {0} with eneeId {2} (id = {1}) is inactive. Please contact PCS support team
  • Multiple locations exists for mandatory location {0} and responsible agency {1}
  • Multiple locations exists for mandatory location {0} in Distances sheet
  • PaP ID {0} is not unique in the timetable sheet
  • There is no loco type defined for loco type number: ()
File