Hitachi

JP1 Version 12 JP1/Automatic Operation Service Template Reference


4.13.2 Acquisition of cell data from a CSV/Excel file

Function

This plug-in retrieves data from the specified cells in a CSV (Comma Separated Values) file or Microsoft Excel worksheet, and then sets the retrieved data in the output property. In a Windows environment, this plug-in can retrieve data from a CSV file or Excel file. In a UNIX environment, this plug-in can retrieve data from a CSV file.

When the data retrieved from the cells is set in the output property, the data is output to the task log.

Note that the data is not output to the task log when the output level of the task log is set to the default value (10) and the task ends normally.

This plug-in can be used for files that meet the following conditions:

- In a Windows environment, the file can be edited in Microsoft Excel.

- For CSV files, the file must be a text file with the csv extension.

- In a UNIX environment, the CSV file must be written with the encoding specified for the LANG user environment variable, and must use LF as the line break code.

- For Excel files, the file must have the xls extension (Excel format saved as "Excel 97 - 2003 book") or the xlsx or xlsm extension (Excel book or Excel macro enabled book of Excel 2007 or later).

This plug-in requires the following server:

- Execution-target server

The server on which this plug-in is to be executed. In a Windows environment, the prerequisite version of Microsoft Excel must be installed on this server.

The script contained in this plug-in performs the following process:

From the input file specified in the file name property (common.fileName), data in the cells specified in the cell list property (excel.cellList) are retrieved and then output to the output properties (excel.OutputValue1 to excel.OutputValue50).

When using 32-bit version of Microsoft Excel, if the following folder does not exist, the folder is created.

%SystemRoot%\SysWOW64\config\systemprofile\Desktop

When using 64-bit version of Microsoft Excel, you need to create the following folder in advance.

%SystemRoot%\System32\config\systemprofile\Desktop

%SystemRoot% mentioned above specifies the same folder as the environment variable "SystemRoot".

In a UNIX environment, this plug-in checks whether the following condition is met for each cell of the retrieved data. If the condition is met, the plug-in terminates abnormally.

(a) The data length in the cell exceeds 1,024 bytes.

Use situation

You can acquire the data from the CSV/Excel file containing management information, and use the data as input for a service.

Prerequisites

For the most recent information about the prerequisite product in the system, prerequisite products on the execution-target server, and supported OSs for the execution-target server, see the Release Notes.

Prerequisite product in the system:

JP1/Automatic Operation 11-10 or later

Prerequisite products on the execution-target server:

In a Windows environment

(1) Microsoft Excel 2007

(2) Microsoft Excel 2010

(3) Microsoft Excel 2013

(4) Microsoft Excel 2016

(5) Microsoft Excel 2019

Supported OSs for the execution-target server:

(1) Windows Server 2008 R2 Standard/Enterprise/Datacenter

(2) Windows Server 2012 Standard/Datacenter, Windows Server 2012 R2 Standard/Datacenter

(3) Windows Server 2016 Standard/Datacenter

(4) Windows Server 2019 Standard/Datacenter

(5) Red Hat Enterprise Linux Server 6 (32-bit x86), Red Hat Enterprise Linux Server 6 (64-bit x86_64)

(6) Red Hat Enterprise Linux Server 7 (64-bit x86_64)

(7) Red Hat Enterprise Linux Server 8 (64-bit x86_64)

(8) CentOS 6 (64-bit x86_64)

(9) CentOS 7 (64-bit x86_64)

(10) CentOS 8 (64-bit x86_64)

(11) Oracle Linux 8 (64-bit x86_64)

Conditions for using the prerequisite products on the execution-target server:

None

Cautions

- Notes when the server is running in a Windows environment

(1) Do not specify character strings containing double quotation marks (") or single quotation marks (') for properties of this plug-in.

(2) This plug-in cannot be used for Excel files for which read passwords or write passwords are set.

(3) This plug-in can retrieve strings in the same format as that displayed in cells in Excel files. Note the following:

- Depending on the versions of the OS and Excel, the data might be retrieved in a different format from that displayed in the cell.

For example, from an Excel 2007 file on a device running Windows Server 2008 R2 or Windows Server 2012 R2 environment, even if data is displayed in the format "yyyy/mm/dd", the retrieved data might be displayed as "2017/4/1" instead of "2017/04/01".

- The maximum length of data that this plug-in can retrieve is the same as the maximum number of characters that Windows PowerShell can retrieve.

For example, from an Excel 2007 file on a device running Windows Server 2008 R2 or Windows Server 2012 R2 environment, you can retrieve a maximum of 8,221 characters.

(4) If this plug-in terminated abnormally and "80080005 Server execution failed" was output in the task log, the Excel file might be open on the execution-target server. Confirm the status of the Excel file. At this time, the process "OfficeC2RClient.exe" might start up. If you want to terminate this process, operate with Task Manager, etc.

- Notes when the server is running in a UNIX environment

(1) Do not specify character strings containing double quotation marks (") or single quotation marks (') for properties of this plug-in.

(2) The data of each cell in a CSV file cannot include a comma (,) or line break. Cells that include a comma (,) or line break are not correctly handled as columns.

(3) If a locale environment variable (such as LC_ALL or LC_MESSAGES) that has higher priority than LANG has been specified, the higher-priority locale environment variable will take precedence over LANG. Therefore, when executing a plug-in, cancel the higher-priority locale environment variable (such as LC_ALL or LC_MESSAGES) or change its value to the same value as the LANG environment variable.

Execution privilege

Windows environment: User with Administrator permission

UNIX environment: User with read permission for the CSV file

Version

02.51.00

Plug-in tags

File Operations,Gather OS information,Windows,Linux

Plug-in name displayed in the task log

osReadCSVExcelFileCell

Return code

0: Normal

11: Error (Mistake by user) Invalid content in the definition file

12: Error (mistake by user) Invalid property

21: Error (invalid environment) Command cannot be found (error detected in the plug-in script)

27: Error (check task logs for the nature of error)

Property list

The following table lists the properties:

Property key

Property name

Description

Default value

I/O type

Required

plugin.destinationHost

Host name of the execution target server

Specify the host name or IP address of the server on which this plugin will be executed. IPv6 addresses are not supported.

--

Input

R

common.fileName

File name

Specify the full path of the target CSV or Excel file to be retrieved data.

--

Input

R

common.sheetName

Worksheet name in Excel file

Specifies the name of the worksheet in the Excel file. Does not need to be specified in the case of a CSV file.

Sheet1

Input

O

excel.cellList

List of cells

In a Windows environment, use the A1-style cell reference string or cell name to specify the location of the cell whose data you want to retrieve. You can specify a maximum of 50 cells delimited by commas (,). In a UNIX environment, use the "R;C" format (R: row number, C: column number) to specify the location of the cell whose data you want to retrieve. You can specify a maximum of 50 cells delimited by commas (,).

--

Input

R

excel.OutputValue1

Data 1

Stores the retrieved data.

--

Output

O

excel.OutputValue2

Data 2

Stores the retrieved data.

--

Output

O

excel.OutputValue3

Data 3

Stores the retrieved data.

--

Output

O

excel.OutputValue4

Data 4

Stores the retrieved data.

--

Output

O

excel.OutputValue5

Data 5

Stores the retrieved data.

--

Output

O

excel.OutputValue6

Data 6

Stores the retrieved data.

--

Output

O

excel.OutputValue7

Data 7

Stores the retrieved data.

--

Output

O

excel.OutputValue8

Data 8

Stores the retrieved data.

--

Output

O

excel.OutputValue9

Data 9

Stores the retrieved data.

--

Output

O

excel.OutputValue10

Data 10

Stores the retrieved data.

--

Output

O

excel.OutputValue11

Data 11

Stores the retrieved data.

--

Output

O

excel.OutputValue12

Data 12

Stores the retrieved data.

--

Output

O

excel.OutputValue13

Data 13

Stores the retrieved data.

--

Output

O

excel.OutputValue14

Data 14

Stores the retrieved data.

--

Output

O

excel.OutputValue15

Data 15

Stores the retrieved data.

--

Output

O

excel.OutputValue16

Data 16

Stores the retrieved data.

--

Output

O

excel.OutputValue17

Data 17

Stores the retrieved data.

--

Output

O

excel.OutputValue18

Data 18

Stores the retrieved data.

--

Output

O

excel.OutputValue19

Data 19

Stores the retrieved data.

--

Output

O

excel.OutputValue20

Data 20

Stores the retrieved data.

--

Output

O

excel.OutputValue21

Data 21

Stores the retrieved data.

--

Output

O

excel.OutputValue22

Data 22

Stores the retrieved data.

--

Output

O

excel.OutputValue23

Data 23

Stores the retrieved data.

--

Output

O

excel.OutputValue24

Data 24

Stores the retrieved data.

--

Output

O

excel.OutputValue25

Data 25

Stores the retrieved data.

--

Output

O

excel.OutputValue26

Data 26

Stores the retrieved data.

--

Output

O

excel.OutputValue27

Data 27

Stores the retrieved data.

--

Output

O

excel.OutputValue28

Data 28

Stores the retrieved data.

--

Output

O

excel.OutputValue29

Data 29

Stores the retrieved data.

--

Output

O

excel.OutputValue30

Data 30

Stores the retrieved data.

--

Output

O

excel.OutputValue31

Data 31

Stores the retrieved data.

--

Output

O

excel.OutputValue32

Data 32

Stores the retrieved data.

--

Output

O

excel.OutputValue33

Data 33

Stores the retrieved data.

--

Output

O

excel.OutputValue34

Data 34

Stores the retrieved data.

--

Output

O

excel.OutputValue35

Data 35

Stores the retrieved data.

--

Output

O

excel.OutputValue36

Data 36

Stores the retrieved data.

--

Output

O

excel.OutputValue37

Data 37

Stores the retrieved data.

--

Output

O

excel.OutputValue38

Data 38

Stores the retrieved data.

--

Output

O

excel.OutputValue39

Data 39

Stores the retrieved data.

--

Output

O

excel.OutputValue40

Data 40

Stores the retrieved data.

--

Output

O

excel.OutputValue41

Data 41

Stores the retrieved data.

--

Output

O

excel.OutputValue42

Data 42

Stores the retrieved data.

--

Output

O

excel.OutputValue43

Data 43

Stores the retrieved data.

--

Output

O

excel.OutputValue44

Data 44

Stores the retrieved data.

--

Output

O

excel.OutputValue45

Data 45

Stores the retrieved data.

--

Output

O

excel.OutputValue46

Data 46

Stores the retrieved data.

--

Output

O

excel.OutputValue47

Data 47

Stores the retrieved data.

--

Output

O

excel.OutputValue48

Data 48

Stores the retrieved data.

--

Output

O

excel.OutputValue49

Data 49

Stores the retrieved data.

--

Output

O

excel.OutputValue50

Data 50

Stores the retrieved data.

--

Output

O

common.returnValue

Return value for the plugin

The return value of this plugin stored.

--

Output

O