4.13.5 Acquisition of row data from a CSV/Excel file(SYSTEM)
Function
This component acquires the data from the CSV (Comma Separated Values) file or from the specified row in the Excel sheet created with Microsoft Excel, and then outputs the data to the output property, in a Windows or Linux environment.
In a Windows environment, obtain data from a CSV or Excel file. In a UNIX environment, obtain data from a CSV file.
This component supports data acquisition from files that meet the following requirements:
[For CSV files]
- The file must be a text file with the csv extension.
- In the first row of the file, a comma-separated (single byte) column names are written.
- If data are read with the column name specified, make sure to specify the column name in the first row of the file.
- When reading data with the column number specified, the column name can be omitted.
- In the second and subsequent row in the file, comma-separated (,) data to be read must be written. Note that regardless of whether the column name is written or not, the data will be read from the second row.
- In a Windows environment, the character code is written in MS932, and the line feed code is CR+LF. 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 of Excel 2007 or later, or an Excel macro enabled book)
- The column name must be written. The first description found after the first row will be the column name.
- In the next row of the line where the column name is written or in the subsequent rows, the value corresponding to the column name must be written.
The following shows a file description example:
[File description example]
Sheet name: ServerInfo (Excel file only)
First row: Host name, number of CPU cores, memory, disk capacity, and license key
Second row: hostVM001, 2, 2048, 30, key1
Third row: hostVM002, 1, 1024, 10, key2
...
By specifying the sheet name to acquire data, the column name (or number) where the key information is written, the key value of the row to be acquired, or the name (or number) of the columns from which data is acquired, up to 90 items of the column data of the specified row can be acquired.
Check the data read in the field about the following. If any of these conditions is met, the component terminates abnormally.
(a) The character string length in the field exceeds 1,024 bytes.
(b) The field contains any of the following special characters:
<, >, |, ; (semi-colon), &, or "(double quotations) except at each end.
(c) The field contains a control character (0x00 to 0x1f).
The output information is output to each specified column after storing in the odbc.OutputValue - odbc.OutputValue90 property the value of the column specified in the odbc.ColumnList property in the row that matches the key value. If the number of characters in the field in any of the columns exceeds 1,011, the component terminates abnormally.
[Use example of component]
When acquiring the number of CPU cores of hostVM002 or the disk capacity from the file described above in "File description example", specify the input property as follows:
[Input property example 1 (for specifying the column name in the odbc.ColumnList property)]
odbc.SheetName:ServerInfo (Excel file only)
odbc.KeyColumnName:Host name
odbc.KeyValue:hostVM002
odbc.ColumnList:number of CPU cores, disk capacity
odbc.ColumnsAreNumeric:FALSE
[Input property example 2 (for specifying the column number in the odbc.ColumnList property)]
odbc.SheetName:ServerInfo (Excel file only)
odbc.KeyColumnName:1
odbc.KeyValue:hostVM002
odbc.ColumnList:2, 4
odbc.ColumnsAreNumeric:TRUE
When specified as Input property example 1 or 2 described above, the output property of the component is as follows:
[Output property]
odbc.ColumnValue1:1
odbc.ColumnValue2:10
odbc.ColumnValue3 - 90: No value
Use situation
You can acquire the data from the CSV or Excel file containing management information, and use the data as the input property of other component within the service.
Prerequisites
For the latest support information about [Required product in the System]/[Required products on the execution target server]/[OS running in the execution target server], see the release notes.
[Required product in the System]
(1) JP1/Automatic Operation 11-10 or later
[Required products on the execution target server]
(1) When specifying an Excel book or an Excel macro enabled book in Excel 2007 or later as the data acquisition target, the following Microsoft program needs to be installed.
- 2007 Office system driver, or
Microsoft Access Database Engine 2010(32bit), or
Microsoft Access Database Engine 2016(32bit)
[OS running in 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)
[Usage conditions for required products on the execution target server]
None.
Cautions
- Notes when the server is running in a Windows environment
(1) Due to the limitations of the ODBC driver, [, ], and ! cannot be used in file names.
(2) If the cells in the Excel file are merged, or if an error occurs in the mathematical expression, the obtained value becomes null. Also, the component is abnormally terminated if a written password is set. Remove these states from the read target file.
(3) Do not mix character string data and numerical data in the reading columns. If this data is mixed, the values might not be obtained properly depending on the ODBC driver specifications.
(a) Numerical data refers to character strings in the following formats:
Signed integer values (decimal format) Example: -12345
Values with fixed decimal point (decimal format) Example: 3.1415
Values with a floating decimal point (decimal format) Example: 6.543E+10
(b) Character string data refers to character strings in formats other than the above numerical formats.
(4) If columns containing different mixed data types is obtained from the file that is read, the following corrective action must be taken.
- For Excel files, add a single quotation mark (') to the beginning of the numerical data.
- For CSV files, enclose the numerical data in double quotations (").
(5) If the file being read is a CSV file, enclose the IP address in double quotations ("). If the value is not enclosed in double quotations ("), it might be falsely identified as numerical data, and the value will not be obtained properly.
(6) Specify the content shown below for the column name.
- Always specify a character string. Do not specify numerical data.
- The following characters cannot be used in the column name.
- For the first character of the column name serving as the key, the following characters cannot be used:
! " # $ % & ' ( ) - = ^ ~ \ | ` ; + * [ ] { } , . < > ? _ / 0 1 2 3 4 5 6 7 8 9
- For the second and later characters of the column name serving as the key, the following characters cannot be used.
" # % & ' ( ) - = ^ ~ \ | @ ; : + * [ ] { } , . < > ? /
- For a column name other than the column name serving as the key, the following characters cannot be used.
! [ ] ` , .
- The specified character string can include up to 64 characters and a mix of single-byte and double-byte characters.
(7) If the file being read is an Excel file, the displayed value and actual obtained value might differ depending on the data type of the data contained in the cell. Take careful note if the following data types are contained in the cell.
(a) Boolean type (True/False)
Displayed value: TRUE/FALSE
Value entered in cell: TRUE/FALSE
Actual obtained value: True/False
(b) Date data
Displayed value: mm (month) dd (day)
Value entered in cell: yyyy/mm/dd
Actual obtained value: yyyy/mm/dd
(c) Time data
Displayed value: hh:mm
Value entered in cell: hh:mm:ss
Actual obtained value: hh:mm:ss
(d) Percentage
Displayed value: ~% (Example: 10%)
Value entered in cell: ~% (Example: 10%)
Actual obtained value: 0.~ (Example: 0.1)
(e) Mathematical expression
Displayed value: (Result of mathematical expression)
Value entered in cell: (Mathematical expression)
Actual obtained value: (Result of mathematical expression)
To obtain values for the data in (a), (d), or (e) exactly as they are entered in the cell, add a single quotation (') at the start of the entry.
If the file being read is a CSV file, the values contained in the cells can be obtained without any changes.
(8) If the cells contain any of the data types in (7), make the entries so that the data excluding the column with the column names is a single data type only. If data in multiple types coexist in one line, data might not be able to be acquired.
(9) If column numbers are to be specified for the key column name property (odbc.KeyColumnName) and for the list of columns for which data is to be obtained (the property odbc.ColumnList), specify integer values less than or equal to 255 or less. If you specify values of 256 or greater, the plug-in ends abnormally. Note that empty columns are included in the column count.
(10) If a column name is specified for the key column name property (odbc.KeyColumnName) and for the list of columns for which data is to be obtained (the property odbc.ColumnList), specify the column name of a column on or before the 255th column. If you specify the column name of a column on or after the 256th column, this plug-in ends abnormally. Note that empty columns are included in the column count.
(11) Specify an integer value from 0 to 32,767 for the retry count for reading a file. The component is abnormally terminated if a value out of range is specified.
(12) Specify an integer from 1 to 60 for the retry interval for reading a file. The component is abnormally terminated if a value out of range is specified.
(13) For the value serving as the key, use characters other than double quotations (") and single quotations (').
(14) For CSV files, the character string length of the field should be within 255 bytes. If it is more than 256 bytes, the ODBC driver can not acquire the value correctly, and the component may be abnormally terminated.
(15) If you execute this plug-in on the same file at the same time, the file access may fail. In this case, adjust the retry count and retry interval, and then retry until you can avoid the error.
(16) Characters that cannot be displayed with MS932 in the data acquired from a Excel file are replaced with "?".
- Notes when the server is running in a UNIX environment
(1) Specify column names as follows:
- The following characters cannot be used as part of a column name.
- The following characters cannot be used as the first character of a key column name:
! " # $ % & ' ( ) - = ^ ~ \ | ` ; + * [ ] { } , . < > ? _ / 0 1 2 3 4 5 6 7 8 9
- The following characters cannot be used as the second or subsequent characters of a key column name:
" # % & ' ( ) - = ^ ~ \ | @ ; : + * [ ] { } , . < > ? /
- The following characters cannot be used for character names other than key column names:
! [ ] ` , .
- A maximum of 64 characters can be specified.
(2) Specify an integer value of 255 or less for the column number of the file being read. The component is abnormally terminated if a value of 256 or more is specified. Note that blank columns between data rows are also included in the number of columns.
(3) For key values, use characters other than double quotation marks (") or single quotation marks (').
(4) Commas (,) cannot be used in CSV file data. If you attempt to use commas in CSV file data, columns cannot be separated correctly, making it impossible to acquire data.
(5) Any number of single-byte spaces or tab characters at the beginning or end of a row in a CSV file are ignored.
(6) 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
Users who belong to the Administrator group
Version
03.10.02
Plug-in tags
Gather OS information,Windows,Linux
Plug-in name displayed in the task log
osReadCSVExcelFileRow_System
Return code
0: Normal
11: Error (Mistake by user) Invalid content in the definition file
12: Error (Mistake by user) Invalid property
14: Error (Mistake by user) File inaccessible
15: Error (Mistake by user) No row matches the specified key value
27: Error (Check with the task log about the error detail)
41: Error (An error has been detected in the component) Property not entered (An error has been detected in the component script)
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 file name. |
-- |
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 |
|
odbc.KeyColumnName |
Column name serving as the key |
Specifies the title character string of the column where the key information is to be stored, or the column numbers. |
-- |
Input |
R |
|
odbc.KeyValue |
Value serving as the key |
Specifies the character string to identify the row from which data is acquired. |
-- |
Input |
R |
|
odbc.ColumnList |
List of the columns from which data is acquired. |
Specifies the character string of the title of the columns from which data is acquired, or the comma-separated column numbers. Up to 90 column names (or column numbers) can be specified. |
-- |
Input |
R |
|
odbc.ColumnsAreNumeric |
Column number specification of the list of columns |
Specify TRUE if numbers are used to specify the column where key information is stored and the columns for which data is to be obtained. Specify FALSE if column names (character strings) are used. |
-- |
Input |
R |
|
odbc.fileOpenRetryCount |
Retry count for reading a file: Windows specific |
Specifies the number of times to retry when reading a file fails (only applicable to Windows). This value multiplied by the retry interval gives the maximum waiting time. If "0" is specified, a retry is not performed. |
20 |
Input |
R |
|
odbc.fileOpenRetryInterval |
Retry interval for reading a file: Windows specific |
Specifies the retry interval in seconds for when reading a file fails (only applicable to Windows). |
5 |
Input |
R |
|
odbc.OutputValue1 |
Data 1 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue2 |
Data 2 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue3 |
Data 3 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue4 |
Data 4 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue5 |
Data 5 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue6 |
Data 6 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue7 |
Data 7 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue8 |
Data 8 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue9 |
Data 9 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue10 |
Data 10 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue11 |
Data 11 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue12 |
Data 12 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue13 |
Data 13 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue14 |
Data 14 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue15 |
Data 15 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue16 |
Data 16 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue17 |
Data 17 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue18 |
Data 18 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue19 |
Data 19 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue20 |
Data 20 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue21 |
Data 21 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue22 |
Data 22 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue23 |
Data 23 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue24 |
Data 24 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue25 |
Data 25 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue26 |
Data 26 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue27 |
Data 27 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue28 |
Data 28 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue29 |
Data 29 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue30 |
Data 30 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue31 |
Data 31 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue32 |
Data 32 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue33 |
Data 33 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue34 |
Data 34 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue35 |
Data 35 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue36 |
Data 36 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue37 |
Data 37 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue38 |
Data 38 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue39 |
Data 39 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue40 |
Data 40 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue41 |
Data 41 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue42 |
Data 42 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue43 |
Data 43 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue44 |
Data 44 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue45 |
Data 45 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue46 |
Data 46 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue47 |
Data 47 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue48 |
Data 48 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue49 |
Data 49 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue50 |
Data 50 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue51 |
Data 51 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue52 |
Data 52 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue53 |
Data 53 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue54 |
Data 54 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue55 |
Data 55 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue56 |
Data 56 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue57 |
Data 57 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue58 |
Data 58 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue59 |
Data 59 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue60 |
Data 60 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue61 |
Data 61 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue62 |
Data 62 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue63 |
Data 63 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue64 |
Data 64 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue65 |
Data 65 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue66 |
Data 66 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue67 |
Data 67 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue68 |
Data 68 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue69 |
Data 69 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue70 |
Data 70 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue71 |
Data 71 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue72 |
Data 72 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue73 |
Data 73 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue74 |
Data 74 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue75 |
Data 75 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue76 |
Data 76 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue77 |
Data 77 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue78 |
Data 78 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue79 |
Data 79 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue80 |
Data 80 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue81 |
Data 81 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue82 |
Data 82 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue83 |
Data 83 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue84 |
Data 84 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue85 |
Data 85 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue86 |
Data 86 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue87 |
Data 87 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue88 |
Data 88 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue89 |
Data 89 |
Specifies the acquired data. |
-- |
Output |
O |
|
odbc.OutputValue90 |
Data 90 |
Specifies the acquired data. |
-- |
Output |
O |
|
common.returnValue |
Return value for the plugin |
The return value of this plugin stored. |
-- |
Output |
O |