Community

Articles in this section

Advanced Job Import CSV Formatting

Jacob Lauzier
  • Updated
Follow

The MachineMetrics Job Import CSV (comma-separated value) file provides an import mechanism that companies with large numbers of Jobs can use to manage those Jobs more efficiently. This article presents the functional capabilities, requirements and instructions for using the CSV file in Job import operations, and recommendations for setting up the Job import process. 

Prerequisite Concepts

This article presumes you have at least a basic understanding of the following concepts: 

  • CSV Delimited Files: If you are not familiar with the general concept of delimited files and the specific requirements of formatting CSV files, a good introduction may be found here. 

  • MachineMetrics Jobs: The Jobs article contains information on the overall function and use of Jobs in the MachineMetrics environment as well as instructions on how to create, view and modify individual Jobs through the 'Jobs' page. 

IMPORTANT! Before attempting a Job import from a CSV file, it is strongly recommended that you review both this article and the Jobs article, and consult with your MachineMetrics Customer Success Manager if you still have questions. 

Article Topics

This article contains the following topics:

Job Import Capabilities of the CSV File Back to Topics

Using a properly formatted Job Import CSV file, companies can simultaneously create, edit, and archive large numbers of Jobs in their MachineMetrics environment through a single import operation, all but eliminating the need for manual entry of individual Job settings in MachineMetrics. Nearly all individual Job settings can be imported using the CSV file, with the only exception being Notifications, which must still be set up individually. 

Implementing the Job Import CSV Functionality Back to Topics

Completing the actions and tasks described in the following procedure will help ensure that your company successfully implements the MachineMetrics Job import functionality using the Job Import CSV file:

  1. Understand MachineMetrics Jobs: Review the Jobs article to learn how Jobs are used in MachineMetrics and the purpose and use of all the data fields in a single Job.
  2. Understand the Job Import CSV File: Review this article to learn how to format and use the Job Import CSV file, including how the fields in the CSV file relate to the Jobs interface in MachineMetrics.
  3. Understand Your Company Data: Identify what data from your company systems corresponds to the fields in a MachineMetrics Job, and how that data will be generated/collected/retrieved from your systems for use in a CSV file.
  4. Create a Test CSV File: Create a "test" Job Import CSV file according to the options and requirements described in this article. This file should contain at least one test Job that accurately represents each different type of machine operation which may run in your production environment.
  5. Choose your Import Method and Process: Choose a CSV import option and plan your import process based on your system capabilities and production requirements.
  6. Run a Test Import: Run an import operation on your test Job Import CSV file using your chosen Job import option.
  7. Validate Your Test Import and Retest: In the MachineMetrics 'Jobs' page, validate and confirm that the Jobs and their data were correctly imported. Analyze any discrepancies or errors found, adjust your data collection and import method or process as necessary, and run the import operation again.
  8. Implement Full Job Import Process: Once your Job import test operation is running consistently without errors, implement your full Job import process. 

Consult with your MachineMetrics Customer Success Manager if you have any questions on how to complete any of the above tasks.

Job Import CSV File Creation and Import
Options Back to Topics

The file attached to this article (CSV Template.csv) is the Job Import template CSV file created in Microsoft Excel by MachineMetrics that you can use to create your first Job Import CSV file. The Job Import CSV file also can be created by a script, from application code, through a database export or SQL query, in a program like Microsoft Excel, or in any application capable of generating plain-text files such as NotePad. How you create your Job Import CSV file depends on the requirements and capabilities of your environment and how you intend to use the Job Import function. 

Job Import Options Back to Topics

Once you have created your Job Import CSV file, you have the following options to import your Jobs into MachineMetrics:

  • Import Tool in the Jobs Page:  The Jobs page lists all the Jobs in your MachineMetrics environment and contains an Import tool that allows you to browse to and import your Job Import CSV file. The Jobs article briefly describes this tool and its function. There is also a specific article available on how to automate job imports. 
  • Job Importer Windows Service: MachineMetrics provides an application service that can be installed on any Windows machine that will allow you to partially or fully automate the import of your Job Import CSV files. The level of automation you can achieve will vary based on the capabilities of your systems and your IT resources. For an in-depth description of how to install and use this service, see Setting up the Automated Job Importer Windows Service.

Companies that do not change their Job data and import Jobs frequently (such as once a month or less often) usually choose to use the Jobs page Import tool. Companies that do change their Job data and import Jobs frequently usually choose to use the Job Importer Windows service.

Job Import CSV File Fields Back to Topics

Below is the list of fields that must be present in the CSV file used to import jobs into MachineMetrics. All of these fields and their values must be present in the CSV file in the same order they are listed below, with no differences in capitalization or spelling, and no additional fields.

IMPORTANT! Do NOT format numbers in your CSV file fields to include commas, dollar signs, and so on, as this will cause the information to be interpreted incorrectly by MachineMetrics.

name
part
operation
lot
description
due_date
quantity
partvalue
default_expectedparttime
default_idealparttime
default_setuptime
default_partcountmultiplier
default_actualparts
warningpercent
failurepercent
workcenter
expectedparttime
idealparttime
setuptime
partcountmultiplier
actualparts
programname

The remainder of this article explains how these fields are used in the CSV file to create MachineMetrics Jobs.

Job Import CSV File Fields and the Jobs
Interface Back to Topics

All MachineMetrics Jobs are viewed through the 'Jobs' page, and the settings for individual Jobs are viewed in the Job interface dialog. The Jobs article explains in detail the function and purpose of the Job interface dialog and the layout and function of the 'Jobs' page. This section explains how the different fields of the Job Import CSV file map to each section of the Job interface dialog, and describes the entry requirements and options for each CSV file field to achieve the intended Job setting.

The fields in the Job interface dialog are divided into the following functional sections:

With the exception of Notifications, the fields in the Job Import CSV file map to the settings of individual Jobs as described in the following sections of this article. 

Job Section Back to Topics

The Job section in the Job interface dialog contains the fields that are used to identify the MachineMetrics Job, including its due date, quantity, and value.

Jobs_Page_Jobs_Section_Job_Dialog_Job.png

CSV File Fields and the Job Section Back to Topics

The fields in the CSV file that map to the Job section of the Job interface dialog are shown in the following figure and are briefly described below. See the Jobs article for detailed descriptions of the purpose and use of each field.

CSV_File-Job_Dialog_Map_Job_Section-test.png

name (Job: Name)

The name/ID of the Job. This is typically the part number/ID or work order number. See the Jobs article for important information on Job naming considerations.

part (Job: Part)

The part number/ID. This field can be left blank if the name field is used for the part number/ID.

operation (Job: Operation)

The name/ID of the machine part production operation represented by the Job such as lathe turning, milling, and so on. This field must be populated for Jobs that require more than one operation to produce the part.

lot (Job: Lot)

The lot number associated with the Job. This field is only used by companies who want to track their part production operations by lot.

description (Job: Description)

Used to provide a brief description of the Job. This field is optional and typically used to further describe the operation, part, work order, or lot.

due_date (Job: Due Date)

Used to provide the date (MM/DD/YYYY) by which the Job or work order must be completed. This field is optional.

quantity (Job: Quantity Required)

The number of parts required to complete the Job or work order, used for tracking and reporting Job progress. This field is optional.

partvalue (Job: Part Value)

The value of the part in dollars. This field is optional.

Defaults Section Back to Topics

The Defaults section in the Job interface dialog contains the fields that are used to set the default performance thresholds that the machines that run the Job are expected to meet. As highlighted in the figure below, the performance metrics for part production that are available for settings in the Defaults section will vary depending on the System Setting for Cycle Time Entry. See the Jobs article for a complete explanation of this interface behavior.

Jobs_Page_Jobs_Section_Job_Dialog_Defaults-Metrics_Vary.png

CSV File Fields and the Defaults Section Back to Topics

The fields in the CSV file that map to the Defaults section of the Job interface dialog are shown in the following figure and are briefly described below. See the Jobs article for detailed descriptions of the purpose and use of each field.

CSV_File-Job_Dialog_Map_Defaults_Section.png

default_expectedparttime (Defaults: Expected Part Time)

In this field, provide the numeric value representing the average time (in milliseconds) that a machine is expected to take to complete the Job operation for one part, including the time required for all Job transitional activities such as part changeover. The value entered in this field will be used as the part production goal on which the percentage Goals are based when creating new Job/Workcenter associations, unless it is overridden for specific workcenters as described in the Job Dispatch section of this article.

Note the following additional considerations for this value in the CSV file:

  • Time value entered in milliseconds: The requirement for entering the value in this field in milliseconds differs from the Job Interface Dialog, where the Expected Part Time field in the Defaults section requires you to enter the time in hours/minutes/seconds.
  • Value must map to Single Part Duration/Expected Part Time: The value entered in this field must represent the value for Single Part Duration/Expected Part Time, regardless of the setting for Cycle Time Entry setting in System Settings. That is, even if the Cycle Time Entry is set to either Full Cycle Duration or Parts Per Hour, you still need to enter the value for Single Part Duration/Expected Part Time in this field. MachineMetrics will then automatically convert the value entered in this field to the equivalent Expected Cycle Time or Part Rate value (part goal) that displays in the Defaults section of the Job interface dialog. 

default_idealparttime (Defaults: Ideal Part Time)

In this field, provide the numeric value representing the average time (in milliseconds) that a machine is expected to take to complete the Job operation for one part, including only machine processing time and excluding the time required for all non-processing activities such as part changeover. The value entered in this field will be used in the Overall Equipment Effectiveness (OEE) calculations for Job/Workcenter associations, unless it is overridden for specific workcenters as described in the Job Dispatch section of this article.

Note the following additional considerations for this value in the CSV file:

  • Time value entered in milliseconds: Entering the value in this field in milliseconds differs from the Job Interface Dialog, where the Ideal Part Time field in the Defaults section provides separate fields that require you to enter the time in hours/minutes/seconds.
  • Value must map to Single Part Duration/Ideal Part Time: The value entered in this field must represent the value for Single Part Duration/Ideal Part Time, regardless of the setting for Cycle Time Entry setting in System Settings. That is, even if the Cycle Time Entry is set to either Full Cycle Duration or Parts Per Hour, you still need to enter the value for Single Part Duration/Ideal Part Time in this field. MachineMetrics will then automatically convert the value entered in this field to the equivalent Ideal Cycle Time or Part Rate value that displays in the Defaults section of the Job interface dialog. 

default_setuptime (Defaults: Expected Setup Time)

In this field, provide the numeric value that represents the expected amount of time (in milliseconds) that machine setup activities will take for the Job. This value will be used when creating new Job/Workcenter associations, unless it is overridden for specific workcenters as described in the Job Dispatch section of this article.

Note the following additional considerations for this value in the CSV file:

  • Start In Production (Blank): If a value is not specified (field is left blank), the workcenter will immediately be placed into production when a Job is started.
  • Allow Startup In Setup (0): If the value provided in this field is zero (0), startup in setup will be allowed but all setup time recorded will be tracked and reported as unplanned downtime.
  • Allow Startup In Setup with Expected Time (Number>0): For all numeric values greater than zero, setup time will be considered planned setup activities until that time (in milliseconds) has elapsed, after which point all time will be tracked and reported as unplanned downtime. 
  • Time value entered in milliseconds: Entering the value in this field in milliseconds differs from the Job Interface Dialog, where the Allow Startup In Setup with Expected Time field in the Defaults section requires you to enter the time in hours/minutes/seconds.

default_partcountmultiplier (Part Counting/Parts Reported and Cycles Required)

This field is used in combination with the default_actualparts field to tell MachineMetrics how to count parts for machines that produce multiple parts per cycle or that require multiple cycles to produce one part. 

  • Multiple Parts Per Cycle/Parts Reported: For machines that produce multiple parts per cycle,  the default_partcountmultiplier value is used to calculate the value for the Parts Reported field. This field displays in the Job interface dialog when the Part Counting field is set to Multiple Parts Per Cycle.
  • Multiple Cycles Per Part/Cycles Required: For machines that require multiple cycles to produce one part, the default_partcountmultiplier value is used to calculate the value for the Cycles Required field. This field displays in the Job interface dialog when the Part Counting field is set to Multiple Cycles Per Part. 

Provide the value in the default_partcountmultiplier field based on the following:

  • Single Part Per Cycle: If the workcenters running the Job produce only one part per cycle, leave this field blank.
  • Multiple Parts & Workcenter Part Counter = Actual Parts Per Cycle: For workcenters that produce multiple parts per cycle, and the parts produced by each machine cycle are the same as reported by the workcenter part counter, the part count multiplier=1. Enter 1 in this field
  • Multiple Parts & Workcenter Part Counter < Actual Parts Per Cycle: For workcenters that produce multiple parts per cycle, and the workcenter part counter only increments once per cycle even though the machine actually produces multiple parts in a single cycle, enter the number of parts produced in a single cycle.
    • Example: If 4 parts are produced in a single cycle, but the workcenter only increments its part counter by 1 for each cycle, the part count multiplier entered in the default_partcountmultiplier field would be 4.
  • Multiple Cycles Per Part: If the workcenter part counter increments multiple cycles while the machine actually produces only one part, enter the decimal representing the percentage/fraction of a part produced in a single cycle.
    • Example: If 4 cycles are required to produce a single part, the part count multiplier entered in the default_partcountmultiplier field (and the default_actualparts field) would be .25. If 3 cycles are required to produce a single part, the part count multiplier entered in the default_partcountmultiplier field (and the default_actualparts field) field would be .33.

The value entered in the default_partcountmultiplier field will be used when creating new Job/Workcenter associations, unless it is overridden for specific workcenters as described in the Job Dispatch section of this article.

default_actualparts (Part Counting/Parts Produced)

Provide the value in the default_actualparts field depending on the Part Counting method used by the workcenters that run the Job:

  • Single Part Per Cycle: If the workcenters running the Job produce only one part per cycle, leave this field blank.
  • Multiple Parts Per Cycle:  If the workcenters running the Job produce multiple parts per cycle, provide the number of parts that are produced each machine cycle, regardless of what is reported by the workcenters running the Job. In this condition, the default_actualparts value maps directly to the Parts Produced field that displays in the Job interface dialog when the Part Counting field is set to Multiple Parts Per Cycle. 
  • Multiple Cycles Per Part: If multiple cycles are required to produce a single part, enter the same decimal representing the percentage/fraction of a part produced in a single cycle as is entered in the default_partcountmultiplier field (see the default_partcountmultiplier field description and the Multiple Cycles Per Part Example).

The value entered in the default_actualparts field will be used when creating new Job/Workcenter associations, unless it is overridden for specific workcenters as described in the Job Dispatch section of this article. 

Goals Section Back to Topics

The Goals section in the Job interface dialog contains the fields that are used to set the default percentage of the performance goals that the machines that run the Job are expected to meet.

Jobs_Page_Jobs_Section_Job_Dialog_Goals.png

CSV File Fields and the Goals Section Back to Topics

The fields in the CSV file that map to the Goals section of the Job interface dialog are shown in the following figure and are briefly described below. See the Jobs article for detailed descriptions of the purpose and use of each field.

CSV_File-Job_Dialog_Map_Goals_Section.png

warningpercent (Goals: Orange)

In this field, provide the percentage of the Parts Goal (default_expectedparttime) that workcenters should use as the warning threshold for the Job. Note the following about the entry in this field:

  • The value must be an integer between 0 and 100. If this field is left blank, the system will use the default value of 100.
  • Workcenter tiles will be green for machines performing better than the warning threshold percentage entered here.
  • When a machine's performance degrades to the point that the warning percent threshold for the Job is crossed but the failure threshold has not been reached, that machine's workcenter tile will turn orange and any warning-level notifications that have been set up will be sent out.

failurepercent (Goals: Red)

In this field, provide the percentage of the Parts Goal (default_expectedparttime) that workcenters should use as the failure threshold for the Job. Note the following about the entry in this field:

  • The value must be an integer between 0 and 100 that is less than the warningpercent entry. If this field is left blank, the system will use the default value of 80.
  • Workcenter tiles will be orange or green for machines performing better than the failure threshold percentage entered here.
  • When a machine's performance degrades to the point that the failure percent threshold for the Job is crossed, that machine's workcenter tile will turn red and any failure-level notifications that have been set up will be sent out.

Notifications Section Back to Topics

Notifications cannot be created or managed through the Job Import CSV file. Importing new Job CSV file settings does not change or impact existing notifications. Notifications must be set up individually for each user/recipient and Job in the Job interface dialog. See the Jobs article for instructions on setting up and using Job notifications.

Jobs_Page_Jobs_Section_Job_Dialog_Notifications.png

Job Dispatch Section Back to Topics

The Job Dispatch section in the Job interface dialog contains the fields that are used to associate workcenters with the Job. 

Jobs_Page_Jobs_Section_Job_Dialog_Job_Dispatch-Adding_Machine-1.png

CSV File Fields and the Job Dispatch Section Back to Topics

The fields in the CSV file that map to the Job Dispatch section of the Job interface dialog are shown in the following figure and are briefly described below. See the Jobs article for detailed descriptions of the purpose and use of each field.

CSV_File-Job_Dialog_Map_Job_Dispatch_Section.png

workcenter (Job Dispatch: All Workcenters, Workcenter Group and/or Workcenter)

This field is used in conjunction with the other CSV file fields highlighted above to associate a Job with the workcenters that can run it. You can associate a Job with all workcenters or one Workcenter Group, and/or associate the Job with individual workcenters.

The Job Import CSV file must have a Job record/row for each separate workcenter association. For example, in the Job interface dialog, you can associate a Job with a Workcenter Group that can run the Job with the Default settings, and then also associate individual workcenters that can run the Job with non-default settings.

To do this in the CSV file, you must have a Job record/row with the workcenter field entry for the Workcenter Group, followed by separate Job records/rows for each individual workcenter. The following figure shows an example of this arrangement (only workcenter-related CSV fields are shown).

CSV_File-Workcenter_Fields_Example-Group_and_Individuals.png

The general considerations and constraints for making Job/workcenter associations are as follows:

  • All Workcenters: Jobs associated with all workcenters will run the Job using the default settings entered in the fields described in CSV File Fields - Defaults Section. A Job in a CSV file cannot have records/rows associating the Job with all workcenters AND a Workcenter Group. 
  • Workcenter Group: Jobs associated with a Workcenter Group will run the Job using the default settings entered in the fields described in CSV File Fields - Defaults Section. A Job cannot be associated with more than one Workcenter Group.
  • Specified Individual Workcenters: Jobs associated with specified individual workcenters will run the Job using the workcenter-specific settings (expectedparttime and so on) highlighted in the figure above. The settings in the workcenter-specific fields will override the default settings in the fields described in CSV File Fields - Defaults Section for the specified workcenter.
  • Combinations: You can combine the above Job/workcenter associations in the CSV file as follows:

    • Workcenter Group & Specified Workcenters: As described and shown in the figure above, you can include a record in the CSV file that associates a Job with a Workcenter Group (that will use the default settings), with additional records for the same Job that specify individual workcenters (that use the workcenter-specific settings).
      IMPORTANT! In this combination, the record/row for the Workcenter Group must be entered in the CSV file for the associated Job before the individual workcenter record(s).

    • All Workcenters & Specified Workcenters: You can include a record in the CSV file that associates a Job with all workcenters (that will use the default settings), with additional records for the same Job that specify individual workcenters (that use the workcenter-specific settings).
      IMPORTANT! In this combination, the record/row for all workcenters must be entered in the CSV file for the associated Job before the individual workcenter record(s).

    • Only Specified Workcenters: In this field arrangement, the Job in the CSV file will only have entries for one or more individual workcenters, one record/row for each workcenter. This arrangement is the same as selecting the "Only Workcenters Listed Below" option in the Job interface dialog of the Jobs page. 

      IMPORTANT! The default settings must be the same in each specified workcenter record/row. For example, if workcenter-specific settings are entered the CSV file for workcenters CNC 104 and CNC 108, the default settings (default_expectedparttime and so on) entered in the CSV file record/row for workcenter CNC 104 must be exactly the same as the default settings entered for CNC108.

Provide the value in the workcenter field based on the following:

  • All Workcenters (Blank): Leave this field (and all the remaining workcenter-specific fields) blank if you want the Job to be available to all workcenters in the environment.  
  • Workcenter Group: You can provide one of the following for a Workcenter Group entry:

    • ERP ID: Provide the ID in your company ERP system of the Workcenter Group. Using the ERP ID in the workcenter field does not require ERP integration with MachineMetrics, but does require the assistance of MachineMetrics to enable the ERP feature. Consult with your MachineMetrics Customer Success Manager if you need more information.
    • Group Name: Provide the exact name of a Workcenter Group that has already been created in MachineMetrics.

IMPORTANT! In the CSV file entry for a Workcenter Group, the remaining workcenter-specific/non-default fields (expectedparttime and so on) in the workcenter group row must be left blank. 

  • Individual Workcenter Name/ID: Provide the name or workcenter ID (see options below). You can have only one workcenter per record/row in the CSV file. If you want to have more than one workcenter with non-default settings for the Job, you need to have a Job record/row for each additional workcenter.

    Workcenter Name/ID Options: You can provide any one of the following in the workcenter field for a specific workcenter that has been installed and configured in MachineMetrics:
     
    • ERP ID: Provide the ID in your company ERP system of the workcenter. Using the ERP ID in the workcenter field does not require ERP integration with MachineMetrics, but does require the assistance of MachineMetrics to enable the ERP feature. 
    • Name:  Provide the name of the workcenter exactly as specified in the MachineMetrics Workcenters page.
    • MachineMetrics System ID: Provide the MachineMetrics system ID of the workcenter exactly as specified in the MachineMetrics Workcenters page.

Both the name and the MachineMetrics system ID of a workcenter can be found in the General section of the workcenter settings dialog (see figure below). Consult with your MachineMetrics Customer Success Manager if you need more information on using the workcenter ERP ID in the workcenter field of the CSV file.

CSV_File-Workcenter_Name___MM_ID_Examples.png

expectedparttime (Job Dispatch: Workcenter > Expected Part Time)

Provide the expected part time that MachineMetrics should use to measure the part production performance of the individual workcenter associated with the Job. The value entered here will override the value entered in the default_expectedparttime field described in CSV File Fields - Defaults Section.

idealparttime (Job Dispatch: Workcenter > Ideal Part Time)

 Provide the ideal part time that MachineMetrics should use to measure the part production performance of the individual workcenter associated with the Job. The value entered here will override the value entered in the default_idealparttime field described in CSV File Fields - Defaults Section.

setuptime (Job Dispatch: Workcenter > Setup Time)

 Provide the setup time threshold that MachineMetrics should use to track the setup time of the individual workcenter associated with the Job. The value entered here will override the value entered in the default_setuptime field described in CSV File Fields - Defaults Section.

 partcountmultiplier (Job Dispatch: Workcenter > Part Counting)

Provide the part count multiplier that MachineMetrics should for the individual workcenter associated with the Job. The value entered here will override the value entered in the default_partcountmultiplier field described in CSV File Fields - Defaults Section.

actualparts (Job Dispatch: Workcenter > Part Counting)

Provide the actual parts setting that MachineMetrics should for the individual workcenter associated with the Job. The value entered here will override the value entered in the default_actualparts field described in CSV File Fields - Defaults Section.

programname 

This field is used for environments that are implementing the MachineMetrics Automatic Job Dispatch functionality. If this functionality is enabled, provide the name in this field of the program that will automatically cause the associated Job to be dispatched on the specified workcenter. The name entered here must exactly match the CNC program name. In addition, for the Automatic Job Dispatch to function, the data stream from the environment must provide the program name information to the workcenter. Contact your MachineMetrics Customer Success Manager if you need more information about entering the setting for this field.

Note: The programname CSV file field corresponds to the Program Name field in the Job interface dialog. The Program Name field only displays in the Job interface dialog if the MachineMetrics Automatic Job Dispatch functionality has been configured for the system and for the associated workcenter.

Related articles

Comments

0 comments

Article is closed for comments.

Powered by Zendesk