Microsoft Excel Input
Description
Microsoft Access Input is a step in the Input Plugin for Process Studio Workflows. Microsoft Access Input plugin step reads directly from Microsoft Access "MDB" files.
Configurations
| No. | Field Name | Description |
|---|---|---|
| 1 | Step name | Specify the name of the step. This name can me modified from the default name and has to be unique within the workflow. |
| 2 | Spread sheet type (engine) | Specify the spreadsheet type according to the file to be read. - EXCEL 97-2003 XLS (JXL) - EXCEL 2007 XLSX (Apache POI) - EXCEL 2007 XLSX (Apache POI Streaming) - Open Office ODS(ODFDOM) - EXCEL Streamer - EXCEL Binary XLSB - Default value: EXCEL 97-2003 XLS (JXL) Notes:- Excel streamer like Apache POI Streaming is also for reading large XLSX files. It is memory efficient and works across machine configurations. Comparatively, Apache POI is faster in reading files but fails for some lower configuration machines with memory error. - Excel Binary XLSB is provided to read Excel binary files. - The File format is available from Excel 2007 onwards. Advantages: - The Excel workbook sheets can be read and written a bit faster, making them useful for the larger spreadsheets. Notes: - A little extra time is required for fetching headers of the file. - By default, the type of all the columns are defined as String. The developer can change the type from the drop-down in Fields tab. - Some options on Content tab (Stop on empty rows, No empty rows, Encoding) and Error handling tab (Ignore Errors) are disabled. Limitations: - File type as runtime parameter is not supported for Excel Binary files. - Add filenames to result’ checkbox in Parameters Content tab to get Result Filenames is not supported for Excel Binary files. |
| 3 | File or directory | Specify the location or the name of the input file or browse for file. |
| 4 | Button: Add | Click to add files to the selected files group. |
| 5 | Button: Folder | Click to browse and select a folder. |
| 6 | Button: File | Click to browse and select a file. |
| 7 | Regular Expression | Specify a regular expression for selecting files in the directory specified in the previous option. |
| 8 | Exclude Regular Expression | Specify a location to exclude all files that meet criteria specified by the regular expression. |
| 9 | File Password | Specify the password for the file. Note: The field is available only if you select any one of the following options in the Spread sheet type (engine) field: - EXCEL 2007 XLSX (Apache POI) - EXCEL 2007 XLSX (Apache POI Streaming) If the file you are using is already password protected, then ensure that you specify the same password in the field. |
| 10 | Selected files | It contains a tabular list of selected files (or wildcard selections) and a property specifying whether the file is required or not. The tabular list also includes columns for subfolders and file password (which appears encrypted). Notes: - If a file is required and it is not found, and an error is generated, otherwise, the file name is skipped. - If the directory contains password-protected files, all files must have the same password. - The step will fail if any file has a different password in the case of a directory path. |
| 11 | Delete | Click Delete button to delete rows from Selected files data grid. |
| 12 | Edit | Click Edit button to enable fields to be edited in data grid. |
| Accept filename from previous steps: | ||
| 13 | Accept filenames from previous step | Check the checkbox to read file names from the previous step in the workflow. |
| 14 | Step to read filenames from | Specify the step name to read filenames from. |
| 15 | Field in the input to use as filename | Specify the field in the input to use as a filename. |
| 16 | Field in the input to use as password | Select the field that contains the file(s) password. Note: - The file name must be provided by the previous field. - For file(s) that are not password-protected, leave the field value blank. - The field is enabled only if the Spreed sheet type (engine) is Excel 2007 XLSX (Apache POI Streaming) or, Excel 2007 XLSX (Apache POI). |
| 17 | Button: Show filenames | Click to view the file names of the sources connected successfully to the Microsoft Excel Input step. |
| Sheet Tab: | ||
| 1 | Sheet | Specify single or multiple sheet names or index in the excel sheet. Note: You can also specify the sheet name as a variable or field. |
| 2 | Select By | Select either Name or Index if you want to validate the value specified in the Sheet column, that is, either sheet name or sheet index. You will see an error message if the value Select By column is unable to validate the value in the Sheet column. For example, if you provide the name of the sheet in the Sheet column and select Index in the Select By column. |
| 3 | Start row | Specify a start row in the corresponding sheet. Note: You can also specify the start row as a variable or field. |
| 4 | Start column | Specify the start column in the corresponding sheet. Note: You can also specify the start column as a variable or field. |
| 5 | Button: Get Sheet name(s) | Click to retrieves a list of sheet names in the input excel file. |
| Content Tab** | ||
| 1 | Header | If checked, it skips a header row in the sheet. The default value is checked. |
| 2 | No empty rows | If checked, no empty rows are generated in the output sheet. Default value: checked. The default value is checked. |
| 3 | Stop on empty row | Enable checkbox to stop reading the current sheet of a file when an empty line is encountered. |
| 4 | Limit | Specify the Limit on the number of rows. |
| 5 | Encoding | Specify text file encoding to use. |
| 6 | Enable Batching | This checkbox is editable only if the Spreadsheet type engine in the Files tab is Excel Binary (XLSB). Enable this option for reading large XLSB files using batch processing. |
| 7 | Batch Size | This field is enabled only if the Enable Batching checkbox above is checked. Specify the number of rows per batch, for processing. The data type is number. |
| Result filenames: | ||
| 1 | Add filenames to result | Enable checkbox to add filenames to result. Default value: checked |
| Error Handling Tab | ||
| 1 | Strict types? | Enable checkbox to report data type errors in the input. |
| 2 | Ignore errors? | Enable checkbox to ignore errors during parsing. |
| 3 | Skip error lines? | Enable checkbox to it skips lines with errors. |
| 4 | Warning files directory | Specify the location for storing warnings. It can be set by variables using Variable button or you may browse a directory location using the browse button. |
| 5 | Error files directory | Specify the location for storing errors. It can be set by variables using Variable button or you may browse a directory location using the browse button. |
| 6 | Failing line numbers files directory | Specify the location of files with errors on a line number. It can be set by variables using Variable button or you may browse a directory location using the browse button. |
| Fields Tab: | ||
| 1 | Read Based on Column Name | Check if you want to get input based on name of the column. Table data is copied to the Get fields based to column name table when you click Get fields from header row. You can specify the column name as a static value, field, parameter, or global or local PS variable. Specify the column name as Sheetname.ColumnName. For example, if the name of the sheet is Country and the column name is Capital, then define a parameter as Param1 with value Country.Capital and in the Get Fields Based on ColName table specify the parameterized column name as If the value is set in a field, then select the field name in Get Fields Based on ColName table. Note: - If the checkbox is not selected, table data is copied from column-based table to index based table. - If the checkbox is selected, then the Fail on column name not found and Case insensitive comparison fields are available. - The option is also available for XLSB type of file(s). |
| Get fields based on column index | ||
| i. | Name | Specify the name of the field. |
| ii. | Type | Specify the data type of the field. - Number - String - Date - Boolean - Integer - BigNumber - Binary - Timestamp - Internet Address |
| iii. | Length | Specify the maximum length of the field. |
| iv. | Precision | Specify Precision of the field. |
| v. | Trim Type | Truncate spaces for the field by selecting one of the following. - None - Left - Right - Both |
| vi. | Repeat | Specify a Boolean value for repeating field. |
| vii. | Format | Specify Format of the field. |
| viii. | Currency | Specify symbol for Currency. |
| ix. | Decimal | Specify Decimal value. |
| x. | Grouping | Specify grouping of numbers. |
| Get fields based on column name | ||
| 2 | Fail on column name not found | Select the checkbox, if required. If the checkbox is selected, then the workflow will fail if a specified column is not found in the Excel file. Else, the workflow will continue and return null for any missing column. The workflow will also fail, if Ignore Case for Column Name and Alias Names checkbox is clear, and the column names are specified with different case in the Excel file and the step. For example, Username and userName |
| 3 | Ignore Case for Column Name and Alias Names | Select the checkbox, if required. If you select the checkbox then the names of the columns in the Excel file and those specified in the Column Name and Alias Names fields are compared, while the difference in case, if any, is ignored, that is, the workflow will execute even if the column name in the Excel file is, Username and in the step the column name is userName. And, if the checkbox is clear then the workflow will fail if the case of the column names is different. |
| Get fields based on column name: Table | ||
| i. | Column Name | Select the parameterized column name. |
| ii. | Field Name | Specify the name of the field. |
| iii. | Alias Name | Click in the cell and in the List of Alias Names box, specify another name for the field name. |
| iv. | Type | Specify the data type of the field. - Number - String - Date - Boolean - Integer - BigNumber - Binary - Timestamp - Internet Address |
| v. | Length | Specify the maximum length of the field. |
| vi. | Precision | Specify Precision of the field. |
| vii. | Trim type | Truncate spaces for the field by selecting one of the following. - None - Left - Right - Both |
| viii. | Repeat | Specify a Boolean value for repeating field. |
| ix. | Format | Specify Format of the field. |
| x. | Currency | Specify symbol for Currency. |
| xi. | Decimal | Specify the decimal value. |
| xii. | Grouping | Specify grouping of numbers. |
| Get fields from header row | Click to populate the field names from the header row. | |
| Additional Outputs fields Tab | ||
| 1 | Full filename field | Specify Full filename along with the extension. |
| 2 | Sheet name field | Specify worksheet name. |
| 3 | Sheet row nr field | Specify current sheet row number. |
| 4 | Row nr written field | Specify the number of rows written. |
| 5 | Short filename field | Specify a filename without a path. |
| 6 | Extension field | Specify extension of the file. |
| 7 | Path field | Specify the path in operating system format. |
| 8 | Size field | Specify the size of the file. |
| 9 | Is hidden field | Specify whether the file is hidden or not. |
| 10 | Last modification field | Specify field name to contain the latest modified field. |
| 11 | Uri field | Specify a field name that contains URI. |
| 12 | Root uri field | Specify a field name that contains only root part of URI. |