Problem description & analysis

We have an Excel file Mydata.xlsx as follows:

RegTypes column contains single values. Options column contains comma-separated strings. Now we are trying to split each Options value to extend it to multiple rows, concatenate each row with the corresponding RegTypes value to generate a two-dimensional table. Below is the expected result:

Solution

We write the following script p1.dfx in esProc:

Explanation:

A1  Import the Excel table as a table sequence.

A2   Loop through each row of A1 to split Options value into a sequence and generate a new row for each member. The new row consists of two columns. The first column is RegTypes and the second column, which is named Option, is the current member (which is represented by ~) of the split sequence.

A3  Export A2’s result to result.xlsx.

Read How to Call an SPL Script in Java to learn about the integration of an SPL script with a Java program.

Q & A Collection

https://stackoverflow.com/questions/61429514/i-need-to-print-the-excel-data-from-two-columns-in-sequeal-fromat-first-column