Importing master routes/BOM from Excel

ROB-EX has an option for importing and exporting master routes and master BOM’s from Excel. This is provided as scripts/macros you install to your <sch_client_dir>\custom folder, and execute from macros. From ROB-EX v6.4, the scripts are included as part of the client installation in <sch_client_dir>\data\scripts\excel\masterRouteImportExport

This is a great option for setting up a plan for the first time, including creating a mockup plan for demo purposes. Note you may also use this option to import a bunch of resources and resource groups. You may watch this video to learn how to install and use this option.

Installation

Copy the content of the masterRouteImportExport/custom folder to your <sch_client_dir>\custom folding (excluding the *.sql file). Restart your ROB-EX client and answer yes to import the two new macros. One macro for import and one macro for export.

!Important multiuser note: When importing master routes to a ROB-EX Multiuser connected client, then the GanttMultiuser database needs modifications by running the script located in the zip-file in custom\masterRouteImportExport_scripts_and_macro.sql . The script will expand the size of two id fields. Open the script in MS SQL Management Studio and run it once against the GanttMultiuser database, prior to any imports.

Example Excel template

You may create a template Excel file by opening an existing ROB-EX plan, next run the “Master Route Export” macro. Specify e.g. “Robex_excel_routes.xlsx” as the file name.

Alternatively an example Excel template is also available for download from this link. The columns of this template has additional help notes regarding the meaning of the different codes. The example data in the Excel template, models the following two master routes Item A and Item B

Usage for import

*Note that “Routes” and “BOM’s” are provided in two different tabs of the Excel file!

Regarding the Routes tab

1. Each row in the sheet is an operation – either a primary or a sub-operation. Repeat the “Route Name”/“Route alternative name” for each operation on same route.
2. The combination of “Route name” and “Route alternative name” should be unique among all master routes
3. If a resource does not exist it will be created – also the group it belongs to is created if it does not exist. New resources will be assigned the system default general calendar (Edit->Calendar->Properties->“Use as default calendar”). So, this is also a great tool for importing resource master data.
4. Use the “Opr no.”, “Depends on” and the “Binding rule” columns to create advanced constraint links
   
   Note: the values in “Depends on” can be a comma separated list (e.g. “20,30”) to model many-to-one relationships. Multiple different operations may depend on the same operation no, to model one-to-many.

Binding rule meanings are (same values allowed in other interfaces like XML):

• 1=SS=Start/Start=StartStart
• 2=FF=Finish/Finish=FinishFinish
• 3=FS=Finish/Start=FinishStart
• 4=SF=Start/Finish=StartFinish
• 5=SSE=Start/Setup=StartSetup
• 6=SSW=Start/Switchover=StartSwitchover
• 7=SES=Setup/Start=SetupStart
• 8=SESE=Setup/Setup=SetupSetup
• 9=SESW=Setup/Switchover=SetupSwitchover
• 10=SEF=Setup/Finish=SetupFinish
• 11=SWS=Switchover/Start=SwitchoverStart
• 12=SWSE=Switchover/Setup=SwitchoverSetup
• 13=SWSW=Switchover/Switchover=SwitchoverSwitchover
• 14=SWF=Switchover/Finish=SwitchoverFinish
• 15=FSE=Finish/Setup=FinishSetup
• 16=FSW=Finish/Switchover=FinishSwitchover
  

Advanced constraint arc modifiers

As of 7.0.0.713 support was added for specifying additional constraint arc modifiers. E.g. sourceTransportTime etc.

Modifiers are added in square brackets just after the “Binding rule”. As an example (the string ahead of the : sign is the Excel column name):

Depends on: 10,20
Binding rule: SES#[dstOH=1],SES[dstOH=2.0;srcOQ=50]

Following modifiers are supported (case matters and each modifier is separated with ; as shown in the example above).

srcOH

Source Overlap Hours

dstOH

Destination overlap hours

srcOQ

Source Overlap Quantity

dstOQ

Destination Overlap Quantity

srcQH

Source Queue time Hours

dstQH

Destination Queue time hours

srcTH

Source Transport time Hours

synRE

Synchronize resource (true or false)

alwVI

Always Visible (true or false)

Regarding the BOM tab

Each row is a single BOM line – repeat the “Route Name”/“Route alternative name” for each BOM line on same BOM.

1. The material is the produced material (will be assigned to the operation specified in column “Material Opr no.”. Or if this is missing, to the last operation in the route)
2. and the “Comp Material Number” is the consumed material (will be consumed on the operation specified in column “BomLine Opr no.”. Or if this is missing the first operation of the route).

Any material used which does not exist is automatically created (this is also a great tool for importing material master data).

1. The rate consumed is specified using the columns “BomLineBaseQuantity” and “BomLineQuantity”
   
2. In the example above the BOM line consumes 20 pcs for each 100 produced. So in normal ROB-EX rate term that is 20/100 = 0.2

Material type:

• 0: raw material
• 1: resource tool
• 2: semi finished
• 3: finished good
  

BomLineAccessMethod:

• 200 (Front), 201 (End), 202 (Linear), 203 (Front_Incl_Setup), 204 (End_Incl_Switchover), 205 (Linear_Incl_Setup_And_Switchover), 206 (Linear_Over_Workload), 207 (Linear_Over_Fillup), 210 (Dependency_Front), 211 (Dependency_End), 220 (Next_Operation_Start), 221 (Next_Operation_End), 222 (Offset_Front), 223 (Offset_Front_Incl_Setup), 224 (Offset_End), 225 (Offset_End_Incl_Switchover)
  

Unit of measure (UoM):

• Any of the alternatives listed on this page may be used (case matters). To support anything besides this, then the groovy script code can be modified.

Recursively import excel files in a directory

At the top of the script you have this option “importRecursively”:

// set true to import multiple files using a pattern
public static boolean importRecursively = false;

If you set this to “true” then all files mathing the “fileRegexpFilter” will be imported. After import the file is moved to the “processedDir” folder. It is important that you adjust the rootDir and processedDir to point to something valid for your installation.

if (GlobalVars.importRecursively) {
   // get parent path - and import all xlsx file in that directory
   def fileRegexpFilter =  ~/(?i).*master.*.xlsx$/
   // edit root dir here
   def rootDir = "c:/users/jensb/rbx62/input" 
   // edit processed dir here (files are moved to this location after being processed)
   def processedDir = "c:/users/jensb/rbx62/input/processed" 

By default when importRecursively = false it will ask the user for the file to import.

Note that the “importRecursively” only applies to import. Export is always to a single selected file.

Usage for Export

Run the “Master Route Export” macro. You will be asked for the location of the exported excel file.

Note that the exported file can also be imported – i.e. the export and import follow the same syntax. This way it is possible to e.g. export all existing master routes to Excel. Perform a mass change to e.g. the setup in Excel. Finally re-import the master routes. The existing master routes in ROB-EX will now be updated with the new changed setup times

!Important: note that in case operations or operation constraints are added/moved for existing routes, then make sure to delete the existing route first in ROB-EX, before re-importing. The current script implementation has limitations on what it is capable of updating on existing master routes.