JS7 - PowerShell Import from Excel Script - New-JS7WorkflowFromExcel.ps1

Name

New-JS7WorkflowFromExcel.ps1

SYNOPSIS

Crreates workflow files (.json) from worksheets in one or more Excel files.

SYNTAX

\New-JS7WorkflowFromExcel.ps1 [-ExcelPath] <String> [[-WorksheetName] <String>] [[-WorksheetColumns] <Hashtable>] [[-WorkflowName] <String>] [[-Title] <String>] [[-AgentName] <String>] [[-SubagentCluster] <String>] [-OutputDirectory] <String> [[-OutputArchive] <String>] [[-ArgumentSeparator] <String>] [[-Timezone] <String>] [-WhatIf] [-Confirm] [<CommonParameters>]

DESCRIPTION

This cmdlet reads data from an Excel worksheet and creates respective workflow files for
jobs and dependencies.

The cmdlet makes use of the ImportExcel PowerShell Module, see https://github.com/dfinke/ImportExcel
To install this module use: Install-Module ImportExcel -scope CurrentUser

PARAMETERS

ExcelPath

-ExcelPath <String>
Specifies the path (directory and file name) to the Excel file that holds the worksheet with jobs and dependencies.

WorksheetName

-WorksheetName <String>
Specifies the name of the worksheet that should be processed.

If this parameter is not specified then all worksheets from the given Excel files will be processed to create workflows.

WorksheetColumns

-WorksheetColumns <Hashtable>
Specifies a hashtable to map internal names to column names of the Excel input file.
For each internal name corresponds to the respective column name from the Excel file can be specified.

The mapping of internal names to column names by default includes:

* nodeid => NodeID
* successorid => SuccessorID
* instruction => Instruction
* name => Name
* description => Description
* argument => Argument
* agent => Agent
* subagentCluster => Subagent Cluster
* jobResource => Job Resource
* jobClass => Job Class
* jobTemplate => Job Template
* script => Script
* errorHandling => Error Handling
* failOnStderr => Fail on stderr

WorkflowName

-WorkflowName <String>
Specifies the name of the workflow that will be created. This parameter applies to use of a single Excel worksheet.
If more than one Excel worksheet is used then the name of the respective worksheet will be used as the workflow name.

Title

-Title <String>
Secifies the title that will be assigned the workflows.

AgentName

-AgentName <String>
Specifies the name of an Agent that will be assigned the jobs. An Agent with the given name has to exist in the target JS7 environment.

SubagentCluster

-SubagentCluster <String>
If an Agent Cluster is used then the -AgentName parameter specifies the Agent Cluster and this parameter specifies the Subagent Cluster.

OutputDirectory

-OutputDirectory <String>
Specifies the directory to which workflow files (.json) will be stored.

OutputArchive

-OutputArchive <String>
Specifies the name of a .zip archive file to which workflows will be added and that can be used for import into JS7 JOC Cockpit.

ArgumentSeparator

-ArgumentSeparator <String>
If the Excel worksheet provides the "Argument" column then this can hold arguments in JSON format or in a

* JSON input format
** { "p_calc_id": 365985, "p_calc_ord": 1 }
** { p_calc_id : 365985, p_calc_ord : 1 }
* HashTable input format
** p_calc_id = AB2312 `n p_calc_name = rolling calculator
** p_calc_id = AB2312; p_calc_name = rolling calculator

For the HashTable input format by default a newline is used to seperate key/value pairs. If a different separator is used as the semicolon in the last example then this can be specified by using: -ArgumentSeparator ';'

Timezone

-Timezone <String>
Specifies the time zone that is applied to the workflow. If workflows make use of Admission Times then they are calculated based on the given time zone.

WhatIf

-WhatIf <SwitchParameter>

Confirm

-Confirm <SwitchParameter>

EXAMPLES

-------------------------- EXAMPLE 1 --------------------------

PS > ./New-JS7WorkflowFromExcel.ps1 -ExcelPath "/js7/excel/input/jobs.xlsx" -OutputDirectory /js7/excel/output/jobs -AgentName primaryAgent

Generates workflows per worksheet as available from the given Excel file.

-------------------------- EXAMPLE 2 --------------------------

PS > ./New-JS7WorkflowFromExcel.ps1 -WorkflowName Jobs01 -Title "EOD Processing" -ExcelPath "/js7/excel/input/jobs.xlsx" -OutputDirectory /js7/excel/output/jobs -AgentName primaryAgent -WorksheetName "Workflow01" -WorksheetColumns @{ 'nodeid' = 'NodeID'; 'successorid' = 'SuccessorID'; 'instruction' = 'Instruction'; 'name' = 'Name'; 'description' = 'Description'; 'argument' = 'Argument'; 'agent' = 'Agent'; 'subagentCluster' = 'Subagent Cluster'; 'jobResource' = 'Job Resource'; 'jobClass' = 'Job Class'; 'jobTemplate' = 'Job Template'; 'script' = 'Script'; 'errorHandling' = 'Error Handling'; 'failOnStderr' = 'Fail on stderr' }

Generates workflows for the indicated worksheet of the given Excel file. The -WorksheetColumns parameter specifies the mapping of internal names and column names from the Excel file.

-------------------------- EXAMPLE 3 --------------------------

PS > Get-ChildItem *.xlsx | Foreach-Object { ./New-JS7WorkflowFromExcel.ps1 -ExcelPath $_.FullName -Title $_.BaseName -OutputDirectory /js7/excel/output/jobs -AgentName AgentCluster001 -SubagentCluster active-active-all-agents }

Generates workflows from all Excel files in the given directory.