Tutorials: Creating xls2csv streaming task

Overview

xls2csv task is designed to provide insrument to convert xls, xlsm or xlsx files to csv files inside IWS solutions.

xls2csv conversion solution consists of two elements: Java executable jar file which performs actual translation and streaming task adapter which provides interface to interact with jar file and set execution parameters.

Java executable jar file works with Java 6 and later.

Deployment

xls2csv is provided for deployment as workflow MC package which can be installed through MCE and contain task itself and jar file which performs actual translation.

To deploy xls2csv solution on the box open MCE,

1. go to Message Center Streams tab

      2. Press Right Mouse Button in Message Center Streams tab

      3. And choose Import Stream option

    4. Choose xls2csv_dplmt_wrf_main.pkg file in xls2csv_dplmt_wrf folder you want to import. Choose Next after. Agree to rewrite existing files. Agree with appeared Warning.

    5. Press Next twice, wait for data upload to finish and press Create Streams button.

Using adapter task in Solution

To add xls2csv adapter to your solution follow this steps:

1. Drag and drop translator activity on your solution canvas:
   
2. Double-click on new translator task and choose Existing To make your search faster – use search window in upper right corner of a tab.
   
3. Add proper data artifact (if it’s external of inner data artifact of solution) and create connection to xls2csv adapter. Task will use incoming file as source for translation and provide result file(s) as its outgoing tasks.
4. Set launch options if needed. (See Launch options chapter)

Launch options

Translation launch options allows user to modify the way converter performs file translation. Set CustomCMParams in task properties to specify translation properties.

By default outgoing file name will contain source file name without extension in the beginning, outgoing file will contain data from all the sheets contained in source file merged one after another from 1^st sheet to last, outgoing file has comma as delimiter.

Following launch options available (options are case sensitive!):

delimiter

allows to set delimiter that will be used in result.

Note: due to some restrictions user must use comma  parameter value to explicitly set comma (“,”) as delimiter in outgoing file (also comma is default option), pipe if wants to set pipe (“|”) delimiter and tab if wants to set tabulation (“\t”) delimiter. Other delimiter options does not have special translation, feel free to use them as they meant to be but consider escaping special characters appropriate way for workflow syntax.

encoding

Allows to set encoding that will be used in result file(s).

dateFormat

Allows to set specific date format. Date format is MM/dd/yyyy by default.

Converter process date format by Java rules so MM stands for months, dd – for days, yyyy – for years (where mm stands for minutes, DD – days from beginning of the year and YYYY – week year). But task adapter performs proper conversion before passing parameters to jar converter so even if user enters mm-dd-yyyy or YYYYMMDD it will be converted to MM-dd-yyyy and yyyyMMdd correspondingly.

filenameToken

Allows to set token that will be used in result file name.

mergeSheets

mergeSheets flag allows to switch off sheets merge or explicitly specify that sheets must be merged in one file (sheets are merged by default). Use “Y” ot “true” in any case to explicitly enable merge, use any other value to switch merge off.

Note: if merge is disabled every sheet will be translated to separate file with token in its name. In case option sheetsByName or no sheetsBy… was chosen – Sheet name will be used as token. In case if option sheetsByNumber is enabled – Sheet number will be used as token.

password

password option allows to set password which must be used to access protected source files. Ecsape “!” as “u+0021”

saveSourceName

saveSourceName by default is on, if needed to be switched off – use any value but “Y”.

sheetsByName

sheetsByName option allows to choose which sheet(s) from source must be processed. For multiple sheet names use “?” delimiter as follows Sheet1?Sheet2?Sheet3.

If sheet name contain special characters and mergeSheets option is off, consider using option sheetsByNumber to escape any issues regarding creating result file with sheet name token (some special characters are allowed in sheet name but can not be used as part of file name).

Note: if sheet name is specified but sheet with such name does not present in source file – no exception will be thrown.

sheetsByNumber

sheetsByNumber option allows to choose which sheet(s) from source must be processed. For multiple sheet use comma delimiter as follows 2,3,5. Sheets counter starts from 1. Note that if sheet number is specified but no such sheet number presents in source file – no exception will be thrown.

newLineInCell

newLineInCell allows to set character(s) that will be used as replacement for new line and carriage return if they present in cell data.

 “~” is default replacement.

Use “none” if new line and carriage return must be removed completely.