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).
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 products on the execution-target server, and supported OSs for the execution-target server, see the Release Notes.
In addition, the following OS and products use abbreviations. For the abbreviations of OS and products, see the "Preface".
Prerequisite products on the execution-target server:
In a Windows environment
(1) Microsoft Excel
Supported OSs for the execution-target server:
(1) Windows Server
(2) Red Hat Enterprise Linux Server
(3) Oracle Linux
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.
- 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.
(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 |