4.13.8 Division of data from a CSV file
Function
This plug-in divides a CSV (Comma Separated Values) file into multiple files containing the
specified number of rows.
You can also specify column filter conditions so that only rows that meet those conditions
are output to the files resulting from the division.
This plug-in can be used for CSV files that meet the following conditions:
- The column names are entered in the first row. Also, the second and subsequent rows must
exist. The items in the first row are copied, as column names, into each of the output files.
- The file must be a text file with the csv extension.
- The line feed code must be CR + LF or LF.
- The character encoding must be UTF-8 with BOM (Byte Order Mark) or Shift-JIS. If you divide
a CSV file that uses an unsupported character encoding, use the plug-in for converting
the character encoding of a file in advance.
- The delimiter for CSV is a comma (,).
- If you include commas (,) in the column name and each item of the second and subsequent
rows, enclose each item in double quotation marks (""). Note that you cannot perform
filtering by specifying column names that include a comma (,). However, you can perform
filtering by specifying column numbers.
The prerequisite server for this plug-in is as follows:
- Execution target server
This server is used as the target for executing this plug-in.
The script in this plug-in uses the Import-CSV commandlet to perform the following processing.
For details about the Import-CSV commandlet, visit the Microsoft library Web site.
(1) The input file specified for the file name(common.fileName property) is copied to an
output file (serial number 0)#.
(2) If a list of columns to be filtered (csv.filterColumnList property) is specified, for
each list of columns, only the rows that match the filter conditions
(csv.filterColumnString 1 to 10 properties) for the column data are extracted. After
extraction, the file is overwritten and saved as a temporary file (serial number 0)#.
(3) The rows are copied in batches of the number of rows specified for number of rows to be
divided (common.divideLineCount property), from a temporary file (serial number 0)# to
each of the other output files (serial number 1 onward)#.
#: The temporary file and output files are assigned a name that is made by concatenating
the following elements:
- Path of the folder to which the file is output (value of the common.outputFolderPath
property)
- Folder delimiter character "\"
- File prefix (value of the common.filePrefix property)
- Serial number based on the character string associated with the repeated execution plug-in
(value of the common.loopInputProperties property)
- ".csv"
The CSV file format after the division is as follows:
- The output CSV files are separated by commas (,). Column names are output enclosed in double
quotation marks (""), and each item on the second and subsequent rows is output enclosed
in double quotation marks ("") if it is not numerical data.
- 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
- The line feed code is CR+LF.
- The character encoding specified at the time of loading is used.
- If an error occurs when reading a CSV file because the CSV file structure cannot be analyzed
or if the CSV file has less than one row, the temporary file (serial number 0) is a copy
of the file to be divided.
- If the folder specified in the folder path for the file output (common.outputFolderPath
property) does not exist, create a folder to output the files after the division of the
file under that folder. Note that if a file with the same name as the divided file already
exists, the file is overwritten.
Use situation
When values entered in the rows of a CSV file are processed by using a repeated execution plug-in, dividing the CSV file by using this plug-in allows you to process a greater number of rows than the maximum number of repetitions allowed by the repeated execution plug-in.
In addition, by specifying filter conditions for columns of your choice, you can choose to extract and process only rows that meet those conditions.
The environment that unable to install Microsoft Excel.
If the encoding of the CSV file that does not supported format of the CSV data split part, consider changing the encoding by the character code conversion part.
If you want to split an Excel file with the CSV data split part, consider using an Excel CSV file conversion part. Note: the Excel CSV file Conversion component works with Microsoft Excel to convert a file.
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:
None.
Supported OSs for the execution-target server:
(1) Windows Server
Conditions for using the prerequisite products on the execution-target server:
None.
Cautions
(1) For the properties of this plug-in, do not specify character strings that contain double
quotation marks (") and single quotation marks ('). Also, for the properties of filter
conditions, do not specify character strings that contain commas (,) or line feeds.
(2) If no row matches the filter conditions, files are not created after the files are divided.
(3) If no files are created after the files are divided (because there were no rows that
match the filter conditions or other reasons), the character string for the repeated
execution plug-in (the serial number of the file name after the file is divided)
(common.loopInputProperties property) is left blank.Therefore, if this property is
passed to the repeated execution plug-in as it is, the repeated execution plug-in
terminates abnormally.
(4) The following characters cannot be used in the file prefix (common.filePrefix property).
< > ? [ ] : | *
(5) Specify an integer value from 1 to 65535 for the number of rows to divide
(common.divideLineCount property). If a value less than 0 or greater than 65536 is
specified, this plug-in terminates abnormally.
(6) If the specified type of header (csv.isNumericHeader property) is not specified, the
behavior is the same as if FALSE is specified.
(7) If a column name contains two commas (,,), a comma and a space only (, ), or a comma
followed by two double quotation marks followed by a comma (,"",) these strings are
replaced with an arbitrary character string. Neither filtering by specifying such column
names nor column numbers can be performed.
(8) You can specify double quotation marks (") only at the beginning or at the end for column
names and each item in the second and the subsequent rows. You cannot set a tab at the
beginning or end of CSV file rows or at the beginning or end of each item of the column
name.
(9) Hidden files cannot be specified for the properties of this plug-in.
Execution privilege
Administrator permission
Version
03.60.01
Plug-in tags
Control OS,File Operations,Windows
Plug-in name displayed in the task log
osDivideCSVFile
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 file to be divided. |
-- |
Input |
R |
|
common.characterEncoding |
Character encoding |
Specify the encoding of the target CSV file to be divided. You can specify DEFAULT or UTF-8. When using Shift-JIS, specify DEFAULT. |
DEFAULT |
Input |
O |
|
common.outputFolderPath |
Destination folder path for file output |
Specify the full path of the folder to which to output the output files. |
-- |
Input |
R |
|
common.filePrefix |
File prefix |
Specify the prefix of the output files. |
-- |
Input |
R |
|
common.divideLineCount |
Number of rows to be divided |
Specify the number of rows (excluding the header) in each output file, in halfwidth numerical values. |
-- |
Input |
R |
|
csv.isNumericHeader |
Specified category of the header |
If columns to undergo filter processing are specified by using numbers, specify TRUE. If columns are specified by name (string), specify FALSE. |
True |
Input |
O |
|
csv.filterColumnList |
List of columns to undergo filter processing |
specify the columns to undergo filter processing by either their column numbers or their column names (string), separated by commas. You can specify up to 10 column numbers (or column names). |
-- |
Input |
O |
|
csv.filterColumnString1 |
Filter condition 1 for column data |
Specify the regular expression of the filter to be applied to the first column specified in csv.filterColumnList. |
-- |
Input |
O |
|
csv.filterColumnString2 |
Filter condition 2 for column data |
Specify the regular expression of the filter to be applied to the second column specified in csv.filterColumnList. |
-- |
Input |
O |
|
csv.filterColumnString3 |
Filter condition 3 for column data |
Specify the regular expression of the filter to be applied to the third column specified in csv.filterColumnList. |
-- |
Input |
O |
|
csv.filterColumnString4 |
Filter condition 4 for column data |
Specify the regular expression of the filter to be applied to the fourth column specified in csv.filterColumnList. |
-- |
Input |
O |
|
csv.filterColumnString5 |
Filter condition 5 for column data |
Specify the regular expression of the filter to be applied to the fifth column specified in csv.filterColumnList. |
-- |
Input |
O |
|
csv.filterColumnString6 |
Filter condition 6 for column data |
Specify the regular expression of the filter to be applied to the sixth column specified in csv.filterColumnList. |
-- |
Input |
O |
|
csv.filterColumnString7 |
Filter condition 7 for column data |
Specify the regular expression of the filter to be applied to the seventh column specified in csv.filterColumnList. |
-- |
Input |
O |
|
csv.filterColumnString8 |
Filter condition 8 for column data |
Specify the regular expression of the filter to be applied to the eighth column specified in csv.filterColumnList. |
-- |
Input |
O |
|
csv.filterColumnString9 |
Filter condition 9 for column data |
Specify the regular expression of the filter to be applied to the ninth column specified in csv.filterColumnList. |
-- |
Input |
O |
|
csv.filterColumnString10 |
Filter condition 10 for column data |
Specify the regular expression of the filter to be applied to the tenth column specified in csv.filterColumnList. |
-- |
Input |
O |
|
common.loopInputProperties |
String for the repeated execution plug-in (serial numbers of output file names) |
The serial number of an output file name can be stored as one or more numerical values separated by commas. You can process each output file by passing this property on to a repeated execution plug-in. |
-- |
Output |
O |
|
common.fileExtension |
File extension |
This stores the output file extension including the initial period (.). |
-- |
Output |
O |
|
common.returnValue |
Return value for the plugin |
The return value of this plugin stored. |
-- |
Output |
O |