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 ${Param1}. 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.