4.10 Merge by column - convert file names to column names

 

Before merging:

Apple.xlsx
imagepng

Bread.xlsx
imagepng

Pork.xlsx
imagepng

After merging:
imagepng

Script:

A
1 =directory@p(“tmp/*.xlsx”)
2 =A1.(filename@n(~))
3 =A1.(T(~))
4 =A3(1).new(Name,Amount:${A2(1)},A3(2)(#).Amount:${A2(2)},A3(3)(#).Amount:${A2(3)})
5 =file(“Amount.xlsx”).xlsexport@t(A4)

A1: List all file names in the directory
A2: Obtain file names without extension
A3: Read files as a table sequence
A4: Convert Amount fields of the original table sequence to corresponding file names while generating a new table sequence