Split and Extend Each Comma-separated String to Multiple Rows

Problem description & analysis

We have an Excel file Mydata.xlsx as follows:

RegTypes

Options

001

AFD,PB_P,PB_DL,EGS_D1,EGS_D2

10A

AFD,PB_P,PB_DL,EGS_D1,EGS_D2

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:

RegTypes

Option

001

AFD

001

PB_P

001

PB_DL

001

EGS_D1

001

EGS_D2

10A

AFD

10A

PB_P

10A

PB_DL

10A

EGS_D1

10A

EGS_D2

Solution

We write the following script p1.dfx in esProc:

A

1

=file("Mydata.xlsx").xlsimport@t()

2

=A1.news(Options.split@c();RegTypes,~:Option)

3

=file("result.xlsx").xlsexport@t(A2)

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