4.12.6 Acquisition of row data from a CSV/Excel file
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.
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.
- The character code is written in MS932, and the line feed code is CR+LF.
[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 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:host name
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 system]/[Prerequisite product OS running in the execution target server], see the release notes.
[Required product in the System]
Job Management Partner 1/Automatic Operation 10-10 or later
[Required products on the execution target system]
(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
[Prerequisite product OS running in the execution target server]
(1) Windows Server 2003,Standard Edition/Enterprise Edition (x86/x64) SP1 or later,Windows Server 2003 R2,Standard Edition/Enterprise Edition (x86/x64)
(2) Windows Server 2008 Standard/Enterprise (x86/x64),Windows Server 2008 R2 Standard/Enterprise/Datacenter
(3) Windows Server 2012 Standard/Datacenter, Windows Server 2012 R2 Standard/Datacenter
[Usage conditions for required products on the execution target server]
None.
Cautions
(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) 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.
(10) For the value serving as the key, use characters other than double quotations (") and single quotations (').
(11) If there are blank rows between data rows of the file being read, the blank rows are also counted in the number of rows. Even if the number of rows containing data is 99 or less, if the number of rows counted by including blank rows in between is 100 or more, the component is abnormally terminated.
Version
01.10.01
Category
OperatingSystem/Windows/Basic
Plug-in name displayed in the task log
osReadCSVExcelFileRow
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. |
-- |
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 |
O |
|
odbc.ColumnsAreNumeric |
Column number specification of the list of columns |
When specifying the column number for data acquisition, specify TRUE. When specifying the column name (character string), specify FALSE. |
-- |
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 |