Tutorial: Creating xls2csv Streaming Task

Overview

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

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

Java executable jar file works with Java 6 and newer.

Deployment

xls2csv is provided for deployment as workflow MC package which can be installed through Message Center Editor and contains the task itself and the jar file which performs actual translation.

To deploy xls2csv solution on the box:

      1. Open MCE and go to Message Center Streams tab

      2. Right-click 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. Click Next. Agree to rewrite existing files. Dismiss the Warning pop-up.

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

Using Adapter Task

To add a xls2csv adapter to your solution, please follow these steps:

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

Launch Options

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

The following launch options are available (options are case sensitive!):

delimiter

Allows to set delimiter that will be used in result.

Note: Due to symbol restrictions the user must use comma parameter value to explicitly set comma (“,”) as a delimiter in the outgoing file, pipe to set pipe (“|”) delimiter or tab to set tabulation (“\t”) delimiter. Other delimiter options do not have any special translation, feel free to use them as they are meant to be, but please avoid using special characters in workflow syntax.

encoding

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

dateFormat

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

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

filenameToken

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

mergeSheets

Allows to switch off sheets merge or explicitly specify that sheets must be merged in one file (sheets are merged by default). Use “Y” or “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 a separate file with the token in its name. In case option sheetsByName or no sheetsBy was chosen, sheet name will be used as token. In case option sheetsByNumber is enabled, sheet number will be used as token.

password

Allows to set password which is used to access protected source files. To translate “!” symbol please use “u+0021”

saveSourceName

saveSourceName by default is enabled, set to "N" to switch off.

sheetsByName

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

If the sheet name contains special characters and mergeSheets option is off, consider using option sheetsByNumber to avoid any issues with 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 is not present in the source file, no exception will be thrown.

sheetsByNumber

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

newLineInCell

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

 “~” is default replacement.

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