Group Rows and Concatenate Each Value of a Specific Column into a String

Problem description & analysis

We have an Excel file Book1.xlsx. Its data is as follows:

Session

FirstName

LastName

Email

Address

1

Bob

Smith

bob@1.com

Main Street

2

Bob

Smith

bob@1.com

Main Street

4

Bob

Smith

bob@1.com

Main Street

1

Frank

Jones

Frank@2.com

2nd Street

2

Frank

Jones

Frank@2.com

2nd Street

3

Frank

Jones

Frank@2.com

2nd Street

4

Frank

Jones

Frank@2.com

2nd Street

1

Henry

Andrews

henry@3.com

3rd Street

2

Henry

Andrews

henry@3.com

3rd Street

We are trying to group the file by Email and concatenate each Session value into a string by comma. Below is the desired result:

Email

FirstName

LastName

Address

Session

Frank@2.com

Frank

Jones

2nd Street

1,2,3,4

bob@1.com

Bob

Smith

Main Street

1,2,4

henry@3.com

Henry

Andrews

3rd Street

1,2

Solution

Write the following script p1.dfx in esProc:

A

1

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

2

=A1.group(Email;FirstName,LastName,Address,~.(Session).concat@c():Session)

3

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

Explanation:

A1   Import the Excel file as a table sequence.

A2   Group the table sequence by Email, retain all columns except for Session, and convert each Session value into a comma-separated string.

A3   Export A2’s result to result.xlsx.

Q & A Collection

https://stackoverflow.com/questions/63623624/merge-excel-rows-based-on-single-field