4.13.6 Acquisition of column data from a CSV/Excel file
Function
This component acquires all the data from the CSV (Comma Separated Values) file or from the specified column 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.
- If a column name is required, the comma-separated (single byte) column names must be written in the first row of the file. If no column name is required, it 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:
[Description example 1]
Host name
hostVM001
hostVM002
[Description example 2]
Host name, comment
hostVM001, #Comment1
hostVM002, #Comment2
Check the data read in the field for the conditions below. If any of these conditions is met, the component terminates abnormally.
(a) The character string length in the field exceeds 1,024 characters.
(b) The field contains any of the following special characters:
, (comma, in a Windows environment), <, >, |, ; (semi-colon), &, "(double quotations) except at each end, or a trailing \.
(c) The field contains a control character (0x00 to 0x1f).
The information is output to each specified row. If the number of data items exceeds 99 in any of the columns or the [(total of the number of characters in a field) + number of data lines] exceeds 1,017, the component terminates abnormally.
When the execution target server is Windows, the built-in Administrator is required to execute this plug-in. If the built-in Administrator does not exist or is invalid on the execution target server, execute "Acquisition of column data from a CSV/Excel file(SYSTEM)" plug-in that executes with the system account.
Use situation
- You can acquire the data from the CSV/Excel file containing management information, and use the data as input for a service.
- You can use this component to acquire the column as the key information of the CSV/Excel file and then use the information as the input to repeat components or Acquisition of row data from a CSV/Excel file components. By doing so, you can perform special processing for all the rows written in the file.
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-00 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 ! are 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 are 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) 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.
(7) If the cells contain any of the data types in (6), 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 column, data might not be able to be acquired.
(8) 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.
(9) 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.
(10) 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.
(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.
(12) 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.
(13) 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.
(14) 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 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.
(2) 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.
(3) Commas (,) cannot be used in data. If you attempt to use commas in data, columns cannot be separated correctly, making it impossible to acquire data.
(4) 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
Built-in Administrator
Version
02.00.01
Plug-in tags
Gather OS information,Windows,Linux
Plug-in name displayed in the task log
osReadCSVExcelFileColumn
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
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.Column1 |
Column number 1 |
Specifies the column number of the data to acquire. |
-- |
Input |
R |
|
odbc.Column2 |
Column number 2 |
Specifies the column number of the data to acquire. |
-- |
Input |
O |
|
odbc.Column3 |
Column number 3 |
Specifies the column number of the data to acquire. |
-- |
Input |
O |
|
odbc.Column4 |
Column number 4 |
Specifies the column number of the data to acquire. |
-- |
Input |
O |
|
odbc.Column5 |
Column number 5 |
Specifies the column number of the data to acquire. |
-- |
Input |
O |
|
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 |
|
common.returnValue |
Return value for the plugin |
The return value of this plugin stored. |
-- |
Output |
O |