4.13.1 Update cell data in a CSV/Excel file
Function
This plug-in updates cells in CSV (Comma Separated Values) files or excel sheets created in Microsoft Excel with the specified input data. In a Windows environment, this plug-in can update the data in a CSV file or Excel file. In a UNIX environment, this plug-in can update the data in a CSV file.
The updated data will be displayed according to the display format of cells.
Do not specify any confidential information, such as passwords, as input data because the input data will be output to the task log file.
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.
- In a UNIX environment, when you specify a CSV file name, make sure that the length of the file name, excluding the path name, does not exceed 225 bytes.
- 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 in this plug-in performs the following processing:
- Updates the cells specified in the list of cells (the property excel.cellList) of the file whose name is specified for the file name property (common.fileName) with the data specified as input data (the properties excel.inputValue1 to 50)
A maximum of 5,120 characters in total can be specified as input data.
In a UNIX environment, make sure that the length of each row does not exceed 127 KB after the file is updated with input data.
In a UNIX environment, this plug-in can update the data of only existing rows or columns. An attempt to update non-existent rows or columns results in an error.
This plug-in does not create a file even if the specified CSV file or Excel file does not exist.
In a Windows environment, if the specified file is already opened by the built-in Administrator, this plug-in ends abnormally.
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 creates a temporary file whose name is the specified CSV file name suffixed by a string in -tmp-yyyymmdd_hhmmss-pid format (yyyymmdd_hhmmss: date and time that the plug-in was run, pid: PID of the plug-in). Note that a new temporary file will overwrite an existing temporary file if the names of these temporary files include the same CSV file name.
For example, if you specify /tmp/data.csv as the target CSV file, the temporary file to be created will have the following name:
/tmp/data.csv-tmp-yyyymmdd_hhmmss-pid
(yyyymmdd_hhmmss: date and time that the plug-in was run, pid: PID of the plug-in)
Note that this temporary file is deleted when the plug-in terminates.
In a UNIX environment, the directory that contains the target CSV file must have free space whose size is equal to or larger than the CSV file.
In a UNIX environment, if an ordinary user other than the owner of the CSV file runs the plug-in, the owner and group of the CSV file are replaced with the respective information of that user after the plug-in is run.
Use situation
You can use this plug-in to write data, such as output information and the results of services that were already executed, to CSV or Excel files.
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) Do not simultaneously execute multiple instances of this plug-in for the same file.
(4) To input data to merged cells, in the list of cells (the property excel.cellList), specify only the cell at the upper left corner of the merged range. If you specify a cell other than the cell at the upper left corner of the merged range, the plug-in ends normally but the data is not input.
(5) 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.
(6) If the plug-in ends abnormally, the file will not be updated. Accordingly, data already input to the file will not be saved.
- 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) 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.
(3) Do not simultaneously execute multiple instances of this plug-in for the same file.
(4) If the plug-in ends abnormally, the file will not be updated. Accordingly, data already input to the file will not be saved.
(5) 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.
Execution privilege
Windows environment: User with Administrator permission
UNIX environment: Root user or the owner of the CSV file and read permission for the CSV file and write permission for the parent directory of the CSV file
Version
02.51.00
Plug-in tags
Control OS,File Operations,Windows,Linux
Plug-in name displayed in the task log
osUpdateCSVExcelFileCell
Return code
0: Normal
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 updated. |
-- |
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 which data to be input. 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 which data to be input. You can specify a maximum of 50 cells delimited by commas (,). |
-- |
Input |
R |
|
excel.inputValue1 |
InputData 1 |
Specify the data to be input to the 1st column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue2 |
InputData 2 |
Specify the data to be input to the 2nd column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue3 |
InputData 3 |
Specify the data to be input to the 3rd column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue4 |
InputData 4 |
Specify the data to be input to the 4th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue5 |
InputData 5 |
Specify the data to be input to the 5th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue6 |
InputData 6 |
Specify the data to be input to the 6th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue7 |
InputData 7 |
Specify the data to be input to the 7th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue8 |
InputData 8 |
Specify the data to be input to the 8th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue9 |
InputData 9 |
Specify the data to be input to the 9th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue10 |
InputData 10 |
Specify the data to be input to the 10th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue11 |
InputData 11 |
Specify the data to be input to the 11th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue12 |
InputData 12 |
Specify the data to be input to the 12th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue13 |
InputData 13 |
Specify the data to be input to the 13th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue14 |
InputData 14 |
Specify the data to be input to the 14th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue15 |
InputData 15 |
Specify the data to be input to the 15th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue16 |
InputData 16 |
Specify the data to be input to the 16th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue17 |
InputData 17 |
Specify the data to be input to the 17th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue18 |
InputData 18 |
Specify the data to be input to the 18th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue19 |
InputData 19 |
Specify the data to be input to the 19th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue20 |
InputData 20 |
Specify the data to be input to the 20th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue21 |
InputData 21 |
Specify the data to be input to the 21st column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue22 |
InputData 22 |
Specify the data to be input to the 22nd column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue23 |
InputData 23 |
Specify the data to be input to the 23rd column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue24 |
InputData 24 |
Specify the data to be input to the 24th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue25 |
InputData 25 |
Specify the data to be input to the 25th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue26 |
InputData 26 |
Specify the data to be input to the 26th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue27 |
InputData 27 |
Specify the data to be input to the 27th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue28 |
InputData 28 |
Specify the data to be input to the 28th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue29 |
InputData 29 |
Specify the data to be input to the 29th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue30 |
InputData 30 |
Specify the data to be input to the 30th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue31 |
InputData 31 |
Specify the data to be input to the 31st column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue32 |
InputData 32 |
Specify the data to be input to the 32nd column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue33 |
InputData 33 |
Specify the data to be input to the 33rd column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue34 |
InputData 34 |
Specify the data to be input to the 34th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue35 |
InputData 35 |
Specify the data to be input to the 35th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue36 |
InputData 36 |
Specify the data to be input to the 36th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue37 |
InputData 37 |
Specify the data to be input to the 37th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue38 |
InputData 38 |
Specify the data to be input to the 38th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue39 |
InputData 39 |
Specify the data to be input to the 39th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue40 |
InputData 40 |
Specify the data to be input to the 40th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue41 |
InputData 41 |
Specify the data to be input to the 41st column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue42 |
InputData 42 |
Specify the data to be input to the 42nd column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue43 |
InputData 43 |
Specify the data to be input to the 43rd column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue44 |
InputData 44 |
Specify the data to be input to the 44th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue45 |
InputData 45 |
Specify the data to be input to the 45th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue46 |
InputData 46 |
Specify the data to be input to the 46th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue47 |
InputData 47 |
Specify the data to be input to the 47th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue48 |
InputData 48 |
Specify the data to be input to the 48th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue49 |
InputData 49 |
Specify the data to be input to the 49th column specified in excel.cellList. |
-- |
Input |
O |
|
excel.inputValue50 |
InputData 50 |
Specify the data to be input to the 50th column specified in excel.cellList. |
-- |
Input |
O |
|
common.returnValue |
Return value for the plugin |
The return value of this plugin stored. |
-- |
Output |
O |